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
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
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
Code:
? "&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
Code:
? ("&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.
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
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.
Code:
? 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.:
Code:
'? 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