Excel graph dynamic data range

Posted on December 24, 2014January 3, 2016 by Wyman W

This Excel tutorial explains how to set Excel graph dynamic data range. You may also want to read: Excel dynamic Data Validation list Excel Dynamic Print Area Excel dynamic data range Excel graph dynamic data range The purpose of Excel graph dynamic data range is to select data range based on how many data you have input instead of using a fixed range. Assume that you have the data set below. You can draw a graph from January to October easily by selecting A1 to B11, bu...

Excel OFFSET Function for worksheet

Posted on December 23, 2014January 9, 2015 by Wyman W

What is Excel OFFSET Function? Excel OFFSET Function returns the Range object which is number of rows and columns away from a specific range. You can specify to shift up, down, left or right from a cell. For example, if column A contains employee ID, and column B contains employee name, you can find the employee name in B2 by shifting A2 to the right. (shift 1 column to the right and shift 0 row). The advantage of using OFFSET property is that even when the actual column change, you don't ...

Excel VBA OFFSET Property for Range

Posted on December 23, 2014February 26, 2017 by Wyman W

This Excel VBA tutorial explains how to use Excel VBA OFFSET property, and explain difference between VBA OFFSET Property and worksheet OFFSET Function. Excel VBA OFFSET Property Excel VBA OFFSET property returns the Range object which is number of rows and columns away from a specific range. You can specify to shift up, down, left or right from a cell. For example, if column A contains employee ID, and column B contains employee name, you can find the employee name in B2 by shifting A2 to ...

Excel VBA run hyperlink

Posted on December 18, 2014February 25, 2015 by Wyman W

Solution to trigger hyperlink Use FollowHyperlink function to go to a website. Public Sub hyper() ThisWorkbook.FollowHyperlink Range("C6").Value End Sub For details of the FollowHyperlink, check out the below article Excel VBA function FollowHyperlink

Excel VBA Check cell value and cell value above

Posted on December 17, 2014March 4, 2015 by Wyman W

This tutorial provides a solution to check the Excel cell value and the cell value above in Excel worksheet using VBA. Check cell value and cell value above This was originally a question posted in Microsoft Community. I extract it to illustrate an example how to check Excel cell value and cell value above. Question Given a range of 12 cells in a row filled with only 0's and 1's... Would anyone know a way to return a true or false in a cell if looking in all the above cells, a 0 appeare...

Excel DATEDIF Function to calculate date difference

Posted on December 8, 2014July 7, 2017 by Wyman W

This tutorial explains how to use Excel DATEDIF Function to calculate date difference (difference between two dates) in complete year, month or day. You may also want to read: Access Excel VBA day month year difference between two dates Excel DATEDIF Function DATEDIF function stands for "Date Difference". It calculates the difference between two dates in complete year, month or day. Note that DATEDIF Function returns the "complete" value, for example, if the year difference between two d...

Excel VBA For Loop / For…Next

Posted on December 7, 2014September 19, 2017 by Wyman W

This tutorial explains how to use For Loop (For...Next Loop) to perform iteration in Excel VBA. What is Loop? Loop is repeating a set of actions. When you listen to a song repeatedly, it is said you are looping the song. In VBA, there are different kinds of loop methods, they mainly differ in the way that how the loop is triggered and ended. What is For Loop? (For...Next Loop) For Loop (For...Next Loop) is the most frequently used loop, using For to begin loop, Next to end loop, Exit Fo...

Change font color and format in Visual Basics Editor VBE

Posted on December 6, 2014March 2, 2015 by Wyman W

This tutorial explains how to change font color and formats in Visual Basics Editor (VBE) to make your VBA code more readable. This article is suitable for Excel and Access. Change the VBE font color and format, why? Depending on the color and brightness setting of your monitor, the default font color in VBE (Visual Basic Editor) is not always easy to read. It is possible to change the VBE font color and format, for different types of codes, such as comment, syntax error, identifier, etc. ...

Custom VBA Excel Access Networkdays Function

Posted on December 4, 2014March 7, 2015 by Wyman W

This tutorial explains how to create a custom Excel Access Networkdays Function to simulate Excel Networkdays Function, exclude holiday and weekend Why do we need Access Networkdays Function? As of Access 2013 version, there is no Access Networkdays Function available, the function is only available in Excel all versions. In Excel , Networkdays Function is used to calculate the number of "net working days" between two days, excluding Saturday and Sunday, and include both start date and end ...

Run Excel Macro from Access VBA

Posted on December 4, 2014September 16, 2017 by Wyman W

This Access tutorial explains how to run Excel Macro from Access VBA, either write Excel Macro directly in Excel or you can write Excel Macro in Access. You may also want to read: Run Access Macro using RunCode Run Excel Macro from Access VBA One of the uses to run Excel Macro from Access is when you use VBA to export tons of Access reports to Excel. By default, exporting Access report as Excel gives you no formatting but plain text. However, it would be convenient if you can export th...