We will use the sample dataset below to show how to set variables to cell values using VBA.
Example 1 – Set Variable to a Single Cell Value Using Excel VBA
Set the value of cell B4 to a variable called Customer_Name.
It’s better if you know the data type of the value and declare it beforehand, and set the value in that variable.
Cell B4 contains the name, Boris Pasternak. It’s a string-type variable. Therefore, you can use the following 2 lines of codes:
Dim Value As String
Value = Range("B4")
Use only the 2nd line if you don’t know the data type. VBA will read the value correctly for you.
Value = Range("B4")
To check whether VBA has read the value correctly, enter the line MsgBox Value with either of the two sets of codes and run the code.
A message box will appear showing you the value of cell B4, Boris Pasternak.
Example 2 – Set Values of an Adjacent Range of Cells into a Variable Using Excel VBA
Let’s read the range B3:D13 into the variable Rng.
The data type of the variable will be a Range. You can either declare it beforehand like this:
Dim Rng As Range
Set Rng = Range("B3:D13")
Or you can directly assign the values with it.
Set Rng = Range("B3:D13")
To access the values of the Range object, you have to access them with their row and column numbers with a Cells object.
To access cell B4, you have to use Rng.Cells(2,1) [Cell B4 is in the 2nd row and 1st column of the range B3:D13.]
Enter the line MsgBox Rng.Cells(2,1) inside your code and run it. It’ll show Boris Pasternak, the value in cell B4.
Example 3 – Set Values of a Non-Adjacent Range of Cells into a Variable Using Excel VBA
Let’s try to read the range B3:B13 and D3:D13 into a variable called Rng.
It’s again a data type of Range. You can either declare it beforehand or not.
We’ll use the Union method of VBA to join 2 or more non-adjacent ranges of cells into a single range.
Dim Rng As Range
Set Range = Union(Range("B3:B13"), Range("D3:D13"))
To access cell B4, enter the line MsgBox Rng.Cells(1,1). Run the code, and it’ll display the content in cell B4, Boris Pasternak.
Things to Remember
All the methods described above work for the active worksheet only. If you want to access a worksheet that is not active, you have to mention the name of the worksheet before the range.
To access the range B4 of Sheet1, you have to use:
Value = Worksheets("Sheet1").Range("B4")
Download Practice Workbook
Related Articles
- Excel VBA Variable Types
- [Fixed!] Excel VBA: Global Variable Loses Value
- Excel VBA: Declare Global Variable and Assign Value
- Excel VBA Declare Global Variable
