EDUCBA

Home VBA VBA Resources VBA Conversion Functions VBA Value

VBA Value

Updated June 12, 2023

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 functions in VBA, like how the value gets stored in cells, how to set the value, how to insert the value, and many more.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Use Excel VBA Value Function?

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

You can download this VBA Value Excel Template here – VBA Value Excel Template

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

The setting of cell/s value using the range. Value function roughly consists of two essential 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).

vba value module

Step 2: To store a macro, define a new sub-procedure under the inserted module.

Code:

Sub VBA_Value_Ex1()

End Sub

vba value Example 1

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

Code:

Sub VBA_Value_Ex1()

Dim setValue_Var As Range

End Sub

vba value Example 1.1

Step 4: With the help of the VBA RANGE function, assign the cells in the range to a variable defined using 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

vba value Example 1.2

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

vba value Example 1.3

Step 6: Put a dot (.) after the 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

vba value Example 1.4

Step 7: Drag down the list and select the “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

vba value Example 1.5

Step 8: Assign “Welcome to the world of VBA!” as a value to this range using the 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

vba value Example 1.6

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

vba value 1

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

Step 10: Change the Range from “A1” to “A1: A5” in the same code above.

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

vba value Example 1.7

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

vba value Example 1.8

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.

New Module

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

Code:

Sub VBA_Value_Ex2()

End Sub

vba example 2

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

Code:

Sub VBA_Value_Ex2 ()

ThisWorkbook.worksheets

End Sub

vba example 2.2

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

Code:

Sub VBA_Value_Ex2()

ThisWorkbook.Worksheets ("Setting_Cell_Value_2")

End Sub

vba example 2.1

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

vba example 2.6

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

vba example 2.5

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

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

vba Value 2

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. Suppose a case is precisely 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 the sheet “Getting_Cell_Value.”

which programming language

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

Step 1: Insert a new module under VBE.

module long 3

Step 2: Add a sub-procedure to store a macro as a code.

Code:

Sub VBA_Value_Ex3()

End Sub

Example 2.2

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

Code:

Sub VBA_Value_Ex3()

Dim Get_Value As Variant

End Sub

Example 2.3

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

Example 2.4

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

Code:

Sub VBA_Value_Ex3()

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

End Sub

Example 2.5

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

Example 2.6

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

Code:

Sub VBA_Value_Ex3()

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

End Sub

Example 2.7

Step 8: As we discussed, we need to get the value stored in cell A1. For that, use MsgBox to show the value of the variable Get_Value (Which already has 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

vba value Example 2.8

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

vba value 3 example

We mentioned This value 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, and A3, as shown in the screenshot below:

vba value Example 3.1

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 only stored under cells A1, A2, and A3.

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

Add Variant

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

worksheet 3

Step 3: Finally, use the MsgBox function to display the values stored in cells 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

work sheet cell value

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

vba value example 4

You’ll get a Run-time error’13’: Type Mismatch. The reason for getting this error is that 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 things up with some points to be remembered.

Things to Remember

Using the VBA CELLS method, you can’t set multiple cell values because it takes rows and columns as arguments. You can only specify the row number and column number in it.

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

Recommended Articles

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

  1. VBA On Error
  2. VBA Number Format
  3. Excel VBA Macro
  4. VBA Function