PDA

View Full Version : Solved: Convert Decimal To Binary

sheeeng
06-12-2005, 09:24 PM
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

xld
06-13-2005, 05:16 AM
What does the DecimalToBinary routine do?

sheeeng
06-13-2005, 07:59 AM
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
06-13-2005, 09:55 AM
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
06-13-2005, 10:04 AM
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

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
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
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
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.

xld
06-13-2005, 07:06 PM
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

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

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
06-14-2005, 08:27 AM
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
06-14-2005, 12:35 PM
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
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
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 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)
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))
Select Case Sum
Case Is = 0
TempBin = TempBin & "0"
Case Is = 1
TempBin = TempBin & "1"
Case Is = 2
TempBin = TempBin & "0"
Case Is = 3
TempBin = TempBin & "1"
Case Else
MsgBox "error. sum value = " & Sum
Exit Function
End Select
Next I
If Adder = 1 Then TempBin = TempBin & "1"
End Function

mvidas
06-15-2005, 05:49 AM
Hi sheeeng,

You should just be able to use something likePublic 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 FunctionThat 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 FunctionLet 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!! :thumb

xld
06-17-2005, 01:44 AM
Thanks, Aaron. It solved my problem. Thanks again.
Another marked solved!! :thumb

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

Aaron Blood
06-17-2005, 05:46 AM
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
06-17-2005, 07:11 AM
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