Overflow error occurs quicky

[martingaleh]

All I did was type the item above and I got an overflow error. The answer is 60001. Can it really be that excel can handle a number as big as 60001. In fact, it can't handle more than 2^15-1. I'm sure that this is some special programming crap, but it's the year 2018.

It also can hand 1 + 60000 * 1. So if I want to do
x = mid(string,1+30000*2,30000) and there's an overflow, what's the right way to do it? I tried
dim z as integer
x = int(string/30000)
for y = 0 to x-1
somefunction mid(string,1+30000*y,30000)
next y

to pump chunks of a string into a function. Integer should be an enormous number. So when does python take over for vba?

 

[martingaleh]

Oh wait, I have more data now:

https://stackoverflow.com/questions/8955488/vba-overflow-error-with-type-double-excel?rq=1
https://stackoverflow.com/questions/45215514/excel-vba-overflow-error-when-it-shouldnt/45215661

Neither of these solutions work
?cdbl(30000*2)+1
or 1& + 30000*2 both overflow
I can't believe I'm having a problem like this programming in a language that does not involve punch cards

 

[joeu2004]

The problem arises because 30000 and 2 are small enough that VBA treats them as type Integer. And since both operands of multiply (*) are type Integer, VBA does a type-Integer multiplication, which is limited to 32767, the largest type Integer.

Th remedy is to ensure that at least one operand is type Long or Double. Examples:

1 + 30000& * 2
1 + CLng(30000) * 2
1 + 30000 * 2.0 ....which VBA displays as 1 + 30000 * 2#

The latter causes type-Double arithmetic to be performed. In modern computers, that's okay. In the old days, type-Double arithmetic was many times slower than type-Long arithmetic.

1 + 60000*2 works without our doing anything special because VBA must treat 60000 as type Long, since it already exceeds 32767.

----

PS.... When using Dim and Const, "always" use type Long and Double, not Integer and Single.

The only exception might be if you are allocating a humungous array of such values.

Even then, I would not use Single because it causes problems when type Single values are stored into Excel cells (type Double).

It you would have to twist my arm to use type Integer, since most modern computers have sufficient memory and cache to handle megabytes of data.

 

[martingaleh]

I use integer when I know I'm going to use it as an index. For example here, I'm doing this calculation to cut my string into chunks of an array so each chunk is smaller than excel's cell limit. What's really amazing is even if you do:
dim x as Long, it will retype it to integer because it's smarter than you. also, as i've mentioned above 1 + 30000& * 2 may work, but 1& + 30000 * 2 doesn't. How much backward compatibility would excel be risking by removing this "feature"

 

[shg]

even if you do:
dim x as Long, it will retype it to integer

What makes you think that?

 

[joeu2004]

I use integer when I know I'm going to use it as an index.

And I'm suggesting that you use type Long instead.

You are less likely to stumble over problems because assumptions that work in one situation do not apply to another. Bad programming habits are difficult to break.

dim x as Long, it will retype it to integer

Nonsense ! VBA never downgrades type Long to type Integer, unless we do it explicitly or we assign a calculation to a variable of a lesser type.

I'm quite sure that your misunderstanding is rooted in your bad programming habit of using type Integer variables, in the first place.

1 + 30000& * 2 may work, but 1& + 30000 * 2 doesn't. How much backward compatibility would excel be risking by removing this "feature"

It's not a feature to be removed. You simply do not understand how expression evaluation (operator precedence) and data type coercion work.

You mistakenly think that operand type passes (or should pass) from left to right. You're entitled to your opinion. But that is simpy not how it works, in most programming languages as well as VBA.

1& + 30000*2 is evaluated as 1& + (30000*2).

Since both operands of the multiplication are type Integer, type-Integer multiplication is performed.

Then for the addition, the right-hand operand is coerced to type Long because the left-hand operand (1&) is.

 

[martingaleh]

If you can't see the image, here's the link:
https://pasteboard.co/HJDzL9T.png

This makes me think Excel downgrades a long into an integer. I haven't tried in years, but as I recall, even C or Java, which is pretty strongly typed, doesn't need a special character after the number in a multiplication if you've declared the variable properly.

 

[RoryA]

It does not change x to an Integer, since x is never even used when you run the code because the error occurs before that. The issue is that your literal numbers are being treated as Integers, not that the x variable is somehow being converted to an Integer.

 

[joeu2004]

https://pasteboard.co/HJDzL9T.png
This makes me think Excel downgrades a long into an integer.

I already addressed this misunderstanding.

In post #4, you wrote: ``dim x as Long, it will retype it to integer``.

In post #6, I explained: ``You mistakenly think that operand type passes (or should pass) from left to right. [....] that is simp[l]y not how it works``.

The type of the left-hand side of an assignment does not influence the type of the right-hand side (expression).

Moreover, in the right-hand expression, the type of subexpressions on the left does not influence the type of subexpressions on the right, subject to operator precedence.

Again, I already explained that in post #6 with a concrete example, to wit: 1& + 30000*2.

You can continue to ignore the explanation. Or you can recognize that since the explanation matches VBA's behavior, it is probably correct.

as I recall, even C or Java [....] doesn't need a special character after the number in a multiplication if you've declared the variable properly.

Your example deals with constants, not variables. We are talking about the implicit and explicit type of constants and constant expressions per se.

English does not need (nor does it have) a gender-specific article ("the") before nouns. But German requires that we use the correct gender ("der", "die" or "das").

The point is: each language -- natural or programming -- has its own rules. In post#6, I explained VBA's rules as they relate to your example.

So the rules for VBA might indeed be different from C, Java and any other programming language. So what?!

-----

BTW, original C does indeed behave like VBA in this respect, according to my first-edition copy of "The C Programming Language" by Kernighan and Ritchie.

I programmed in C professionally for 20 years. But it has been 20 years since then; I've forgotten the details. "Use it, or lose it." Sigh.

Unless indicated otherwise, subexpressions with just type char, short and int operands are converted to type int for evaluation.

Constants can be treated as type long either by their magnitude (i.e. greater than 32767 or less than -32768) or by appending "L" (e.g. 123L).

IIRC, type long long constants can be indicated by appending "LL" (e.g. 123LL). Type long long was introduced into the language much later.

Also, in C, the type of the left-hand side of an assignment does not influence the type of the right-hand side. For example, if x is type int:

x = 2.4 * 3

results in 7 stored into x, not 6 if 2.4 were converted to type int first (it is not !).

Your question has been asked and answered, as far as I'm concerned. You're "beating a dead horse".

 

[joeu2004]

BTW, original C does indeed behave like VBA [...]. ubexpressions with just type char, short and int operands are converted to type int for evaluation.
Constants can be treated as type long either by their magnitude (i.e. greater than 32767 or less than -32768) or by appending "L" (e.g. 123L).

Oh my, I had a "senior moment". My memory went all the way back to computers where short and int were 16 bits, and long was 32 bits. But on any modern computer that I worked on professionally, int and long were 32 bits. (And on most 64-bit architectures, long is 64 bits.)

So you might not have realized that, technically, C was behaving the same as VBA, because int constants would not overflow expressions like 30000*2.