AND function overflow (VBA - Excel 2003)

[ErikJan]

Somewhere in my VBA code a calculation comes out like this:

2147532800 and 512

I get an overflow; I understand some of it but what can I do about it?

[StuartR]

I have no idea why this happens. I even get an overflow error if I define three variables as LONG or DOUBLE and then try to do this bitwise AND.

What is very annoying is that this calculation is so easy to do in your head!

2147532800 is 8000C000 in hex
512 is 200 in hex
There are no bits that are set to 1 in both of those numbers, so the answer would be 0

[ErikJan]

Yeah I know. Did the same trying to define the vars long. The fact that in Hex this is easy doesn't help VBA ;-)

[HansV]

Since the number 2,147,532,800 is larger than 2^31-1= 2,147,483,647, it is negative as a Long:

&H8000C000 = -2147434496

[ErikJan]

So, then converting to Double should work, right?

Wrong! Try this:

Dim a As Double, b As Double
a = 2147532800#
b = 512
Debug.Print a And b

Now what?

[HansV]

The And operator assumes that both arguments are Boolean or Long; it can't handle floating point numbers such as Single or Double.

You can use

Dim a As Long
Dim b As Long
a = 2147532800# - 2 ^ 32 ' convert to a valid Long value
b = 512
Debug.Print a And b

[ErikJan]

OK but of course 'a' is a variable that gets a value and so is 'b'. So sometimes, 'a' can be small, e.g. 2 and sometimes 'b' can be very large (like 2^32).

In fact what is done is that I set bits in variable 'a' to indicate membership in one or more classes (and recently I added class 32; more may follow in the future). Variable 'b' get's a value and what the AND statement does is find out if 'b' is a member... (by finding out if the class-bit is set). Any better / alternative approach suggestions woudl be appriciated. Maybe I should set up four cases (both smaller 2^32, both larger 2^32 and resp 'a' or 'b' larger 2^32)??

[StuartR]

I think you may have to modify your code to use two or more variables with fewer bits in each variable.

[HansV]

One option is to stop at 30 classes (the largest positive value for a Long is 2^31-1, so you can't even use 2^31), and use a second variable for classes 31, 32 et. (up to 60).
Another option is to use an array of Booleans instead of a number.

[ChrisGreaves]

In fact what is done is that I set bits in variable 'a' ...

I stopped using bits a long time ago. The savings in memory (for end-user applications as distinct from system software) doesn't provide an economic payback when compared to the cost of programming and maintaining the task.
Character-based flags have the advantage of being human-readable.

[HansV]

Here are some character-based flags...

Flag.png

[ChrisGreaves]

Here are some character-based flags...

Up to your usual Standard I see ...

[ErikJan]

In fact what is done is that I set bits in variable 'a' ...

I stopped using bits a long time ago. The savings in memory (for end-user applications as distinct from system software) doesn't provide an economic payback when compared to the cost of programming and maintaining the task.
Character-based flags have the advantage of being human-readable.

Actually, I normally find working with AND easy from a programming point of view, I don't care about memory here. Even now, when it looks as if my problem is becoming complex, and I am thinking about implementing a more 'verbose' coding, it might still look like "000100100100010010010010010" etc. (but as a string this time). Then if I need to check if I'm a member of class 4, I can do something like: MID(Classes,4,1)=1 (where 'Classes' is the "000100..." string). Is that what you meant?

Encoding is more complex maybe as now I'm doing that like this (for row 17, where an 'X' in a cell indicates membership): =SUM((A17:AO17="X")*$A$12:$AO$12)
Where in Row 12 the cells are 1, 2, 4, 8, 16, 32, 64 --of course built by a simple formula as well.

Now in the new coding I would not have a clue yet how I could come up with a formula that could build one string that combines 32+ cells with either an 'X" or not...

Complicating factor might be that for compatibility reasons I might not want to change too much about the current setup other than maybe adding an extra coding field with e.g. "000100100..."

[ChrisGreaves]

Actually, I normally find working with AND easy from a programming point of view, .... Is that what you meant?

Yes, I agree.
I too prefer to think in terms of and/or/not, to the extent of writing cover functions that effect and/or/not on a data type or data structure; the cover functions take care of the fetch-and-store of data while leaving me free to translate a logic table into program code.
I think the point made in this thread is that and/or/not worked thoughtlessly when we all programmed in machine code, then assembly language, and then in some 3rd-level languages such as FORTRAN etc., but as we move away from the bit-oriented machine and towards the (I hate to say it) object-oriented machine, we also move away from the platform on which implementation of and/or/not was established.

[ErikJan]

when we all programmed in machine code, then assembly language, and then in some 3rd-level languages such as FORTRAN etc., but as we move away from the bit-oriented machine and towards the (I hate to say it) object-oriented machine, we also move away from the platform on which implementation of and/or/not was established.

Maybe I'll just develop a wrapper function that can cope with this... I'll think about it.

I too originate from the old days of punch cards, Algol (was it 66?), FORTRAN 77, machine-language like 6502, paper terminals & main frames. I had most fun however with APL... Boy am I getting old...

[ChrisGreaves]

I too originate from the old days of punch cards, Algol (was it 66?), FORTRAN 77, machine-language like 6502, paper terminals & main frames. I had most fun however with APL... Boy am I getting old...

Before any other Lounger jumps in and says it, let me say it:
Don't get Chris started off on that track again