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.