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

    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

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.