Hi.
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.SaveSettings ActiveDocument.PreserveSelection = False Else ActiveDocument.PreserveSelection = True ActiveDocument.RestoreSettings EventsEnabled = True Optimization = False ActiveWindow.Refresh 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.
or..
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.
~John
John
Thank you for sharing this one. Will definitely help increase the speed of my macros.