 # Thread: [RESOLVED] DECTOBIN use in VBA

1. ## [RESOLVED] DECTOBIN use in VBA

I've come across code that contains these lines. I've never used DECTOBIN in Excel at all. I looked up an explanation and it seems to say that the number must be in the range -512 to 511, so I don't understand why if I change 255 to 256 the code fails.

Code:
```  Dim i As Long, s As String
Dim wf As WorksheetFunction

For i = 0 To 255
s = wf.Dec2Bin(i, 8)```
Can someone explain what's going on please?  Reply With Quote

2. ## Re: DECTOBIN use in VBA

When you looked up the documentation for Dec2Bin, did you notice the significance of the second parameter you are passing? That tells how many binary digits to use to display the value. How many digits does it take to represent 256 in binary?  Reply With Quote

3. ## Re: DECTOBIN use in VBA

I should have realized that it's more than 8, but disregarding that can you explain what the For i = 0 To 255 line is doing in the code shown here https://superuser.com/questions/1057...ons-of-numbers  Reply With Quote

4. ## Re: DECTOBIN use in VBA

So, the question posed in that thread is, given a set of 8 numbers (n1, n2, n3, ... , n8), what are all the possible sums of any subset of those values?

And the code posted enumerates all decimal values from 0 to 255. For each binary value, it then loops through the digits of the binary converted number. If a digit is a 1, the associated n value is added to the sum; if a digit is 0, that n value is not added.

After all 8 binary digits are evaluated, that sum is logged as one of the potential subset sums, and the next decimal to binary conversion takes place, repeat.

It is a way to ensure that all possible combinations of sums of values are found; 00000000 (the sum of no elements of the set is 0) through 11111111 (the sum of all 8 values of the set), and every combination in between.  Reply With Quote

5. ## Re: DECTOBIN use in VBA

But why 255?  Reply With Quote

6. ## Re: DECTOBIN use in VBA

255 = 11111111 in binary, which represents the sum of all 8 values in the set. It stops there because 256 = 100000000, and there are only 8 values in the set as given, so there is no reason to move to nine digits.

If your curiosity is tied solely to how Dec2Bin works, then that thread is way beyond the scope of that function - in that thread that function is just used as a convenient way to generate a brute force complete list of subset combinations.  Reply With Quote

7. ## Re: DECTOBIN use in VBA

Thanks for taking the time to help me.  Reply With Quote

8. ## Re: [RESOLVED] DECTOBIN use in VBA

If you don't mind, I've got another questions about that code. There are two eights; one the number of rows of data and the other (I think) the number of bits in in the bit string but the code refers to the both with 'j'. Are there two, or am I wrong, and if I'm correct, which is which?  Reply With Quote

9. ## Re: [RESOLVED] DECTOBIN use in VBA

So, the one thing that code does that I didn't mention above is that it is only looking for subset sums that fall within a specific range. That is important because it is the reason there are two inner for loops.

That being said, the first for j = 1 to 8 loop simply adds the decimal to binary result one digit at a time to the first 8 rows of column b. There is a cell that is set with a formula such that it takes the sum of a1*b1+a2*b2+a3*b3,...,+a8*b8etc. When a value in b2 is zero, for example, the result of a2*b2 is also 0. When a value in b3 is 1, for example, the result of a3*b3 is the value in a3. So the formula in that cell ensures that cell will always contain the subset sum for the current subset group.

Then, the If statement checks to make sure that the subset sum value in that cell is in the desired range. If it is, it then triggers the second for j = 1 to 8 loop which simply uses string concatenation to build a comma separated list of values in that subset, by checking to see which values in b1 through b8 have a value of 1, and then adding the associated value from column a to that string.

I hope that answers your question. There are "two 8's" because it is looping over the same cells (b1 through b8) twice, for two different purposes, once to populate the cells, and then once again to read the values of those cells back. It only enters the second loop if the sum is in the desired range.  Reply With Quote

10. ## Re: [RESOLVED] DECTOBIN use in VBA

What wuld the code look like if there were 9 rows of data?  Reply With Quote

11. ## Re: [RESOLVED] DECTOBIN use in VBA

The outer for loop would be from 0 to 511

The dec2bin line would have 9 as the second parameter

for j = 1 to 9 in both inner for loops

The formula that calculates the sum would need to reference the first 9 rows in columns a and b

Possibly other changes as well, but that's the gist of it.

Edit: And of course there would need to be a 9th numeric value added in column a

All that being said, for what its worth, it wouldn't be hard to write a routine that will convert a decimal number to binary "string" that can handle much much larger decimal values than 511, if you have a need for binary values larger than 9 digits long.  Reply With Quote

12. ## Re: [RESOLVED] DECTOBIN use in VBA

This is VB6 code that will convert any valid Long value to Binary, which is returned as a String. Up to 31 binary digits, and a negative sign up front for negative numbers. Possibly not copy/paste compatible with VBA, but shouldn't be too hard to convert I would imagine. Its been ages since I've done any VBA specific code. This code can likely be optimized, but in a test I had it convert decimal values of 1 through 100000 and return 31 digits for each result and it took about 3 seconds, and less than 2 seconds if I had it not do any "up front" 0 digit padding; all on a roughly 10 year old Dell box.

Code:
```Option Explicit

Public Function DecToBin(ByVal DecimalValue As Long, Optional ByVal BinaryDigits As Integer = 0) As String

Dim err_code As Integer
Dim BinaryResult As String
Dim i As Integer

On Error GoTo Err_Handler

'If BinaryDigits isn't a valid value, return an error
If BinaryDigits < 0 Or BinaryDigits > 31 Then
err_code = 1
GoTo Err_Handler
End If

'Get out early if the DecimalValue passed is just 0
If DecimalValue = 0 Then
DecToBin = "0"
Exit Function
End If

'If a negative DecimalValue is passed, handle that up front
If DecimalValue < 0 Then
BinaryResult = "-"
DecimalValue = -DecimalValue
End If

'BinaryDigits = 0 means don't 0 pad the front end of the result at all
'So we need to calculate the exact number of binary digits the result will need
If BinaryDigits = 0 Then
BinaryDigits = Int(Log(DecimalValue) / Log(2)) + 1
End If

'If DecimalValue will use more binary digits than the value of BinaryDigits,
'return an error
If Int(Log(DecimalValue) / Log(2)) + 1 > BinaryDigits Then
err_code = 2
GoTo Err_Handler
End If

'Loop through powers of 2 and perform the conversion
For i = BinaryDigits - 1 To 0 Step -1
If DecimalValue >= 2 ^ i Then
BinaryResult = BinaryResult & "1"
DecimalValue = DecimalValue - 2 ^ i
Else
BinaryResult = BinaryResult & "0"
End If
Next i

DecToBin = BinaryResult
Exit Function

Err_Handler:

Select Case err_code
Case 0
DecToBin = "Error - Unknown conversion error"
Case 1
DecToBin = "Error - BinaryDigits parameter must be between 0 and 31 inclusive"
Case 2
DecToBin = "Error - Insufficient size of BinaryDigits parameter"
End Select

End Function```  Reply With Quote

13. ## Re: [RESOLVED] DECTOBIN use in VBA

Thanks OB1   Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Featured

Click Here to Expand Forum to Full Width