[RESOLVED] Odd Round behaviour

[zaza]

Something odd I've noticed in VBA.

If you evaluate Round(16 * 1000/100,1) you will get 160.

If you do Round(16 * 10000/1000,1) you will generate an overflow exception.

If you put a tiny decimal in, though, you will be fine.

Round(16.0001 * 10000/1000,1) = 160


There must be some form of coercion to an integer form with a specific number of bytes if you don't use a decimal, which then trips the overflow.

[Spoo]

Zaza

Interestingly, these also produce an overflow exception

Dim v2 As Long
v2 = Round(CLng(16 * 10000) / 1000, 1)
v2 = Round(CLng(16 * 10000 / 1000), 1)

Spoo

[szlamany]

16.0001 must be "forcing" the calculation to use floating point - which loses precision before you would ever get an overflow error.

[incidentals]

you need to ensure that no part of the evaluation process exceeds any limits of the types currently in place during the expressions exaluation.

multiplying by 100000 is enough to push you to said limit if you wrap the x/x portion in a pair of bracjets you will cause the multiplier to be reduced and aleaviate the problem!

here to help

[Spoo]

you need to ensure that no part of the evaluation process exceeds any limits of the types currently in place during the expressions exaluation.

multiplying by 100000 is enough to push you to said limit if you wrap the x/x portion in a pair of bracjets you will cause the multiplier to be reduced and aleaviate the problem!

here to help

Isn't that what I did in post #2?
How do you explain that the issue persists?

Spoo

[szlamany]

Long in VBA must be really integer - limited to 32767 - right?

That's like old mainframe basic's I used in the 80's.

@spoo - how does this work?

Dim v2 As Long
v2 = 32767
v2 = v2 * 2

Does it blow up on the multiplication line??

[Spoo]

SZ

Hmm.. per MSDN, for both VB6 and VBA ..

CLng .. Long .. -2,147,438,648 to 2,147,843,647

What am I missing?

BTW .. I just tried your snippet in Excel

Dim v2 As Long
v2 = 32767
v2 = v2 * 2     ' << v2 = 65534

It does not blow up on the multiplication line.

Spoo

[szlamany]

Very odd - how about this

Dim v2 As Long
v2 = Round(CLng(16 * 10000), 1)

160,000 is not a very big number...

[Spoo]

SZ

These all fail with an "Overflow" error 6

Dim v2 As Long
v2 = Round(CLng(16 * 10000), 1)
v2 = Round(16 * 10000, 1)
v2 = 16 * 10000

.. but, these all work !

Dim v2 As Long
v2 = 32767
v2 = v2 * 2         ' v2 =      65,534
v2 = v2 * 10000     ' v2 = 655,340,000

Spoo

[szlamany]

Tell me - does this fail?

I believe it will work

Dim v2 As Long
v2 = 0
v2 = Round(v2 + (16 * 10000), 1)

[zaza]

No, that still breaks.

Slightly worryingly, this also breaks with an overflow error:

Public Sub test()

    Dim x As Long
    x = 1600 * 21
    
End Sub

although multiplying by 20.9999999 or 21.0000001 does not, both returning 33600.

[szlamany]

Using something like 20.999999 is forcing floating point - that is obvious.

What is not so clear is why it's using WORD vs LONGWORD internally for math!!

[zaza]

Right, I think I understand what's going on here but not why.

The following will break:

Public Sub test()
    Dim x As Long
    x = 1
    Debug.Print 32767 + 1
End Sub

but this will not:

Public Sub test()
    Dim x As Long
    x = 1
    Debug.Print 32767 + x
End Sub

This will break:

Public Sub test()
    Dim x As Long
    x = 1    
    Debug.Print 32767 + 1 + x   
End Sub

but this will not:

Public Sub test()
    Dim x As Long
    x = 1   
    Debug.Print 32767 + x + 1
End Sub

Clearly there is a bug in the internal calculation process which assigns all results of number-to-number integer calculations to a 16bit integer data type, but which is circumvented when evaluating variables of a different datatype.
And in the last example even operator order is enough to make the difference.

Also interestingly, this fails:

Public Sub test()
    Dim x As Integer
    Dim y As Long
    x = 1    
    y = 32767 + x + 1   
End Sub

So even though y is technically capable of storing the result, it cannot do so because setting x to Integer allows the result of the calculation to be coerced as an Integer again.
CLng does not do anything here, and nor does bracketing.

This seems pretty dodgy to me. Not only does the internal calculation model have a pretty serious limitation, but it also means that the implicit type conversion of VBA6 no longer works under VBA7 (which in some cases is no bad thing) and neither does a direct type conversion attempt.

From doing a bit of poking around, it seems there have been a few changes in this area in the VBA7 upgrade, with the introduction of LongLong etc.

