Moving numpy arrays from VBA to Python and back

Question

I have a VBA script in Microsoft Access. The VBA script is part of a large project with multiple people, and so it is not possible to leave the VBA environment.

In a section of my script, I need to do complicated linear algebra on a table quickly. So, I move the VBA tables written as recordsets) into Python to do linear algebra, and back into VBA. The matrices in python are represented as numpy arrays.

Some of the linear algebra is proprietary and so we are compiling the proprietary scripts with pyinstaller.

The details of the process are as follows:

The VBA script creates a csv file representing the table input.csv.
The VBA script runs the python script through the command line
The python script loads the csv file input.csv as a numpy matrix, does linear algebra on it, and creates an output csv file output.csv.
VBA waits until python is done, then loads output.csv.
VBA deletes the no-longer-needed input.csv file and output.csv file.

This process is inefficient.

Is there a way to load VBA matrices into Python (and back) without the csv clutter? Do these methods work with compiled python code through pyinstaller?

I have found the following examples on stackoverflow that are relevant. However, they do not address my problem specifically.

Return result from Python to Vba

How to pass Variable from Python to VBA Sub

If the VBA script is there just to create csv from your tables, pass it to Python, get back from Python, and delete the leftovers, then it looks like there is no need to run your code from VBA. Do you know you can [connect MS Access database in python](https://stackoverflow.com/questions/853370/what-do-i-need-to-read-microsoft-access-databases-using-python) using [PYODBC](https://pypi.python.org/pypi/pyodbc/) / [PYPYODBC](https://pypi.python.org/pypi/pypyodbc)? If you still need VBA for your goal, please explain. — Tehscript, Jul 17 '17 at 19:24

score 7 · Accepted Answer · answered Jul 19 '17 at 18:27

Solution 1

Either retrieve the COM running instance of Access and get/set the data directly with the python script via the COM API:

VBA:

Private Cache

Public Function GetData()
  GetData = Cache
  Cache = Empty
End Function

Public Sub SetData(data)
  Cache = data
End Sub

Sub Usage()
  Dim wshell
  Set wshell = VBA.CreateObject("WScript.Shell")

  ' Make the data available via GetData()'
  Cache = Array(4, 6, 8, 9)

  ' Launch the python script compiled with pylauncher '
  Debug.Assert 0 = wshell.Run("C:\dev\myapp.exe", 0, True)

  ' Handle the returned data '
  Debug.Assert Cache(3) = 2
End Sub

Python (myapp.exe):

import win32com.client

if __name__ == "__main__":

  # get the running instance of Access
  app = win32com.client.GetObject(Class="Access.Application")

  # get some data from Access
  data = app.run("GetData")

  # return some data to Access
  app.run("SetData", [1, 2, 3, 4])

Solution 2

Or create a COM server to expose some functions to Access :

VBA:

Sub Usage()
  Dim Py As Object
  Set Py = CreateObject("Python.MyModule")

  Dim result
  result = Py.MyFunction(Array(5, 6, 7, 8))
End Sub

Python (myserver.exe or myserver.py):

import sys, os, win32api, win32com.server.localserver, win32com.server.register

class MyModule(object):

  _reg_clsid_ = "{5B4A4174-EE23-4B70-99F9-E57958CFE3DF}"
  _reg_desc_ = "My Python COM Server"
  _reg_progid_ = "Python.MyModule"
  _public_methods_ = ['MyFunction']

  def MyFunction(self, data) :
    return [(1,2), (3, 4)]


def register(*classes) :
  regsz = lambda key, val: win32api.RegSetValue(-2147483647, key, 1, val)
  isPy = not sys.argv[0].lower().endswith('.exe')
  python_path = isPy and win32com.server.register._find_localserver_exe(1)
  server_path = isPy and win32com.server.register._find_localserver_module()

  for cls in classes :
    if isPy :
      file_path = sys.modules[cls.__module__].__file__
      class_name = '%s.%s' % (os.path.splitext(os.path.basename(file_path))[0], cls.__name__)
      command = '"%s" "%s" %s' % (python_path, server_path, cls._reg_clsid_)
    else :
      file_path = sys.argv[0]
      class_name = '%s.%s' % (cls.__module__, cls.__name__)
      command = '"%s" %s' % (file_path, cls._reg_clsid_)

    regsz("SOFTWARE\\Classes\\" + cls._reg_progid_ + '\\CLSID', cls._reg_clsid_)
    regsz("SOFTWARE\\Classes\\AppID\\" + cls._reg_clsid_, cls._reg_progid_)
    regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_, cls._reg_desc_)
    regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\LocalServer32', command)
    regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\ProgID', cls._reg_progid_)
    regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\PythonCOM', class_name)
    regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\PythonCOMPath', os.path.dirname(file_path))
    regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\Debugging', "0")

    print('Registered ' + cls._reg_progid_)


