0

I want to send an array of decimal numbers to python from VBA and return a set of them. Currently I can run a python script from a VBA macro and pass it string arguments but I can't send arrays.

I've looked into other ways to do this but can't seem to get any other way working. Also I'm trying to stay away from paid software like PyXll or anything similar.

I'm much more familiar with Python then VBA so I would preferer to work in Python and just use VBA to send and receive the data for the CSV file This is what I've got so far,


    Pythonexe = """C:\ ~~~ \python.exe""" 'path of the python.exe
    PythonScript = """C:\ ~~~ \ExcelToPython.py""" 'path of the Python script

    Dim ColumnLength As Integer
    Dim RowLength As Integer
    Dim counter As Integer

    counter = 0
    
    
    
    For RowLength = 0 To 10
        For ColumnLength = RowCounter
        
            PythonArg(counter) = (ThisWorkbook.Worksheets("location sort").Cells(ColumnLength, RowLength))
            counter = counter + 1
            
        Next ColumnLength

        objShell.Run Pythonexe & PythonScript & PythonArg
        counter = 0
        
    Next RowLength

I'm trying to get this as simple as possible as I will be ran on several different machines. Any help would be appreciated.

LikeMatt
  • 13
  • 6
  • You could use [xlOil](https://xloil.readthedocs.io/en/stable/) which is free and allows you to build UDFs / macros in python so you won't need VBA at all. If you don't want to install a package, you could write the array as a string then using one of the approaches [here](https://stackoverflow.com/questions/38886641/convert-string-representation-of-array-to-numpy-array-in-python) ? If the arrays are large you may hit length limits, then you could write to a CSV and use numpy.read_csv – stevecu Feb 04 '23 at 11:41

0 Answers0