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.

<!-- language: vb -->

    Public Function GetExcelApp() As Excel.Application
    ' Returns open excel instance.
    '   If it doesn't exist, creates one to return
    On Error GoTo ErrHandler
    
        Set GetExcelApp = GetObject(, "Excel.Application")
        
    CleanExit:
        Exit Function
    ErrHandler:
        If Err.number = 429 Then 'no instance of excel is open
            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