Convert Decimal To Binary

**sheeeng** · 06-12-2005, 09:24 PM

Hello!

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.

**Bob Phillips** · 06-13-2005, 05:16 AM

What does the DecimalToBinary routine do?

**sheeeng** · 06-13-2005, 07:59 AM

Thanks for reply.

This is the sub for DecimalToBinary.
Can anyone find out what went wrong?

' 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** · 06-13-2005, 09:55 AM

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

PHP Code:


=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** · 06-13-2005, 10:04 AM

Originally Posted by sheeeng

Hello!

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.

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** · 06-13-2005, 04:55 PM

Originally Posted by 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.

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** · 06-13-2005, 05:03 PM

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** · 06-13-2005, 06:30 PM

Originally Posted by 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.

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

**sheeeng** · 06-13-2005, 06:36 PM

Originally Posted by MWE

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?

Thanks.

**Bob Phillips** · 06-13-2005, 07:06 PM

Originally Posted by sheeeng

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** · 06-14-2005, 12:57 AM

Hi all.

Please help.

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** · 06-14-2005, 08:22 AM

Originally Posted by sheeeng

Hi all.

Please help.

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.

**sheeeng** · 06-14-2005, 08:27 AM

Originally Posted by MWE

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.

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.

**MWE** · 06-14-2005, 12:35 PM

Originally Posted by sheeeng

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.

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** · 06-14-2005, 07:44 PM

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** · 06-15-2005, 05:49 AM

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** · 06-15-2005, 07:38 AM

Thanks. I'll try it tomorrow.

**Aaron Blood** · 06-15-2005, 08:20 AM

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** · 06-16-2005, 10:58 PM

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

**Bob Phillips** · 06-17-2005, 01:44 AM

Originally Posted by sheeeng

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

Can you tell me what was wrong with the post by mvidas?

Thread: Convert Decimal To Binary

Thread Tools

Display

Convert Decimal To Binary

DecimalToBinary

BinCon Function

Posting Permissions