Excel VBA: Set Variable to a Cell Value (3 Practical Examples)

In this article, I’ll show you how you can set a variable to a cell value using VBA in Excel. You’ll learn to set a variable to the value of a single cell and a range of cells.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Quick Examples to Set Variable to a Cell Value in Excel

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 Set Variable to a Cell Value in Excel

We aim to set variables to cell values from this data set using VBA.


1. Set Variable to a Single Cell Value Using Excel VBA

First, we’ll learn to set a variable to a single cell value using VBA.

For example, let’s 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 before. And then set the value in that 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 Set Variable to a Cell Value 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 Set Variable to a Cell Value in Excel


2. Set Values of an Adjacent Range of Cells into a Variable Using Excel VBA

Now, we’ll set 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 a Cells object.

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 Set Variable to a Cell Value in Excel


3. Set Values of a Non-Adjacent Range of Cells into a Variable Using Excel VBA

Finally, we’ll set 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 Set Variable to a Cell Value in Excel

Now to access cell B4, you again have to enter the line MsgBox Rng.Cells(1,1). Then if we run the code, 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.

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 set 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 the 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