Excel VBA – Explicitly Reference Current Application Instance

0

The following code is a function that takes as inputs the name of a target workbook to check if is already open as well as the excel application instance in which it may be open. The function will then cycle thru the open workbooks in that application instance. If the target workbook is open, then the workbook object is returned. If not already open, the target workbook is opened and then returned.

I am attempting to treat the “app” argument as an optional input, as normally there will only be one Excel instance. However, with the following format, I get the following compile error: “Constant expression required.”

Function wbOpen(currFile, Optional app As Application = Application) As Workbook

    Dim oWB As Workbook

    currFile = StrConv(currFile, vbLowerCase)

    For Each oWB In app.Workbooks

        oWBName = StrConv(oWB.name, vbLowerCase)
        If oWBName = currFile Then

            fileOpen = True
            Exit For

        End If

    Next oWB

    If Not fileOpen Then Set oWB = Workbooks.Open(currFile)

    Set wbOpen = oWB

End Function

Whenever I’m referencing the current Application within a method, I can simply write “Application.[method or property]”.

How do I explicitly reference the active Excel application instance so that I can have it as a default value for the “app” argument?

If possible, I’d prefer to not have “app” have no default value and then check if the value is nil in code.