[SOLVED] How to pass an empty optional argument from Python to Excel VBA macro

How to pass an empty optional argument from Python to Excel VBA macro

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"

Solution

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