Easy Application.Object References

Those of you who have been following this blog should be familiar with the following code snippet, which sets an object reference to the Outlook.Application object.

Function GetOutlookApp() As Object
On Error Resume Next
  Set GetOutlookApp = GetObject(, "Outlook.Application")
  If Err.Number <> 0 Then
    Set GetOutlookApp = CreateObject("Outlook.Application")
  End If
On Error GoTo 0
End Function

The typical usage being:

' get Outlook
Dim olApp As Object ' Outlook.Application
Set olApp = GetOutlookApp
If olApp Is Nothing Then Exit Sub

I've generalized this code to allow you to set an object reference to any Application object for many of the MS Office products (Word, Excel, Outlook, PowerPoint, etc) from each other.

The following VBA function will return a late-bound object reference to the specified application's Application object from any program that supports VBA.

Function GetApp(AppName As String) As Object

' AppName can be one of the following:
' Word, Outlook, Excel, PowerPoint, InternetExplorer (no space), Access, Shell
' or any other application that can be created using "AppName.Application"

' if any error occurs, just continue
On Error Resume Next

' basic error check
If AppName = "" Then Exit Function

' if the application reference being sought is actually the current application,
' set a reference to the native application object
If InStr(UCase$(Application.Name), UCase$(AppName)) > 0 Then
  Set GetApp = Application
  Exit Function
End If

If (UCase$(AppName) = "OUTLOOK") Or (UCase$(AppName) = "POWERPOINT") Then
  ' PPT and OL can only have one instance running, try to hook it before
  ' creating new process
  Set GetApp = GetObject(, AppName & ".Application")
  If Err.Number <> 0 Then
    Set GetApp = CreateObject(AppName & ".Application")
  End If
  Exit Function
Else
  ' Word, Excel, Powerpoint, InternetExplorer, etc
  Set GetApp = CreateObject(AppName & ".Application")
End If

End Function

As explained in the comments above, The argument for this function can be any application that can be expressed in terms of 'name.Application', for example:

Word.Application
Outlook.Application
Excel.Application
PowerPoint.Application
InternetExplorer.Application
Access.Application
Shell.Application

and so on. I'm sure there are others, but these are the ones I came up with off the top of my head.

The code to release an Application object you've created is even easier.

Function ReleaseApp(App As Object, Quit As Boolean) As Boolean

On Error GoTo ExitProc

If Quit Then
  App.Quit
End If
Set App = Nothing

' if we got this far, assume success
ReleaseApp = True
Exit Function

ExitProc:
ReleaseApp = False
End Function

You simply pass in the object you created earlier, and use a boolean flag to indicate if the instance should be closed. You might do this for Excel or Word, that can have multiple instances running. Whereas with Outlook or PowerPoint, which can only have one instance running, you might not want to quit the application to avoid disturbing the end user that might not want it to close just yet.

Usage:

Sub test()

Dim App As Object
Dim success As Boolean

' get internet explorer reference
Set App = GetApp("Internetexplorer")

' App will equal Nothing if an error occurred in the function
If App Is Nothing Then Exit Sub

' release it and close instance
If ReleaseApp(App, True) Then
  MsgBox "All gone!"
End If

End Sub