In this article, I’ll show you how to read a cell value into a variable using VBA in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Quick Methods to Read a Cell Value into a Variable Using Excel VBA
Here we’ve got a data set that contains the names of some customers, their contact numbers, and email addresses in range B3:D13 of a worksheet.
We aim to read cell values from this data set into a variable using VBA.
1. Read the Value of a Single Cell into a Variable Using Excel VBA
First, we’ll learn to read the value of a single cell into a variable.
For example, let’s read the value of cell B4 into a variable called Customer_Name.
It’s better if you know the data type of the value and declare it before. And then read the value in the variable.
For example, here 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")
But it’s okay if you are not sure about the data type. In that case, write only the 2nd line, and VBA will read the value correctly for you.
Value = Range("B4")
Now, to check whether VBA has read the value correctly or not, enter the line MsgBox Value with either of the two sets of codes and then run the code.
A message box will appear showing you the value of cell B4, Boris Pasternak.
2. Read the Values of an Adjacent Range of Cells into a Variable Using Excel VBA
Now, we’ll read the values of an adjacent range of cells into a variable. For example, let’s read the range B3:D13 into the variable Rng.
Here the data type of the variable will be a Range. You can either declare it before 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")
Now, to access the values of the Range object, you have to access them with their row and column numbers with the Cells property.
For example, 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.
3. Read the Values of a Non-Adjacent Range of Cells into a Variable Using Excel VBA
Finally, we’ll read the values of a non-adjacent range of cells into a variable.
For example, 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"))
Now to access cell B4, you again have to enter the line MsgBox Rng.Cells(2,1). Then if we run the code, it’ll display the content in cell D4, [email protected].
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.
For example, to access the range B4 of Sheet1, you have to use:
Value = Worksheets("Sheet1").Range("B4")
Therefore, these are the ways to read one more cell value into a variable using VBA in Excel. Do you have any problems? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.