GoBlue_MathMan GoBlue_MathMan - 5 months ago 35
Python Question

how to wait for xlwings to complete python code in vba?

I am calling RunPython in VBA from the xlwings module with the following code:

Sub Portfolio_Optimze()
RunPython ("import quotes; quotes.get_quote()")
SolverAdd CellRef:="$H$18:$H$24", Relation:=1, FormulaText:="$J$18:$J$24"
SolverAdd CellRef:="$B$15:$G$15", Relation:=4
SolverOk SetCell:="$H$16", MaxMinVal:=1, ValueOf:=0, ByChange:="$B$15:$G$15", _
Engine:=1, EngineDesc:=" GRG Nonlinear "
SolverSolve


How do i make the SolverAdd code wait until the RunPython code is completely finished?

Answer

Some info to start with: Excel on Mac only allows external processes to write to the cells when Excel is idle, i.e. while no Macros are running. That's why the RunPython call on Mac runs as background process that only really kicks in when the calling macro is finished running.

I would suggest one of the two following solutions:

1) Program the solver part in Python, too, for example using scipy.optimize.

2) Put the solver VBA into its own Sub and call it from Python. This is currently a planned feature for the next version of xlwings, see here, but you can work around for now like so (assuming wb is your Workbook):

app = xlwings.Application(wkb=wb)
app.xl_app.run_VB_macro('SolverMacro')

UPDATE, since v0.7.1 this is now properly supported:

>>> solver_macro = wb.macro('SolverMacro')
>>> solver_macro()