I want to write a VBA macro in Excel to call a python script. The python script will execute some commands then return a pandas dataframe. I have code that writes to the excel spreadsheet and that is inside the python script.
I have installed xlwings and read through the VBA macro tutorial but am still confused. Should I use a User Defined Function? Do I need to "return anything" in the script?
If your Python script writes to the workbook directly, this is not difficult. First, you don't need to return anything from either the VBA macro or the Python if what you write to the workbook is sufficient for you.
You don't need a UDF. All you need is the following (which I've basically copied from the xlwings official docs here):
1) Make sure your excel book can talk to the file you have your script in (best done using the command xlwings quickstart <project_name> on the command line, then copying your script to the resulting Python file.
2) Make sure you can call everything you need from a single function like __main__() (or be prepared to run execfile or something similar)
3) Go to your excel book and make a command button, then assign it the macro SampleCall which xlwings has kindly provided for you
4) Now open the VBA editor. You should see the SampleCall macro immediately:
Sub SampleCall()
RunPython ("import testproj;testproj.world()")
End Sub
Just edit the names of testproj and world appropriately.
You can now click your CommandButton to execute your script!
