If you type a number into an unformatted cell in Excel, that number is stored in the Double data type. When you format that number, you show it in a specific way, but you don’t change the number. For instance, if you format the number 1 as a Comma style, you get 1.00. The underlying data is still a 1, you’re just showing it differently. The Date format and the Currency format are two exceptions to this rule.
When you format something as a Date or Currency, you actually change the underlying data type (of the value stored in the Value property). When it comes to the Date format, this is semantics because you can switch between the Date and Double data types without any change to the data. Not so much with the Currency data type. Currency only supports four decimal places, so jamming a Double with, say, five decimals into a Currency data type will result in a different number.
| Number Format | Value | Type | Value2 | Type | Same Number? |
|---|---|---|---|---|---|
| General | 1.12345 | Double | 1.12345 | Double | True |
| m/d/yyyy | 12/31/1899 2:57:46 AM | Date | 1.12345 | Double | True |
| $#,##0.00 | 1.1235 | Currency | 1.12345 | Double | False |
The Value2 property of the Range object ignores the effects of the Date and Currency data types. In the last row, the Value property returns a number rounded to four decimal places and is different than the value that was entered.
I almost never use the Value2 property because I never use the Currency data type. But if you do, you should be aware of this potential problem. Here’s the code behind that table.
Dim sOutput As String
Dim rng As Range
Debug.Print “Number Format”, “Value”, “Type”, “Value2”, “Type”, “Same Number?”
Set rng = ThisWorkbook.Sheets(1).Range(“A1”)
rng.NumberFormat = “General”
rng.Value = 1.12345
Debug.Print rng.NumberFormat, rng.Value, TypeName(rng.Value), rng.Value2, TypeName(rng.Value2), CDbl(rng.Value) = rng.Value2
rng.NumberFormat = “m/d/yyyy”
Debug.Print rng.NumberFormat, rng.Value, TypeName(rng.Value), rng.Value2, TypeName(rng.Value2), CDbl(rng.Value) = rng.Value2
rng.NumberFormat = “$#,##0.00” ‘currency
Debug.Print rng.NumberFormat, rng.Value, TypeName(rng.Value), rng.Value2, TypeName(rng.Value2), CDbl(rng.Value) = rng.Value2
End Sub
If you use .Value with a cell formatted as currency the value will be converted to Currency datatype by VBA.
Of course you probably won’t see this because most of the time you imeditaley do some other VBA operation (like arithmetic) that converts it from currency to Double.
.Value2 is faster than .Value and avoids this under-the-covers currency truncation: there is no good reason to ever use .Value apart from laziness (its the defaulr .Range property)
The .Value property in VBA may give only 4 decimals, but the value used in worksheet calculations still has all the decimals.
Niek Otten
Does anyone know the logic for this?
Having the currency data type behaving differently in the spreadsheet and in VBA, and having 4 decimal places, rather than 2?
Doug,
4 Decimals: maybe because exchange rates (which are prices too) are often in 4 decimals
Niek Otten
Doug,
There is no equivalent to the currency datatype in the spreadsheet, values formatted as currency are still floating point doubles in Excel.
My personal guess is that this was a bug in the original design of Excel VBA, but by the time MS discovered it the bug had already escaped into the wild so that the only way of fixing it without breaking backwards compatibility was by adding .Value2
Charles, are you saying that typename(.Value) returning Currency is not right? Or that when you read the value into VBA it’s caste as Currency, but the calculation engine always uses the floating point number?
Dick,
The Excel Calculation engine always works in floating point. Excel itself only holds internally one type of number (double precision floating point). In Excel’s presentation layer you see the formatted values as Dates, currencies etc, but how you format a value does not affect Excel’s internal underlying value (apart from Precision as Displayed of course).
So personally I think its a bug that var=Range.Value converts Excel’s double to a VBA Currency datatype if the cell is formatted as currency, because the values get truncated and you cannot consistently round-trip a value from Excel to VBA and back to Excel: I cannot see any circumstance where this conversion is a good thing.
Niek,
With data type = Currency, you can really mess up the user’s data. For example
Selection.Value = Selection.Value + 5
The data feeding the calculation (as we now know) gets truncated according to the number of decimals being displayed.
Stuffing this into the default property of the Range object forces the result to be based on the truncated value. Yikes.
I agree with Charles and now know that I HAVE TO start using .Value2
Even if I do not format things as Currency, the user might. Naughty user.
Good post Dick.
Bill