VBA Value Property

What is Excel VBA Value Property?

In Excel VBA, the Value property is used to retrieve or set the value of a cell, or a range of cells, in a worksheet. The value can be a number, text, formula, or any other valid data type entered into a cell, including the Excel VBA value property from another sheet.

For example, the following code makes use of the Excel VBA Value property in cell form:

It will print the number 10 in cell “A1.” The Value property is commonly used in Excel VBA to perform calculations, manipulate data, and automate repetitive tasks.

How to use Value property in Excel VBA?

To access the VBA Mod command, write the necessary code to implement it through the following steps.

Step 1: Go to the “Developer” section in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and sub-procedures. Then, click the “Insert” and the “Module” button to create a new module or blank page.

Step 2: Consider an example where we must simultaneously print values across a range. Here, we print the value ‘123’ in cells ranging from B1-C2.

Step 3: Declare a sub-procedure AssignRangeValue(), which will print the values as mentioned above.

Step 4: Declare a variable ws as the datatype Worksheet and declare the sheet number you’re currently working in.

Step 5: Print the values in the respective cells using the VBA Range function.

Step 6: Run the code by pressing F5 or the Run button. It will print the necessary output.

Code:

Sub AssignRangeValue()
Dim ws As Worksheet
Set ws = Worksheets(“Sheet7”)
ws.Range(“B1:C2”).Value = 123
End Sub

Examples

Let us look at some examples to better understand the Excel VBA Value property.

Example #1

Consider an example where you must print a string and some random values in another cell. But first, let us look at how to use the Excel VBA Value property here.

Step 1: Initialize a sub-procedure AssignValues() to print “Hello World” in cell A1 and print random values from cells B1-B3.

Step 2: Declare a variable ‘w’ of the datatype Worksheet and assign it to the sheet you’re working on.

Step 3: Using the Range function, print “Hello World” in cell A1 and some random values in ranges B1-B3 using the rand() function.

Step 4: Run the function by pressing ‘F5’ or the Run button. The output is shown below, where it exhibits Excel VBA value property in a range.

Code:

Sub AssignValues()
Dim w As Worksheet
Set w = Worksheets(“Sheet1”)
w.Range(“A1”).Value = “Hello World”
w.Range(“B1:B3”).Value = “=Rand()”
End Sub

Example #2

Consider an example for the Excel VBA Value Property where you can print a string using the Cell function.

The Cell(r,c) function accepts two integers, the first being the row in the cell and the second being the column.

Step 1: Create a sub-function AssignCells(), which prints the value of cells using the Range function.

Step 2: Define a variable ‘w’ of datatype Worksheet and assign it the sheet name you’re currently working in.

Step 3: Print the required string in the desired row and column of the cell using the Cells function. It is printed in row 4, column 3, or cell C4.

Step 4: When the code is run, it will result in the output below:

Code:

Sub AssignCells()
Dim w As Worksheet
Set w = Worksheets(“Sheet1”)
w.Cells(4, 3).Value = “Sample String”
End Sub

Example #3

Consider an example for the Excel VBA Value Property where we use the Value function to print the value present in different types of cells regardless of its datatype.

Step 1: Declare a function GetValue(), which returns a value present in cells.

Step 2: Define variables ‘ws’ and ‘val’ as the datatype ‘Worksheet’ and ‘Variant’ and initialize it to the current sheet you’re working in.

Step 3: Initialize a random integer in cell “B1” using the Rand excel function and assign the cell value in ‘B1’ to the variable ‘val.’

Step 4: Print the value of ‘val’. Set another value in cell “A1” as “Hello World.”

Step 5: Overwrite the value in variable ‘val’ with the new value in cell A1 and print it.

Step 6: Press ‘F5’ or the Run button to run the code. The outputs are printed in the Immediate tab.

Code:

Sub GetValue()
Dim ws As Worksheet
Dim val As Variant
Set ws = Worksheets(“Sheet1”)
ws.Range(“B1”).Value = “=rand()”
val = ws.Range(“B1”).Value
Debug.Print val; “is the value”
ws.Range(“A1”).Value = “Hello World”
val = ws.Range(“A1”).Value
Debug.Print val; ” is the value”
End Sub

Example #4

Consider an Excel VBA Value Property example where you might get an error when using the Excel VBA invalid property value in case multiple values are declared in one cell.

Step 1: Declare a sub-procedure GetMultipleCellValues() that returns an error if it returns more than one cell value.

Step 2: Declare a variable ‘ws’ as Worksheet and assign the same with the current sheet you’re working in.

Step 3: Declare a variable ‘cellRange’ of the datatype Range and assign it the range from cells B1-C2.

Step 4: Before getting the value of the cell range, the code checks if the cell range contains more than one cell using the Count property of the Cells collection. If it includes more than one cell, the code displays an error message in the Immediate tab and exits the sub-procedure using the Exit Sub statement.

Step 5: If the cell range contains a single cell, the code gets a value for the cell using the Value property with a variable named ‘’cellValue.’ Finally, the code displays the cell’s value in a message box using the MsgBox function. Note that this code can be modified to handle other errors that may occur when getting the value of a cell or range of cells, such as when the cell is empty or contains an error value.

Code:

Sub GetMultipleCellValues()
Dim ws As Worksheet
Set ws = Worksheets(“Sheet7”)
Dim cellRange As Range
Set cellRange = ws.Range(“B1:C2”)
If cellRange.Cells.Count > 1 Then
Debug.Print “Cell range has more than one value. Please specify the cell”
Exit Sub
End If
Dim cellValue As Variant
cellValue = cellRange.Value
End Sub

Step 6: Run the code using ‘F5’ or the Run button. The output is shown below:

Step 7: Assume cells B1-C2 have been assigned the value ‘123’ as shown:

Step 8: Change the cellRange value to ‘B1’ to bypass the error value if more than one cell is called.

Step 9: Run the code to see the output printed in the Immediate tab.

Important Things To Note

• Use the Excel VBA Value Property to retrieve or set the value of a single cell or a range of cells.
• When using the Excel VBA Value Property, choose the Variant data type to handle any value type stored in a cell.
• Use the IsError excel function to check if a cell contains an error value.
• When setting range values, use the Value2 property as it can improve performance.
• Use the Value property sparingly to retrieve or set values of an extensive range of cells, which can be slow and impact performance.
• Don’t forget to use error handling to prevent potential runtime errors when working with the Value property.
• When setting the Excel VBA Value Property to a formula, you can include cell references in excel in the formula by enclosing the cell reference in square brackets, as shown in the following code: 
  • Range(“B1”).Value = “=[A1]*2”

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Excel VBA Value Property, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Excel VBA Value Property. Here we explain how to use Value Property in excel VBA with examples and downloadable template. You can learn more from the following articles –

• VBA Data Type
• VBA InStr
• VBA Application.Match