Support calling globally defined macro functions with arguments from form controls

[liviofetahu-betm]

Issue Description.

When one defines a Python function which takes input arguments and decorates it with xloil.func(macro=True, command=True) so that it can be executed as an Excel VBA Macro in a Macro-Enabled Worksheet File (.xlsm), one is forced to use the setting WorkbookModule="*.py" in the xlOil.ini file in order for the Python function to be exposed as an Excel VBA Macro successfully.
If one needs this Python function to be exposed globally to Excel sessions (not just the workbook with the same filename as the Python module file), disabling the setting WorkbookModule="*.py" leads to the Python function not being recognized as a VBA Macro when one supplies the input arguments to the function in the Excel Developer Macros dialog box.
The article at (https://www.teachexcel.com/excel-tutorial/1893/pass-arguments-to-a-macro-called-from-a-button-or-sheet-in-excel#:~:text=We%20must%20type%20in%20the,to%20send%20to%20the%20macro.&text=Note%3A%20the%20text%20is%20surrounded,surrounded%20by%20single%20quotation%20marks.) provides instructions on how to execute Excel VBA Macros which take input arguments from the Developer ribbon, Macros dialog box. Regarding the example given below, the full literal string to run the macro would be
'write_single_kwarg_to_active_worksheet [{"username","liviofetahu-betm";"",""}]'
and note that the single quote characters at the beginning and at the end are required.

Reproducing Code Example

import xloil

@xloil.func(macro=True, command=True)
def write_single_kwarg_to_active_worksheet(**kwargs):
    kwarg_name = list(kwargs.keys())[0]
    kwarg_value = kwargs[kwarg_name]
    current_active_worksheet = xloil.active_worksheet()
    current_active_worksheet.at("A1").value = kwarg_name
    current_active_worksheet.at("B1").value = kwarg_value

Error message

When using the setting WorkbookModule="*.py" in the xlOil.ini file, the Run button in the Macros dialog box appears enabled and clicking it results in writing to the 2 cells in the active worksheet.
However, when the setting WorkbookModule="*.py" is disabled, the Run button is still frozen even after typing the call to the function in the Macro name text box.

xloil/Python/Excel/Windows version information

0.19.1 / sys.version_info(major=3, minor=11, micro=3, releaselevel='final', serial=0) / Version 2407 Build 16.0.17830.20210 / 10.0.19045.4894

[liviofetahu-betm]

Another important note: regardless of the Python function arguments' types, the VBA Macro cannot be run when disabling the setting WorkbookModule="*.py" in the xlOil.ini file. The reproducing code example I provided above simply happened to pick a case when the function takes kwargs. The same issue still occurs when the function argument is a pandas DataFrame, string etc.

[cunnane]

This seems to be a limitation in Excel.

If no arguments are needed it is possible to call a global macro without quotes, however, if quotes or arguments are passed, Excel needs the macro to be defined in VBA. The "WorksheetModule" setting causes VBA stubs to be generated in the associated worksheet when a companion py file is found which explains why things work with this enabled.

One workaround is to define macros without arguments. It may also be possible to create VBA stubs in a xla addin to keep Excel happy.

[liviofetahu-betm]

Thanks for looking into this, Steven. This is one of the typical use cases of xloil within my team.

So far, I'm keeping the setting WorkbookModule="*.py" enabled, and I execute the macro with user-defined arguments in the workbook with the same name as the Python file.

Do you think there might be space within the xloil library/framework for implementing logic that can globally (a.k.a. disabling WorkbookModule="*.py") expose macros which take user-defined inputs? It would be quite handy for a user to open a workbook with any filename, and be able to execute the macro with user-defined arguments.

[cunnane]

I tested my proposed workaround above (creating an xla file) and this does work. To replicate this xloil would need to dynamically create and load an xla/xlam file and populate it with vba stubs. This may be possible, I need to investigate further.

[liviofetahu-betm]

Thank you very much for looking into this, Steven!

This feature would be a huge plus to users who want to bring the full power of function signatures to global Excel sessions.

[cunnane]

Actually there's a much simpler approach here. Create an xlam addin with this function:

Public Sub Invoke(func As String, ParamArray P() As Variant)
 Select Case UBound(P) + 1
    Case 0: Application.Run func
    Case 1: Application.Run func, P(0)
    Case 2: Application.Run func, P(0), P(1)
    Case 3: Application.Run func, P(0), P(1), P(2)
    Case 4: Application.Run func, P(0), P(1), P(2), P(3)
    Case 5: Application.Run func, P(0), P(1), P(2), P(3), P(4)
    Case 6: Application.Run func, P(0), P(1), P(2), P(3), P(4), P(5)
    Case 7: Application.Run func, P(0), P(1), P(2), P(3), P(4), P(5), P(6)
    Case 8: Application.Run func, P(0), P(1), P(2), P(3), P(4), P(5), P(6), P(7)
    Case 9: Application.Run func, P(0), P(1), P(2), P(3), P(4), P(5), P(6), P(7), P(8)
    Case 10: Application.Run func, P(0), P(1), P(2), P(3), P(4), P(5), P(6), P(7), P(8), P(9)
  End Select
End Sub

Now you can run

'Invoke "write_single_kwarg_to_active_worksheet", [{"username","liviofetahu-betm";"",""}]'

Where this doesn't work is trying to assign a macro to a button - there seems to be a one argument limit.

Of course I can distribute the above addin with xlOil - I'll look into that for a future release

[liviofetahu-betm]

That would be great. We've continuously upgraded xloil since we started implementing with it.

Our use case for globally-enabled macros was a function that took a pandas DataFrame, a string, and kwargs, which we were able to call like
'get_exposure Evaluate("A41:BV43"),"Annual",[{"target_date","2024-09-11";"",""}]'
when we used the setting WorkbookModule="*.py".

[cunnane]

Are you able to test the above to see if it works for you?

[liviofetahu-betm]

Apologies for the delay in getting back, Steven!

Yes, the workaround you proposed worked for me. So far, I've been able to call my Python function as an Excel VBA Macro command in various workbooks without issues, with my Excel being upgraded constantly to newer versions on the background.

We have other users who are not very proficient in terms of using Excel, and they mostly rely on Python for automating some logic. Do you think there will be a fix sometime in the near future in the xloil package to handle calling globally defined macro functions with arguments from form controls?

If you plan to make a new release with such a feature, that would be a huge help for a lot of our users!

[cunnane]

Sorry I should give you a reply here,

Excel is built so that buttons must invoke VBA, there is no other possibility. In the case of form controls, you can specify a macro, but only one present in the workbook. For ActiveX controls, the macro must be called ButtonName_Click, but Excel auto-generates this stub for you.

I can see three options:

1. In VBA it's straightforward to invoke a (globally defined) python command with Application.Run myPyMacro, arg1, arg2, ...
2. With local functions, i.e. WorkbookModule, xlOil generates VBA stubs of the defined function name which directly invoke python.
   This would allow you to create a macro with an appropriate name for the button.
3. Hook the WorkbookOpen method to programmatically write a VBA stub similar to (2) to every opened workbook, or better search every opened workbook for a button with a certain macro name, then write the stub. This requires looking up the appropriate COM incantations, but is not difficult. Possibly xlOil could expose some of the code it already uses for this purpose.

It depends on the use-case, but (1) or (2) are the most transparent.