Referencing Data and Methods in VBA
Performing actions with macros in VBA
Performing actions with macros in VBA
After creating the macro and declaring the variables, the next step is to actual refer to each variable and use them to manipulate the data within the Excel sheet. Performing an action on a variable or data reference is known as a method.
To do this, you will need to know the different methods you can apply within VBA. The ideal first step is to learn how to refer to specific data cells.
To know which data it needs to manipulate, you will need to tell Excel where to find the data. This is done in the following hierarchy: Workbook (Excel File) > Worksheet > Range or Cell. If you skip levels of the hierarchy, Excel will simply assume that it should look in the currently activate Workbook or Worksheet. You cannot skip the last level. You need to tell Excel a specific range or cell, or tell Excel to look in the active sell. Telling Excel which workbook and worksheet to look under is not necessary, but is valuable when multiple workbooks and worksheets are open.
Selecting ranges and cells works a bit differently as compared to the selecting books and sheets. You can select one or more cells (known as a range), or even multiple ranges. Looking at the code, you may be familiar with it, as the text enclosed in the parenthesis is similar to that used in Excel formulas.
Know that you know how to refer to books, sheets and cells, you need to combine these into one line, separated by a period symbol. For example, to select range A42 to D45 in sheet3 of the currently activate workbook:
This is the reference that Excel needs to find the data you want to work with. However, now you need to tell Excel what to do with the data. This is where you start working with methods.
From here, it gets a little complicated. Each reference has many different actions (known as “methods”) it can take, and certain references have methods that are unavailable to others. However, to keep things simple, lets discuss how to tell Excel to select and highlight the data in the model, how to copy it, and how to store it.
To highlight the data in the model, the proper method to use is “Select” or “Activate”.
Running this macro by pressing F5 will cause Excel to highlight the range A42 to D45.
To copy the data in the model, the method to use is “Copy”.
Running this macro will tell Excel to copy the contents of the range to be pasted later. If you run this macro, you’ll notice that Excel will highlight the range with the “running ants” that you normally see when you copy data.
To access the value in a Cell, you use the “Value” method. Using value, you can store the value in a cell within a variable, or copy the exact value of that cell into another cell. Note that the value command does not work with ranges, and will only work with singular cells.
Macros will always read from left to right. This means that Cell A45 will now contain the value within Cell A42 after the macro is run.
To store the value of a cell in a variable, you use the “Value” method, but you must also tell Excel which variable to store it under. This action won’t work for a range, as Excel can only store one piece of data within each Variable.
Dim myVariable as String
myVariable = ActiveWorkbook.Sheets(“sheet3”).Range(“A42”).Value
If, for example, range A42 contained the phrase “Corporate Finance Institute”, it would be now stored under the myVariable string variable. You can use this method to store values for later use. This action is also useful when you need to use the same piece of data multiple times, so you don’t have to type the long reference each time. Instead of typing the workbook, sheet and range, you can simply use the variable you stored it under.