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

1. ## [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.  Reply With Quote

2. ## 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.  Reply With Quote

3. ## 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  Reply With Quote

4. ## 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`  Reply With Quote

5. ## 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.  Reply With Quote

6. ## 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```  Reply With Quote

7. ## 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  Reply With Quote

8. ## 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  Reply With Quote

9. ## 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.  Reply With Quote

10. ## 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).  Reply With Quote

11. ## Re: [EXCEL 2003] =HEX2DEC equivalent

Thanks si, Everything looks like it works now =3  Reply With Quote

#### 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