if __name__ == "__main__":
  if len(sys.argv) > 1 :
    win32com.server.localserver.serve(set([v for v in sys.argv if v[0] == '{']))
  else :
    register(MyModule)

Note that you'll have to run the script once without any argument to register the class and to make it available to VBA.CreateObject.

Both solutions work with pylauncher and the array received in python can be converted with numpy.array(data).

Dependency :

https://pypi.python.org/pypi/pywin32

wow, I don't use Python but it is impressive that the OLE Variant is inter-operable with Python arrays. — S Meaden, Jul 19 '17 at 21:20
Option 2 works great (exact code you gave), except that the result is some kind of [variant](https://msdn.microsoft.com/VBA/Language-Reference-VBA/articles/variant-data-type). How do I turn the variant into a table, recordset, or some other object I can use? Or is it already in some usable form? I'm just not familiar with VBA. — Paul Terwilliger, Jul 21 '17 at 19:13
The marshaler converts the python array to a 1 or 2 dimensions array of variants depending on what you return. It's possible to directly get an array from the recordset with `GetRows` but I don't think it's possible to directly add an array. So you'll probably have to loop over the array to add/update each record. — Florent B., Jul 21 '17 at 19:27
Note that you need to return a python array and not a numpy array. You can convert it back with `data.tolist()` or `numpy.asarray`. — Florent B., Jul 21 '17 at 19:43
The first solution is frankensteins monster.. the second works perfect though! Thanks a lot! — Lucas Raphael Pianegonda, Aug 22 '18 at 11:42

score 0 · Answer 2 · answered Jul 24 '17 at 12:27

0

You can try loading your record set into an array, dim'ed as Double

Dim arr(1 to 100, 1 to 100) as Double

by looping, then pass the pointer to the first element ptr = VarPtr(arr(1, 1)) to Python, where

arr = numpy.ctypeslib.as_array(ptr, (100 * 100,)) ?

But VBA will still own the array memory

answered Jul 24 '17 at 12:27

drgs

375
2
8

Wait, VBA stores arrays as column-major, numpy -- the opposite... Unless your array is 1-dim. – drgs Jul 24 '17 at 12:33

score 0 · Answer 3 · answered Jun 16 '20 at 10:47

There is a very simple way of doing this with xlwings. See xlwings.org and make sure to follow the instructions to enable macro settings, tick xlwings in VBA references, etc. etc.

The code would then look as simple as the following (a slightly silly block of code that just returns the same dataframe back, but you get the picture):

import xlwings as xw

import numpy as np
import pandas as pd

# the @xw.decorator is to tell xlwings to create an Excel VBA wrapper for this function.
# It has no effect on how the function behaves in python
@xw.func
@xw.arg('pensioner_data', pd.DataFrame, index=False, header=True)
@xw.ret(expand='table', index=False)
def pensioner_CF(pensioner_data, mortality_table = "PA(90)", male_age_adj = 0, male_improv = 0, female_age_adj  = 0, female_improv = 0,
    years_improv = 0, arrears_advance = 0, discount_rate = 0, qxy_tables=0):

    pensioner_data = pensioner_data.replace(np.nan, '', regex=True)

    cashflows_df = pd.DataFrame()

    return cashflows_df

I'd be interested to hear if this answers the question. It certainly made my VBA / python experience a lot easier.

Moving numpy arrays from VBA to Python and back

3 Answers3

Solution 1

Solution 2

Linked