dcsimg
Results 1 to 13 of 13

Thread: [RESOLVED] DECTOBIN use in VBA

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,037

    Resolved [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?

  2. #2
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    524

    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?

  3. #3

  4. #4
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    524

    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.

  5. #5

  6. #6
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    524

    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.

  7. #7

  8. #8

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,037

    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?

  9. #9
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    524

    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.

  10. #10

  11. #11
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    524

    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.

  12. #12
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    524

    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

  13. #13

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