Speed up your macros.


Brian recently needed to speed up some vba code that he had written. He had a macro that was creating 100's of items and this can some take time to complete. Usually the part that adds a lot of time to the macro's operation is having to redraw each action on the screen. One of the fastest an easiest ways to make your macro fast is to use the Optimization property.

This property can turn the screen redraw of the program on or off. It's easy to use. Simply set it's value to True to turn it on, or False to turn it off.

Here's an example of a sub I use in some of my scripts that take advantage of this powerful feature.

Add this sub to your code. Paste it anywhere before or after an existing Sub:

Private Sub myOptimize(bUse As Boolean, Optional bIsStart As Boolean = True)
    If bUse Then
        If bIsStart Then
            Optimization = True
            EventsEnabled = False
            ActiveDocument.PreserveSelection = False
            ActiveDocument.PreserveSelection = True
            EventsEnabled = True
            Optimization = False
        End If
    End If
End Sub


Use like this:

myOptimize True, True 'turn it on

'do your stuff

myOptimize True, False 'turn it off

The reason the first parameter is there is so that you can put something like this if you have a form.

myOptimize frmMain.chkOptimize, True

'do your thing

myOptimize frmMain.chkOptimize, False


That way if a form checkbox to use optimization is on form you can toggle the use of the function.


you can set one value to turn it on and off while testing like this

Dim bFast as Boolean

bFast = True 'Optimization will be on

myOptimize bFast, True

'do your thing

myOptimize bFast, False


One of the main things you need to remember about using this function is that you have to turn off the optimization or you'll won't be able to see anything on the screen after the macro completes. After turning it off you also have to redraw/refresh the screen. Notice in the code above we use ActiveWindow.Refresh.

Also if your macro has an error and the macro exits before turning of the Optimization the user will not be happy about having to restart draw in order to get the program functioning normally again. In your error handler you can add the code to turn it off. The same goes for the Command Group function for undo's but this is another subject.

I figured this would be useful to many since it received a good response in Brian's Leaf mkaing macro post.