VBA dynamic multidimensional array

W

WVinVA

New Member
Joined
Jul 24, 2010
Messages
28
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.
 
Sort by date Sort by votes
Ok, following your logic, I came up with 5,000, not 500. And I think I see why:

Using the matrix below (it is the same as yours, just transposed due to the order I loop):
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="border-bottom: 1px solid black;;">j</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-right: 1px solid black;;">i</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;;">50</td><td style="text-align: right;border-top: 1px solid black;;">50</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;;">50</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">50</td><td style="text-align: right;border-right: 1px solid black;;">50</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-left: 1px solid black;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">50</td><td style="text-align: right;border-right: 1px solid black;;">50</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-bottom: 1px solid black;;">0</td><td style="text-align: right;border-bottom: 1px solid black;;">0</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;">50</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br />

When it is testing (1,1) {in (i,j) format}, it equals 0, which means that we must change its value to (2,1)*5 + (2,2)*10 = 0*5 + 50*10 = 500

Then when it gets to (0,0), it equals 0, which means that we much change its value to (1,0)*5 + (1,1)*10 = 0*5 + 500*10 = 5000.

Can you please verify this?
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Yes, that's correct. Sorry for the errors. Haha. I guess it makes it even tougher for you when I throw those in there. Thanks again for the help.
 
Upvote 0
Alrighty, give this a shot. Note that the maximum allowable array size is set to 100. The reason being is that when defining an array in VBA, it must have a constant.

I have commented each section of the code so it can be better understood.

Code: 
'MrKowz:03/09/2011 [URL]http://www.mrexcel.com/forum/showthread.php?t=534830[/URL]
'Function to return (0,0) of an array after a series of operations
'Note: CubeSize must be a number from 1 to 100
'Note2: If you want to view the arrays generated, remove the apostophe (') from
'       in front of each str = and Debug.Print lines
Public Function CubeArray(CubeSize As Long, InitValue As Double, _
                          x As Double, Y As Double, Z As Double, _
                          A As Double, B As Double) As Double
' Since an array must be defined with a constant, we set the maximum array
' size to be 100.
If CubeSize < 1 Or CubeSize > 100 Then
    MsgBox "Please enter a Cube Size from 1 to 100"
    Exit Function
End If
' Declare Variables
Dim Cube(0 To 100, 0 To 100)  As Double, _
    i                       As Long, _
    j                       As Long, _
    str                     As String
' Populate the array
Cube(0, 0) = InitValue
For i = 1 To CubeSize
    Cube(i, 0) = Cube(i - 1, 0) * x
    'str = Cube(i, 0) & " "
    For j = 1 To CubeSize
        Cube(i, j) = Cube(i - 1, j - 1) * Y
        'str = str & Cube(i, j) & " "
    Next j
    'Debug.Print str
Next i
'Debug.Print " "
' Test each member of the array.  If the value is >= to the defined Z-value then
' Change the value to 0.  Otherwise, change the value to 50
For i = 0 To CubeSize
    'str = ""
    For j = 0 To CubeSize
        If Cube(i, j) >= Z Then
            Cube(i, j) = 0
        Else
            Cube(i, j) = 50
        End If
        'str = str & Cube(i, j) & " "
    Next j
    'Debug.Print str
Next i
'Debug.Print " "
' Test each member of the array.  If the value <> 50, then perform operation.
For i = CubeSize - 1 To 0 Step -1
    'str = ""
    For j = CubeSize - 1 To 0 Step -1
        If Cube(i, j) <> 50 Then
            Cube(i, j) = Cube(i + 1, j) * A + Cube(i + 1, j + 1) * B
        End If
        'str = str & Cube(i, j) & " "
    Next j
    'Debug.Print str
Next i
' Output
CubeArray = Cube(0, 0)
End Function

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">5000</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A1</th><td style="text-align:left">=CubeArray(<font color="Blue">3,10,2,0.5,4,5,10</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
Awesome. Thank you so much. I really appreciate it. I'm not sure how you did it, but it's running perfectly. Thanks again.
 
Upvote 0
No problem! I actually learned a couple of things from this (and you gave a really solid description), so it was my pleasure!
 
Upvote 0
If this wasn't a class assignment it would definitely make a good one. {grin}

WVinVA said:
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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA, OLAP cube pivot : refer to parent of selected filter?
Replies
1
Views
523
srgexcel
S
C
  • Question Question
Allocation Across Months
Replies
3
Views
407
NickFru
N
J
  • Question Question
Group last name based on range in alphabet
Replies
2
Views
102
Peter_SSs
Peter_SSs
R
  • Question Question
VBA Code Works in Visual Basic Coder, but not with Command Button in Excel
Replies
3
Views
230
rheintz
R
S
  • Solved
Combine columns using dynamic array formula
Replies
3
Views
201
Fluff
Fluff

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top