I call a python script from VBA to do a fit that excel cannot do and scipy has a convenient function for it. So I use this code to call the function and get and set data in VBA:
Public Function GetData()
GetData = Cache
Cache = Empty
End Function
Public Sub SetData(data)
Dim ok As Boolean
Cache = data
End Sub
Sub LSQCBSplineIO(targetrng As Range)
Dim oShell As Object
Dim OutputArr() As Double
Dim InputArr() As Double
Dim ok As Boolean
Call getinputarray(targetrng, InputArr) ' This works, tested it
Set oShell = VBA.CreateObject("WScript.Shell")
' Make the data available via GetData()'
Cache = InputArr
Debug.Assert 0 = oShell.Run("<FullpathtoScript>.py", 0, True)
' Handle the returned data '
targetrng = Cache
End Sub
This does work according to the answer to this question.
The python code I call is the following:
import win32com.client
import numpy as np
import os
import matplotlib.pyplot as plt
import win32com.client
from scipy.interpolate import LSQUnivariateSpline, UnivariateSpline
print "Hello, User!"
# get the running instance of Excel
app = win32com.client.GetObject(Class="Excel.Application")
# get some data from Excel
data = app.Run("GetData")
ssc=np.array(data)
# do fitting ...
<insert fitting code here>
# return some data to excel
app.run("SetData")
The program is basically this:
VBA calls Python
Python calls VBA and gets data
Python does stuff
Python calls VBA and sets data
Python is done and VBA takes over again
If now Python has an error VBA will get stuck. VBA doesn't get an errormessage and hangs in there forever, causing excel to crash. So a bug in python means that I need to forcequit excel afterwards. The python script above is made to be called from VBA because only then the required data is at the right place for it to be gathered.
The problem is now: That this cannot be debugged in python and not in VBA.
In python the program doesn't work because it is not meant to work like that and in VBA I get no errormessage and the program gets stuck.
So how does one debug something like this?
OR even better
Is there a smarter way to move data between python and VBA?
Footnote: I initially used .txt files to intermediately, so python would have a input.txt and an output.txt. This was slow though and had issues running at full speed. See my other question.
