goFast Macro needs some experienced eyes on it

[Tim Hanson]

I wrote a goFast macro and need some input on it from those who know more then I do

I use like this:

Sub myMacro()
goFast False

Code

goFast True
End Sub

Do I have redundant elements in it e.g is
Case False
.Calculation = xlCalculationManual

Case True
.Calculation = xlCalculationAutomatic

The same as
Case False
.EnableCalculation = False

Case True
.EnableCalculation = True

Are there elements that would be good to have but I am missing or are there elements that I should remove?

Thanks for any input

Sub goFast(Optional iReset As Boolean = False, Optional EnableEvents As Boolean = True)
' Set various application properties.

   ThisWorkbook.Application.EnableEvents = True
    Select Case iReset
        Case False
          With ThisWorkbook.Application
              .Calculation = xlCalculationManual
              .ScreenUpdating = False
              .DisplayAlerts = False
              .CutCopyMode = False
             
              With ThisWorkbook.ActiveSheet
              .EnableCalculation = False
              End With
          End With
                     
      Case True
        With ThisWorkbook.Application
              .Calculation = xlCalculationAutomatic
              .ActiveSheet.UsedRange
              .ScreenUpdating = True
              .DisplayAlerts = True
              .EnableEvents = True
              .CutCopyMode = True
       
            With ThisWorkbook.ActiveSheet
              .EnableCalculation = True
            End With
        End With
    End Select
   
End Sub

 

[Hui]

Tim

What problems are you experiencing ?

The use of all those parameters in the subroutine is dependent on what your code is actually doing
so it is a bit hard to say what is right / wrong generically

 

[NARAYANK991]

Hi Tim ,

In general , putting Application.EnableEvents = False is not recommended , unless it is absolutely necessary and you know that leaving it on will create other problems ; this is because , in case it is turned off , and for any reason , Excel aborts execution of the macro , then the statement Application.EnableEvents = True will not get executed , as a result of which event triggering will remain turned off.

It is a different matter if you have a comprehensive error handler in place , which will ensure that Excel never aborts execution of the macro.

The same goes for Application.Calculation = xlManual.

The other two viz.

Application.ScreenUpdating = False
Application.DisplayAlerts = False

are not so dangerous , but neither are they so useful , unless your macro is doing a lot of moving between sheet tabs , inserting / deleting / hiding / unhiding rows / columns.

For normal Excel macros , these options will not significantly impact speed , though your case may be different ; it is always better to have these statements where ever they are required , than to put all of them in a procedure. If at all you want to retain this procedure , it would be nice to use the Status bar to display a message to this effect , so that it is clear that these options have been set to their non-default values.

The other setting CutCopyMode , is not a general-purpose setting , and setting it to True / False has no significance , since it is relevant only after a copy operation.

Narayan

 

[Hui]

It also appears that the excessive use of DoEvents should be avoided

 

[Tim Hanson]

Hui, Narayan, thank you both for the input.
I have modified the goFast macro in-particulate I have removed the EnableEvents . I am doing a lot of deleting, moving, matching across sheets and there is a very noticeable difference between the time it take to run my series of macros with and with out the goFast macro