Calling vba macro from python with unknown number of arguments

Question

The goal is to have a reusable python function which calls using win32.com a macro from VBA Excel, and do so using **kwargs, or other method that can pass unknown number of arguments.

No specific questions were using **kwargs and explicit win32.com, although some resembled questions were found, none have answers accepted, even if not exactly similar.
What follows are some resembled but not similar issues:

using python

def run_vba_macro(str_path, str_modulename, str_macroname, **kwargs):
    if os.path.exists(str_path):
        xl=win32com.client.DispatchEx("Excel.Application")
        wb=xl.Workbooks.Open(str_path, ReadOnly=0)
        xl.Visible = True
        if kwargs:
                xl.Application.Run(os.path.basename(str_path)+"!"+str_modulename+'.'+str_macroname,
                                          **kwargs)
        else:
              xl.Application.Run(os.path.basename(str_path)
                                          +"!"+str_modulename
                                          +'.'+str_macroname)
        wb.Close(SaveChanges=0)
        xl.Application.Quit()
        del xl

#example
kwargs={'str_file':r'blablab'}
run_vba_macro(r'D:\arch_v14.xlsm',
              str_modulename="Module1",
              str_macroname='macro1',
              **kwargs)
#other example
kwargs={'arg1':1,'arg2':2}
run_vba_macro(r'D:\arch_v14.xlsm',
              str_modulename="Module1",
              str_macroname='macro_other',
              **kwargs)

with VBA

Sub macro1(ParamArray args() as Variant)
    MsgBox("success the str_file argument was passed as =" & args(0))
End Sub

Sub macro_other(ParamArray args() as Variant)
    MsgBox("success the arguments have passed as =" & str(args(0)) & " and " & str(args(1)))
End Sub

Expected result is a MessageBox in VBAs:

success the str_file argument was passed as = blablab
success the arguments have passed as = 1 and 2

score 4 · Accepted Answer · answered Oct 01 '19 at 16:34

4

in python, kwargs is a dict, so it is not something that can be passed over COM. Pass the kwargs.values as an *args array, like so:

params_for_excel = list(kwargs.values())

xl.Application.Run(os.path.basename(str_path)+"!"+str_modulename+'.'+str_macroname,
                                      *params_for_excel)

answered Oct 01 '19 at 16:34

David Zemens

53,033
11
81
130

