VBA array resizing for cell range covering 10 cells

[joejackson123]

Hi All, I have the following VBA script, which works for doing a series of somewhat random draws from a model, stores those values in an array, and then pastes the array into a sheet for graphing. Storing values from a single cell on each draw works fine, when I try to store an entire range of cells in the

curvearray

array variable, it throws an error. I'm 90% certain this has something to do with sizing/resizing of the array, but am fairly green with VBA and cribbed most of this code from online, so am not entirely sure. Any help is as always much appreciated, thank you!

[FONT=arial]Dim randa As Double[/FONT]
[FONT=arial]Dim stdeva As Double[/FONT]

[FONT=arial]Dim looprun As Double[/FONT]
[FONT=arial]Dim arrayone() As Variant[/FONT]
[FONT=arial]Dim arraytwo() As Variant[/FONT]
[FONT=arial]Dim curvearray() As Variant[/FONT]

[FONT=arial]ReDim [/FONT][FONT=arial]arrayone[/FONT][FONT=arial](1 To 1)[/FONT]
[FONT=arial]ReDim [/FONT][FONT=arial]arraytwo[/FONT][FONT=arial] [/FONT][FONT=arial](1 To 1)[/FONT]
[FONT=arial]ReDim curvarray(1 To 10)[/FONT]

[FONT=arial]stdeva = 1[/FONT]
[FONT=arial]a = Range("baseline").Value[/FONT]

[FONT=arial]For looprun = 1 To 100[/FONT]

[FONT=arial]randa = Rnd[/FONT]

[FONT=arial]Range("baseline").Value = WorksheetFunction.NormInv([/FONT]<wbr style="font-family: arial, sans-serif; font-size: small;">[FONT=arial]randa, a, stdeva)[/FONT]
[FONT=arial]Application.Calculate[/FONT]

[FONT=arial]arrayone[/FONT][FONT=arial] [/FONT][FONT=arial](UBound(arrayone)) = Range("net").Value[/FONT]
[FONT=arial]ReDim Preserve [/FONT][FONT=arial]arrayone[/FONT][FONT=arial](1 To UBound([/FONT][FONT=arial] [/FONT][FONT=arial]arrayone[/FONT][FONT=arial]) + 1)[/FONT]

[FONT=arial]arraytwo(UBound([/FONT][FONT=arial]arraytwo[/FONT][FONT=arial])) = Range("multi").Value[/FONT]
[FONT=arial]ReDim Preserve [/FONT][FONT=arial]arraytwo[/FONT][FONT=arial](1 To UBound([/FONT][FONT=arial]arraytwo[/FONT][FONT=arial]) + 1)[/FONT]

[FONT=arial]curvearray(UBound([/FONT][FONT=arial]curvearray)) = Range("curve").Value[/FONT]
[FONT=arial]ReDim Preserve curvearray(1 To UBound(curvearray) + 1)[/FONT]

[FONT=arial]Next looprun[/FONT]

[FONT=arial]Range("onepaste").Resize([/FONT]<wbr style="font-family: arial, sans-serif; font-size: small;">[FONT=arial]UBound(arrayone)) = Application.Transpose([/FONT][FONT=arial]arrayone[/FONT][FONT=arial])[/FONT]
[FONT=arial]Range("twopaste").Resize([/FONT]<wbr style="font-family: arial, sans-serif; font-size: small;">[FONT=arial]UBound([/FONT][FONT=arial]arraytwo[/FONT][FONT=arial])) = Application.Transpose([/FONT][FONT=arial]arraytwo[/FONT][FONT=arial])[/FONT]
[FONT=arial]Range("curvepast").Resize([/FONT]<wbr style="font-family: arial, sans-serif; font-size: small;">[FONT=arial]UBound([/FONT][FONT=arial]curvearray[/FONT][FONT=arial])) = Application.Transpose([/FONT][FONT=arial]curvearray[/FONT][FONT=arial])[/FONT][FONT=arial] [/FONT]

[FONT=arial]End Sub[/FONT]

 

[HackSlash]

Let's start by getting rid of those arrays altogether. Arrays are great when you know how many of any given thing you have. If you need an array of a dynamic size then that is called a "collection" or a "dictionary".

