Glitch with Currency format?

[rpaulson]

Hello all,

I have a problem with the currency format with 4 decimal places.

here is my macro.

Sub go()
Range("C5") = Range("C5") + Range("C4")
End Sub

as you can see it adds the conecnt of "R4" to what is in "R5"

If works fine when the calls are formatted as number with 4 decimal places.
example if R4 is 0.1111 and R5 is 0.2222 after running the macro R5 becomes 0.3333

But if the cells are formatted as currency and 4 decimal places the then excel returns $0.3300

Give it a try and see what you get.
Anyone else have the same problem?

Ross

 

[Mala]

You are right- this indeed looks like a glitch. Looking for a workaround.

 

[DonkeyOte]

You could hold the ans in a Double variable before reapplying:

Sub goe()
Dim ans As Double: ans = Range("C5") + Range("C4"): Range("C5") = ans
End Sub

(note I wouldn't use "go" as a name for a subroutine!)

 

[pgc01]

Hi

Another option:

Sub goe()
Range("C5") = Range("C5").Value2 + Range("C4").Value2
End Sub

 

[cornflakegirl]

PGC - neat! I always wondered what Value2 was for...

 

[Richard Schollar]

PGC - neat! I always wondered what Value2 was for...

I understand that Value2 is the fastest way to assign range values to a variant array (but this doesn't apply if they are string values though)...

 

[cornflakegirl]

Why?

 

[Richard Schollar]

Per Charles Williams' site (DecisionModels.com):

It is faster (15-20%) to use the Range.Value2 property rather than the (default) Range.Value property. The Range.Value property attempts to convert cells formatted as Dates to a variant containg a VBA date type, and cells formatted as currency to a variant containing a VBA Currency type. Range.value2 attempts to convert date and Currency formatted cells into Variants containing Doubles.

which suggests that the speed improvement will only be garnered if the range conatins Date or Currency formatted cells (so no improvements for other types).

 

[cornflakegirl]

Thanks Richard. That makes sense - the help for Range2 said it was the same as Range except that it doesn't use the Currency and Date data types.

 

[pgc01]

Hi

We sometimes use libraries with code written in other languages. We can also access excel from other languages, other than vba.

I'd use Value2 with code written in other languages. Other languages may not have Currency and Date data types. The Double type, however, is usually available. Using Value2 in these cases, makes sure you'll always work with doubles, which are common to both languages. I think that this alone justifies the existence of Value2.