I admit that allowing Excel to update the screen each time your macro makes a change, helps during development, but it slows down the code a lot.It is best to set the Screen Updating and Enable Events properties of the Application object to at the outset.I intend to write about the best practices in the coming weeks, but let us get back to the issue at hand.
Excel is versatile by itself and VBA makes it even better by allowing us to do our own thing.
Most of us use VBA to automate tasks of varying complexity – some macros are executed in a flash, but others take hours to run.
While there are users who are happy with just a If you are thinking, “Hey I can see exactly what is going on, why would I need to display messages?
”, you may have to consider digging into optimizing your code a little.
Status Bar = False 'Enable Screen Updating and Events Application. Enable Events = True End Sub Another popular method is to deploy a User Form and have a label control that displays a message.
We may also choose to use the width property of a control and resize it as the code progresses, to display a progress bar of sorts.
A fellow struggler, a good friend of mine, used to create dedicated forms for each of his macros, just to display a progress bar.
Users can display custom messages on Excel’s status bar when needed, and allow excel to take over after their macro finishes executing.
Here is a short example: '======================================================================== 'A Macro to Illustrate the use of Application.
Statusbad property '======================================================================== Sub Status Bar Example() 'Disable Screen Updating and Events Application. Screen Updating = False 'Declaring Sub Level Variables Dim lng Counter As Long Dim lng Number Of Tasks As Long 'Initilaizing Variables lng Number Of Tasks = 10000 For lng Counter = 1 To lng Number Of Tasks 'Altering the Statusbar Property Application.
Status Bar = "Executing " & lng Counter & _ " of " & lng Number Of Tasks & " | " & _ "Custom Message " & lng Counter Next lng Counter 'Letting Excel Take over the status bar Application.