VBA Alchemy: Turning Methods Into Properties

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.

Sample code

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.

Sample usage

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

Image by Valeryna from Pixabay