Excel VBA Value And Value2: Step-by-Step Guide And 8 Examples

In this VBA Tutorial, you learn how to set a cell's or cell range's value and get a cell's or cell range's value.

This VBA Tutorial is accompanied by Excel workbooks containing the macros and data I use in the examples below. You can get immediate access to these example workbooks by subscribing to the Power Spreadsheets Newsletter.

Use the following Table of Contents to navigate to the section that interests you.

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

• General VBA constructs and structures:
  • Begin working with macros here.
  • Learn about basic VBA constructs and structures here.
  • Learn how to enable or disable macros in Excel here.
  • Learn how to work with the Visual Basic Editor here.
  • Learn how to work with Sub procedures here.
  • Learn how to create object references here.
  • Learn several ways to refer to cell ranges here.
  • Learn how to work with properties here.
  • Learn how to declare and assign data to variables here.
  • Learn about VBA data types here.
  • Learn how to work with arrays here.
  • Learn how to work with R1C1-style references here.
  • Learn how to work with loops here.
• Practical VBA applications and macro examples:
  • Learn how to copy and paste values here.
  • Learn how to find the last row in a worksheet here.
  • Learn how to find the last column in a worksheet here.
  • Learn how to specify a column's width here.
  • Learn how to convert strings to numbers here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Set cell value

VBA code to set cell value

To set a cell's value with VBA, use a statement with the following structure:

Cell.ValueOrValue2 = CellValue

Process to set cell value

To set a cell's value with VBA, follow these steps:

1. Identify and return a Range object representing the cell whose value you want to set (Cell).
2. Set the cell's value with the Range.Value or Range.Value2 property (ValueOrValue2 = CellValue).

VBA statement explanation

1. Item: Cell.
   • VBA construct: Range object.
   • Description: Cell is a Range object representing the cell whose value you want to set.For purposes of returning such a Range object, work with constructs such as the Worksheet.Range, Worksheet.Cells, Application.ActiveCell, Application.Selection, Range.Range, Range.Cells, or Range.Offset properties.If you explicitly declare an object variable to represent Cell, use the Range object data type.
2. Item: Value or Value2.
   • VBA construct: Range.Value or Range.Value2 property.
   • Description: Both the Range.Value and Range.Value2 properties set the value of Cell.The difference between Range.Value and Range.Value2 is the data types they work with. Range.Value2 doesn't use Currency nor Date. This difference is particularly important for purposes of getting a cell's value. For a more detailed discussion of this topic, please refer to the appropriate section.
3. Item: =.
   • VBA construct: Assignment operator.
   • Description: The = operator assigns the value returned by the expression on its right (CellValue) to the property on its left (Cell.ValueOrValue2).
4. Item: CellValue.
   • VBA construct: New value of Range.Value or Range.Value2 property.
   • Description: CellValue is the new value you specify for Cell. You can specify, among others, numeric values or text strings.

Macro examples to set cell value

The following macro example sets a cell's (myCellSetValue) value to the string “set cell value with Range.Value” with the Range.Value property.

Sub setCellValue() 'source: https://powerspreadsheets.com/ 'sets a cell's value 'for further information: https://powerspreadsheets.com/excel-vba-value-value2/ 'declare object variable to hold reference to cell where you write the value Dim myCellSetValue As Range 'identify cell where you set the value Set myCellSetValue = ThisWorkbook.Worksheets("set cell value").Range("A7") 'set cell value with Range.Value property myCellSetValue.Value = "set cell value with Range.Value" End Sub

The following macro example sets a cell's (myCellSetValue2) value to the string “set cell value with Range.Value2” with the Range.Value2 property.

Sub setCellValue2() 'source: https://powerspreadsheets.com/ 'sets a cell's value 'for further information: https://powerspreadsheets.com/excel-vba-value-value2/ 'declare object variable to hold reference to cell where you write the value Dim myCellSetValue2 As Range 'identify cell where you set the value Set myCellSetValue2 = ThisWorkbook.Worksheets("set cell value").Range("A11") 'set cell value with Range.Value2 property myCellSetValue2.Value2 = "set cell value with Range.Value2" End Sub

Effects of executing macro example to set cell value

The following GIF illustrates the results of executing the first macro example, which works with the Range.Value property. The value of cell A7 is set to the string “set cell value with Range.Value”.

The following GIF illustrates the results of executing the second macro example, which works with the Range.Value2 property. The value of cell A11 is set to the string “set cell value with Range.Value2”.

#2: Set cell range value

VBA code to set cell range value

To set a cell range's value with VBA, use a statement with the following structure:

CellRange.ValueOrValue2 = CellRangeValue

Process to set cell range value

