I am using Python to test an Excel VBA macro. The macro has two optional parameters. As long as only the last or both parameters should be omitted I am fine. I just pass the preceding parameters. I don't know how to handle the case when the first optional parameter is to be omitted, but the second included. I can't use None, as that is interpreted by Excel as "Null", which is a different case. I also can't leave out the argument as I get a syntax error in Python.
How do I omit an optional argument, that is not the last one, when running an Excel macro using win32com?
Minimum example:
Consider this macro:
Function getarg(Optional anArg, Optional anotherArg) As String
Dim s As String
If IsMissing(anArg) Then
s = "No argument 1 passed! "
Else
s = "Argument 1 was: " CStr(anArg) ". "
End If
If IsMissing(anotherArg) Then
s = s "No argument 2 passed!"
Else
s = s "Argument 2 was: " CStr(anotherArg)
End If
getarg = s
End Function
This is my Python code:
import win32com.client as win32
from os.path import join
xl = win32.Dispatch("Excel.Application")
wb = xl.Workbooks.Open(r"C:\Users\...\whatever.xlsm")
print(xl.Run("'{}'!{}.{}".format(join(wb.Path,wb.Name), "reportbuilder", "getarg"), "A", "B"))
print(xl.Run("'{}'!{}.{}".format(join(wb.Path,wb.Name), "reportbuilder", "getarg"), "A"))
print(xl.Run("'{}'!{}.{}".format(join(wb.Path,wb.Name), "reportbuilder", "getarg")))
print(xl.Run("'{}'!{}.{}".format(join(wb.Path,wb.Name), "reportbuilder", "getarg"), None, "B"))
The output is:
Argument 1 was: A. Argument 2 was: B
Argument 1 was: A. No argument 2 passed!
No argument 1 passed! No argument 2 passed!
There is no output for the fourth case, because in Excel I get "Run-time error '94': Invalid use of Null"
CodePudding user response:
An empty list will do the job:
import win32com.client as win32
from os.path import join
xl = win32.Dispatch("Excel.Application")
wb = xl.Workbooks.Open(r"C:\a\b.xlsm")
print(xl.Run("'{}'!{}.{}".format(join(wb.Path,wb.Name), "Module1", "getarg"), "A", "B"))
print(xl.Run("'{}'!{}.{}".format(join(wb.Path,wb.Name), "Module1", "getarg"), "A"))
print(xl.Run("'{}'!{}.{}".format(join(wb.Path,wb.Name), "Module1", "getarg")))
print(xl.Run("'{}'!{}.{}".format(join(wb.Path,wb.Name), "Module1", "getarg"), [], "B"))
returns:
Argument 1 was: A. Argument 2 was: B
Argument 1 was: A. No argument 2 passed!
No argument 1 passed! No argument 2 passed!
No argument 1 passed! Argument 2 was: B