Array Variables in VBA

artvandelay

New Member
Joined
Jul 31, 2010
Messages
11
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,375
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.
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,767
Office Version
365
Platform
Windows
What is it you are trying to do?

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

artvandelay

New Member
Joined
Jul 31, 2010
Messages
11
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.
Code:
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

New Member
Joined
Jul 31, 2010
Messages
11
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?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,819
Messages
5,513,584
Members
408,960
Latest member
dichthuatco

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top