Referencing and Methods in VBA

Performing actions with macros in VBA

Referencing Data 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.

Selecting Files and Worksheets

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.

  • To refer to a workbook: Workbooks(“NameOfFile.xls”).
  • Use the specific name of the file, followed by the extension.
  • To refer to the current workbook the macro is located in: ThisWorkbook
  • To refer to the active workbook: ActiveWorkbook
  • To refer to a worksheet: Worksheets(“Sheet1”) or Sheets(“Sheet1”)
  • Use the name of the sheet.
  • To refer to the active worksheet: ActiveWorksheet

Selecting Ranges or Cells

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.

  • Selecting one cell: Range(“B5”)
  • Selecting one range: Range(“B5:D5”)
  • Selecting multiple ranges and cells: Range(“B5:D5,E10:J50,M99”)
  • Selecting the active cell (whatever is currently selected in the Excel file): ActiveCell
  • Selecting the active selection (multiple active cells): Selection
  • Selecting all cells within a current worksheet: Cells

Putting it all together

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:

ActiveWorkbook.Sheets(“sheet3”).Range(“A42:D45”)

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.

Manipulating Data

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”.

ActiveWorkbook.Sheets(“sheet3”).Range(“A42:D45”).Select

ActiveWorkbook.Sheets(“sheet3”).Range(“A42:D45”).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”.

ActiveWorkbook.Sheets(“sheet3”).Range(“A42:D45”).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.

Range(“A45”).Value = Range(“A42”).Value

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.