I am using 32bit Office 2010 on 64bit Windows 7 Ultimate, so in principle I would think that operations like this should be fine.


Must be a bug. Where do these things get reported? And does anybody listen?

[zaza]

Also, MSDN claims here that integer data types really no longer exist and that they are all Long behind the scenes, irrespective of how they are declared.
Maybe they meant that they are converting everything to Integer behind the scenes instead!

[techgnome]

it's the 16 * 10000 calculation, it exceeds the integer limit of 32676. It's only a 16-bit number. so the moment it does that, it blows the limits of integer and errors out. It isn't the dim statement at all. That's why szlamany's code fails, but zaza's coerces the numbers into decimal, which is capable of the larger numbers, even if inaccurately. When dealing with mathmatical computations VB looks at the two numbers, decides which is the larger type, and coerces the types to the same larger type and returns that. Because 16 and 10000 are both coercable to integers, that's what it returns.

if you were to clng the 16 AND the 10000, then you shouldn't get the overflow. Or if you dim two longs and set them to 16 & 10000 it should work, or even one long and set it to 16 or 10000, it should be fine.

-tg

[zaza]

Indeed, it is definitely the actual calculation rather than the dimensioning of the variable. What seems odd to me is that this code:

Public Sub test()
    Dim x As Integer
    Dim y As Long
    x = 1    
    y = 32767 + x + 1   
End Sub

will cause an error, even though I have specified that the result of the addition is a Long data type.

You are correct, tg, that using CLng(32767) + x + 1 does work, because the math operation now recognises that it needs to deal with a Long data type internally.

However, I have never before seen this behaviour. Isn't there normally an implicit type conversion in internal math functions to allow you to add 1 to 32767 without either having to specify that one or both components of the calculation need to be treated as Long?

[techgnome]

it looks for the smallest, largest container it needs to hold hte numbers... otherwise it would have to wait until AFTER the calculation to determine what it should be... but then where would it put the result? that's why it scans the line to see what the numbers are, then allocates a memory address capable of holding the numbers, then does the calculation. since it sees two integers, it assumes, more often right than incorrectly, that the results will also be an integer and allocates an integer space in the memory.

It has to do with how things are handled on the stack so that the operations can be handled.

Now.... try this, see what happens (I don't have the ability to check it)... If I remember right... I *think* this will work...

Public Sub test()
    Dim x As Long
    Dim y As Long
    x = 1    
    y = x + 32767 + 1   
End Sub

I maybe wrong, but it seems to me that I remember reading somewhere that when there's no order of operation, that it uses the type of the left most item... since X is now dimmed as Long, it will treat the rest as a long... I think... but I don't remember if that applied only to multplication and division or all math operations.

-tg

[zaza]

It works if there's anything in the operation that is classed as Long. Doesn't matter where it is.

I don't have an older version of Office on this PC but I'm almost certain that this has changed; that historically VBA will automatically increase the required memory allocation and change the datatype accordingly unless you specifically attempt to allocate to a variable that has already been dimensioned to a smaller size.

It definitely doesn't work like this in VB.Net, for example. Admittedly that's compiled, but you can compile a VBA project too.

[szlamany]

Tell me - does this fail?

I believe it will work

Dim v2 As Long
v2 = 0
v2 = Round(v2 + (16 * 10000), 1)

Then why didn't this work for you?

T-SQL I believe is left-to-right in regard to pre-evaluation of a formula to determine data typing.

That MS link for zaza kind of indicated that things have changed in regard to datatyping in recent versions. Anytime things change new bugs (or "features") get introduced.

[zaza]

Yes, what I mean is that in any given operation which gets an evaluation, the default output is Integer unless there is something in the operation to coerce it differently.

So in your example, (16 * 10000) cannot be evaluated even though v2 is Long.
Similarly, CLng(1)+16*10000 also produces an overflow because the multiplication operator takes precedence over addition and hence that operation cannot be evaluated.
However 1+16*CLng(10000) will evaluate.


In tg's example, there are only addition operators so, as they all have equal parity, they are processed left-to-right.
It's also why my example from earlier failed.

Public Sub test()
    Dim x As Long
    x = 1    
    Debug.Print 32767 + 1 + x   
End Sub

[zaza]

Well, I stand corrected.

It does this in Office 2002 as well. I am quite surprised, but I guess it goes to show that it isn't as common an occurrence as I had feared.

Cheers chaps.

[techgnome]

I guess I'm a little surprised too that more people in this thread haven't run into this before. I've dealt with a lot of financial-based systems over the years so this was something I ran to pretty early in my career. I guess it's something more common when $250,000 is chump change.

-tg

[szlamany]

Always used currency datatypes myself - so that's a longword with a 4-digit implied decimal point.

[edit] back when I did vb6 [/edit]