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

Advertisement