## AND function overflow (VBA - Excel 2003)

ErikJan
BronzeLounger
Posts: 1310
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### AND function overflow (VBA - Excel 2003)

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
Posts: 12774
Joined: 16 Jan 2010, 15:49
Location: London, Europe

### Re: AND function overflow (VBA - Excel 2003)

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
StuartR

ErikJan
BronzeLounger
Posts: 1310
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: AND function overflow (VBA - Excel 2003)

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
Posts: 79324
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: AND function overflow (VBA - Excel 2003)

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
Best wishes,
Hans

ErikJan
BronzeLounger
Posts: 1310
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: AND function overflow (VBA - Excel 2003)

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
Posts: 79324
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: AND function overflow (VBA - Excel 2003)

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
Best wishes,
Hans

ErikJan
BronzeLounger
Posts: 1310
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: AND function overflow (VBA - Excel 2003)

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
Posts: 12774
Joined: 16 Jan 2010, 15:49
Location: London, Europe

### Re: AND function overflow (VBA - Excel 2003)

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

HansV
Posts: 79324
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: AND function overflow (VBA - Excel 2003)

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.
Best wishes,
Hans

ChrisGreaves
PlutoniumLounger
Posts: 16099
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

### Re: AND function overflow (VBA - Excel 2003)

ErikJan wrote: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.
The most expensive thing a man can own is ignorance.

HansV
Posts: 79324
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: AND function overflow (VBA - Excel 2003)

Here are some character-based flags...
Flag.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

ChrisGreaves
PlutoniumLounger
Posts: 16099
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

### Re: AND function overflow (VBA - Excel 2003)

HansV wrote:Here are some character-based flags...
Up to your usual Standard I see ...
The most expensive thing a man can own is ignorance.

ErikJan
BronzeLounger
Posts: 1310
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: AND function overflow (VBA - Excel 2003)

ChrisGreaves wrote:
ErikJan wrote: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.
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
PlutoniumLounger
Posts: 16099
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

### Re: AND function overflow (VBA - Excel 2003)

ErikJan wrote: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.
The most expensive thing a man can own is ignorance.

ErikJan
BronzeLounger
Posts: 1310
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: AND function overflow (VBA - Excel 2003)

ChrisGreaves wrote:
ErikJan wrote: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
PlutoniumLounger
Posts: 16099
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

### Re: AND function overflow (VBA - Excel 2003)

ErikJan wrote: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
The most expensive thing a man can own is ignorance.