VBA Round function and pasting result into a cell

[Skiff]

Hi, I've got this problem that I don't uderstand.

I'm calculating values in VBA, and then pasting them into a cell

PLRBG = Round(czLak * (1 - rabat) + 0.00001, 2)
Range("T13").Value = Round(PLRBG, 2)

And I completely don't understand why cell Value is 1,51999998092651 instead of 1,52 ???
Do I really have to make calculations on sheet with Excel functions and then paste just results to cells?
Or is there a way to force two places decimal precision into cell from VBA?

Regards,
Skiff

 

[pgc01]

Hi

Can you post the declaration of the variables so that we can test?

(Also some initial values that you have tested with)

 

[joeu2004]

PLRBG = Round(czLak * (1 - rabat) + 0.00001, 2)
Range("T13").Value = Round(PLRBG, 2)

And I completely don't understand why cell Value is 1,51999998092651 instead of 1,52 ???

You neglect to tell us the values of czLak and rabat, and you do not show us how variables are declared (Dim). That makes it difficult for us to understand the root cause of the problem.

But in this particular case, we make an educated guess.

Your problem has nothing to do with rounding. And rounding the same value twice the same way never improves the result.

Instead, the following probably demonstrates the problem:

Sub testit()
Dim x As Single
x = 1.52
Range("t13") = x
MsgBox Range("t13")
End Sub

The result is indeed about 1.51999998092651.

The problem is the use of type Single. Always use type Double for non-integer arithmetic.

Explanation....

Most decimal fractions cannot be represented exactly in binary floating-point, which is how Excel represents number. Instead, they are represented by an approximation that is a sum of consecutive powers of 2 ("bits") times an exponential factor.

Excel always uses type Double effectively: 64-bit binary floating-point. VBA type Single uses 32-bit binary floating-point. Thus, type Single has less precision, which usually changes the approximation of a decimal fraction.

To demonstrate, suppose type Single has only 4 bits (times an exponential factor) and type Double has 8 bits. The value 0.1 would be represented as follows:

Single:  0.09375     = 1/16 + 1/32 + 0/64 + 0/128
Double:  0.099609375 = 1/16 + 1/32 + 0/64 + 0/128 + 1/256 + 1/512 + 0/1024 + 0/2048

When type Single is stored into type Double (Excel cell), it is simply extended on the right with 0 bits.

Thus, in this demonstration, we would add 0/256 + 0/512 instead of 1/265 + 1/512. Consequently, we retain the (usually) less-precise approximation.

I found on internet that VBA function Round() 1.5 rounds down, so there was advice to add small amout to ensure round up

Bad advice!

First, VBA Round(1.5) rounds to 2, which you can validate yourself. However, VBA Round(2.5) does round down instead of rounding up, as Excel would.

The reason: VBA Round rounds "half to even", whereas Excel ROUND rounds "half up".

Use WorksheetFunction.Round if you want to round like Excel.

 

[Skiff]

Yes - that was the trouble. Thanks joeu2004. dchaney too.

Everything was defined as Single. Stupid VBA tutorials that always tell you to conserve memory. And then results are like this.
Now every cell has two decimal value.

That is the knowledge that is not available at any tutorial on the web.

When type Single is stored into type Double (Excel cell), it is simply extended on the right with 0 bits.

(+0.0001) didn't cause issues.
About that, what is real difference between round(), and WorksheetFunction.Round ? I understand that second comes from Excel itself, and first is VBA function, that exists outside Excel.
But is there any downside/consequence using WorksheetFunction.Round? If there is not I can finally drop clean VBA functions and start using WorksheetFunction since 90% of my VBA coding is done in Excel.

 

[joeu2004]

Everything was defined as Single. Stupid VBA tutorials that always tell you to conserve memory.

Agreed: "stupid"!

Arguably, there is a performance issue, too.

In modern computers, single floating-point and double floating-point arithmetic operations take the same amount of time. The fact is: Intel-compatible CPUs use the same 80-bit floating-point arithmetic in both cases. The difference comes only when the result is stored into memory.

But there might be more "cache misses", resulting in more "real memory" accesses, which are much slower.

Even though that could result in noticeable performance consequences in some algorithm (e.g. billions of calculations), it is not worth the arithmetic inaccuracy, IMHO.

(+0.0001) didn't cause issues.

It certainly can. Adding 0.0001 might cause unintended rounding up.

Arguably, theoretically, it might make some sense to add a relatively infinitesimal small value; for example, 2^-52 for values in the range of 1.52.

The reason is: since 0.005 cannot be represented exactly in binary, we might wonder if 1.515 would be rounded to 1.52, since 1.515 - 1.51 is actually less than 0.005 (about 0.00499999999999989) [3]. (But it is rounded up, as intended!

[3] That is the 64-bit floating-point difference. It might be more than 0.005 in the 80-bit floating-point representation. I do not have tools for determining that.

But the problem is: determining what is "relative infinitesimally small". It varies with the magnitude of the value. It's certainly doable; but it is not worth the effort, IMHO.

About that, what is real difference between round(), and WorksheetFunction.Round ? I understand that second comes from Excel itself, and first is VBA function, that exists outside Excel. But is there any downside/consequence using WorksheetFunction.Round?

As I said before, the "real difference" between the two is: VBA Round rounds "half to even" [1], whereas Excel ROUND rounds "half away from zero" [2].

[1] "Round half to even" is also called banker's rounding, which is a misnomer because most modern banks now use "round half away from zero", in my experience.

[2] I was wrong to write "round half up" before. The difference is with negative numbers: Excel ROUND(-1.525,2) results in -1.53 (away from zero), not -1.52 ("up", meaning: next higher integer).

Some government and industry standards might require the use of "round half to even". On the other hand, some uses with Excel spreadsheet require that we round the same way as Excel ("round away from zero"). And most people expect the latter, IMHO.

Presumably, WorksheetFunction.Round is implemented in machine language, just like VBA Round. In particular, WorksheetFunction.Round does not perform the operation in the Excel thread.

However, WorksheetFunction.Round does take about 70 times longer than VBA Round on my computer (YMMV).

Nevertheless, again, I would make the choice based on which yields the "correct" (intended) result, not based on performance. There is no benefit to getting the "wrong" answer fast, IMHO.

[EDIT] FYI, I "always" use WorksheetFunction.Round, except when I'm too lazy to type the extra characters.

If there is not I can finally drop clean <acronym title="visual basic for applications">VBA</acronym> functions and start using WorksheetFunction since 90% of my <acronym title="visual basic for applications">VBA</acronym> coding is done in Excel.

Oh, I see your point now: Round in Excel v. some other Office(?) application. I never considered that since I cannot imagine being able to use the same macro in both contexts. For example, surely Range(...)=... applies only to Excel and perhaps similar spreadsheet applications.

 

[Skiff]

Many thanks. Now I exactly know what I'm dealing with.
And I won't forget to declare Doubles, and using WorksheetFunction.

BTW: on this forum I also learned that Integers are automatically converted to Long... so everyday we learn something new.
BTW2: 70 times longer in terms of milliseconds doesn't bother me at all

 

[joeu2004]

on this forum I also learned that Integers are automatically converted to Long.

Typo? [1] That is incorrect. That is why the following results in an overflow error, despite the type of x.

Dim x As Long
x = 123 * 267

[1] I often omit the word "not", usually when it's most important. Klunk!

70 times longer in terms of milliseconds doesn't bother me at all

70 times longer in milliseconds might bother me, although that does depend on the magnitude.

We tend to perceive delays that are 300 milliseconds or more.

Fortunately, I was talking about 70 times long in microseconds (on my computer; YMMV).