16bit number split into bits

[Neil]

Hi, I have a data stream coming into Excel, specifically
a 16 bit number whos int value would be between 0 &
65535, I want to be able to see what parts of the 16 bit
number are on and which are off, e.g. 0000000000000000,
are bits are off, 0001000000000000, bit 4 is on,
0011100000000000 bits 3,4,5 are all on. Is there an easy
way to do this in VBA ?

 

[Harald Staff]

Hi

Sure. But count positions from the right, not from the left. They are
generated by 2^n with highest value to the left, just like decimals.
See if you can modify this to fit your needs:

Sub test()
Dim i As Long
Dim TestNumber As Long
TestNumber = 64536
For i = 1 To 16
MsgBox TestNumber & Chr(10) & _
Dec2Bin(TestNumber) & Chr(10) & _
"Bit " & i & " " & _
BitTrue(TestNumber, i)
Next
End Sub

Function BitTrue(Number As Long, BitNumber As Long) As Boolean
BitTrue = Number And 2 ^ (BitNumber - 1)
End Function

Function Dec2Bin(ByVal What As Long) As String
Dec2Bin = ""
Do
Dec2Bin = What Mod 2 & Dec2Bin
What = What \ 2
Loop Until What < 1
End Function

 

[Jerry W. Lewis]

The Analysis ToolPak includes a function DEC2BIN. Unfortunatly it is
limited to integers between -512 and 511. However you can use it on the
individual bytes and combine the results

=TEXT(DEC2BIN(INT(A2/256)),"00000000")&TEXT(DEC2BIN(MOD(A2,256)),"00000000")

Jerry

 

[Guest]

There isn't any easy way to do this, but if you are
confortable with HEX, then you can use the "hex" VB command to convert
a decimal value to a HEX String. Then instead of 16 binary bits,
you'll get 4 HEX digits. Unfortunately leading zeros are not imposed,
so you have have to enforce that using the "Format" command.

Otherwise, you have to do the conversion manually by parsing the
decimal data and generating the string of 0 and 1's.

Alternatevly, you can write a C DLL file, and call it from VBA. Then
you could code it in about 20 lines in C.

 

[Guest]

Here's a way to parse it

Function ConvertToBinary(lngNumber As Long) As Strin
' This function will return a 16 character string tha
' contains the binary values of the long integer argument
Dim strBinary As Strin
Dim lngSubtractor As Lon
Dim i As Intege

If lngNumber > 65535 Or lngNumber < 0 Then Exit Functio
lngSubtractor =
For i = 1 To 1
lngSubtractor = lngSubtractor *
Nex

For i = 1 To 1
If lngNumber >= lngSubtractor The
strBinary = strBinary & "1
lngNumber = lngNumber - lngSubtracto
Els
strBinary = strBinary & "0
End I
If i <> 16 The
lngSubtractor = lngSubtractor /
End I
Nex
ConvertToBinary = strBinar
End Functio
Sub FindBits(ByVal strBinary As String, ByRef Bits() As Integer
' This subroutine will receive an integer array by referenc
' and fill it with the positions of the 1s within the binarystring
Dim i As Intege
ReDim Bits(1 To 1) As Intege
For i = 1 To 1
If Mid(strBinary, i, 1) = "1" The
If Bits(1) = 0 The
If UBound(Bits) = 1 The
Bits(1) =
End I
Els
ReDim Preserve Bits(1 To UBound(Bits) + 1) As Intege
Bits(UBound(Bits)) =
End I
End I
Nex

End Su
Sub Foo(
' Here's a sample routine to work with the two procedures
Dim lng As Lon
Dim strBinary As Strin
Worksheets("Binary").Range("1:1").Clea
lng = InputBox("Input a number."
If lng <> 0 The
strBinary = ConvertToBinary(lng
Els
Exit Su
End I
Dim Bits() As Intege
FindBits strBinary, Bit

Dim rngTarget As Rang
Set rngTarget = Worksheets("Binary").Range("A1"
Dim i As Integer, j As Intege
For j = 1 To UBound(Bits
rngTarget.Offset(0, i).Value = Bits(j
i = i +
Nex

End Su

-Brad

 

[JE McGimpsey]

One way:

http://www.mcgimpsey.com/excel/longdec2bin.html

Note that, in my experience, bits are usually numbered from right to
left (i.e., bit 3 on = decimal 4 = 0000000000000100), so if you
specifically want left to right (i,e. 0010000000000000), you'll need to
modify the macro.