Value or Value2

The Value2 property does not recognise (and convert into) the VBA Currency data type or the VBA Date data type.
The difference between these two properties is best explained with a simple example.
Enter the number 100 into the cells "C2", "C3" and "C4" and apply the corresponding formatting.


Using Excel Number Format

Cell "C2" has been formatted using the number format "#,##0.00".
Run the following code and when you get to the Stop statement open the Watches window and the Immediate window.

Call ValueOrValue2("C2") 

Public Sub ValueOrValue2(ByVal sAddress As String)
   Dim vCell_value As Variant
   Dim vCell_value2 As Variant
   vCell_value = Range(sAddress).Value
   Debug.Print vCell_value
   vCell_value2 = Range(sAddress).Value2
   Debug.Print vCell_value2
   Stop
End Sub

Notice that the Type for both these local variables is "Variant/Double".


Using Excel Currency Format

Cell "C3" has been formatted using the currency format "£#,##0.00".
Run the following code and when you get to the Stop statement open the Watches window and the Immediate window.

Call ValueOrValue2("C3") 

Notice that the Type for the "Value" property is "Variant/Currency".
And the Type for the "Value2" property is still "Variant/Double".
When you use the "Value2" property in conjunction with cells formatted as Currency they will be recognised as a Double and not as a Currency.
The VBA Currency data type stores numbers as a fixed point number.
If you have numbers that have more than 4 decimal places then using "Value2" will be more accurate.


Using Excel Date Format

Cell "C4" has been formatted using the date format "dd/mm/yyyy".
Run the following code and when you get to the Stop statement open the Watches window and the Immediate window.

Call ValueOrValue2("C4") 

Notice that the Type for the "Value" property is "Variant/Date". This corresponds to what is displayed in the Immediate window.
And the Type for the "Value2" property is still "Variant/Double".
When you use the "Value2" property in conjunction with cells formatted as Dates they will be recognised as a Double and not as a Date..
The VBA Date data type stores numbers as decimal.
The only time it makes sense to use .Value instead of .Value2 is if you want to detect a date in a cell using the VBA.IsDate() function.

Dim vCell As Variant 
vCell = Range("A1").Value
'This creates a sub type of Date which can be detected by the VBA.IsDate function
If (VBA.IsDate(vCell) = True) Then
End If

If you use Value2 in the above example then a date will be converted to a Double data type which is not recognised by the VBA.IsDate function.
Value has an optional parameter of type xlRangeValueDataType
If you pass in Value(xlRangeValueDataType.xlRangeValueDefault) you will get back an object representing the value of the cell.
Both of these methods will return an array if the Range contains multiple cells.

Range("D3").Value2 = Range("A3").Value2 + Range("C7").Value2 

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext