Convert Decimal To Binary

[sheeeng]

Hello! :hi: I have a problem here. When I run this sub, the value in MsgBox is correct, but why there is difference when copy this value to Cell in Sheet1?
I need the binary string to be fixed 32bit. If less, then have bit padding.


Sub ConvDecCELLID()
Dim CON_VAL As Double
Dim IniVal, temp As String
CON_VAL = 2 ^ 10 * 64
IniVal = vbNullString
With UserForm3
Cells(18, 13).Formula = DecimalToBinary(.tbInput1.Value)
IniVal = DecimalToBinary(.tbInput1.Value)
IniVal = Right$(String(32, "0") & IniVal, 32)
MsgBox (IniVal)
.tbLAC.Text = Format(.tbInput2.Value / CON_VAL, 0)
.tbCI.Text = .tbInput2.Value Mod CON_VAL
End With
end Sub


Please help. :friends:

[Bob Phillips]

What does the DecimalToBinary routine do?

[sheeeng]

Thanks for reply. :hi: This is the sub for DecimalToBinary.
Can anyone find out what went wrong? :friends:



' Function to Convert Decimal to Binary (To CELL ID)
Public Function DecimalToBinary(DecimalNum As Long) As String
Dim tmp As String
Dim n As Long
n = DecimalNum
tmp = Trim(Str(n Mod 2))
n = n \ 2
Do While n <> 0
tmp = Trim(Str(n Mod 2)) & tmp
n = n \ 2
Loop
DecimalToBinary = tmp
End Function

[JKwan]

Why don't you use the build in base converter?


=DEC2BIN(9, 4)
You need to add-in the Analysis Tool Pack

If you do a search with HELP on "dec to hex". You will also get other base conversion functions.

[MWE]

Hello! :hi: I have a problem here. When I run this sub, the value in MsgBox is correct, but why there is difference when copy this value to Cell in Sheet1?
I need the binary string to be fixed 32bit. If less, then have bit padding.


Sub ConvDecCELLID()
Dim CON_VAL As Double
Dim IniVal, temp As String
CON_VAL = 2 ^ 10 * 64
IniVal = vbNullString
With UserForm3
Cells(18, 13).Formula = DecimalToBinary(.tbInput1.Value)
IniVal = DecimalToBinary(.tbInput1.Value)
IniVal = Right$(String(32, "0") & IniVal, 32)
MsgBox (IniVal)
.tbLAC.Text = Format(.tbInput2.Value / CON_VAL, 0)
.tbCI.Text = .tbInput2.Value Mod CON_VAL
End With
end Sub


Please help. :friends:
If you are stating that the value in Cell (18,13) and the value as displayed via MsgBox are different, I am not surprized because the two values are different. They are both "equal" to the binary version of whatever was found in UserForm3.tbInput1.Value, but the first is converted to binary and the 2nd is converted to binary and then padded to 32 bits.

A small point, but IniVal is NOT typed as a string variable in your code. As written, IniVal is declared, but only temp is declared and typed as string. IniVal will default to type = Variant. Many people misunderstand how the Dim statement actually works.

You do not have to type all variables, but your code will run faster if you do. You can declare and type multiple variables in the same line, e.g.,

Dim I as Long, temp as String
Dim J as Long, K as Long, N as Long

but (IMO) it is better coding convention to declare/type each variable on a single line (and alphabetize the statements to make it easier to read). For example:

Dim I as Long
Dim J as Long
Dim K as Long
Dim N as Long
Dim temp as String

[MWE]

Why don't you use the build in base converter?

=DEC2BIN(9, 4)
You need to add-in the Analysis Tool Pack

If you do a search with HELP on "dec to hex". You will also get other base conversion functions.
Sheeeng did not indicate how large the initial number (to be converted to binary) was. If it is less than 512, then the built-in function Dec2Bin will work. If not, Dec2Bin will spawn an error message. I can see three reasons why Sheeeng might want his own Dec2Bin converter:


he may need to convert numbers larger than 511
he may need to convert negative numbers and does not like the representation used by the built-in function for negative numbers
he may need to use this in a non-Excel application where the Analysis Tool Pack is not available.

Having said that, Sheeeng's code could be tightened up, e.g., the first


tmp = ...
n = n / 2

is not really necessary

[Zack Barresse]

Some relevant KB entries that would prove useful to this thread ..

http://www.vbaexpress.com/kb/getarticle.php?kb_id=306 (Decimal to Hex)
http://www.vbaexpress.com/kb/getarticle.php?kb_id=307 (Hex to Decimal)

[sheeeng]

Why don't you use the build in base converter?

=DEC2BIN(9, 4)
You need to add-in the Analysis Tool Pack

If you do a search with HELP on "dec to hex". You will also get other base conversion functions.

What is the difference btw "Analysis Tool Pack" & "Analysis Tool Pack - VBA"?
Thanks for the infromation.

[sheeeng]

Sheeeng did not indicate how large the initial number (to be converted to binary) was. If it is less than 512, then the built-in function Dec2Bin will work. If not, Dec2Bin will spawn an error message. I can see three reasons why Sheeeng might want his own Dec2Bin converter:

he may need to convert numbers larger than 511
he may need to convert negative numbers and does not like the representation used by the built-in function for negative numbers
he may need to use this in a non-Excel application where the Analysis Tool Pack is not available.

is not really necessary

Yes, I'm need the converter to implement convertions on range 1-999. No need to convert -ve numbers. I use this macro in Excel only. So, what other tools I need? :dunno Thanks.

[Bob Phillips]

What is the difference btw "Analysis Tool Pack" & "Analysis Tool Pack - VBA"?

Analysis Toolpak (note the spelling) provides a number of new functions to Excel. Analysis Toolpak - VBA provides an add-in that can be used in VBA to get those same functions.

So, taking WEEKNUM as an example, =WEEKNUM(TODAY()) on a worksheet returns 25. You cannot do the same in VBA, but if you have the Analysis Toolpak - VBA installed, you can do


MsgBox Application.Run("atpvbaen.xla!weeknum", Date)

or set a reference to ARPVBAEN.xla and simply use


MsgBox WEEKNUM(Date)

[sheeeng]

Hi all. :hi: Please help. :banghead:

I got an runtime error '6' overflow when i put in value 2576981797 or value 4295032832. What is wrong with my code?



' Function to Convert Decimal to Binary

Public Function DecimalToBinary(DecimalNum As Long) As String
Dim tmp As String
Dim n As Long
n = DecimalNum
tmp = Trim(Str(n Mod 2))
n = n \ 2
Do While n <> 0
tmp = Trim(Str(n Mod 2)) & tmp
n = n \ 2
Loop
DecimalToBinary = tmp
End Function

[MWE]

Hi all. :hi: Please help. :banghead:

I got an runtime error '6' overflow when i put in value 2576981797 or value 4295032832. What is wrong with my code?



' Function to Convert Decimal to Binary

Public Function DecimalToBinary(DecimalNum As Long) As String
Dim tmp As String
Dim n As Long
n = DecimalNum
tmp = Trim(Str(n Mod 2))
n = n \ 2
Do While n <> 0
tmp = Trim(Str(n Mod 2)) & tmp
n = n \ 2
Loop
DecimalToBinary = tmp
End Function

The long data type can accomodate values approx +/- 2 billion (actually +/- 2,147,483,648). The values you tried are too large; hence the overflow. Although the Mod operator will work with any numeric values including singles and doubles, neither of those data types is any help because they can not store more significant digits than Long. I think that one could fiddle with a user defined function version of Mod and get a few more significant digits. The Decimal Type would be a nice fix, but does not seem to be supported.:banghead:

[sheeeng]

The long data type can accomodate values approx +/- 2 billion (actually +/- 2,147,483,648). The values you tried are too large; hence the overflow. Although the Mod operator will work with any numeric values including singles and doubles, neither of those data types is any help because they can not store more significant digits than Long. I think that one could fiddle with a user defined function version of Mod and get a few more significant digits. The Decimal Type would be a nice fix, but does not seem to be supported.:banghead:

Hi all! Erm...MWE, Can you explain more on how I can solved this problem? I am not clear bout what you say. Is it I must do my own Mod function?

Thanks. :friends:

[MWE]

Hi all! Erm...MWE, Can you explain more on how I can solved this problem? I am not clear bout what you say. Is it I must do my own Mod function?

Thanks. :friends:
Sheeeng: based on what I know right now, yes you will have to write your own Mod function. Writing it is not hard. But it will not be of any value unless it can handle numbers larger than the +/- 2 billion handled by Longs. There are ways of fiddling with the original number (stored as a string), breaking it into pieces, processing the pieces, reassembling, etc., that probably could work. Let me play around with some ideas.

[MWE]

I decided to approach the problem a little differently. Instead of trying to rebuild the Mod function to handle numbers > 2,147,483,648, I went back to the initial problem, i.e., converting from decimal to binary, broke the initial value into manageable pieces, processed each piece and then added up the binary results. Seems to work. 2 billion seemed like a reasonable "chunk", so that is the building block. Could be any other number less than 2,147,483,648.

I have only done a little testing, but it seems to hang together. There are probably more elegant ways to do this, but ...


Sub Dec2Bin_Test()
Dim N As Variant
Dim strN As String
strN = InputBox(" Dec2Bin: Initial decimal value?")
If strN = vbNullString Then Exit Sub
N = CDec(strN)
MsgBox "[decimal] " & strN & " = [binary] " & Dec2Bin(N)
End Sub

Function Dec2Bin(DecNum As Variant) As String
Dim Count As Long
Dim I As Long
Dim N As Variant
Dim Ntemp As Long
Dim strTemp As String
Dim strBin2B As String
strBin2B = "1110111001101011001010000000000"
Dec2Bin = "error encountered"
N = CDec(DecNum)
Again:
If N < 2000000000 Then
Ntemp = N
Do While Ntemp <> 0
strTemp = Trim(Str(Ntemp Mod 2)) & strTemp
Ntemp = Ntemp \ 2
Loop
Dec2Bin = strTemp
For I = 1 To Count
Dec2Bin = BinAdd(Dec2Bin, strBin2B)
Next I
Exit Function
Else
N = N - 2000000000
Count = Count + 1
GoTo Again
End If
End Function

Function BinAdd(Bin1 As String, Bin2 As String) As String
Dim Adder As Long
Dim I As Long
Dim LenMax As Long
Dim Sum As Long
Dim TempBin As String
LenMax = Len(Bin1)
If Len(Bin2) > LenMax Then LenMax = Len(Bin2)
Adder = 0
For I = 1 To LenMax
Sum = 0
If I <= Len(Bin1) Then Sum = Sum + CInt(Mid(Bin1, Len(Bin1) - I + 1, 1))
If I <= Len(Bin2) Then Sum = Sum + CInt(Mid(Bin2, Len(Bin2) - I + 1, 1))
Sum = Sum + Adder
Select Case Sum
Case Is = 0
TempBin = TempBin & "0"
Adder = 0
Case Is = 1
TempBin = TempBin & "1"
Adder = 0
Case Is = 2
TempBin = TempBin & "0"
Adder = 1
Case Is = 3
TempBin = TempBin & "1"
Adder = 1
Case Else
MsgBox "error. sum value = " & Sum
BinAdd = "error encountered"
Exit Function
End Select
Next I
If Adder = 1 Then TempBin = TempBin & "1"
BinAdd = StrReverse(TempBin)
End Function

[mvidas]

Hi sheeeng,

You should just be able to use something like


Public Function DecToBin(ByVal theDec As Variant) As String
Dim i As Long
For i = 31 To 0 Step -1
DecToBin = DecToBin & CStr(Int(theDec / (2 ^ i)))
theDec = theDec - Int(theDec / (2 ^ i)) * (2 ^ i)
Next i
End Function
That will always produce a 32-character string of the binary equivalent of the decimal number. If you could possibly be converting a number larger than 4294967295, then you could use this, which will give you a string containing the binary number regardless of the size (well, up to 1.26765060022823E+30 but you could always increase the 99 if it could be bigger).


Public Function DecToBin(ByVal theDec As Variant) As String
Dim i As Long, j As Long
For j = 99 To 0 Step -1
If Int(theDec / (2 ^ j)) = 1 Then Exit For
Next j
For i = j To 0 Step -1
DecToBin = DecToBin & CStr(Int(theDec / (2 ^ i)))
theDec = theDec - Int(theDec / (2 ^ i)) * (2 ^ i)
Next i
End Function
Let me know how it works for you!
Matt

[sheeeng]

Thanks. I'll try it tomorrow.

[Aaron Blood]

I think I already wrote a pretty decent function to do this... Allows you to specify the bit length and everything.

For such a simple conversion, the builtin Excel function only going to 10 bits (if I remember correctly) is inexcusable. (...and why stop at 10? if anything it should've been 8 or 16 right?)

You might find this example interesting:
http://www.xl-logic.com/xl_files/vba/bincon.zip


I 'think' in my testing I was able to go as high as 1023 bit values (kind of a silly notion to go that high). Not sure anyone would ever need such a thing... 32-64 is probably more than adequate.

[sheeeng]

Thanks, Aaron. It solved my problem. Thanks again.
Another marked solved!! :thumb

[Bob Phillips]

Thanks, Aaron. It solved my problem. Thanks again.
Another marked solved!! :thumb

Can you tell me what was wrong with the post by mvidas (http://www.vbaexpress.com/forum/member.php?u=289)?

[Aaron Blood]

Can you tell me what was rong with the post by mvidas (http://www.vbaexpress.com/forum/member.php?u=289)?

At a glance, I can tell you it doesn't return the proper bit lengths and it doesn't return a binary string for the value zero.

For instance, an 8 bit result for the dec value 25 should return: 00011001

The mvidas formula returns a 5 bit result for 25 and other various bit lengths for other numbers.

[mvidas]

Yeah, I didn't add the ability to change the bit length at runtime, the first one shows it being a hardcoded length and the second one I posted only displays what is necessary.

I also didn't add the ability to handle 0, good catch. (could be fixed by adding 'If DecToBin = "" Then DecToBin = "0"' at the end, but there really isn't a need since Aaron's already does this)

Aaron's formula has the same idea to convert it, his is just better for the actual need of the conversion, mine simply converts without a real use :) More designed to show an easier way to convert, not much else.
Matt