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?
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()
Another approach is to change a value in a hidden sheet at the end of your python code and with a Worksheet_Change snippet which will fire off some vba code. If hidden sheet is "Supplement" then add something like this in end of python code:
Range('Supplement', 'A1').value = Range('Supplement', 'A1').value +1
And the vba in the "Supplement" sheet could be:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Worksheets("Main1").Range("A1").WrapText = True
End If
End Sub
Remember that Application.EnableEvents needs to be = True
A bit clunky, maybe, but also look at Application.OnTime method which lets you schedule code to run at a set time. So put your solver code into another proc, call RunPython and then schedule the solver proc for, say, 10 seconds time.
Or, possibly a better answer, get the python code to either write to a Range or text file when done. The proc with solver code will look at the range or the file to see if python is done. If python not finished then schedules itself to run again in N secs, otherwise runs the solver.
DoEvents(will let OS finish running tasks) andApplication.Wait()(will pause the execution). Because I'm not sure that you can get a return from your Python code to Loop on it while it isn't done