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]

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?

 

[WVinVA]

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.

 

[MrKowz]

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.

'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 />

 

[WVinVA]

Awesome. Thank you so much. I really appreciate it. I'm not sure how you did it, but it's running perfectly. Thanks again.

 

[MrKowz]

No problem! I actually learned a couple of things from this (and you gave a really solid description), so it was my pleasure!

 

[tusharm]

If this wasn't a class assignment it would definitely make a good one. {grin}

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]

If this wasn't a class assignment it would definitely make a good one. {grin}

MrExcel.Com: Doing entire assignments with the push of a button since 1998.