
Jan 5th, 2019, 06:37 PM
#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?

Jan 5th, 2019, 07:04 PM
#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?

Jan 5th, 2019, 07:14 PM
#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...onsofnumbers

Jan 5th, 2019, 07:32 PM
#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.

Jan 5th, 2019, 07:37 PM
#5

Jan 5th, 2019, 07:41 PM
#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.

Jan 5th, 2019, 07:43 PM
#7
Re: DECTOBIN use in VBA
Thanks for taking the time to help me.

Jan 6th, 2019, 12:08 AM
#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?

Jan 6th, 2019, 08:36 AM
#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.

Jan 6th, 2019, 08:54 AM
#10
Re: [RESOLVED] DECTOBIN use in VBA
What wuld the code look like if there were 9 rows of data?

Jan 6th, 2019, 08:59 AM
#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.
Last edited by OptionBase1; Jan 6th, 2019 at 09:15 AM.

Jan 6th, 2019, 01:29 PM
#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

Jan 6th, 2019, 01:53 PM
#13
Re: [RESOLVED] DECTOBIN use in VBA
Thanks OB1
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
