CODEWITHSUNDEEP
vbaexcelcurrencyregional-settings
Excel VBA Noob
Excel VBA Noob

Reputation: 35

.Value returns only 4 decimal places of a value in currency format

I have the following VBA function to retrieve a currency value from somewhere else with the same column but a different row:

Function GetValue(RefCell As Range) As Double

    Dim i As Integer

    i = Application.Caller.Column - RefCell.Column

    Obtain = RefCell.Offset(0, i).Value

End Function

And here's the setup:

Column1 | Column2
Test 1  | 0.11111
Test 2  | $0.11111

Using GetValue(Test1) gave me $0.11111 with all 5 decimal places. However, GetValue(Test2) yield $0.11110 with only the first 4 decimals. How come the function only returns 4 decimal places if the input is in Currency format as opposed to Number format? I want to retrieve the most accurate number with all the decimal places from cells with Currency format - ie $0.11111 as opposed to $0.11110.

Upvotes: 3

Views: 1586

Answers (1)

user4039065
user4039065

Reputation:

The Range.Value property holds some regional currency idiosyncrasies; one of these could be a 4 digit precision (common for some regional currency variable types).

Use the Range.Value2 property for the raw double value.

GetValue = RefCell.Offset(0, i).Value2

The returned value may have to be manually formatted as currency; any regional information has been stripped.

Upvotes: 2

Related Questions