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.