How to Read Cell Value into Variable using VBA?

---

You can use the following syntax in VBA to read a cell value into a variable:

Sub ReadCellValueIntoVar()

Dim CellVal As String
CellVal = Range("A1")

MsgBox CellVal

End Sub

This particular macro creates a string variable called CellVal and then reads the value in cell A1 of the currently active sheet into the variable.

Lastly, we use MsgBox to display the value of this variable in a message box.

The following example shows how to use this syntax in practice.

Example: How to Read Cell Value into Variable in VBA

Suppose we have an Excel sheet with a value of 500 in cell A1:

We can create the following macro to read this cell value into a variable and then display the value in a message box:

Sub ReadCellValueIntoVar()

Dim CellVal As String
CellVal = Range("A1")

MsgBox CellVal

End Sub

When we run this macro, we receive the following output:

The macro displays the value of the variable, which contains the value 500 from cell A1.

Note that we can also do more complex calculations using the variable if we’d like.

For example, we can create the following macro to read the value of cell A1 into a variable and then create a message box that displays this value multiplied by 5:

Sub ReadCellValueIntoVar()

Dim CellVal As String
CellVal = Range("A1")

MsgBox CellVal * 5

End Sub

When we run this macro, we receive the following output:

The macro displays the value of the variable multiplied by 5, which turns out to be 500 * 5 = 2,500.

The following tutorials explain how to perform other common operations in VBA: