# VBA dynamic multidimensional array

#### WVinVA

##### New Member
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.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### MrKowz

##### Well-known Member
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

##### MrExcel MVP
Maybe like this?
Code:
``````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 i As Long
Dim j As Long

ReDim adCube(0 To n, 0 To n)

For i = 1 To n

For j = 1 To n
Next j
Next i

End Function``````

#### MrKowz

##### Well-known Member
Ok, I think I followed the logic correctly:

Code:
``````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

##### New Member
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

##### New Member
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

##### Well-known Member
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

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

#### MrKowz

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

#### WVinVA

##### New Member
It will always be 50.

Replies
0
Views
304
Replies
14
Views
130
Replies
4
Views
201
Replies
3
Views
186
Replies
0
Views
41

### Forum statistics

1,190,677
Messages
5,982,213
Members
439,769
Latest member
trungminh2802 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back