VBA Value

VBA Value

The journey of VBA starts with knowing how the values get stored in the cells. Therefore, knowing VALUE function under VBA is of more importance. After studying about VBA VALUE property, you will be able to know how the values get stored under VBA. This article explains all the types of VALUE function in VBA like how the value gets stored in cells, hot to set the value, how to insert the value and many more.

How to Use Excel VBA Value Function?

We will learn how to use VBA Value function with few examples in Excel.

Example #1 – Set Cell Value using Range.Value function

Setting of cell/s value using Range. Value function roughly consists of two important steps:

• Setting the range where you want the value to be stored.
• Assigning a value to the range set.

Follow the below steps to set value in Excel VBA:

Step 1: Insert a new module under Visual Basic Editor (VBE).

Step 2: Define a new sub-procedure under the inserted module so that a macro can be stored in it.

Code:

Sub VBA_Value_Ex1()

End Sub

Step 3: Define a new variable as a range which can be used to define a range where you wanted to store the value.

Code:

Sub VBA_Value_Ex1()

Dim setValue_Var As Range

End Sub

Step 4: With the help of the VBA RANGE function, assign the cells in the range to a variable defined with the help of the assignment operator.

Code:

Sub VBA_Value_Ex1()

Dim setValue_Var As Range
Set setValue_Var = ThisWorkbook.Worksheets("Setting_Cell_Value_1").Range("A1")

End Sub

Step 5: Now, use Range.Value property to be able to assign value to the range defined. Type the variable name “setValue_Var” under which the range is defined.

Sub VBA_Value_Ex1()

Dim setValue_Var As Range
Set setValue_Var = ThisWorkbook.Worksheets("Setting_Cell_Value_1").Range("A1")
setValue_Var

End Sub

Step 6: Put a dot (.) after variable to be able to select the IntelliSense list of functions available for this range variable defined.

Code:

Sub VBA_Value_Ex1()

Dim setValue_Var As Range
Set setValue_Var = ThisWorkbook.Worksheets("Setting_Cell_Value_1").Range("A1")
setValue_Var.

End Sub

Step 7: Drag down through the list and select “Value” function to store the value under this range variable.

Code:

Sub VBA_Value_Ex1()

Dim setValue_Var As Range
Set setValue_Var = ThisWorkbook.Worksheets("Setting_Cell_Value_1").Range("A1")
setValue_Var.Value

End Sub

Step 8: Assign “Welcome to the world of VBA!” as value to this range using assignment operator.

Code:

Sub VBA_Value_Ex1()

Dim setValue_Var As Range
Set setValue_Var = ThisWorkbook.Worksheets("Setting_Cell_Value_1").Range("A1")
setValue_Var.Value = "Welcome to the world of VBA!"

End Sub

Step 9: We are done with the coding part. Run this code by hitting F5 or Run button under VBE and see the output.

You also can assign the value to a range of cells. All you have to do is while setting the range for the output you will give one-dimensional array instead of a single cell reference.

Step 10: In the same code above, change the Range from “A1” to “A1: A5”.

Code:

Sub VBA_Value_Ex1()

Dim setValue_Var As Range
Set setValue_Var = ThisWorkbook.Worksheets("Setting_Cell_Value_1").Range("A1:A5")
setValue_Var.Value = "Welcome to the world of VBA!"

End Sub

Step 11: Hit F5 or Run button and see the output.

Example #2 – Set Cell Value using VBA Cells Property

This is another method for setting the cell value under VBA. Unlike the RANGE function, cells do not even need them. Value operator to be able to set value for the specific cell/s.

Follow the below steps to set the cell value in Excel VBA:

Step 1: Insert a new module under VBE where you can start writing your new piece of code.

Step 2: Add new sub-procedure under module inserted, where you can store your macro code in.

Code:

Sub VBA_Value_Ex2()

End Sub

Step 3: Start typing thisWorkbook.Worksheet to access the sheet named “Setting_Cell_Value_2”.

Code:

Sub VBA_Value_Ex2 ()

ThisWorkbook.worksheets

End Sub

Step 4: Mention the sheet name under parentheses in double quotes.

Code:

Sub VBA_Value_Ex2()

ThisWorkbook.Worksheets ("Setting_Cell_Value_2")

End Sub

Step 5: Use .Cells property to set the cell range from the given excel sheet.

Code:

Sub VBA_Value_Ex2()

ThisWorkbook.Worksheets("Setting_Cell_Value_2").Cells(1, 1)

End Sub

The first argument of the Cells property represents the rows and the second argument represents the column. Therefore, Cells(1, 1) means cell associated with the first row and first column of the sheet “Setting_Cell_Value_2”.

Step 6: Now, with the help of the assignment operator, assign a value to this cell set.

Code:

Sub VBA_Value_Ex2()

ThisWorkbook.Worksheets("Setting_Cell_Value_2").Cells(1, 1) = "VBA is Flexible."