To set a cell range's value with VBA, follow these steps:

1. Identify and return a Range object representing the cell range whose value you want to set (CellRange).
2. Set the cell range's value with the Range.Value or Range.Value2 property (ValueOrValue2 = CellRangeValue).

VBA statement explanation

1. Item: CellRange.
   • VBA construct: Range object.
   • Description: CellRange is a Range object representing the cell range whose value you want to set.For purposes of returning such a Range object, work with constructs such as the Worksheet.Range, Worksheet.Cells, Application.Selection, Range.Range, Range.Cells, Range.Offset or Range.Resize properties.If you explicitly declare an object variable to represent CellRange, use the Range object data type.
2. Item: ValueOrValue2.
   • VBA construct: Range.Value or Range.Value2 property.
   • Description: Both the Range.Value and Range.Value2 properties set the value of CellRange.The difference between Range.Value and Range.Value2 is the data types they work with. Range.Value2 doesn't use Currency nor Date. This difference is particularly important for purposes of getting a cell range's value. For a more detailed discussion of this topic, please refer to the appropriate section.
3. Item: =.
   • VBA construct: Assignment operator.
   • Description: The = operator assigns the value returned by the expression on its right (CellRangeValue) to the property on its left (CellRange.ValueOrValue2).
4. Item: CellRangeValue.
   • VBA construct: New value of Range.Value or Range.Value2 property.
   • Description: CellRangeValue is the new value you specify for CellRange. You can specify, among others, numeric values or text strings.

Macro examples to set cell range value

The following macro example sets a cell range's (myCellRangeSetValue) value to the string “set cell range value with Range.Value” with the Range.Value property.

Sub setCellRangeValue() 'source: https://powerspreadsheets.com/ 'sets a cell range's value 'for further information: https://powerspreadsheets.com/excel-vba-value-value2/ 'declare object variable to hold reference to cell range where you write the value Dim myCellRangeSetValue As Range 'identify cell range where you set the value Set myCellRangeSetValue = ThisWorkbook.Worksheets("set cell value").Range("A15:C19") 'set cell range value with Range.Value property myCellRangeSetValue.Value = "set cell range value with Range.Value" End Sub

The following macro example sets a cell range's (myCellRangeSetValue2) value to the string “set cell range value with Range.Value2” with the Range.Value2 property.

Sub setCellRangeValue2() 'source: https://powerspreadsheets.com/ 'sets a cell range's value 'for further information: https://powerspreadsheets.com/excel-vba-value-value2/ 'declare object variable to hold reference to cell range where you write the value Dim myCellRangeSetValue2 As Range 'identify cell range where you set the value Set myCellRangeSetValue2 = ThisWorkbook.Worksheets("set cell value").Range("A23:C27") 'set cell range value with Range.Value2 property myCellRangeSetValue2.Value = "set cell range value with Range.Value2" End Sub

Effects of executing macro example to set cell range value

The following GIF illustrates the results of executing the first macro example, which works with the Range.Value property. The value of cells A15 to C19 is set to the string “set cell range value with Range.Value”.

The following GIF illustrates the results of executing the second macro example, which works with the Range.Value2 property. The value of cells A23 to C27 is set to the string “set cell range value with Range.Value2”.

#3: Get cell value

VBA code to get cell value

To get a cell's value with VBA, use a statement with the following structure:

myVariable = Cell.ValueOrValue2

Process to get cell value

To get a cell's value with VBA, follow these steps:

1. Identify and return a Range object representing the cell whose value you want to get (Cell).
2. Get the cell's value with the Range.Value or Range.Value2 property (ValueOrValue2).
3. Assign the value returned by Range.Value or Range.Value to a variable (myVariable =).

VBA statement explanation

1. Item: myVariable.
   • VBA construct: Variable.
   • Description: myVariable is the variable you want to hold Cell's value.If you explicitly declare myVariable, use a data type that's capable of holding all the potential values that Cell may hold.
2. Item: =.
   • VBA construct: Assignment operator.
   • Description: The = operator assigns the value returned by the expression on its right (Cell.ValueOrValue2) to the variable on its left (myVariable).
3. Item: Cell.
   • VBA construct: Range object.
   • Description: Cell is a Range object representing the cell whose value you want to get.For purposes of returning such a Range object, work with constructs such as the Worksheet.Range, Worksheet.Cells, Application.ActiveCell, Application.Selection, Range.Range, Range.Cells, or Range.Offset properties.If you explicitly declare an object variable to represent Cell, use the Range object data type.
