ActiveCell.Value copies unwanted currency format of source cell

[Bluda]

Hi Experts!
I'm confused by the way ActiveCell.Value works. Here is my problem:

I try to pull the value of a cell from a different worksheet. This
'source-cell' contains the value e.g. "6.6513" formatted as currency with two
decimal places "$6.65".

Source Sheet/Cell:
TestValue = ActiveCell.Value
Debug.Print TestValue (=shows 6.6513)

Destination Sheet/Cell:
Range("I17").Value = TestValue

The value shown in destination cell I17 now shows "$6.65" and its absolute
value is "6.65" the last two decimal places were not put in I17. The same
example with the source-cell being formatted as number with two decimal
places (instead of currency) shows also "6.65" in I17 but its absolute is
"6.6513"????

Does anyone have an explanation for me? I cannot see any logical reason
behind it...

Thank you,
Bluda

 

[JMB]

Try declaring TestValue as a double. I don't know the details, but has
something to do w/Testvalue being a variant (currency subtype) which is the
default data type when you don't declare them.

 

[Bluda]

Thanks JMB.

Sorry for not mentioning this information before but the Variable is
declared as a 'Double'.
I have looked through my entire code and verified that it's not a code
'misinterpretation'. I used the code sample in a different workbook and just
to make sure but I have received the same results. I have absolutely no clue
why this is happening.

 

[JMB]

I cannot duplicate your problem then. When declared as variant, I get your
results. When declared double I get 6.6513 in cell I7 (although only 2
decimals may be displayed). Only other thing I can suggest is to double
check the spelling of your variable. for example:

Dim MyVariable as Double

MyVaraible = Activecell
Range("I7") = MyVaraible

If Option Explicit is not at the top of your module, XL will happily create
MyVaraible (and it will be a variant).

 

[JLGWhiz]

When you use VBA to make a cell equal the value of another cell, that is what
you get, the value. You had the source cell formatted to two decimal points
and that is the value of the cell, although the underlying value is 12 one
hundreths greater, only the two decimal point value will be transferred to
another cell if you use:
Range(x) = Range(y).Value.

 

[Bluda]

Thanks JLGWhiz.

I'm not sure I fully understand your comments. If the value is e.g. "9.1234"
then no matter how you format this number the value "9.1234" should never
change to "9.12".

I just noted the following, I declared my Variables in VBA the way I was
used to declare my Variables in Delphi:
Dim TestValue1, TestValue2 As Double
Apparently the first Variable was not declared as a double? Is that possible?
When changed the variables declaration to:
Dim TestValue1 As Double
Dim TestValue2 As Double
Declaring each variable individually worked now in my example. Could that
have something to do with it???

 

[Bluda]

Thanks JMB.

I just noticed something in my declaration of the variables but I'm not sure
if that was/is the reason. Please see my reply to JLGWhiz.

 

[JMB]

Dim TestValue1, TestValue2 As Double

Testvalue1 is a variant.

JLGWhiz is right when you use Range(x) = Range(y).Value, but I get all of
the decimal places when the value is stored in a variable (if it is declared
as double and not variant) then assigned to another cell, regardless of the
formatting.

 

[JMB]

BTW, to declare both on one line, try:
Dim TestValue1 As Double, TestValue2 As Double

 

[Dave Peterson]

Try:

Dim TestValue as Double
Testvalue = activecell.value2

Check out VBA's help for .Value2. It's particularly useful for dates and
currency.

 

[JMB]

Thanks for pointing that out Dave - never really looked at Value2 enough to
understand its purpose. Although since Testvalue is declared as double, I
think Value2 is not needed. However, probably would be a good idea to use it
to ensure the data is properly entered in case the variable type is ever
modified.

 

[Bluda]

Thank you all for your instructive answers. This solves my problem.

 

[Dave Peterson]

What happened when you tried it?

Thanks for pointing that out Dave - never really looked at Value2 enough to
understand its purpose. Although since Testvalue is declared as double, I
think Value2 is not needed. However, probably would be a good idea to use it
to ensure the data is properly entered in case the variable type is ever
modified.

 

[Bluda]

As soon as I defined/declared my variable correctly as a double it worked as
expected.
Instead of declaring
Dim TestValue1, TestValue2 as Double
I declared
Dim TestValue1 as Double, TestValue2 as Double

However, this makes me not see any difference in the usage of 'value' and
'value2' if thevariables are delcared properly in my example i.e. as Double

Thanks,
Bluda

 

[Dave Peterson]

What was in the activecell and was it formatted as currency?

Option Explicit
Sub testme()

Dim myCell As Range
Dim test1 As Double
Dim test2 As Double

Set myCell = ActiveCell

With myCell
.Clear
.Value = 123.45678901234
.NumberFormat = "$#,##0.00;[Red]$#,##0.00"
test1 = .Value
test2 = .Value2
End With

Debug.Print "Test1=" & test1 & vbLf & "Test2=" & test2

End Sub

Returned this in the immediate window:

Test1=123.4568
Test2=123.45678901234

As soon as I defined/declared my variable correctly as a double it worked as
expected.
Instead of declaring
Dim TestValue1, TestValue2 as Double
I declared
Dim TestValue1 as Double, TestValue2 as Double

However, this makes me not see any difference in the usage of 'value' and
'value2' if thevariables are delcared properly in my example i.e. as Double

Thanks,
Bluda

 

[Bluda]

Got your point, thanks!

What was in the activecell and was it formatted as currency?

Option Explicit
Sub testme()

Dim myCell As Range
Dim test1 As Double
Dim test2 As Double

Set myCell = ActiveCell

With myCell
.Clear
.Value = 123.45678901234
.NumberFormat = "$#,##0.00;[Red]$#,##0.00"
test1 = .Value
test2 = .Value2
End With

Debug.Print "Test1=" & test1 & vbLf & "Test2=" & test2

End Sub

Returned this in the immediate window:

Test1=123.4568
Test2=123.45678901234

As soon as I defined/declared my variable correctly as a double it worked as
expected.
Instead of declaring
Dim TestValue1, TestValue2 as Double
I declared
Dim TestValue1 as Double, TestValue2 as Double

However, this makes me not see any difference in the usage of 'value' and
'value2' if thevariables are delcared properly in my example i.e. as Double

Thanks,
Bluda