Copy-paste value loop within VBA array

[dr_gzus]

I wish to speed up my Monte Carlo simulation. I'm reading that dumping values generated by a loop into a VBA array, versus into Excel itself, followed by then dumping the completed array into Excel cells (if I need those values, which I do) is much faster. I've been reading about arrays all morning and I can't wrap my head around it. I believe that I want to create a two-dimensional array that copies a single row of live values and then pastes the value in 'iterations' number of rows. Then take that two dimensional array and paste it back into Excel. Am I thinking about this correctly? I've pasted my dysfunctional code below - what I'm getting is a "Subscript out of Range" error.

Dim Iterations As Long
Dim i As Long
Iterations = 10000
Dim Results As Variant
Results = Sheets("Probabilistic Sensitivity").Range("E18:CO" & Iterations).Value
For i = 1 To Iterations
Run Sheets("Calculation").Calc 'Separate, simple macro
Results(i, 0).Offset(i - 1).Value = Sheets("Probabilistic Sensitivity").Range("E6:CO6").Value
DoEvents
Next i
Range("E18:CO" & Iterations) = Results

 

[Greg Hitchon]

Hey,

No matter what your Option Base is set to (1 or 0) when you set an array from a range as (myRange=Range("A1:A10").value) then your variant (in this cast "Results") will be a 2D array of dimensions (1 to Number of Rows, 1 to Number of Columns).

As far as I can tell you just need to adjust your assignment to:

Results(i, 1).Offset(i - 1).Value = Sheets("Probabilistic Sensitivity").Range("E6:CO6").Value

 

[ChrisM]

Think of a 2-D array simply as a table of rows and columns, where in Results(i,x) i specifies the row and x specifies the column. Arrays are just holding places in system memory, and yes they run very fast.

What you read about relates to the fact that reading and writing data from a worksheet takes time, and yes it's much faster if you read/write all at once using an array. But to take advantage of the speed, you have to continue to work with the array. It seems your calculations are still dependent on the values in E6:CO6, which implies the root logic in your problem is embedded in the worksheet and not in VBA. Unless you move everything to VBA you probably won't see the speed bump you are expecting (but it depends on your code).

Please post the code for "Run Sheets("Calculation").Calc

This line will not work: Results(i,0).Offset...... You have mixed working with arrays and working with ranges. Offset and Value are range properties, they don't work with arrays.

Please explain in english pseudocode what you are trying to accomplish with the array. What I'm seeing is:

1. Read some worksheet values in to the array
2. Set up to loop 10000 times
3. For each loop run the Calc macro
4. That macro puts some kind of result in to range E6:CO6
5. I can't follow your code after this

 

[dr_gzus]

Ok, so I have to get the base/copied row into the 'top' row of the one array OR as a separate array that I read into the bigger array, if I read this right. Referring back to the sheet is messing it up.

The code for the "Calc" macro located in the "calculation" sheet, and is:

Sub Calc()


Application.ScreenUpdating = False


Sheets("Parameter Table").Range("K1") = True
Sheets("Parameter Table").Range("K1") = False


Range("F64") = 1
Range("M6") = Range("AC71")
Range("P6") = Range("AD71")
Range("R6") = Range("AE71")


Range("F64") = 2
Range("M14") = Range("AC71")
Range("P14") = Range("AD71")
Range("R14") = Range("AE71")


Range("F64") = 3
Range("M22") = Range("AC71")
Range("P22") = Range("AD71")
Range("R22") = Range("AE71")


Range("F64") = 4
Range("M31") = Range("AC71")
Range("P31") = Range("AD71")
Range("R31") = Range("AE71")


Range("F64") = 5
Range("M39") = Range("AC71")
Range("P39") = Range("AD71")
Range("R39") = Range("AE71")


Range("F64") = 6
Range("M47") = Range("AC71")
Range("P47") = Range("AD71")
Range("R47") = Range("AE71")


Range("F64") = 7
Range("M55") = Range("AC71")
Range("P55") = Range("AD71")
Range("R55") = Range("AE71")


Range("F64") = 8
Range("M64") = Range("AC71")
Range("P64") = Range("AD71")
Range("R64") = Range("AE71")


End Sub



In English, it performs a simultaneous random draw of each model parameter, then iteratively updates a single cell tied to a bunch of IF() equations, and records the values of the output for each of those 8 iterations (it's for 8 populations of patients, using a single Markov setup instead of 8). The range E6:CO6 values are equal (formulaically "=Sheets("Calculation").Range("...")") to the values the Calc macro generates.

So I want the array macro to somehow take this output found on E6:CO6 and record the value resulting from that random draw iteration of the 10,000, row by row, and spit all (10000 x 89 columns = 890,000) datapoints back into the worksheet for other analyses.

And everything you guys is saying is making great sense, thank you!

 

[dr_gzus]

Or put another way, my old code was:

Dim Iterations As Integer
Dim i As Integer
Iterations = 10000
For i = 1 To Iterations
Run Sheets("Calculation").Calc
Range("E18:CO18").Offset(i - 1).Value = Range("E6:CO6").Value
DoEvents
Next i

It's sorta fast, but I'm trying to do essentially this same thing in a VBA array. More fast.

 

[ChrisM]

Ok, unless you move all the =IF logic in to VBA, I'm not sure you will get the speed bump you are hoping for...but...give it a shot and maybe it will speed things up.

First, look at optimizing your Calc code:

1. Do you need to set these values 10000 times over and over?
Sheets("Parameter Table").Range("K1") = True
Sheets("Parameter Table").Range("K1") = False

If not, put them in your main sub, before the loop starts

2. You can speed up your range references by using WITH:
Range("F64") = 1

This forces Excel to lookup the ActiveWorksheet everytime you call it. Better to at least specify the worksheet (as you did in #1 above), or use With

With Sheets("mysheet")
.Range("A1") = .Range("B1")
End With

3. There is no reason to call Application.ScreenUpdating 10,000 times either, call it once in your main sub

Ok, now on to your array:

All you need to do is:

1.create an empty array of 10,000 x 89
2. create a temp array to hold one loop's data (you can't assign directly from the worksheet to a set row of the big array)
3. Each time the loop runs write the results to the temp array
4. Write the temp array row to the big array
3. When all done, write the entire big array to worksheet

So the code for that is:

Dim Iterations As Long
Dim i As Long
Dim j as Long
Dim Results As Variant
Dim aTemp as Variant

Iterations = 10000
Redim Results(1 to Iterations, 1 to 1)
With Sheets("Probabilistic Sensitivity")
  For i = 1 To Iterations
      Run Sheets("Calculation").Calc 'Separate, simple macro
      aTemp = .Range("E6:CO6").Value
      For j = 1 to 89
           Results(i,1) = aTemp(j,1)
      Next j
  DoEvents
  Next i
End With
Range("E18:CO" & Iterations) = Results

I've got to cut this short, hopefully that gives you an idea. I may have the columns/rows backwards on those arrays, I always forget which is which when copying arrays to ranges.

 

[dr_gzus]

(Expletive) that's fast! By orders of magnitude! So I did have to switch the the order of aTemp(j,1) to (1,j) for it to work (otherwise a subscript out of range error), and it burns through. BUT I'm getting the exact same value in each of my 890,000 resultant cells and not the random draw I'm looking for.

I can probably figure it out from here. Seriously dude, thanks.