4. Item: ValueOrValue2.
   • VBA construct: Range.Value or Range.Value2 property.
   • Description: Both the Range.Value and Range.Value2 properties return the value of Cell.The difference between Range.Value and Range.Value2 is the data types they work with. Range.Value2 doesn't use Currency nor Date. Therefore, if Cell's number format is Date or Currency, Range.Value converts Cell's value to the Date or Currency data type, as appropriate. Range.Value2 doesn't carry out this conversion and, therefore, Range.Value2 generally returns such value as of the Double data type.The Currency data type stores numbers in an integer format scaled by 10,000. this results in a fixed-point number with 4 decimal digits. If Cell's value contains more decimal places, Range.Value and Range.Value2 tend to return different values. Generally, if Cell's value may contain more than 4 decimal places, Range.Value2 is more accurate.

Macro examples to get cell value

The following macro example:

1. Gets a cell's (A7) value with the Range.Value property.
2. Assigns the cell's value to a variable (myValue).
3. Displays a message box with the value held by the variable.

Sub getCellValue() 'source: https://powerspreadsheets.com/ 'gets a cell's value 'for further information: https://powerspreadsheets.com/excel-vba-value-value2/ 'declare variable to hold cell value Dim myValue As Variant 'get cell value with Range.Value property and assign it to variable myValue = ThisWorkbook.Worksheets("get cell value").Range("A7").Value 'display cell value MsgBox myValue End Sub

The following macro example:

1. Gets a cell's (A7) value with the Range.Value2 property.
2. Assigns the cell's value to a variable (myValue2).
3. Displays a message box with the value held by the variable.

Sub getCellValue2() 'source: https://powerspreadsheets.com/ 'gets a cell's value 'for further information: https://powerspreadsheets.com/excel-vba-value-value2/ 'declare variable to hold cell value Dim myValue2 As Variant 'get cell value with Range.Value2 property and assign it to variable myValue2 = ThisWorkbook.Worksheets("get cell value").Range("A7").Value2 'display cell value MsgBox myValue2 End Sub

Effects of executing macro example to get cell value

The following GIF illustrates the results of executing the first macro example, which works with the Range.Value property. The message box displays the value of cell A7.

Notice that cell A7 is formatted as currency. The Range.Value property converts the cell's value to the Currency data type, which results in a fixed-point number with 4 decimal digits. Therefore, the message box displays a value with only 4 decimal places, instead of the 10 decimal places that the original value in cell A7 has.

The following GIF illustrates the results of executing the second macro example, which works with the Range.Value2 property. The message box displays the value of cell A7.

The Range.Value2 property doesn't work with the Currency data type. Therefore, the message box displays all the (10) decimal places that the original value in cell A7 has.

#4: Get cell range value

VBA code to get cell range value

To get a cell range's value with VBA, use a statement with the following structure:

Dim myArray() As Variant myArray = CellRange.ValueOrValue2

Process to get cell range value

To get a cell range's value with VBA, follow these steps:

1. Declare an array of the Variant data type (myArray).
2. Identify and return a Range object representing the cell range whose value you want to get (CellRange).
3. Get the cell range's value with the Range.Value or Range.Value2 property.
4. Assign the value returned by Range.Value or Range.Value to the previously-declared array (myArray =).

VBA statement explanation

Line #1: Dim myArray As Variant

1. Item: Dim myArray() As Variant.
   • VBA construct: Dim statement.
   • Description: The Dim statement declares an array (myArray) as of the Variant data type.myArray is the array you want to hold CellRange's values.

Line #2: myArray = CellRange.ValueOrValue2

1. Item: myArray.
   • VBA construct: Array.
   • Description: myArray is the array you want to hold CellRange's values.
2. Item: =.
   • VBA construct: Assignment operator.
   • Description: The = operator assigns the values returned by the expression on its right (CellRange.ValueOrValue2) to the array on its left (myArray).
3. Item: CellRange.
   • VBA construct: Range object.
   • Description: CellRange is a Range object representing the cell range whose values you want to get.For purposes of returning such a Range object, work with constructs such as the Worksheet.Range, Worksheet.Cells, Application.Selection, Range.Range, Range.Cells, Range.Offset or Range.Resize properties.If you explicitly declare an object variable to represent CellRange, use the Range object data type.
4. Item: ValueOrValue2.
   • VBA construct: Range.Value or Range.Value2 property.
   • Description: Both the Range.Value and Range.Value2 properties return the values in CellRange.The difference between Range.Value and Range.Value2 is the data types they work with. Range.Value2 doesn't use Currency nor Date. Therefore, if CellRange's number format is Date or Currency, Range.Value converts the values in CellRange to the Date or Currency data type, as appropriate. Range.Value2 doesn't carry out this conversion and, therefore, Range.Value2 generally returns such values as of the Double data type.The Currency data type stores numbers in an integer format scaled by 10,000. this results in a fixed-point number with 4 decimal digits. If CellRange's values contain more decimal places, Range.Value and Range.Value2 tend to return different values. Generally, if CellRange's values may contain more than 4 decimal places, Range.Value2 is more accurate.

