There are specific formulas that can be used in Excel without VBA, but there can be more useful when used in VBA. Such is the case with the INDIRECT formula.
This formula allows us to evaluate the cell reference as a text string and then return the value from that reference.
In the example below, we will show how to use it.
Using INDIRECT Function in VBA
To use the INDIRECT formula in VBA, the first thing we need to do is to open the VBA itself. We do that by clicking ALT + F11 on our keyboard. Once we do that, the new window will be opened. In it, we will go to the Insert tab and choose Module:
Once we have the module created, we will insert our code in the window on the right side:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Sub GettingCellValue() Dim r As Range Dim cell As Variant Dim indirectFormula As String ' Setting the range to the cell reference you want to retrieve the value from Set r = Range("A2") ' Constructing the INDIRECT formula as a string indirectFormula = "=INDIRECT(""" & r.Address & """)" ' Using the Evaluate method to calculate the formula and retrieve the value cell = Evaluate(indirectFormula) ' Display the cell value in a message box MsgBox "The value in " & r.Address & " is: " & cell End Sub |
This is what our formula looks like in the module:
In this formula, we first declare three variables: r as a Range, cell as a Variant, and indirectFormula as a String.
After that, we set our r variable to be equal to cell A1. Our indirectFormula will be the variable in which the INDIRECT formula will be incorporated, and it will reference the address of our range.
The variable cell will be used to store the Evaluate method which will calculate the formula and retrieve the value.
For the last part, we use the MsgBox to show the address of our cell and its value.
In our worksheet, this is what we have:
We will execute our code by clicking F5 on the keyboard while in the module, and the following message will appear:
Although it cannot be seen from this example, the INDIRECT formula is very useful in cases where we need dynamically referenced cells based on certain variables or conditions. It gives us the chance to create cell references dynamically and then return the values from these references.