Test if Excel is open without relying on catching an error

Question

Getting a hook on an open instance of Excel is something I do all the time. I'm now questioning if I'm doing this the best way. I'm particularly concerned with the fact that I rely on the GetObject method to throw an error. I'd like to avoid it if possible.

I have a Utils module that contains a function that returns a brand new instance of excel and one that tries to get an open instance of excel. When I encounter Err.Number 429 in the GetExcelApp function, I call the CreateExcelApp function. Something I've done routinely for years suddenly feels dirty. There has to be a better way to do this.

Public Function GetExcelApp() As Excel.Application
' Returns open excel instance.
'   If it doesn't exist, creates one to return
On Error GoTo ErrHandler
Const ERR_APP_NOTRUNNING As Long = 429    

    Set GetExcelApp = GetObject(, "Excel.Application")

CleanExit:
    Exit Function
ErrHandler:
    If Err.number = ERR_APP_NOTRUNNING Then
        Set GetExcelApp = CreateExcelApp
    Else
        ShowErrorMessageBox
    End If
End Function

Private Function CreateExcelApp() As Excel.Application
' Returns New instance of Excel
On Error GoTo ErrHandler

    Set CreateExcelApp = CreateObject("Excel.Application")

CleanExit:
    Exit Function
ErrHandler:
    ShowErrorMessageBox
End Function

Private Sub ShowErrorMessageBox()
    ErrHandler.messageBox "Utils", "GetExcelApp", vbCritical, "Unable to get an instance of Excel."
End Sub

This code does also rely on this subroutine in an external module named ErrHandler.

Public Sub messageBox(moduleName As String, procName As String, Optional style As VbMsgBoxStyle = vbCritical, Optional OptionalText As String = vbNullString)
    Dim messageText As String
    messageText = "Module: " & moduleName & vbCrLf & _
                "Procedure: " & procName & vbCrLf & _
                Err.Description

    If Not OptionalText = vbNullString Then
        messageText = messageText & vbCrLf & OptionalText
    End If

    MsgBox messageText, style, "Runtime Error: " & Err.number

End Sub

And here is a quick example of how the GetExcelApp function is used.

Public Sub Test()
    Dim xl As Excel.Application
    Set xl = GetExcelApp
    xl.Visible = True
End Sub

I also read the MSDN documentation, but they're using On Error Resume Next... That's not even remotely acceptable.

Answer 1

As was pointed out in chat earlier, a module called Utils might as well be called Helpers or DumpAnythingYouWantHere.

You should prefer using vbNewLine over vbCrLf: vbNewLine will produce a platform-specific new line, while code that uses vbCrLf might only produce correct results under Windows, which means your comment about it being platform-independent is a false assumption.

I get the GetObject(, "Excel.Application") call to tap into the current instance (what happens when there are 2 or more instances already opened?), but the CreateExcelApp function could be much, much more straightforward than this:

Private Function CreateExcelApp() As Excel.Application
' Returns New instance of Excel
On Error GoTo ErrHandler

    Set CreateExcelApp = CreateObject("Excel.Application")

CleanExit:
    Exit Function
ErrHandler:
    ShowErrorMessageBox
End Function

Consider:

Private Function CreateExcelApp() As Excel.Application
    Set CreateExcelApp = New Excel.Application
End Function

At which point I'd question the need for this CreateExcelApp function! Why not simply handle that in GetExcelApp? Like the comments say, if it doesn't exist, creates one to return:

Public Function GetExcelApp() As Excel.Application
' Returns open excel instance.
'   If it doesn't exist, creates one to return
On Error GoTo ErrHandler
Const ERR_APP_NOTRUNNING As Long = 429    

    Set GetExcelApp = GetObject(, "Excel.Application")

CleanExit:
    Exit Function
ErrHandler:
    If Err.number = ERR_APP_NOTRUNNING Then
        Set GetExcelApp = New Excel.Application
        Resume CleanExit
    Else
        ShowErrorMessageBox
    End If
End Function

I don't like ShowErrorMessageBox. It's Private and single-use, with a hard-coded message that's only relevant for an error occurring in GetExcelApp. I'd get rid of that method.