PDA

View Full Version : Solved: Hex to Binary conversion, not using HEX2BIN



Mr_Mod
02-16-2013, 04:34 AM
I have a little spreadsheet where i have entered hex codes which represent a value, i know i can use "HEX2BIN" to convert these to binary however i cant get them into a format which i want. The total data string consists of 2 parts a Data and Command component.

Example: Data is 37H converted to binary gives 0110111B i neeed it in this format 1110110

Command 10H gives 10000 but i need it as 0000 1

Command 30H gives 00110000 but i need it as 0000 1100

Command 0179H gives 0000101111001 but i need it as 1001 1110 1000 0


Where the command code is between 0 & 31 this needs to be displayeted as 5bits

Where the command code is between 32 & 255 this needs to be displayeted as 8bits

Where the command code is between 0 & 31 this needs to be displayeted as 13bits

the Data code which is always 7 bits.

So my question is how would i write a VBA code to convert the hex values above, the Data value would always be in column "E" and the comand value in column 'G" each commencing from row "7".
The results from the conversion would be on the same row but in columns "F" and "H" respectively

the second part would be to convert them in reverse from binary to hex bearing in ming the transposition of the bit's.

I cant see anyway to convert this other than using VBA.

Any assistance would be appreciated

snb
02-16-2013, 07:38 AM
A UDF:

Function hex_snb(c00)
hex_snb = StrReverse(Application.Hex2Bin(c00, 8))
End Function

Mr_Mod
02-16-2013, 02:34 PM
Thanks,
Using that bit of code works for all hex numbers that are double digits, it returns a "Value" error when dealing with a 3 or 4 digit hex number.
Am i doing something wrong

snb
02-16-2013, 03:54 PM
you requirements weren't mutuallly exclusive....

Please reformulate your requirements.
Or, dive into the code, analyse it and adapt it to your needs.

Teeroy
02-16-2013, 08:42 PM
What @snb avoided saying is that Hex2Bin has limitations. As he suggested you should research what they are.

Try the following as a start. It isn't optimized, Ive cobbled it together from segments I had already. Importantly it does NOT do any input validation, you need to handle that.
Function HexToBin(sHex As String)
Dim normal As String, reverse As String, requiredLength As Integer, i As Integer
'Reversed with strange formatting :-)
'
' straight conversion via decimal
normal = CStr(DecToBin(HexToDec(sHex)))
'pad to required lengths
Select Case Len(normal)
Case Is > 8
requiredLength = 13
Case Is > 5
requiredLength = 8
Case Else
requiredLength = 5
End Select
Do While Len(normal) < requiredLength
normal = "0" & normal
Loop
'revese it
reverse = StrReverse(normal)
'break into groups of 4 with spaces
For i = Int(Len(reverse) / 4) To 1 Step -1
reverse = Mid(reverse, 1, i * 4) & " " & Mid(reverse, i * 4 + 1, Len(reverse))
Next i
HexToBin = reverse
End Function

Function HexToDec(sHex As String)
Dim i As Integer, Dec As Long, D As Long, sHexChar As String
Dec = 0
For i = 0 To Len(sHex) - 1
sHexChar = UCase(HexToDecSingle(Mid(sHex, Len(sHex) - i, 1), i))
If Asc(sHexChar) > 64 Then
D = (Asc(sHexChar) - 55)
Else
D = Val(sHexChar)
End If
Dec = Dec + D
Next i
HexToDec = Dec
End Function


Function DecToBin(D As String) As String
'Chip Pearson code
Dim N As Long
Dim Res As String
For N = 31 To 1 Step -1
Res = Res & IIf(CLng(D) And 2 ^ (N - 1), "1", "0")
Next N
N = InStr(1, Res, "1")
DecToBin = Mid(Res, IIf(N > 0, N, Len(Res)))
End Function

snb
02-17-2013, 04:23 AM
@TeeRoy,


Sub tst()
MsgBox Format("10010110", "@@@@ @@@@")
End Sub

