dcsimg
Results 1 to 11 of 11

Thread: [RESOLVED] [EXCEL 2003] =HEX2DEC equivalent

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2008
    Posts
    25

    Resolved [RESOLVED] [EXCEL 2003] =HEX2DEC equivalent

    I've been looking around the internet for the VBA equivalent of the =HEX2DEC function. I've found many answers as this seems to be a common problem people have. However, the problem I'm having is that I'm bringing in the hex value as a cell, and not a string . To my dismay, i've learned that VBA cannot cast values, so this throws a kink in my plans.

    Does anyone here have en example of a workaround for this? any help is appreciated.
    Z

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,409

    Re: [EXCEL 2003] =HEX2DEC equivalent

    VBA can cast values more easily than most languages, and will often do it without even being asked to.

    Getting the value from a Cell shouldn't be a problem, as a cell is an object data type that has several properties - such as .Value and .Text


    If you show us the code you have, we can show you how to correct it.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jun 2008
    Posts
    25

    Re: [EXCEL 2003] =HEX2DEC equivalent

    Heres the conversion code i gathered online

    Code:
    Function Hex2Dec(n1 As String) As Long
        Dim nl1 As Long
        Dim nGVal As Long
        Dim nSteper As Long
        Dim nCount As Long
        Dim x As Long
        Dim nVal As Long
        Dim Stepit As Long
        Dim hVal As String
        
        nl1 = Len(n1)
        nGVal = 0
        nSteper = 16
        nCount = 1
        For x = nl1 To 1 Step -1
           hVal = UCase(Mid$(n1, x, 1))
           Select Case hVal
             Case "A"
               nVal = 10
             Case "B"
               nVal = 11
             Case "C"
               nVal = 12
             Case "D"
               nVal = 13
             Case "E"
               nVal = 14
             Case "F"
               nVal = 15
             Case Else
               nVal = Val(hVal)
           End Select
           Stepit = (nSteper ^ (nCount - 1))
           nGVal = nGVal + nVal * Stepit
           nCount = nCount + 1
        Next x
        Hex2Dec = nGVal
    End Function
    And heres the code that used that function (or should)

    Code:
    temp = Sheets("NXL_RawData").Cells(i + 7, col) 'gets first cell (i.e. D052)
    temp = Hex2Dec(temp) 'converts entire BASE16 number to BASE10
    I then assume that the function returns it as long, and I won't have to do any casting to perform some math on the result
    Z

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,409

    Re: [EXCEL 2003] =HEX2DEC equivalent

    I haven't checked Hex2Dec in detail, but based on the header what you have should be OK - as long as you have declared temp as a String, but it would be better to also specify which property you want, eg:
    Code:
    temp = Sheets("NXL_RawData").Cells(i + 7, col).Value 'gets first cell (i.e. D052)
    I then assume that the function returns it as long, and I won't have to do any casting to perform some math on the result
    That is correct:
    Code:
    Function Hex2Dec(n1 As String) As Long

  5. #5
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: [EXCEL 2003] =HEX2DEC equivalent

    Are you looking for something like this?
    Code:
    Function HexaNum(aNum As Variant) As String
      HexaNum = Hex(aNum)
    End Function
    Variant allows the function to be passed a Long or an Integer ... I don't think Hex cares.

    Actually, "Hex" is the same thing all by itself. I think "Hex" was new in VBA 2003.
    Last edited by Webtest; Jul 22nd, 2008 at 02:10 PM. Reason: Simplification
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,409

    Re: [EXCEL 2003] =HEX2DEC equivalent

    The Hex function goes the other way (Decimal to Hex).. you have however reminded me of a much simpler way to do the Hex2Dec function:
    Code:
    Function Hex2Dec(n1 As String) As Long
      Hex2Dec = CLng("&H" & n1)
    End Function

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jun 2008
    Posts
    25

    Re: [EXCEL 2003] =HEX2DEC equivalent

    ok, that all should work, but i'm having a problem with the other two pieces of code now. for some reason, the Dec2Bin function can't handle anything that goes over 8 bits

    Code:
    Function Dec2Bin(ByVal n As Long) As Long
    Do Until n = 0
        If (n Mod 2) Then Dec2Bin = "1" & Dec2Bin Else Dec2Bin = "0" & Dec2Bin
        n = n \ 2
    Loop
    End Function

    and i'm not even sure what this piece of code is doing. i put in 01111110, and it gave me 15

    Code:
    Function Bin2Dec(sMyBin As Long) As Long
        Dim x As Integer
        Dim iLen As Integer
    
        iLen = Len(sMyBin) - 1
        For x = 0 To iLen
            Bin2Dec = Bin2Dec + _
              Mid(sMyBin, iLen - x + 1, 1) * 2 ^ x
        Next
    End Function

    i didnt write any of this, and i'm not really that good at coding in VBA yet, so i really have no idea how to solve this problem
    Z

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,409

    Re: [EXCEL 2003] =HEX2DEC equivalent

    That is due to use of the wrong data type.. anything other than decimal numbers should be stored in a String rather than a numeric data type (like Integer).

    Both work OK if you make these changes:
    Code:
    Function Dec2Bin(ByVal n As Long) As String
    Code:
    Function Bin2Dec(sMyBin As String) As Long
    ..and of course change the variables that work with them to also be String

  9. #9
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: [EXCEL 2003] =HEX2DEC equivalent

    Do you have an "Option Explicit" line before the first Sub/Function in your module? The functions you show are actually String functions operating on DECIMAL numbers. Even though the "Binary" numbers LOOK Binary, they ARE DECIMAL, and if you do any math operations with them, the results will be DECIMAL and not Binary.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,409

    Re: [EXCEL 2003] =HEX2DEC equivalent

    While "Option Explicit" is a good idea, it wouldn't make any difference here unfortunately - what you seem to be thinking of is "Option Strict", but that is not available in VBA (or VB6).

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Jun 2008
    Posts
    25

    Re: [EXCEL 2003] =HEX2DEC equivalent

    Thanks si, Everything looks like it works now =3
    Z

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width