An emailer has a 16 digit binary string (1’s and 0’s) and a 17th bit indicating whether the number is positive or negative. He read the Decimal to Binary post and all the great comments, but wants to do the reverse. That is, he wants a UDF to convert the binary string to a decimal number. And he wants to convert negative numbers assuming two’s complement notation.
Function BinToDec(sBinary As String, sSign As String) As Long
Dim i As Long
Dim lReturn As Long
Dim lBit As Long
Const sNEGATIVE As String = “1”
Const sOFF As String = “0”
For i = 1 To Len(sBinary)
If sSign = sNEGATIVE Then
If Mid$(sBinary, i, 1) = sOFF Then
lBit = 1
Else
lBit = 0
End If
Else
lBit = Val(Mid$(sBinary, i, 1))
End If
lReturn = lReturn + (lBit * (2 ^ (Len(sBinary) – i)))
Next i
If sSign = sNEGATIVE Then
BinToDec = -(lReturn + 1)
Else
BinToDec = lReturn
End If
End Function
Dim i As Long
Dim lReturn As Long
Dim lBit As Long
Const sNEGATIVE As String = “1”
Const sOFF As String = “0”
For i = 1 To Len(sBinary)
If sSign = sNEGATIVE Then
If Mid$(sBinary, i, 1) = sOFF Then
lBit = 1
Else
lBit = 0
End If
Else
lBit = Val(Mid$(sBinary, i, 1))
End If
lReturn = lReturn + (lBit * (2 ^ (Len(sBinary) – i)))
Next i
If sSign = sNEGATIVE Then
BinToDec = -(lReturn + 1)
Else
BinToDec = lReturn
End If
End Function
To test it, I broke out the bits manually and added them up
You’re checking the value of sSign on every iteration of the For loop when it doesn’t change. And you’re not trapping easy-to-catch errors such as passing “” as sBinary or having anything other than 0 or 1 in sBinary. Poor coding.
Also, if the binary values are signed using 2s complement, then the sign bit would be the first bit. However, the argument order for your function would require something like =BinToDec(RIGHT(x,n-1),LEFT(x,1)). Seems a bit awkward.
An alternative would be passing the entire binary value and making the maximum length an optional argument with the default set in the VBA module.
Function b2d(b As String, Optional dgts As Long = DEFAULT_BINARY_DIGITS) As Variant
Dim i As Long, j As Long, k As Long
b = Application.WorksheetFunction.Trim(b)
k = Len(b)
If k = 0 Or k > dgts Or b Like “*[!01]*” Then
b2d = CVErr(xlValue)
Exit Function
End If
j = 1
For i = k To 1 Step -1
If Mid$(b, i, 1) = “1” Then b2d = b2d + j
j = 2 * j
Next i
If k = dgts And Left$(b, 1) = “1” Then b2d = -(2 ^ dgts – b2d)
End Function
A variation on this would be to allow Len(b) to exceed dgts, in which case dgts would be set to Len(b).
The interesting question is how does one deal with a number that requires a temporary number larger than the largest value that can be stored in the variable.
In this case, using a Long variable restricts the largest value that can be stored to 2^31 – 1. So, b2d fails when dealing with any number with 31 or 32 bits in it — the j=2*j inside the i loop fails — and BinToDec has a problem with any 32-bit negative number — the 2 ^ (Len(sBinary) — fails. [Of course, DK did write that his solution was for 16 bit numbers with an independent 17th sign bit.]
The code below works — with the limited testing I did — for a range of 32 bit numbers.
Values:
Formulas:
Private Const DEFAULT_BINARY_DIGITS As Long = 8 ‘includes leading sign bit
Function VBABin2Dec(ByVal BinStr As String, _
Optional ByVal NbrDig As Long = DEFAULT_BINARY_DIGITS) As Variant
BinStr = Trim(BinStr)
Dim strLen As Integer
strLen = Len(BinStr)
If strLen = 0 Or strLen > NbrDig Or BinStr Like “*[!01]*” Then
VBABin2Dec = CVErr(xlValue) ‘Could provide more detailed errors
Exit Function
End If
Dim Pow As Long: Pow = 1
Dim I As Integer
For I = strLen To 3 Step -1
If Mid$(BinStr, I, 1) = “1” Then VBABin2Dec = VBABin2Dec + Pow
Pow = 2 * Pow
Next I
If Mid$(BinStr, 2, 1) = “1” Then VBABin2Dec = VBABin2Dec + Pow
If strLen = NbrDig Then Pow = -Pow
If Left$(BinStr, 1) = “1” Then _
VBABin2Dec = VBABin2Dec + Pow + IIf(strLen = 1, 0, Pow)
End Function
If I put a binary number in B8: of 1001100011100010
I can use =SUMPRODUCT((POWER(2,(ROW(INDIRECT(“1:”&LEN(B8)))-1))*MID(B8,LEN(B8)-ROW(INDIRECT(“1:”&(LEN(B8))))+1,1)))
Which returns 39,138
and if I put as Text in B9: ‘11001100011100010
I can extend the previous formula to allow for negatives to:
=IF(LEFT(B9,1)=”1?,-1,1)*SUMPRODUCT((POWER(2,ROW(INDIRECT(“1:”&(LEN(B9)-1)))-1)*MID(RIGHT(B9,LEN(B9)-1),LEN(B9)-ROW(INDIRECT(“1:”&(LEN(B9)-1))),1)))
Which now returns -39,138
Hui,
In 2s complement notation, the most significant bit (MSB) carries “weight”, i.e., is worth -2^(m-1) where it is the m-th digit. It’s not just a sign bit. So, if my arithmetic is correct, 11001100011100010 is -26398 and 01001100011100010 is 39138.
I used the formula =-LEFT(A11,1)*2^(LEN(A11)-1)+IF(LEN(A11)>1,SUMPRODUCT(2^(LEN(A11)-ROW(INDIRECT(“2:”&LEN(A11)))),MID(A11,ROW(INDIRECT(“2:”&LEN(A11))),1)),0)
OK, I was lazy and didn’t consider 31- or 32-bit integers. The following modification handles any integers which can be fully represented by Excel (1 sign bit + 49 mantissa bits for 15 decimal numerals = 50 bits in total). And I’m now copying Dick’s approach to accumulating negatives.
Private Const MAX_XLDP_BITS As Long = 50 ‘includes sign bit
Function b2d( _
b As String, _
Optional dgts As Long = DEFAULT_BINARY_DIGITS _
) As Variant
‘———————————————–
Dim i As Long, j As Double, k As Long, s As String * 1
b = Application.WorksheetFunction.Trim(b)
k = Len(b)
If dgts > MAX_DP_BITS Or k = 0 Or k > dgts Or b Like “*[!01]*” Then
b2d = CVErr(xlValue)
Exit Function
End If
j = 1#
s = IIf(k = dgts And Left$(b, 1) = “1”, “0”, “1”)
For i = k To 1 Step -1
If Mid$(b, i, 1) = s Then b2d = b2d + j
j = 2# * j
Next i
If s = “0” Then b2d = -b2d – 1
End Function
No special cases for the last few bits. Cost: 4 additional bits of memory for changing j’s type from Long to Double.
BinToDec(
Text: =SUM(MID(T4;ROW(INDIRECT(“2:”&LEN(T4)));1)*2^ROW(INDIRECT(“1:”&(LEN(T4)-1)))*-1^LEFT(T4;1))
Numbers: =SUM(C13:S13*2^(COLUMN($S$13)-COLUMN(C13:$S$13))*-1^$B$13)
…
BinDecReverse(
=SUMPRODUCT(MID(F14,ROW(INDIRECT(“2:”&LEN(F14))),1)*2^-(ROW(INDIRECT(“2:”&LEN(F14)))-LEN(F14))*-1^LEFT(F14,1))
…note that =CODE(“) is 148 and =CODE(“) is 34.
In Excel they look exactly the same. The wrong ones are slightly leaning, in the posts.
Replace all ” with ” and the formulas above will work.
Try =(-LEFT(G19,1)*2^(LEN(G19)-1)+BIN2DEC(RIGHT(G19,(LEN(G19)-1))))