slow loop! any advice??

[danmcdon]

I have a loop which currently runs 96*19*10 = 18,240 times and it's taking a long time (about 5 minutes). Is this to be expected? Anyone have any advice on how to increase efficiency? Thanks!!

Sub simulations()

'set timer
Dim StartTime As Double
Dim EndTime As Double

StartTime = Timer

Application.ScreenUpdating = False
    'Select toggle sheet to start

Dim iterations As Integer
Dim days As Integer
Dim startdate As Date
Dim securities As Integer

Dim i As Long
Dim j As Integer
Dim d As Long

Dim swap As String
Dim rows As Integer
Dim cols As Integer


iterations = Range("iterations")
days = Range("dates").rows.Count - 1
startdate = DateSerial(2008, 5, 30)
n = 1
securities = Range("swaps").Columns.Count

ReDim Output(1 To iterations) As Long
ReDim Results(1 To securities, 1 To days) As Long

For Each security In Range("swaps")

    swap = security
    Range("swap") = security

    
    'loop through valuation dates
    For d = 1 To days
    
        Range("ValuationDate") = Range("base").Offset(Application.Match(CLng(startdate), Range("dates")) - 2 + d, 0)
    
            'loop through iterations
            For i = 1 To iterations
        
                Range("toggle") = i
            
                Output(i) = Application.WorksheetFunction.Sum(Range("results"))
              
            Next
        
        On Error Resume Next
        Results(n, d) = Application.WorksheetFunction.Average(Output) / Range("spotvaluationdate")
        Erase Output
        ReDim Output(1 To iterations) As Long

    Next
    
    'increment swap number
    n = n + 1

Next

    'paste results
    rows = Application.Match(CLng(startdate), Range("dates"), 0) - 1
    Range(Range("base").Offset(rows, 1), _
          Range("base").Offset(rows + d - 2, securities)) = WorksheetFunction.Transpose(Results)



'display run time
EndTime = Timer - StartTime
MsgBox EndTime


End Sub

[westconn1]

please use code tags when posting code

how are you calling this procedure? is it called from some other procedure?

what is output used for? i can see values are assigned to it, but then it is redimmed

[danmcdon]

Sorry I'm new to the forum I'm sure I made all sorts of posting faux pas.

This procedure is not being called by another procedure. It is run on its own and the results (Result array) is pasted into the worksheet.

The output array is a temporary array used to store the results of each iteration, and then the average value of the values in Output is put into the Results array.

Thanks!

[westconn1]

then the average value of the values in Output is put into the Results array.

i missed that bit

do you have any code in worksheet change (or other) event?