1

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Lucas Raphael Pianegonda
  • 1,151
  • 12
  • 29
  • What can't VBA do? – Tom Aug 22 '18 at 08:39
  • @Tom When calling python script above the VBA program above does not print the output from the python script. This way one is completely blind of what is going wrong on the python side. Is that a better explaination? – Lucas Raphael Pianegonda Aug 22 '18 at 08:42
  • Sorry I meant - Why are you passing it over to Python. What can't VBA do that you're using Python for instead – Tom Aug 22 '18 at 08:44
  • @Tom Ahhh.. gotcha: Scipy.LSQUnivariateSpline is the method I couldn't find for VBA and I decided rather to implement a python VBA interface instead of coding the LSQUnivarianteSpline in VBA by myself. – Lucas Raphael Pianegonda Aug 22 '18 at 08:47
  • 2
    ah fair enough. Have you thought about using something like [xlWings](https://www.xlwings.org/) so that it can all be done in Excel instead of passing it to an eternal application? – Tom Aug 22 '18 at 08:57
  • Have you tried using `pdb`? Do you see the `'Hello User!'` getting printed? – Dan Aug 22 '18 at 09:20
  • @Dan No I don't see the 'Hello User!'. I am trying to make it work but no success until now.. – Lucas Raphael Pianegonda Aug 22 '18 at 09:35
  • @LucasRaphaelPianegonda does you command prompt window stay open long enough to see what is output? Instead of `Debug.Assert 0 = oShell.Run(".py", 0, True)`, see if `ret_val = Shell(".py", vbNormalFocus)` works any better – Dan Aug 22 '18 at 09:46
  • @Dan Well that doesn't solve it completly but I see the shell now since I use pdb.set_trace and I see the 'Hello User!'. After the error the shell closes and VBA continues. It doesn't solve the error in the python script but I have hope now. – Lucas Raphael Pianegonda Aug 22 '18 at 10:24
  • @LucasRaphaelPianegonda but can you read the error now? If you can't, worst case, print something at every second line so you can at least find exactly which line is erroring – Dan Aug 22 '18 at 10:25
  • 1
    @Dan I implemented the second solution from this post: https://stackoverflow.com/questions/45105388/moving-numpy-arrays-from-vba-to-python-and-back This works like a charm and prints the errormessages into a VBA errormessage. This solves the issue then. – Lucas Raphael Pianegonda Aug 22 '18 at 13:39
  • 2
    Possible duplicate of [Moving numpy arrays from VBA to Python and back](https://stackoverflow.com/questions/45105388/moving-numpy-arrays-from-vba-to-python-and-back) – Lucas Raphael Pianegonda Aug 22 '18 at 13:41
  • Hi, so do you have the solution already, @LucasRaphaelPianegonda ? would appreciate an update on the thread. – sutan May 24 '22 at 10:47

0 Answers0