One of the best ways to speed up code execution in Excel is to turn off screen updating using the Application.ScreenUpdating property. You can do the same thing in Access by using the Application.Echo method.
Notice that I referred to the Excel version as a property and the Access version as a method. That means that we can check the status of screen painting in Excel, but we cannot do that in Access. This turns out to be an important difference.
I have several functions in my code library that temporarily turn off screen painting. The technique is not generally used to provide the sort of performance boost we see in Excel. Instead, it improves the interface by preventing the type of "form flashing" that occurs if we make rapid changes to the appearance of our forms.
A simple use case
I have a class module that provides advanced features for individually resizing form controls when the form itself is resized. When I was first developing this module, the visual effect was very unsettling. Whenever the form was resized, each individual control would resize on the screen one at a time.
Public Sub weForm_Resize() '... loop through and resize controls based on their Tag property... End Sub
This was a jarring user experience. To improve it, I would turn off screen updating, make my changes, then turn screen updating back on at the end of the function.
Public Sub weForm_Resize() Application.Echo False '... loop through and resize controls based on their Tag property... Application.Echo True End Sub
This improved the user experience substantially. I started using this technique in all of my code that was modifying the user interface. And that's when I started running into problems.
The problem came when I would call multiple functions that turned off screen painting in a row. The first function would turn off screen painting, make its changes, then turn screen painting on again. The interface would flash its update, then the second function would turn off screen painting again, make its changes, and finally turn screen painting back on for good.
Preserving screen painting state
The better way to handle this situation would be to save the screen painting status at the beginning of the routine, turn screen painting off, then restore the original screen painting status saved at the beginning of the routine. In Excel, this was straightforward:
Sub ComplexExcelProcess() Dim SavePaintStatus As Boolean SavePaintStatus = Application.ScreenUpdating '...run some complex calculations... Application.ScreenUpdating = SavePaintStatus End Sub
Perhaps you've already spotted the problem in Access. The code to turn screen painting on and off in Access is a method, which means there is no way to check its current status. Writing code like the example above is impossible in Access.
How did I handle the problem? I created a class module and wrapped the
Application.Echo method inside a class property. I used the Singleton pattern (without realizing that's what it was at the time) to maintain this piece of program state. I named this class clsApp and created a single public instance of the class declared with the New keyword so that it would always be available.
Here is an excerpt from my clsApp class:
'--== clsApp class module ==-- Option Explicit Option Compare Database Private m_bEcho As Boolean Private Sub Class_Initialize() Application.Echo True m_bEcho = True End Sub Public Property Get Echo() As Boolean Echo = m_bEcho End Property Public Property Let Echo(ByVal bEcho As Boolean) Application.Echo bEcho m_bEcho = bEcho End Property
In a separate standard module, I declared a public instance of the class like so:
Public App As New clsApp
I now had a way to check the status of my application's screen painting. The only requirement was that I would never use
Application.Echo directly in any of my code. I always use
App.Echo to set the screen painting flag now.
This allowed me to change my resizing code to this, which looks a lot like my Excel example from earlier:
Public Sub weForm_Resize() Dim SaveEcho As Boolean SaveEcho = App.Echo 'Save the current screen painting state App.Echo = False '... loop through and resize controls based on their Tag property... App.Echo = SaveEcho 'Restore the screen painting state End Sub