Excel dropping decimal places

[robotman]

I have a weird problem when trying to grab numbers with more than 4
decimal places.

For example if A1 = 123.123456

If I try to debug.print or use Range("A1") in a calculation, it only
shows: 123.1234

If I directly enter a cell on the spreadsheet like make cell B1 equal
to "=A1", the decimal places stay intact on the spreadsheet. I just
can't figure out how to get all the decimal places into a variable so I
can make a calculation with it.

Any ideas why Excel is dropping the extra decimals when I'm trying to
get the value as a Range?!!

Thanks.

John

 

[Ken Puls]

Are you, by any chance, declaring your variable as the currency type?
Try changing it to a double:

Dim val1 As Currency
val1 = Range("A1").Value
Debug.Print val1
~123.1235

Dim val2 As Double
val2 = Range("A1").Value
Debug.Print val2
~123.123456

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[robotman]

No. In fact, I can go to the immediate windows and just:

? range("A1")

and Excel drops the decimals.

It seems like the source cell formatting may be part of the cause. I
have A1 formatted with:
$#,##0;$ "("#,##0")"

If I do a general format, Excel doesn't drop the decimal.

This is a really weird problem. Maybe an Excel bug?!

Why would formatting affect the value Excel returns?

Ideas?

 

[Dave Peterson]

Try
?range("a1").value2

But that's close to the suggestion that Ken proffered.

 

[Ken Puls]

You're dead on, Dave. Laying on robotman's mask caused the issue for
me, but switching to the Value2 cleared it up. Still need to make it a
double, though.

With the numberformat applied:
Dim val1 As Currency
val1 = Range("A1").Value
Debug.Print val1
~123.1235

Dim val2 As Double
val2 = Range("A1").Value
Debug.Print val2
~123.1235

And this time with Value2:
Dim val1 As Currency
val1 = Range("A1").Value2
~123.1235

Dim val2 As Double
val2 = Range("A1").Value2
Debug.Print val2
~123.123456

So, without setting it to a variable first:
? cdbl(range("A1").Value2)

Cheers,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[Peter T]

Why would formatting affect the value Excel returns?

Currency format could indeed limit the number of dp returned to the Value
property. As Dave suggested use Value2 to return the full value.

Assuming a currency cell format try -

MsgBox VarType(ActiveCell.Value) = vbCurrency
MsgBox VarType(ActiveCell.Value2) = vbDouble

Regards,
Peter T

 

[robotman]

I didn't even know there was a Value2. Interesting.

I still don't get why the cell format changes the value that Excel
returns. Isn't the format just a MASK? When directly referencing the
cell (not using any variable), I don't see why a mask changes the
value.

Again.. with no variables involved:

? Range("A1")

You lose dp when A1 is formatted as currency. But you are correct that

? Range("A1").Value2

returns all the dp.

Just trying to understand the relationship between cell format and
returned values.

Thanks for your insight!

 

[Dave Peterson]

Currency and dates have different .value2 from .value

If you look at VBA's help, you'll see a description of when you can expect this
behavior--it's not quite an explanation (to me), but it does describe it.