HEX to Decimal

[Roger Whiteley]

Could somebody explain the following results in the immediate window?

debug.Print clng(&HF060)
-4000
debug.Print clng("&HF060")
61536
debug.Print hex(-4000)
F060
debug.Print hex(61536)
F060

I am obviously missing something about the handling of HEX numbers - it seems strange that two different calls to the HEX function return different results

Thanks

 

[Weaver]

It looks like without the quotes, it assumes the hex value refers to a signed integer, between 32767 (&H7FFF) & -32768 (&H8000) but as a string it's seeing it as an unsigned integer between 0 and 65535.

if the first digit is >7 then it's setting the 4th bit (binary) which is acting as a sign (+ if not set, - if it is)

Not sure why it's doing this however!

 

[pgc01]

Hi

I started writing and I think I wrote too much.

The problem is that you have not specified the type for the constant and that's why the results are confusing.

1 - Constant without explicit type

VBA will convert automatically the constant &HX...X to a different type according to its value, avoiding overflow. The constant is converted to an Int up to 4 hex digits (4 bytes), then to a Long up to 8 hex digits (8 bytes) and to a Double if more than 8 hex digits (in this case using a string in an arithmetic operation, like "&HFFFFFFFFF" + 0).

In your example

? CLng(&HF060)

You have the constant &HF060 that excel evaluates to the Int -4000, that then converts to Long. The value doesn't change when you convert an Int value to Long

? CLng("&HF060")

Here you are converting the string to Long, and so the result is 61536 as you would expect. This is NOT an unsigned value, this is a signed Long value.

If you have an Hex value bigger than &H7FFFFFFF you'll also get negative values, like

? "&HFFFFF060"
? CLng("&HFFFFHF060")

Here you'll get -4000. The conversion function CLng() in this case is redundant because excel will automatically convert the constant to a Long since it has between 5 and 8 hex bytes.

If you wanted to know what the unsigned value of an 8 hex digit bigger than &H7FFFFFFF would be, you could use a Double to print it

? ("&HFFFFF060" & "0") / 16

Notice that when I add a zero the string has now 9 hex digits and so will be converted to a Double. We get the value 4294963296. We know that this is a Double, but this is the value that an unsigned Long would have, if it existed.

2 - Constants with explicit type

You won't have this type of doubts if you make the type you want for the constant explicit. You can do it with a type-declaration character.

? &HF060%
? &HF060&

Now you don't care about what the default conversion rules of vba are. In the first case you are saying you want an Int (signed) and in the second case a Long (signed).

3 - Bigger numbers

Although you can use VBA to convert hex numbers with more than 8 hex digits to a Double, you must remember that a Double has a maximum of 15 significant digits and so you only get the complete value up to &H38D7EA4C67FFF (10^15-1). For bigger numbers you lose digits, like

?CDbl("&HFFFFFFFFFFFFF")

You get 4.5035996273705E+15, losing some digits. In that case although there's no type bigger than Double you can still use the Variant subtype Decimal up to 16 hex digits for a (signed) value with all significant digits.

?CDec("&HFFFFFFFFFFFFF")

Now you get 4503599627370495.

4 - Decimal to Hexadecimal

Unfortunately the VBA Hex() function does not have a parameter to specify the number of digits of the result like its counterpart in the worksheet has.

It will show you just some hex digits which makes it sometimes difficult to understand.

You can force it to 8 digits in the case of a negative number by adding a 0 before it.

? Hex(61536)
? Hex(-4000)
? Hex("-04000")

You'll get F060, F060, FFFFF060.

You can also use the worksheet function to see all the hex numbers with 8 digits, the results are easier to understand, ex.:

'? Right(Application.WorksheetFunction.Dec2Hex(DecNumber, 8), 8)
' Like:
? Right(Application.WorksheetFunction.Dec2Hex(61536, 8), 8)
? Right(Application.WorksheetFunction.Dec2Hex(-4000, 8), 8)

You'll get FFFFF060 and 0000F060 which are clearer results.

Remarks:
- It's a pity that vba doesn't have other unsigned types besides Byte, like Word, DWord and QWord. Also reliable conversions Bin-Oct-Dec-Hex.
- If you work a lot with these, maybe it pays to write your own conversion functions

I tested in the Immediate Window, hence the "?"s.

HTH