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.