Mr_Mod
02-17-2013, 04:33 AM
I am only learning VBA but can see whats happening for most of it except "
sHexChar = UCase(HexToDecSingle(Mid(sHex, Len(sHex) - i, 1), i)) " where the routine will crash and just cant see how that is derived.

Teeroy
02-17-2013, 08:52 PM
@snb I originally tried Format and discounted it as it overlaid the string on the pattern from the right end, not the left. Your comment forced me to revisit my decision and I realised I could take a substring of the pattern of the required length from the left side. Thanks.

@Mr_Mod. My fault. There was a function in there that I didn't like so I backed it out but I sent you an earlier version that included it.

Below is fully revised code which includes the improvement from @snb's suggestion and a sub to test the examples you gave.

BTW each the 3 functions can be used as a UDF from the workshhet.

Sub test()
Debug.Print HexToBin(37)
Debug.Print HexToBin(10)
Debug.Print HexToBin(30)
Debug.Print HexToBin(179)
End Sub
Function HexToBin(sHex As String)
Dim normal As String, reverse As String, requiredLength As Integer, i As Integer, sFormat As String
'Reversed with strange formatting :-)
'
sFormat = "@@@@ @@@@ @@@@ @@@@ @"
' straight conversion via decimal
normal = CStr(DecToBin(HexToDec(sHex)))
'pad to required lengths
Select Case Len(normal)
Case Is > 8
requiredLength = 13
Case Is > 5
requiredLength = 8
Case Else
requiredLength = 5
End Select
Do While Len(normal) < requiredLength
normal = "0" & normal
Loop
'revese it
reverse = StrReverse(normal)
'break into groups of 4 with spaces
sFormat = Left(sFormat, requiredLength + Int(requiredLength / 4))
reverse = Format(reverse, sFormat)
HexToBin = reverse
End Function
Function HexToDec(sHex As String)
Dim i As Integer, Dec As Long, D As Long, sHexChar As String
Dec = 0
For i = 0 To Len(sHex) - 1
'sHexChar = UCase(HexToDecSingle(Mid(sHex, Len(sHex) - i, 1), i))
sHexChar = UCase(Mid(sHex, Len(sHex) - i, 1))
If Asc(sHexChar) > 64 Then
D = (Asc(sHexChar) - 55)
Else
D = Val(sHexChar)
End If
Dec = Dec + D * 16 ^ i
Next i
HexToDec = Dec
End Function

Function DecToBin(D As String) As String
'Chip Pearson code
Dim N As Long
Dim Res As String
For N = 31 To 1 Step -1
Res = Res & IIf(CLng(D) And 2 ^ (N - 1), "1", "0")
Next N
N = InStr(1, Res, "1")
DecToBin = Mid(Res, IIf(N > 0, N, Len(Res)))
End Function

snb
02-18-2013, 04:30 AM
Hex2Bin produces the same results in the used examples.

so you can use

Sub M_snb()
c00 = StrReverse(Application.Hex2Bin(179))
y = Switch(Len(c00) > 8, 13, Len(c00) > 5, 8, Len(c00) < 8, 5)
Debug.Print Format(Left(c00 & String(y, "0"), y), Left("@@@@ @@@@ @@@@ @@@@", IIf(y = 13, 15, y + 1)))
End Sub

If you want to avoid Hex2Bin's limitations this is an alternative

Sub M_snb2()
sn = Split(StrConv("C7", vbUnicode), Chr(0))
For j = 0 To UBound(sn) - 1
x = x + 16 ^ (UBound(sn) - 1 - j) * IIf(Val(sn(j)) = 0, Asc(sn(j)) - 55, sn(j))
Next

For j = 31 To 1 Step -1
z = z & IIf(CLng(x) And 2 ^ (j - 1), 1, 0)
Next

z = StrReverse(Val(z))
y = Switch(Len(z) > 8, 13, Len(z) > 5, 8, Len(z) < 8, 5)
Debug.Print Format(Left(z & String(y, "0"), y), Left("@@@@ @@@@ @@@@ @@@@", IIf(y = 13, 15, y + 1)))
Edn Sub

Mr_Mod
02-19-2013, 05:40 PM
Many thanks for your assistance, the above code work great.