excel vba large hex string to decimal code example

Example 1: excel vba big hex string to decimal string

'VBA function to convert a hexadecimal string into a decimal. Uses
'the Decimal Variant subtype to allow for much larger values than the
'worksheet function HEX2DEC() or CLng("&h" & HexVal) can achieve. It 
'returns an UNSIGNED Decimal integer...


Function HexToDec(hexStr$)
    Dim c&, i&, bitVal
    Static hexVals&(), b() As Byte
    
    If (Not Not hexVals) = 0 Then
        ReDim hexVals(48 To 102)
        For i = 48 To 57
            hexVals(i) = c
            c = c + 1
        Next
        For i = 65 To 70
            hexVals(i) = c
            c = c + 1
        Next
        c = 10
        For i = 97 To 102
            hexVals(i) = c
            c = c + 1
        Next
    End If
    
    bitVal = CDec(1)
    HexToDec = CDec(0)
    b = StrConv(hexStr, vbFromUnicode)
    For i = UBound(b) To 0 Step -1
        HexToDec = HexToDec + hexVals(b(i)) * bitVal
        bitVal = bitVal * 16&
    Next
End Function



'-------------------------------------------------------------------------------------

MsgBox HexToDec("fffffffffffffffffffffff")	'<--displays: 4951760157141521099596496895

MsgBox TypeName(HexToDec("ABCDEF0123456"))	'<--displays: Decimal

'
'
'

Example 2: excel vba parse hex digits from string

'Extremely fast function to parse hexidecimal digits from text string:

Function ForceStringToHexDigitsOnly$(s$)
    Dim i&, p&, max&, t&
    Dim b() As Byte, res() As Byte
    Static keep() As Boolean

    Const VALS$ = "0123456789 ABCDEFabcdef"
    
    If (Not Not keep) = 0 Then
        ReDim keep(0 To 255)
        For i = 1 To Len(VALS)
            keep(Asc(Mid$(VALS, i, 1))) = 1
        Next
    End If
    
    max = Len(s)
    ReDim res(0 To max)
    b = StrConv(s, vbFromUnicode)
    For i = 0 To Len(s) - 1
        t = b(i)
        If keep(t) Then
            res(p) = t
            p = p + 1
        End If
    Next
    ForceStringToHexDigitsOnly = Left$(StrConv(res, vbUnicode), p)
End Function

'------------------------------------------------------------------------------

MsgBox ForceStringToHexDigitsOnly("qA01mzBoo7o2F%F")  '<--displays: A01B72FF

'
'
'