Code to convert from Hexa to Binary Excel VBA a large number

[chavesbreno]

I'm trying to convert 6E29210100 (hexa) to binary. With smaller hexa numbers, the code works fine. and I don't know why. I would like it was working to 10 characters at least.


Code returns: 00101001001000010000000100000000
Right answer: *0110111000101001001000010000000100000000‬

Please, have a look in short version code bellow (full in attachment):

Please Login or Register  to view this content.

[CK76]

Hmm I think it's due to limitation of VAL().

You can use code in text file attached (since Securi is blocking code from being posted as is...), that I found in my reference library. Based on very old post in Mr.Excel by pgc01.

Basically using list of Hex/Bin conversion. Uses Replace function to replace each hex char with bin string. And checks that nothing other than 0/1 exists in final string.

[Marc L]

Hi CK76 !

Tip for Securi issue : when you use two consecutive Replace VBA function use a $ like Replace$(
or a space between both, no matter as VBE removes automatically any space not necessary :

Function lgHex2Bin(strHex As String) As String 
    Dim hbArr 
    Dim i As Long 
    hbArr = Array( _ 
            Array("0", "0000"), _ 
            Array("1", "0001"), _ 
            Array("2", "0010"), _ 
            Array("3", "0011"), _ 
            Array("4", "0100"), _ 
            Array("5", "0101"), _ 
            Array("6", "0110"), _ 
            Array("7", "0111"), _ 
            Array("8", "1000"), _ 
            Array("9", "1001"), _ 
            Array("A", "1010"), _ 
            Array("B", "1011"), _ 
            Array("C", "1100"), _ 
            Array("D", "1101"), _ 
            Array("E", "1110"), _ 
            Array("F", "1111")) 
 
    For i = 0 To UBound(hbArr) - 1 
        strHex = Replace(strHex, hbArr(i)(0), hbArr(i)(1), , , 1) 
    Next 
 
    If Len(Replace( Replace(strHex, "0", ""), "1", "")) > 0 Then 
        lgHex2Bin = "Not a valid Hex" 
    Else 
        lgHex2Bin = strHex 
    End If 
End Function  

[CK76]

Ah thanks Marc didn’t know that one.

[Marc L]

It's not only a Val limitation as with CDec instead that raises a capacity error #6 so it's over the Excel capacity !

But just using the Hex2Bin worksheet function :

Function HexBin$(H$) 
   Dim V, C& 
       V = Application.Hex2Bin(H) 
    If V = CVErr(2036) Then 
            V = "" 
        For C = 1 To Len(H) 
            V = V & Application.Hex2Bin(Mid$(H, C, 1), 4) 
        Next 
    End If 
        If IsError(V) Then HexBin = "#" & CLng(V) Else HexBin = V 
End Function 
 
Sub Demo1() 
    Dim H$ 
        H = "6E29210100" 
        MsgBox "Bin = " & HexBin(H), , "Hex = " & H 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[Rick Rothstein]

Here is a function I developed quite a number of years ago...

Please Login or Register  to view this content.

Note 1
-----------------
There is no practical limit to the length of the first argument to this function (the actual limit is the binary output string which is limited to the maximum length of a VBA text string).

Note 2
-----------------
The optional WithBlanks argument, if set to True, will return the Binary string with a blank space every four Binary digits.

[Rick Rothstein]

For those who might be interested in the ability of my function above to work with huge numbers, I posted my function along with several other conversion functions (plus Fibonacci and Factorial functions too) over in the Tip and Tutorials sub-forum. Here is the link to the my initial thread message over there...

https://www.excelforum.com/tips-and-...ml#post5181689