Problem setting cell values in vba

[Legacy 69493]

Hi

Can somebody tell me what I am doing wrong? I use Excel 2002 german version.

I set cell values with code like this:

....cells(4,5).value = 12

In the Excel Sheet the value shows like 12.000000023242
How can I avoid that?

Thanks for your help!

 

[Jon von der Heyden]

Is there nothing else in the code that changes the value? Can we see the rest of your code please?

Thanks
Jon

 

[Legacy 69493]

Hi

Is there nothing else in the code that changes the value? Can we see the rest of your code please?

Thanks
Jon

You're right! I am very new to vba Minimal code is

Private Sub CommandButton1_Click()
   Dim a As Single
   a = 4 * 3.12
   Application.ActiveCell = a  'implicit typecast to double????
End Sub

The single value seems to be typecasted to double that causes the incurracy. In the cell the result is 12,4799995422363. My "solution" for the moment is : Application.ActiveCell = CDbl(CStr(a))

So how to solve it better?
Thx!

 

[erik.van.geit]

Hi,

It seems to me you want to round your value by declaring your variable as Single. "Single" though still allows for commas. You need the variable type "LONG". Check out VBAhelfiles for declaration types...

Dim a As Long

(you could use Integer, but anyway VBA will use the same amount of memory as Long)

kind regards,
Erik

 

[Legacy 69493]

Hi

Hi,

It seems to me you want to round your value by declaring your variable as Single. "Single" though still allows for commas. You need the variable type "LONG". Check out VBAhelfiles for declaration types...

No. my problem is that I have some values in variables of type single. For some reason I need these values in variables of type double. According to microsoft help CDbl(expression) returns a double value from the expression.

See code that illustrates my problem:

   Dim a As Single
   Dim b As Double
   
   a = 4 * 3.12
   b = CDbl(a)

a contains 12,48 as expected
b contains 12,4799995422363

Why???? What's wrong with my code?

thx!

 

[erik.van.geit]

Oh! that problem
This is one of the most bizar examples I've ever seen! They certainly can explain it when you call Microsoft.

see
http://support.microsoft.com/default.aspx?scid=kb;en-us;78113

If you want, send a little PM with the link to this thread - no questions! - to member tusharm. I think he can shed some more light on this.

best regards,
Erik

 

[Legacy 69493]

Hi

Oh! that problem
This is one of the most bizar examples I've ever seen! They certainly can explain it when you call Microsoft.

see
http://support.microsoft.com/default.aspx?scid=kb;en-us;78113

If you want, send a little PM with the link to this thread - no questions! - to member tusharm. I think he can shed some more light on this.

best regards,
Erik

Thx for the link. I too send pm to tusharm.

 

[tusharm]

Unfortunately, I don't have much to add. This isn't the first time I've encountered this kind of a problem and am at a loss as to why it happens.

Assigning the calculation to a double works, assigning it to a single works. But, assigning a single to a double leads to a rounding error. Maybe, if I laid out the number in binary both as a single and a double I'd understand why it happens...

 

[Legacy 69493]

Thx for your comments!