VBA Application methods compatibility in Excel and Access

When working on VBA code that should work in both MSExcel and MSAccess VBA, the different application object methods can be hard to manage. For example, Application.Screenupdating = False is used in Excel but would raise a compile error in Access. The workaround is to use late binding so the host application will not check the validity of the methods until runtime. In the code below, I would use SetScreenUpdating(False) instead, and that code will work in both Excel and Acess.

Option Explicit
' Functions to apply system commands in either host using late binding for Application object
Private Const MSACCESS As String = "Microsoft Access"
Private Const MSEXCEL As String = "Microsoft Excel"
Function SetScreenUpdating(bOnOff) As Boolean
Dim oApp As Object: Set oApp = Application
Static bSetOnOff As Boolean
If oApp.Name = MSACCESS Then
' no way to get the status, just return the last status set
SetScreenUpdating = bSetOnOff
bSetOnOff = bOnOff
oApp.Echo bOnOff
ElseIf oApp.Name = MSEXCEL Then
SetScreenUpdating = oApp.Screenupdating
oApp.Screenupdating = bOnOff
End If
End Function
Function SetHourGlass(bOnOff) As Boolean
Dim oApp As Object: Set oApp = Application
If oApp.Name = MSACCESS Then
SetHourGlass = oApp.Screen.MousePointer = 11 ' 11 Busy (Hourglass) 1=Normal 0=Default
oApp.DoCmd.Hourglass bOnOff
ElseIf oApp.Name = MSEXCEL Then
SetHourGlass = oApp.cursor = 2
oApp.cursor = IIf(bOnOff, 2, -4143) ' xlwait=2, xldefault=-4143
End If
End Function
Sub testApp()
SetHourGlass True
SetScreenUpdating False
MsgBox "hi"
SetScreenUpdating True
SetHourGlass False
End Sub


About Patrick O'Beirne, Excel/VBA developer

Patrick provides consultancy and training in spreadsheet automation, data analysis, testing and model review; and the Excel spreadsheet auditing addin XLtest
This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s