Solved: BIN2HEX Worksheet Problem

[Ann_BBO]

Hi All,

By using "BIN2HEX" worksheet function, Number cannot contain more than 10 characters (10 bits). But if i need to convert 16 bit to hex value. What can i do?? See the attachement file.

Example
01000 1111 1111 1111 = 7FFF.

Thanks,
Ann

[Bob Phillips]

Your workbook is HEX2BIN not BIN2HEX. Which do you really want?

[Ann_BBO]

Hi xld,

BIN2HEX worksheet function is applied on A2 cell. I would like the A2 value can show the hex number, 7FFF.

Sorry for my misleading. Thanks

Ann

[Ann_BBO]

Just think another idea. Can i say that we can make a new function so that it can convert 16 bit binary to hexadecimal in worksheet?

Thanks,
Ann

[Bob Phillips]

Try =C5&C4&C3&C2 in A2.

Not sure what you mean by your latest post.

[Ann_BBO]

Hi xld,

By the Way, please ignore the attached file. In A2 cell, if the formula is:

A2 =BIN2HEX("0111111111111111")

I would like the output result in A2 is 7FFF. However, due to number cannot contain more than 10 characters (10 bits) (i.e. 16 bits in my case) by using BIN2HEX function so that we cannot convert to 7FFF value directly.

My Idea from my latest post is: Can we create a new self-defined function (e.g. BIN2HEX_16) so that we can convert 16 bits binary value to HEX value.

I hope that my explanation help you to understand.

Thanks.
Ann

[Paul_Hossler]

Bit brute force, but this takes a string of 1's and 0's and makes a Hex string.

Is that what you were looking for?

[VBA]
Option Explicit
Function BinStr2HexStr(sBinary As String) As Variant

Dim s As String

On Error GoTo NiceExit
s = Right("0000000000000000" & sBinary, 16)

BinStr2HexStr = "&H" & _
Byte2Hex(Mid(s, 1, 4)) & _
Byte2Hex(Mid(s, 5, 4)) & _
Byte2Hex(Mid(s, 9, 4)) & _
Byte2Hex(Mid(s, 13, 4))
Exit Function
NiceExit:
BinStr2HexStr = CVErr(xlErrNA)
End Function

Private Function Byte2Hex(s As String) As String
Select Case s
Case "0000": Byte2Hex = "0"
Case "0001": Byte2Hex = "1"
Case "0010": Byte2Hex = "2"
Case "0011": Byte2Hex = "3"
Case "0100": Byte2Hex = "4"
Case "0101": Byte2Hex = "5"
Case "0110": Byte2Hex = "6"
Case "0111": Byte2Hex = "7"
Case "1000": Byte2Hex = "8"
Case "1001": Byte2Hex = "9"
Case "1010": Byte2Hex = "A"
Case "1011": Byte2Hex = "B"
Case "1100": Byte2Hex = "C"
Case "1101": Byte2Hex = "D"
Case "1110": Byte2Hex = "E"
Case "1111": Byte2Hex = "F"
Case Else
Byte2Hex = "?"
End Select
End Function
[/VBA]

Paul

[Ann_BBO]

Hi Paul,

Nice. It is good for me. Thanks. If I already have these codes in my workbook, then I will open a new workbook and insert the above codes in new workbook. How can I do in marco?? (i.e. Insert the modules from A file to B file)

Thanks,
Ann

[Bob Phillips]

A bit less brute force

[vba]

Public Function BinToHex(BinNum As String)
Dim i As Long, j As Long
Dim tmp As Long

BinNum = Right$("0000000000000000" & BinNum, 16)
For i = 1 To 4

tmp = 0
For j = 1 To 4

tmp = tmp + Val(Mid$(BinNum, (i - 1) * 4 + j, 1)) * 2 ^ ((4 - j))
Next j
BinToHex = BinToHex & Hex(tmp)
Next i
End Function
[/vba]