VBA: "n" summation tiers

[ehchandler]

Hello - I'm extremely new to VBA, but have had to learn it at a relatively high level pretty fast, so forgive me if this is a simple question. There are lots of gaps in my understanding.

I have been working with an equation that involves two levels of summation, see formula (a) below, but today was presented with a problem that required an expansion to three levels, given by formula (b) below. Instead of just rewriting the program for three tiered summation, since its likely that I will have to deal with four and five and who knows how many summation tiers in the future, I would like to write it as generalized for "n" summation tiers. See formula (c) below. (Unintentional, but interestingly enough, for this problem, "n" as the number of summation tiers and "N" as the upper bound of each summation are equal, though I would like to understand how to generalize this to any number of upper bounds and tiers, whether or not n=N).

I assume it would look like a series of "For i = 1 To N ..... Next" statements, which I can do, but how do you do an "n-tuple" nested "For ..... Next" statements, where "n" is variable? This is really hurting my head!
(a)

(b)

(c)

Thanks so much in advance for your comments and advice.

 

[Eric W]

The usual approach is to put the upper limits of each loop in an array, and use another array as an index. For example, if you have n tiers, and the upper limits are N1, N2, ... Nn, then pseudocode would look something like:

Sub temp1()
Dim Limits() As Integer, Indexes() As Integer, N As Integer, i As Integer
Dim MySum As Long, work As Long

    N = 5
    MySum = 0
    
    ReDim Limits(N)
    ReDim Indexes(N)
    
    For i = 1 To N
        Limits(i) = N      ' Set the upper bounds here
        Indexes(i) = 1      ' The starting index
    Next i
    
TopOfLoop:
    
    ' Do some code here
    work = 1
    For i = 1 To N
        work = work * x(Indexes(i))
    Next i
    MySum = MySum + work
    
    ' Increment the indexes
    
    For i = 1 To N
        Indexes(i) = Indexes(i) + 1
        If Indexes(i) <= Limits(i) Then Exit For
        Indexes(i) = 1
    Next i
    If i < N + 1 Then GoTo TopOfLoop:
    
    ' All done with all the indexes
    
End Sub

You could also initialize the arrays like:

    Limits = Array(0, N1, N2, N3, N4, N5)
    Indexes = Array(0, 1, 1, 1, 1, 1)

When you use Array to define an array, it's 0 based, which is why there's a 0 at the start.

You can envision the increment section as an odometer.

Hope this helps.

 

[ehchandler]

The usual approach is to put the upper limits of each loop in an array, and use another array as an index. For example, if you have n tiers, and the upper limits are N1, N2, ... Nn, then pseudocode would look something like:

Sub temp1()
Dim Limits() As Integer, Indexes() As Integer, N As Integer, i As Integer
Dim MySum As Long, work As Long

    N = 5
    MySum = 0
    
    ReDim Limits(N)
    ReDim Indexes(N)
    
    For i = 1 To N
        Limits(i) = N      ' Set the upper bounds here
        Indexes(i) = 1      ' The starting index
    Next i
    
TopOfLoop:
    
    ' Do some code here
    work = 1
    For i = 1 To N
        work = work * x(Indexes(i))
    Next i
    MySum = MySum + work
    
    ' Increment the indexes
    
    For i = 1 To N
        Indexes(i) = Indexes(i) + 1
        If Indexes(i) <= Limits(i) Then Exit For
        Indexes(i) = 1
    Next i
    If i < N + 1 Then GoTo TopOfLoop:
    
    ' All done with all the indexes
    
End Sub

You could also initialize the arrays like:

    Limits = Array(0, N1, N2, N3, N4, N5)
    Indexes = Array(0, 1, 1, 1, 1, 1)

When you use Array to define an array, it's 0 based, which is why there's a 0 at the start.

You can envision the increment section as an odometer.

Hope this helps.

Wow. Its always so impressive to see what y'all come up with. The more I code, the more I start to see the elegance in it. I kept reading your code over and over and kept going "he left out this, he left out that," and then realized there was another level of complexity that I didn't notice before that accounted for what I thought was missing. So, thanks so much.

Quick question: Is there a way to have a dynamic number of dimensions in a given array? I can't seem to find much online, and it doesn't seem like VBA has a specific command for that. This would be for the "a-term," as it involves all of the dimensions at once. All I can think of is finding some way to "Loop From 1 To (N-1)" and physically tack on another -- & " ,1 To N" -- to the end of the array statement during each loop, and then after the loop finishes, tack on a final -- & " ,1 To N)" -- but this seems horribly inefficient, and I'm not actually sure if its possible, given the necessary ReDim statement. Is there a way that isn't such a work around, and is more elegant and neat, if its even possible to have a dynamic number of dimensions?

Thanks in advance.

 

[ehchandler]

I suppose I could do the case thing? And just write a simple "ReDim Preserve" statement for 60 different cases and GoTo "Case N," where "N" is the upper bound, and "Case N" ReDims a() to an N dimentional a(N, N, ....., N) array? It just seems bulky and not clean.

 

[Eric W]

I first came up with that basic concept over 35 years ago. Since then it's undergone a fair amount of streamlining and improvement, so you're seeing the "elegant" version, which is what I strive for. I know others have devised essentially the same concept, but this is my version. Thanks for the kind comments.

As far as having a dynamic number of dimensions in an array, I don't believe there is a built-in data structure in VBA to do that. However, you can create the equivalent. For example, imagine you have an array defined as a(2,3). There are therefore 6 elements (2*3). If you define a 1-dimensional array as a(6), then you still have 6 elements. You can map the 2 dimensions into 1 dimension like this:

(index1-1) * 3 + (index2-1) +1

(1,1) maps to 1
(1,2) maps to 2
(1,3) maps to 3
(2,1) maps to 4
(2,2) maps to 5
(2,3) maps to 6

For 3 dimensions, the mapping is:
(index1-1)*(upper bound of second dimension * upper bound of third dimension) + (index2-1) * (upper bound of third dimension) + (index3-1) + 1

With a little imagination, you can see how to go to higher dimensions, even a variable number of dimensions. Here's how I coded it:

Sub temp1()
Dim Limits() As Integer, Indexes() As Integer, N As Integer, i As Integer
Dim MySum As Long, WorkX As Long, WorkA As Long, a() As Double

    N = 3
    MySum = 0
    
    ReDim Limits(N)
    ReDim Indexes(N)
    
    WorkA = 1
    For i = 1 To N
        Limits(i) = N       ' Set the upper bounds here
        Indexes(i) = 1      ' The starting index
        WorkA = WorkA * N
    Next i
    
    ReDim a(WorkA)
    
TopOfLoop:
    
    ' Do some code here

    WorkX = 1                                    ' Calculate the product of the x's
    For i = 1 To N
        WorkX = WorkX * x(Indexes(i))
    Next i
    
' Calculate the 1-dimensional index equivalent of the multiple indexes for a
    WorkA = Indexes(1) - 1
    For i = 2 To N
        WorkA = WorkA * Limits(i) + Indexes(i) - 1
    Next i
    WorkA = WorkA + 1
    
    MySum = MySum + WorkX * a(WorkA)
    
    ' Increment the indexes
    
    For i = 1 To N
        Indexes(i) = Indexes(i) + 1
        If Indexes(i) <= Limits(i) Then Exit For
        Indexes(i) = 1
    Next i
    If i < N + 1 Then GoTo TopOfLoop:
    
    ' All done with all the indexes
    
End Sub

I must say that I haven't fully tested this, but the concept is sound. (Additional thought: it would probably be more efficient to start the WorkA index at 1, then increment it with the other indexes. I may look at that.) You'll also have to find a way to initialize the x and a values, but you should be able to figure it out from the example. I don't claim that this method is particularly neat or elegant, but sometimes you have to work with the capabilities of the language.

 

[Eric W]

I suppose it's always worth taking a second look. Now that I think about it more, the code designed to create the 1-dimensional index for a is only needed if you're given arbitrary indexes. If you're just counting through the x indexes in order, you should just be able to set WorkA to 1 before TopOfLoop, and increment it by 1 at the end of the loop. That counts through the a's in order. Definitely more efficient.