very good i think this answers well. Remember to fix the starred expression: should be `*params_for_excel,` cheers – Francisco Costa Oct 01 '19 at 17:54
@FranciscoCosta that doesn't seem necessary when I run it, but if you'd like to elaborate on *why* you think that's required, please do so and I'll consider the edit :) – David Zemens Oct 01 '19 at 18:33
Hmm interesting, so ParamArray can work with python positional args, *args. Sending tuples (ie `*params_for_excel,` works also) do you think there is any difference in using one or another? Ie passing a tuple or passing positional args into ParamArray VBA arguments. – Francisco Costa Oct 01 '19 at 19:11
I don't think any difference -- `*args` **is** a tuple. Adding the comma does not change that. `*params_for_excel` is a tuple whether there is a trailing comma. – David Zemens Oct 01 '19 at 19:42
Can you give a link for that ? Since **`*args` does not return a tuple accordingly** because `a=*[1,2],` returns tuple, whereas if you do not use the trailing commas you cannot do it. By intuition `*args` is not a tuple, it is like an "abstract sequence" of arguments, although not a Python sequence, `*` is used to designate the arguments are positional, which has meaning when used in functions to specify positional arguments. Unlike, the `*[1,2],` which is a tuple, here `*`is used to unpack the list and convert it into a tuple. – Francisco Costa Oct 01 '19 at 20:21
Also, despite being different constructs in this case both options are accepted, which means that you can pass an abstract sequence which is a bunch of objects `*args` or you can send one Python sequence object `*args,` and both situations are parsed into the same by `ParamArray args()`. – Francisco Costa Oct 01 '19 at 20:28
I think we're talking past one another here and I'm not in a position to explain myself rn. – David Zemens Oct 01 '19 at 20:29
Have not understood well what you mean by "talking past one another" although i consider you are trying to say "is that it is not related directly with the core question, and is hard to explain in comments" accordingly. If that is it i agree, and still the points i raised are completely valid. Otherwise please complete. If not further is added i will accept your answer (which was very useful and again thanks). – Francisco Costa Oct 01 '19 at 20:33
Hi, sorry. What I mean by "args is a tuple" is this: if a python function accepts `*args` as a parameter, and you inspect the `type(args)` within that function, you will see that it is a tuple. I don't observe any difference when adding the comma after `*params_for_excel` so it seems unnecessary to use that in this particular case. – David Zemens Oct 01 '19 at 21:33
Hi :) using `func(*params_for_excel)` we are sending n parameters (not a tuple `type(*params_for_excel)` has no meaning, it is rather a chain of arguments or parameters, are called positional arguments), and stored by ParamArray keyword as an array with n elements per n parameters (easy access). Using `func(*params_for_excel,)` sends one tuple object, stored as an 1 element array, and this element is another array with the n parameters. It's a bit convoluted and understanding this difference helps knowing better Python and VBA accordingly. If no further is added i accept answer.Cheers. – Francisco Costa Oct 01 '19 at 22:22
do this: `def func(*args): print(f'args is a {type(args)}')`, and tell me what is the result? It is a tuple *within* the function that accepts `*args`. That's all I meant. We're passing the parameter list denoted with the `*` to a VBA function that accepts a `ParamArray`. When we send to VBA `*params_for_excel`, that argument list is contained in the `ParamArray` object. – David Zemens Oct 01 '19 at 22:40
Why not accept it **now**? Regardless of what further minutiae we discuss, the answer solves the problem. Clearly there is some miscommunication here that is not solved by continuing to argue. I said `*args` when I meant "`*args`, as evaluated to `args` within the target function". (I thought [this](https://stackoverflow.com/questions/58188684/calling-vba-macro-from-python-with-unknown-number-of-arguments/58189013#comment102764348_58189013) would clarify, but apparently not). You understand that now, so why can we not let this argument alone? – David Zemens Oct 01 '19 at 23:40
"The `*args` will give you all function parameters as a tuple" [source](https://stackoverflow.com/a/36908/1467082). See also: [python dox](https://docs.python.org/dev/tutorial/controlflow.html#arbitrary-argument-lists) – David Zemens Oct 02 '19 at 00:35
Solves but not is very clear in explaining if anyone cares to do it is welcome here of course, Cheers. – Francisco Costa Oct 20 '19 at 17:19
"very good i think this answers well." (your first comment) and repeated follow-up comments promising that you'd "accept if no further answer" etc., But still you have not accepted. You've had ample time to ask for further clarification, but for some reason you have not, only leave vague comment that this answer is now somehow "not very clear". – David Zemens Oct 20 '19 at 17:30
Would make your answer accepted if i) you can give proof of your statement of: *args is a tuple, or ii) somehow agree correctly is *args is arguments notation accordingly. – Francisco Costa Oct 20 '19 at 20:05
Look back 3 or 4 comments I prove from python dox that args is tuple. – David Zemens Oct 20 '19 at 20:08
args is a tuple, yes. *args is not and i'm talking about *args. – Francisco Costa Oct 20 '19 at 20:10
Here is proof that ["`*args` is a tuple"](https://stackoverflow.com/a/36908/1467082) and also [here](https://docs.python.org/dev/tutorial/controlflow.html#arbitrary-argument-lists). This is according to a more or less standard naming convention, used in a particular context. OTOH, `args` as a generic python name (which isn't even part of this answer!!!) is, like every(?) other name in python, whatever you say it is, whenever you choose to define or redefine it. – David Zemens Oct 22 '19 at 13:28
Your link you posted writes: `*args` will give you all function parameters as a tuple. And other says `*args` is only a special parameter. Neither says is a tuple. If you test *args type you will see it is not even defined, you defended when simply other trying get explanations. Would like to accept your original answer, so give you another opportunity if you simply accepting `*args` is different of `args`, if you do will accept your answer, otherwise will offer answers along explanations accordingly. – Francisco Costa Oct 30 '19 at 16:14
Yes, `*args` (within a function signature) is arguments notation for an arbitrary number of arguments which are received as a tuple. They are not "the same thing", but `*args` notation indicates within the function body `args` is a tuple. A function with an `*args` parameter receives `args` as a tuple containing the positional arguments beyond the formal parameter list. This is as I explained on [Oct 1](https://stackoverflow.com/questions/58188684/calling-vba-macro-from-python-with-unknown-number-of-arguments/58189013?noredirect=1#comment102764348_58189013). – David Zemens Oct 30 '19 at 16:15
Really lol what a mess, it didnt't quite sound like although ok surely we're talking agreeing :) All is well, what is important is people politely explain their arguments, as was the case. Really it was perhaps not good communication, i guess it is part of the knowledge process and working with people so it is resolve and all well cheers. – Francisco Costa Oct 30 '19 at 16:17
Yes, what a mess! I think we have been talking the same point all along. Sorry for confusion. – David Zemens Oct 30 '19 at 16:22

Calling vba macro from python with unknown number of arguments

1 Answers1

Linked