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.
Set Variable to a Cell Value in Excel: 3 Quick Examples
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 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")
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. 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.
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"))
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")
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
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
