My VBA code currently loops for 5,000 iterations and takes about 25 minutes to collect the output data in the Output excel sheet. Is there a way to reduce the time taken for instance by improving the copying and pasting of values, collecting the output data in some kind of array etc.? Given that I will be looping it for up to 10,000 iterations when I obtain more data
Note: The calculations are excel formulas within the Calcs excel sheet, as this is a cash flow schedule with irregular cash flows and discount rates, hence I am unable to automate the cash flow calculations via VBA.
Sub x()
Dim r As Long
Dim lastrow As Long
Application.ScreenUpdating = False
Application.CutCopyMode = False
With Worksheets("MCInput")
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
MsgBox lastrow - 1 & " Trials"
End With
With Worksheets("Calcs")
For r = 0 To (lastrow - 2)
.Range("CASHINPUT").Value = Worksheets("MCInput").Range("TCASHRTN").Offset(r).Value
.Range("EQINPUT").Value = Worksheets("MCInput").Range("TEQRTN").Offset(r).Value
.Range("FIINPUT").Value = Worksheets("MCInput").Range("TFIRTN").Offset(r).Value
.Range("Results").Calculate
.Range("Results").Copy
Worksheets("Output").Range("A1").Offset(1 + r, 1).PasteSpecial xlPasteValues
Next r
End With
Application.ScreenUpdating = True
Application.CutCopyMode = True
End Sub
End Sub
but no(Public | Private) Sub FooBar()
to start it. I'm assuming it goes above your variable declarations, but that could be incorrect. \$\endgroup\$Results
range calculating and why does it need to be calculated by Excel, as opposed to being computed by VBA code? If that calculation doesn't need to happen on the worksheet, then you can collect the inputs, perform all calculations, and dump all results at once, with 1 single worksheet read and 1 single worksheet write. \$\endgroup\$x
though? You see naming is one of the hardest things in programming: giving a meaningful name to a procedure that does too many things can even be impossible! Typically, procedure names start with a verb, and describe the purpose or side effects of the procedure... anyway it would be nice to have the formula for theResults
range, and enough context to be able to determine whether a formula whose result is copied and then pasted, is the most efficient way to go about it. \$\endgroup\$