How to Pass Arguments from xlwings to VBA Excel Macro?

Question

I was looking at How do I call an Excel macro from Python using xlwings?, and I understand it's not fully supported, but I Would like to know if there is a way to do this.

some like:

from xlwings import Workbook, Application
wb = Workbook(...)
Application(wb).xl_app.Run("your_macro("%Args%")")

Answer 1

This can be done by doing what you propose. However please keep in mind that the this solution will not be cross-platform (Win/Mac). I´m on Windows so below has to be adjusted to appscript on Mac. http://docs.xlwings.org/en/stable/missing_features.html

The VBA script can be called by following:

linked_wb.xl_workbook.Application.Run("vba_script", variable_to_pass)

Example: Let´s say you have a list of strings that should be used in a Data Validation list in Excel

Python:

from xlwings import Workbook

linked_wb = Workbook.caller()

animals = ['cat', 'dog', 'snake', 'bird']
animal_list = ""
for animal in animals:
    animal_list += animal + "|"

linked_wb.xl_workbook.Application.Run("create_validation", animal_list)

Excel VBA:

Public Sub create_validation(validation_list)
    Dim validation_split() As String

    validation_split = Split(validation_list, "|")

    'The drop-down validation can only be created with 1-dimension array.
    'We get 1-D from the Split above
    With Sheet1.Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:=Join(validation_split, ",")
    End With
End Sub