End Sub

Have a look that we don’t need .Value function which we need to use while setting the value using Range function. Cells Method does not have such IntelliSense options.

Step 7: Hit F5 or Run button to run this code and see the output. You will see an output as shown in the screenshot below.

Example #3 – Get Cell Value in VBA

Until now, in two examples we have seen how to set value for a cell or range of cells. Now suppose a case is exactly reverse, we have to get the value assigned to a particular cell of an excel sheet. How can we get that? Let’s go through a step by step guide.

Suppose I have assigned a value as shown in the screenshot below under the first cell of column A in sheet “Getting_Cell_Value”.

Follow the below steps to get the cell value in Excel VBA:

Step 1: Insert a new module under VBE.

Step 2: Add a sub-procedure so that a macro can be stored as a code.

Code:

Sub VBA_Value_Ex3()

End Sub

Step 3: Define a new variable called Get_Value as a Variant using VBA Dim function.

Code:

Sub VBA_Value_Ex3()

Dim Get_Value As Variant

End Sub

Now, you have to assign the value present in cell A1 to the variable defined above.

Step 4: Type “Get_Value = ThisWorkbook.Worksheets”. This will allow the VBA compiler to access the excel sheet where your value is stored.

Code:

Sub VBA_Value_Ex3()

Dim Get_Value As Variant
Get_Value = ThisWorkbook.Worksheets

End Sub

Step 5: Mention the worksheet name under parentheses with quotations in which value is stored.

Code:

Sub VBA_Value_Ex3()

Dim Get_Value As Variant
Get_Value = ThisWorkbook.Worksheets("Getting_Cell_Value")

End Sub

Step 6: Now, use dot (.) Range method to provide the exact cell where value is stored under sheet named “Getting_Cell_Value”.

Code:

Sub VBA_Value_Ex3()

Dim Get_Value As Variant
Get_Value = ThisWorkbook.Worksheets("Getting_Cell_Value").Range("A1")

End Sub

Step 7: Finally, use dot (.) Value method so that the actual value under cell A1 can be accessed by the compiler.

Code:

Sub VBA_Value_Ex3()

Dim Get_Value As Variant
Get_Value = ThisWorkbook.Worksheets("Getting_Cell_Value").Range("A1").Value

End Sub

Step 8: As we have talked about, we need to get the value stored in cell A1. For that, use MsgBox to show the value of variable Get_Value (Which already have accessed the value typed in cell A1).

Code:

Sub VBA_Value_Ex3()

Dim Get_Value As Variant
Get_Value = ThisWorkbook.Worksheets("Getting_Cell_Value").Range("A1").Value
MsgBox Get_Value

End Sub

Step 9: Hit F5 or Run button to run this code, you will get the value present in cell A1 under the message box.

See, this was the value we have mentioned under cell A1 of sheet “Getting_Cell_Value”. Right?

Example #4 – Can we get Multiple Cell Values at a Time?

Let’s assume I have values stored in three different cells A1, A2, A3 as shown in the screenshot below:

Just don’t get confused if you see the text spread along columns A to E. it is just a visible layout (because I have merged the cells ) and the actual value is stored under cell A1, A2, A3 only.

Follow the below steps to use Excel VBA Value:

Step 1: Define a variable as a variant under a new sub-procedure in your VBA module.

Code:

Sub VBA_Value_Ex4()

Dim Get_Value_2 As Variant

End Sub

Step 2: Use Range.Value method to retrieve the value of cells A1: A3 and assign to a new variable defined in the previous step.

Code:

Sub VBA_Value_Ex4()

Dim Get_Value_2 As Variant
Get_Value_2 = ThisWorkbook.Worksheets("Getting_Cell_Value_2").Range("A1:A3").Value

End Sub

Step 3: Finally, use the MsgBox function to display the values stored in cell A1: A3.

Code:

Sub VBA_Value_Ex4()

Dim Get_Value_2 As Variant
Get_Value_2 = ThisWorkbook.Worksheets("Getting_Cell_Value_2").Range("A1:A3").Value
MsgBox Get_Value_2

End Sub

Step 4: Hit F5 or Run button and see the output for this code.

You’ll get a Run-time error’13’: Type Mismatch. The reason behind getting this error is, we are defining a single variable and assigning values of a one-dimensional array (with three rows) to it. Which is logically not possible.

This is from this article. Let’s wrap the things up with some points to be remembered.

Things to Remember

You can’t set multiple cells value while using the VBA CELLS method, because it takes rows and columns as arguments. You can only specify the row number and column number in it.

You also can’t get values stored in multiple cells in a single message box. If you have to retrieve the range of values, you need to write the separate get arguments for each of the value.

Recommended Articles

This has been a guide to VBA Value. Here we have discussed how to use Excel VBA Value Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA On Error
2. VBA Number Format
3. VBA VLOOKUP
4. VBA Function