Results 1 to 3 of 3

HEX to Decimal

This is a discussion on HEX to Decimal within the Excel Questions forums, part of the Question Forums category; 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 ...

  1. #1
    New Member
    Join Date
    Jun 2009
    Location
    Yorkshire, England
    Posts
    32

    Default HEX to Decimal

    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

  2. #2
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,181

    Default Re: HEX to Decimal

    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!
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  3. #3
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,709

    Default Re: HEX to Decimal

    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

    Code:
    ? 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

    Code:
    ? 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

    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.

    Code:
     
    ? &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

    Code:
    ?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.

    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
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com