Read about collections here:
https://excelmacromastery.com/excel-vba-collections/

Here is an example using your code:

Option Explicit

Sub curves()
    Dim randa As Double
    Dim stdeva As Double

    Dim looprun As Double
    Dim net As New Collection
    Dim multi As New Collection
    Dim curve As New Collection

    stdeva = 1
    a = Range("baseline").Value

    For looprun = 1 To 100

        randa = Rnd

        Range("baseline").Value = WorksheetFunction.NormInv(randa, a, stdeva)
        Application.Calculate

        net.Add Range("net").Value
        multi.Add Range("multi").Value
        curve.Add Range("curve").Value

    Next looprun

    Range("onepaste").Resize(net.Count) = Application.Transpose(net)
    Range("twopaste").Resize(multi.Count) = Application.Transpose(multi)
    Range("curvepast").Resize(curve.Count) = Application.Transpose(curve)
End Sub

NOTE: I have no idea if this code will run because I don't really know what you are doing and it's based on named ranges on a sheet.

 

[HackSlash]

I believe you could do something simpler like:

Range("onepaste").Value = Application.Transpose(Range("net").Value)

but again, I don't really know what you are doing.

 

[joejackson123]

Hi HackSlash, thanks very much for your response. I'm not familiar with collections so this is really interesting, and I'm going to look at the link you sent now.

On first run of the script you sent, it returned

Run-time error '1004' - Application-defined or object-defined error

, starting at the

[COLOR=#333333]Range("onepaste").Resize(net.Count) = Application.Transpose(net)[/COLOR]

line. The named cell onepaste is just a single cell, net is also a single cell. If you have any insight into that error, please let me know, otherwise thanks very much for your response!

 

[HackSlash]

Again, you never stated what this function is supposed to do.

What are you doing?

 

[joejackson123]

Apologies, the sheet itself cycles through an economic model by altering the values of a single cell (baseline).

The values stored in the array (or collections) are different values returned from cells in the sheet (or in the case of the curvearray, from a 10 column/single row range). The NormInv function is just to get a quasi-random number in the Range("baseline").

The arrays are pasted into a blank sheet, which I'm then going to use for graphing and visualizing how the values turned out.

So arrayone is a single column array with 100 rows/draws, arraytwo the same thing, and curvearray a 10 column array with 100 rows/draws.

 

[HackSlash]

You don't need arrays or collections to copy cells from one sheet to another. Just do it. You can transpose one set if it's not aligned with the others. All that can be done directly. You should use fully qualified references to do so. I can't tell you what those would be without seeing the sheet but the pattern is like this:

Workbooks("destinationWorkbook").Sheets("destinSheet").Range("destinationRange").Value = Workbooks("sourceWorkBook").Sheets("sourceSheet").Range("sourceRange").Value 
' This one will transpose:
Workbooks("destinationWorkbook").Sheets("destinSheet").Range("destinationRange").Value = Application.WorksheetFunction.Transpose(Workbooks("sourceWorkBook").Sheets("sourceSheet").Range("sourceRange").Value)

OR you can use copy\paste (Might be easier)

[COLOR=#ED1313]Public[/COLOR] [COLOR=#ED1313]Sub[/COLOR] CopyAndPasteSpecial() 
  [COLOR=#ED1313]Dim[/COLOR] sourceRange [COLOR=#ED1313]As[/COLOR] Excel.Range 
  [COLOR=#ED1313]Dim[/COLOR] targetRange [COLOR=#ED1313]As[/COLOR] Excel.Range 
  [COLOR=#ED1313]Set[/COLOR] sourceRange = ActiveSheet.Range(Cells(1, 1), Cells(5, 1)) 
  [COLOR=#ED1313]Set[/COLOR] targetRange = ActiveSheet.Cells(7, 1) 
  sourceRange.Copy 
  targetRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=[COLOR=#ED1313]False[/COLOR], Transpose:=[COLOR=#ED1313]True[/COLOR] 
[COLOR=#ED1313]End[/COLOR] [COLOR=#ED1313]Sub[/COLOR]