Dynamically refer to variable names?

Question

I understand that VBA can't dynamically refer to variable names, such as b and i in the following code, and interpret it to get the value of b1 as described here.

But I was wondering if there is a more elegant way to accomplish what I am doing below?

Basically, I want to get all combinations of 7 columns.

Sub combination()

    Dim w(1 To 7) As Integer
    Dim b(1 To 7) As Integer

    'get values
    For i = 1 To 7
        w(i) = Cells(6, i + 1).Value
    Next i

    newline = 8
    total = 0

    For b1 = 0 To 1
        For b2 = 0 To 1
            For b3 = 0 To 1
                For b4 = 0 To 1
                    For b5 = 0 To 1
                        For b6 = 0 To 1
                            For b7 = 0 To 1
                                b(1) = b1
                                b(2) = b2
                                b(3) = b3
                                b(4) = b4
                                b(5) = b5
                                b(6) = b6
                                b(7) = b7

                                For i = 1 To 7
                                    total = total + b(i) * w(i)
                                Next i    
                                Cells(newline, 1).Value = total

                                For i = 1 To 7
                                    Cells(newline, i + 1).Value = b(i)
                                Next i

                                total = 0
                                newline = newline + 1

                            Next b7
                        Next b6
                    Next b5
                Next b4
            Next b3
        Next b2
    Next b1
End Sub

There is undoubtedly a more elegant way to do this, but "get all combinations of 7 columns" doesn't really adequately describe what you are trying to do. Do you just need to generate permutations of 7 values across a row? What is the data in the Worksheet and what are you trying to do with it? — Comintern
– Comintern, Commented Apr 12, 2015 at 4:14
VBA can do similar things to what you want. See CallByName. Also you can load a ScriptControl and run any vbs script, or part of a script, you want. But your question usually indicates a poor solution (or a lazy solution sometimes) to a problem. What is your problem? — Serenity
– Serenity, Commented Apr 12, 2015 at 5:23
If you just need to output the permutations, then this site might be what you're looking for - mrexcel.com/forum/excel-questions/… — Shauno_88
– Shauno_88, Commented Apr 12, 2015 at 6:56
Thanks! I will look up Shauno_88's suggestion, that might work. I will also look at CallByName, I haven't used that before. — Bilfrost
– Bilfrost, Commented Apr 13, 2015 at 3:03
Forgot to answer both of your questions about what the problem is: actually, the output of this IS what I am trying to accomplish. It is not an intermediate solution to something else. I am just trying to see if there is a better solution to accomplish it. — Bilfrost
– Bilfrost, Commented Apr 13, 2015 at 20:22

Axel Richter · Accepted Answer · 2015-04-12 07:42:03Z

If the question is: "How to use dynamic variable variable names", then the answer in most cases is please use arrays. This is not only with VBA but also with languages which have the "variable variable names" feature, like PHP for example. So using arrays is definitely the right way.

With your concrete example there could be a more flexible way. Your b1, b2, ..., b7 are binary digits and the array b(1 to 7) then is an array of 7 single binary digits which together are representing one of the decimal values from 0 to 127 (2^7 - 1). So you could have a function which returns such an array and this function could be more flexible as it could return such array in variable length not fixated on 7 digits.

See example:

Function getBinArray(ByVal vDecimal As Double, ByVal digits As Integer) As Variant
 p = 1
 Dim bin() As Integer
 ReDim bin(1 To digits)
 Do While p < digits + 1
  'remainder = vdecimal Mod (2 ^ p)
  remainder = vDecimal - Int(vDecimal / (2 ^ p)) * (2 ^ p)
  If remainder <> 0 Then
   bin(p) = 1
   vDecimal = vDecimal - (2 ^ (p - 1))
  Else
   bin(p) = 0
  End If
  p = p + 1
 Loop
 getBinArray = bin
End Function


Sub combination()
 Dim w As Variant
 Dim b As Variant
 lCount = 7 'count of values to compute subtotals for
 With ActiveSheet
 'get values
  w = .Range(.Cells(6, 2), .Cells(6, 2 + lCount - 1)).Value
  newline = 8
  .Range(.Rows(8), .Rows(.Rows.Count)).Clear
  Total = 0
  For vDecimal = 0 To 2 ^ lCount - 1
   b = getBinArray(vDecimal, lCount)
   For i = 1 To lCount
    Total = Total + b(i) * w(1, i)
   Next i
   .Cells(newline, 1).Value = Total
   .Range(.Cells(newline, 2), .Cells(newline, 2 + lCount - 1)).Value = b
   Total = 0
   newline = newline + 1
  Next vDecimal
 End With
End Sub

My b() array contains the binary digits in following order b(1) = digit 2^0, b(2) = digit 2^1, ... b(n) = digit 2^(n-1).

Collectives™ on Stack Overflow

Dynamically refer to variable names?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related