Excel VBA: Read Cell Value into Variable (3 Suitable Methods)

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.

Data Set to Read a Cell Value into a Variable Using Excel VBA

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")

VBA Code to Read Cell Value into a Variable in Excel

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.

Output to Read a Cell Value into a Variable Using Excel VBA


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")

VBA Code to Read a Cell Value into a Variable Using Excel VBA

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.

Output to Read a Cell Value into a Variable Using Excel VBA


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"))

VBA Code to Read a Cell Value into a Variable Using Excel VBA

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")


Conclusion

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.

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo