Array Variables in VBA

[artvandelay]

I've been working on a macro and am having trouble printing the results back into the excel spreadsheet. Below I included the code for the basic premise of what I am trying to do. Essentially, I have a loop within a loop. I want the results from the first loop (a 1x5 array) to be combined in an array variable for the second loop (a 5x5 array). The code below is the best I've been able to come up with so far, but for some reason I just get #N/A in the last two columns and blank cells in the first three. I'm sure there's even a simpler way to do it than I've proposed, I'm just struggling a bit with the concept of 2+ dimensional array variables. Thanks!

Sub ArrayTest()

Dim MyArray(5)
Dim MyArrayDown(5, 2)
Dim i As Integer
Dim j As Integer


For i = 0 To 5

'Create a 1x5 section filled with values from array variable
​

For j = 0 To 5
MyArray(j) = j
Next j

'Add array from above to each i to fill a 5x5 section with values from the array variable

MyArrayDown(i, 0) = MyArray
​

Next i

'Print array variable to a 5x5 section in excel
Range("array").Resize(5, 5) = MyArrayDown

End Sub

 

[John_w]

Why 'Dim MyArrayDown(5, 2)' if you want a 5x5 array? If you look at the Locals window you will see how MyArrayDown is being created in a way your code isn't expecting. To declare a 5x5 array, the array declaration should be Dim MyArrayDown(4, 4) because the subscripts start at 0 by default, giving 5 elements 0-4.

Sub ArrayTest()

Dim MyArray(4)
Dim MyArrayDown(4, 4)
Dim i As Integer
Dim j As Integer

For i = 0 To 4

    'Create a 1x5 section filled with values from array variable

    For j = 0 To 4
        MyArray(j) = j
    Next j

    'Add array from above to each i to fill a 5x5 section with values from the array variable

    For j = LBound(MyArray) To UBound(MyArray)
        MyArrayDown(i, j) = MyArray(j)
    Next

Next i

'Print array variable to a 5x5 section in excel
Range("array").Resize(5, 5) = MyArrayDown

End Sub

 

[Norie]

What is it you are trying to do?

Are you trying to create combinations or permutations of some sort?

 

[artvandelay]

I was under the impression that the ,2 signified a 2 dimensional array. I thought I had read that somewhere, but I must have misunderstood it. I'm relatively new to VBA and I'm still trying to wrap my head around arrays. Thanks for your help!

Why 'Dim MyArrayDown(5, 2)' if you want a 5x5 array? If you look at the Locals window you will see how MyArrayDown is being created in a way your code isn't expecting. To declare a 5x5 array, the array declaration should be Dim MyArrayDown(4, 4) because the subscripts start at 0 by default, giving 5 elements 0-4.

Sub ArrayTest()
 
Dim MyArray(4)
Dim MyArrayDown(4, 4)
Dim i As Integer
Dim j As Integer
 
For i = 0 To 4
 
    'Create a 1x5 section filled with values from array variable
 
    For j = 0 To 4
        MyArray(j) = j
    Next j
 
    'Add array from above to each i to fill a 5x5 section with values from the array variable
 
    For j = LBound(MyArray) To UBound(MyArray)
        MyArrayDown(i, j) = MyArray(j)
    Next
 
Next i
 
'Print array variable to a 5x5 section in excel
Range("array").Resize(5, 5) = MyArrayDown
 
End Sub

 

[artvandelay]

I'm trying to create a monte carlo simulation of a discounted cash flow model to be used for valuing stocks. So basically the model will generate thousands of possible stock prices based on different scenarios and produce a probability distribution of the intrinsic value. I want to print the summary data so I can make sure everything is working correctly. So far I've been able to figure out the arrays for single variables (ie: price), but in one particular spot, I'm looping 5 times (each representing a year) within the greater loop of each scenario. Essentially I'm trying to create a table like this:

<TABLE style="WIDTH: 292pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=389 border=0 x:str><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 52pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=69 height=17></TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 240pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" align=middle width=320 colSpan=5>Cash Flow</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Year 1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Year 2</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Year 3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Year 4</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Year 5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Scenario 1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Scenario 2</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Scenario 3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Scenario 4</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Scenario n</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">x</TD></TR></TBODY></TABLE>


Hope this makes sense; Let me know if you need me to clarify. Thanks!

What is it you are trying to do?

Are you trying to create combinations or permutations of some sort?