VBA dynamic multidimensional array

[WVinVA]

Hi all,

I'm having trouble writing this program. I want to define a 2-D array, let's call it Cube. The dimensions of the array should be dependent upon an input called Number. Such that, the dimensions of the array Cube should be Cube(0 to Number, 0 to Number).

I would like Cube(0,0) to be set to another input, Starter. From here, I would like the rest of the array to be calculated from this starting point. The whole top row should be calculated as the previous number multiplied by input X. So

Cube(1,0) = Cube(0,0)*X
Cube(2,0) = Cube(1,0)*X
Cube(3,0) = Cube(2,0)*X
And so on.

Starting with the 2nd row, the rest of the array should be calculated by multiplying the number above and to the left by another input Y. For example:

Cube(1,1) = Cube(0,0)*Y
Cube(2,1) = Cube(1,0)*Y
Cube(2,2) = Cube(1,1)*Y
Cube(3,1) = Cube(2,0)*Y
And so on...


Once all the values are defined, I would like to change the values of any numbers below input Z to 500. For example, if Cube(56,42)< Z, then the value of Cube(56,42) will be changed to equal 500.

Finally, I want to work back from the right-most column to get back to the starting point of Cube(0,0). If the array's dimensions ended at 100, then:

If Cube(99,0)=500, then keep it as 500,
Else Cube(99,0)=Cube(100,0)*A + Cube(100,1)*B

For the sake of repetition:

If Cube(68,54)=500, then keep it as 500,
Else Cube(68,54)=Cube(69,54)*A + Cube(69,55)*B


It should keep working backwards until it gets the value for Cube(0,0). I would like the function to then give this number as its output. This is pretty complicated, but I know there's a lot of excel geniuses on this forum. Any help would be extremely appreciated. Thank you.

 

[MrKowz]

I'm trying to figure out some VBA, but, as you said, it is really complicated. Would you be able to tell me how you define the size of the array, and what values you multiply by? Are they located in your spreadsheet?

Would you be able to walk through, step-by-step, the process of say, a 3x3 or 4x4 array, including specific values and the logic behind each step?

 

[shg]

Maybe like this?

Sub demo()
    Const n As Long = 10
    
    Range("A1").Resize(n + 1, n + 1).Value = DaCube(n, 1, 2, 3)
End Sub
 
Function DaCube(n As Long, a As Double, x As Double, y As Double) As Double()
    Dim adCube() As Double
    Dim i As Long
    Dim j As Long
    
    ReDim adCube(0 To n, 0 To n)
    adCube(0, 0) = a
    
    For i = 1 To n
        adCube(i, 0) = adCube(i - 1, 0) * x
        
        For j = 1 To n
            adCube(i, j) = adCube(i - 1, j - 1) * y
        Next j
    Next i
    
    DaCube = adCube
End Function

 

[MrKowz]

Ok, I think I followed the logic correctly:

Public Sub CubeArray()
Dim Cube()      As Double, _
    i           As Long, _
    j           As Long, _
    X           As Double, _
    Y           As Double, _
    Z           As Double, _
    A           As Double, _
    B           As Double
    
Const CubeSize = 10 'Change this value to change your array size
ReDim Cube(0 To CubeSize, 0 To CubeSize) As Double
Cube(0, 0) = InputBox("What would you like the first value of the array to be?")
X = InputBox("X =")
Y = InputBox("Y =")
Z = InputBox("Z =")
A = InputBox("A =")
B = InputBox("B =")
For i = 1 To UBound(Cube)
    Cube(i, 0) = Cube(i - 1, 0) * X
    For j = 1 To UBound(Cube)
        Cube(i, j) = Application.Max(Cube(i - 1, j - 1) * Y, Z)
    Next j
Next i
For i = UBound(Cube) - 1 To 0 Step -1
    For j = 0 To UBound(Cube) - 1
        If Cube(i, j) <> Z Then
            Cube(i, j) = Cube(i + 1, j) * A + Cube(i + 1, j + 1) * B
        End If
    Next j
Next i
MsgBox Cube(0, 0)
End Sub

 

[WVinVA]

MrKowz,

Thanks for the help. I'll try to explain as well as possible. Inputs are:

Number
Starter
X
Y
A
B

For the purpose of this example, lets say:

Number = 3
Starter = 10
X = 2
Y = 0.5
Z = 4
A = 5
B = 10


I would like the array to have the dimensions 3x3, as per the input "Number". Actually, the dimensions will be 4x4 when including the 0 row and 0 column. Such that, the array should be:

(0,0) (1,0) (2,0) (3,0)
(0,1) (1,1) (2,1) (3,1)
(0,2) (1,2) (2,2) (3,2)
(0,3) (1,3) (2,3) (3,3)

Again, let's name the array "Cube". Cube(0,0) should be set equal to 10, as per the "Starter" input. From here, (1,0) should multiply this number by "X" (2). Then (2,0) should multiply (1,0) by "X". So the top row should look like this:

10 20 40 80

Starting with the second row, I would like to multiply the number above and to the left by "Y" (0.5). So,
(1,1) = (0,0) *0.5
(2,1) = (1,0) *0.5
(3,2) = (2,2) *0.5

Now, the array values will look like this:

10 20 40 80
0 5 10 20
0 0 2.5 10
0 0 0 1.25


Now, any values that are below the value of "Z" (4) should be changed to 100. Any values above the value of "Z" should be changed to 0. Now, the array should look like this:

0 0 0 0
50 0 0 0
50 50 50 0
50 50 50 50


Now, I would like to work backwards from the bottom right (3,3) to upper left (0,0) in this fashion:

If (2,2) = 50, then it stays 50
Else (2,2) = (3,2)*5 + (3,3)*10
-------Notice that the 5 and 10 were inputs "A" and "B"-------
-------In this case, it would stay 50-------

If (2,1) = 50, then it stays 50,
Else (2,1) = (3,1)*5 + (3,2)*10
-------Since (2,1) doesn't equal 50, it would run the equation-------
-------In this case, it'd be =0*5 + 0*10 -------------


This should continue until it returns to (0,0). The final array values should be:


500 0 0 0
50 50 0 0
50 50 50 0
50 50 50 50


Finally, the function's output should be this new (0,0) number: 500

Hope this helps.

 

[WVinVA]

I forgot to mention, I'd like it to be a function. Let's call it BackwardsCube. So in excel, I would type:

=BackwardsCube(Number,Starter,X,Y,Z,A,B)

The program would then run it with these inputs and return the "500" value from the last example.

Thanks again.

 

[MrKowz]

Now, any values that are below the value of "Z" (4) should be changed to 100. Any values above the value of "Z" should be changed to 0. Now, the array should look like this:

0 0 0 0
50 0 0 0
50 50 50 0
50 50 50 50

Where does the 100 come into play? I followed your logic as close as I could, and I keep coming up with 50 as the final answer (which might be a problem in the last bit of looping I do.)

 

[WVinVA]

Whoops. Sorry. It doesn't. That "100" should be 50. Any values below "Z" should be changed to 50.

 

[MrKowz]

Will it always be 50, or would it be (A*B) (which, in this case, does = 50)?

 

[WVinVA]

It will always be 50.