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

Please help. :friends:

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.

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

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.

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

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

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:

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.

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

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

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.