Macro examples to get cell range value

The following macro example:

1. Gets a cell range's (A11 to C15) values with the Range.Value property.
2. Assigns the cell range's values to an array (myValuesArray).
3. Loops through each value in the array.
4. Displays a message box with each value it loops through.

Sub getCellRangeValues() 'source: https://powerspreadsheets.com/ 'gets a cell range's value 'for further information: https://powerspreadsheets.com/excel-vba-value-value2/ 'declare Variant array to hold cell range values Dim myValuesArray() As Variant 'declare variables to hold loop counters used to iterate through the individual values in the cell range Dim rowCounter As Long Dim columnCounter As Long 'get cell range values with Range.Value property and assign them to array myValuesArray = ThisWorkbook.Worksheets("get cell value").Range("A11:C15").Value 'loop through each value in array, step #1: loop through each value in first array dimension (rows) For rowCounter = LBound(myValuesArray, 1) To UBound(myValuesArray, 1) 'loop through each value in array, step #2: loop through each value in second array dimension (columns) For columnCounter = LBound(myValuesArray, 2) To UBound(myValuesArray, 2) 'display value loop is currently iterating through MsgBox myValuesArray(rowCounter, columnCounter) Next columnCounter Next rowCounter End Sub

The following macro example:

1. Gets a cell range's (A11 to C15) values with the Range.Value2 property.
2. Assigns the cell range's values to an array (myValues2Array).
3. Loops through each value in the array.
4. Displays a message box with each value it loops through.

Sub getCellRangeValues2() 'source: https://powerspreadsheets.com/ 'gets a cell range's value 'for further information: https://powerspreadsheets.com/excel-vba-value-value2/ 'declare Variant array to hold cell range values Dim myValues2Array() As Variant 'declare variables to hold loop counters used to iterate through the individual values in the cell range Dim rowCounter As Long Dim columnCounter As Long 'get cell range values with Range.Value2 property and assign them to array myValues2Array = ThisWorkbook.Worksheets("get cell value").Range("A11:C15").Value2 'loop through each value in array, step #1: loop through each value in first array dimension (rows) For rowCounter = LBound(myValues2Array, 1) To UBound(myValues2Array, 1) 'loop through each value in array, step #2: loop through each value in second array dimension (columns) For columnCounter = LBound(myValues2Array, 2) To UBound(myValues2Array, 2) 'display value loop is currently iterating through MsgBox myValues2Array(rowCounter, columnCounter) Next columnCounter Next rowCounter End Sub

Effects of executing macro example to get cell range value

The following GIF illustrates the results of executing the first macro example, which works with the Range.Value property. The message boxes display the values of cell A11 to C15.

Notice that the cell range is formatted as currency. The Range.Value property converts the cell range's values to the Currency data type, which results in fixed-point numbers with 4 decimal digits. Therefore, the message boxes display values with a maximum of 4 decimal places, instead of the 10 decimal places that the original values in the cell range have.

The following GIF illustrates the results of executing the second macro example, which works with the Range.Value2 property. The message boxes display the values of cell A11 to C15.

The Range.Value2 property doesn't work with the Currency data type. Therefore, the message boxes display all the (10) decimal places that the original values in the cell range have.

References to VBA Constructs Used in this VBA Tutorial

Use the following links to visit the appropriate webpage in the Microsoft Developer Network:

1. Identify the cell or cell range whose value you want to set or get:
   • Workbook object.
   • Application.ThisWorkbook property.
   • Application.ActiveWorkbook property.
   • Application.Workbooks property.
   • Worksheet object.
   • Application.ActiveSheet property.
   • Workbook.Worksheets property.
   • Range object.
   • Worksheet.Range property.
   • Worksheet.Cells property.
   • Application.ActiveCell property.
   • Application.Selection property.
   • Range.Range property.
   • Range.Cells property.
   • Range.Item property.
   • Range.Offset property.
   • Range.Resize property.
2. Set or get the value of a cell or cell range:
   • Range.Value property.
   • Range.Value2 property.
3. Work with variables, arrays and data types:
   • Dim statement.
   • Set statement.
   • = operator.
   • Loop through all the elements in an array:
     • For… Next statement.
     • LBound function.
     • UBound function.
   • Data types:
     • Currency data type.
     • Date data type.
     • Double data type.
     • Long data type.
     • Variant data type.
4. Display a message box:
   • MsgBox function.