Slow Execution Time Of Loop Macro Code

  • 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:

    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 for ideas and tested the Application.EnableEvents to no avail.



  • Re: Range.offset Execution Time

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

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

    Or a With statement:

    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

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Range.offset Execution Time

    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.

  • 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.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!