Results 1 to 7 of 7
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Hex to Decimal (VBA/Excel/XP)

    I want to convert Hex values to Decimal in VBA. The builtin function HEX2DEC apparently does not work inside a module. Example change EE to 238
    Alan

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Hex to Decimal (VBA/Excel/XP)

    I noticed that VBA has Hex(), Hex$(), Oct() and Oct$() functions. Seems they forgot about going the other direction. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hex to Decimal (VBA/Excel/XP)

    Say that your hex value is stored in a variable strHex.

    CLng("&H" & strHex)

    will yield the decimal value. So you can create your own Hex2Dec:

    Function Hex2Dec(HexVal As Variant) As Variant
    Hex2Dec = ""
    On Error Resume Next
    Hex2Dec = CLng("&H" & strHex)
    End Function

    This function will return a blank if the input is not a valid hex string. If you'd rather return 0 or Null, change the second line accordingly.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hex to Decimal (VBA/Excel/XP)

    There is probably no need as x = &HFF will assign the value 255 to x. The Bin, Oct & Hex functions just return an appropriate string representation of the value passed to them.

    Andrew

  5. #5
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Boston, Massachusetts, USA
    Posts
    389
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hex to Decimal (VBA/Excel/XP)

    CDec() works as well:
    <pre>?hex(1234)
    4D2
    ?cdec(&H4D2)
    1234
    </pre>

    (From the Immediate window)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hex to Decimal (VBA/Excel/XP)

    It will, but (a) VBA does not support variables of type decimal, so you might as well use CLng, and ([img]/forums/images/smilies/cool.gif[/img] you still have to prefix the value with &H. CDec doesn't recognize a hex string such as 3F by itself.

  7. #7
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Hex to Decimal (VBA/Excel/XP)

    Clng is a great instruction. Some idiot set this machine up without help.
    Alan

Posting Permissions

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