Possible VBA AND Operator Anomaly

Question

On my 32-Bit PC, running Office 2003 Excel VBA, the following code

Debug.Print ""
Debug.Print Hex(&HFF00000 And &HF00000)
Debug.Print Hex(&HFF0000 And &HF0000)
Debug.Print Hex(&HFF000 And &HF000)
Debug.Print Hex(&HFF00 And &HF00)
Debug.Print Hex(&HFF0 And &HF0)
Debug.Print Hex(&HFF And &HF)

gives the following Output

F00000
F0000
FF000   '<- Here's the Anomaly 
F00
F0
F

This Only seems to occur with the Hex Value "F"; ie substitute all the "F"'s with any other Hex Digit and the Output will be as expected

Looks like some hangover from 16-Bit processing

My world has been shattered

I'm wondering if;

1) this really is an Anomaly (or am I going mad?)

if it is;

2) does it occur with other Office Versions

3) does it occur on 64-Bit PCs

4) what's the most elegant work-around

Any clues would be appreciated

`Debug.Print &HF000, CLng(&HF000), CLng("&HF000")` gives `-4096 -4096 61440` so seems related to the "bare" `&h` notation... — Tim Williams, Jan 18 '14 at 01:22
For large hex numbers in VBA you need to append `&` (the long type character) to ensure it gets stored correctly as a long. E.g. try this `Debug.Print &HF000, &HF000&` — Tim Williams, Jan 18 '14 at 01:25
@Tim You should post this as an answer. That VBA treats `&H` values as signed, and values <= `&HFFFF` as 16 bit signed integer and quoted values as unsigned are really interesting results. — chris neilsen, Jan 18 '14 at 01:46

score 8 · Accepted Answer · answered Jan 18 '14 at 02:24

8

For large hex numbers in VBA you need to append & (the long type character) to ensure it gets stored correctly as a long.

E.g. try this

Debug.Print &HF000, &HF000&

answered Jan 18 '14 at 02:24

Tim Williams

154,628
8
97
125

Nice one; thank you Tim. Interesting to note that my Excel VBA complier automatically removes the trailing "&" from Hex values greater than &HFFF& eg Debug.Print Hex(&HFF000 And &HF000&) in the above example – mikebinz Jan 18 '14 at 19:47

Possible VBA AND Operator Anomaly

1 Answers1