Slow Execution Time Of Loop Macro Code

A9192Shark · Apr 8th 2008

Hello,

I have a work book that uses four inputs to change an analysis of a time series. The workbook also has a macro that will use 144 different cases (sets of the input variables) and save the results for graphing. The individual case works fine with a calculation time of 8-10 seconds, no problems there as it is processing 48 000 rows.

The macro simply loops through teh 144 cases and sets the input cells using four lines of code highlighted in teh code at the bottom. One line of the code looks like:

Code

Range("nmResults").Offset(lngRowNumber, 0) = Range("nmMonths")(lngMonth)

This line takes approximately 7 to 9 seconds to execute. Four lines like this plus a big calc times 144 starts to add up in execution time.

The problem is that the workbook has at times run very quickly but we can not get it to do so again.

Any ideas would be appreciated; I have looked at http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm for ideas and tested the Application.EnableEvents to no avail.

Thanks,

Alan.

Code

Option Explicit


Private lngMonth                As Long
Private lngHsNumber             As Long
Private lngDurationNumber       As Long
Private lngRowNumber            As Long
Private oldStatusBar
Private strHsStatus             As String
Private rngToClear              As Range
Private dtStart                 As Date
Private dtEFT                   As Date


Public Sub CalculateProbabilities()
    dtStart = Now()
    oldStatusBar = Application.DisplayStatusBar
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = False
    lngRowNumber = 0
    'Clear Results Area
    Set rngToClear = Worksheets("Probabilities").Range("A65536").End(xlUp)
    If Intersect(rngToClear, Range("nmResults")) Is Nothing Then
        Set rngToClear = Range(Worksheets("Probabilities").Range("A65536").End(xlUp), Range("nmResults").Offset(1, 3))
        rngToClear.ClearContents
    End If
    For lngHsNumber = 1 To Range("nmHsCritValues").Rows.Count
        Range("nmHsCrit") = Range("nmHsCritValues")(lngHsNumber)
        strHsStatus = "Hs=" & Format(Range("nmHsCrit"), "0.00") & _
                                "(" & lngHsNumber & "/" & Range("nmHsCritValues").Rows.Count & ")"
        If lngHsNumber > 1 Then strHsStatus = strHsStatus & " EFT: " & dtEFT
        Application.StatusBar = strHsStatus
        Debug.Print strHsStatus
        Application.Calculate
        For lngMonth = 1 To 13
            For lngDurationNumber = 1 To Range("nmDurations").Columns.Count
                lngRowNumber = lngRowNumber + 1
[B]                Range("nmResults").Offset(lngRowNumber, 0) = Range("nmMonths")(lngMonth)
                Range("nmResults").Offset(lngRowNumber, 1) = Range("nmHsCritValues")(lngHsNumber)
                Range("nmResults").Offset(lngRowNumber, 2) = Range("nmDurations")(lngDurationNumber)
                Range("nmResults").Offset(lngRowNumber, 3) = Range("nmP")(lngMonth, lngDurationNumber)[/B]            Next lngDurationNumber
        Next lngMonth
        'ThisWorkbook.Save
        dtEFT = dtStart + (Now() - dtStart) * Range("nmHsCritValues").Rows.Count / lngHsNumber
    Next lngHsNumber
    Application.Calculate
    Application.StatusBar = False
    Application.DisplayStatusBar = oldStatusBar
    Application.ScreenUpdating = True
End Sub

Display More

shg · Apr 8th 2008

Re: Range.offset Execution Time

You might use a range variable so VBA doesn't have to repeatedly evaluate the range expression:

Code

rResults.Offset(lngRowNumber) = rMonths.Offset(lngMonth)

Or a With statement:

Code

With Range("nmResults")
    .Offset(lngRowNumber, 0).Value = rMonths(lngMonth).Value
    .Offset(lngRowNumber, 1).Value = rCritValues(lngHsNumber).Value
    .Offset(lngRowNumber, 2).Value = rDurations(lngDurationNumber).Value 
    .Offset(lngRowNumber, 3).Value = rP(lngMonth, lngDurationNumber).Value
End With

An If / Else If statement is somewhat more efficient than a Case statement.

Added By Admin
Turn Off Calculations

A9192Shark · Apr 8th 2008

Re: Range.offset Execution Time

shg,
Thanks.
I will give that a go in the morning. I did wonder about the with statement, but why should it run fast sometimes and not otehrs????
I will optimise using Range variables and the with statement. I will also look at teh rest of the code to see if that can be improved.
Cheers,
Alan.

shg · Apr 8th 2008

Re: Range.offset Execution Time

Post back and let us know how you do.

Dave Hawley · Apr 9th 2008

If you wish to continue using this free service.

Please read the This Page which you agreed to when joining. In particular the point on Thread Titles. After you have done so please CLICK HERE and Private Message Dave Hawley with your acceptance, or non-acceptance of the rules. Please also include a link to this Thread. You can copy this directly from the Address bar of your Browser. When this is done your thread will be re-opened.

[fa]*[/fa]

Slow Execution Time Of Loop Macro Code

Participate now!

Share