Moving numpy arrays from VBA to Python and back

user_1324967 0
Viewed 2256

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:

  1. The VBA script creates a csv file representing the table input.csv.
  2. The VBA script runs the python script through the command line
  3. 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.
  4. VBA waits until python is done, then loads output.csv.
  5. 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

numpy vba python
3 Answers

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.

user_45519810
Related