0

I have the following Python script named 'func.py':

import numpy as np

def func(x, y):
    return np.array([[x+y, x], [x-y, y]])

and I would like to use the function func with a macro in Excel, I wrote this code for the macro:

Sub CallPythonFunction()
    Dim x As Double
    Dim y As Double
    Dim wb As Workbook
    Dim result As Variant
    
    ' Get the input values from Excel cells
    x = Range("A1").Value
    y = Range("B1").Value
    
    ' Call the Python function using xlwings
    Set wb = ThisWorkbook
    result = RunPython("import func; func.func(" & x & ", " & y & ")")
    
    ' Write the result to Excel
    Range("C1").Value = result(1, 1)
    Range("C2").Resize(result.Rows.Count, result.Columns.Count).Value = result.Value
    
End Sub

However, it does not work. When the cells A1 and B1 in excel are filled with the numbers 1 and 3 respectively, I got an error message 'error 13, type mismatch'.

BigBen
  • 46,229
  • 7
  • 24
  • 40
lulufofo
  • 111
  • 2
  • 1
    Maybe `x` and `y` are being passed as strings? Convert them to ints either in the VBA or in Python... – Tomerikoo May 02 '23 at 13:56
  • 4
    `RunPython` [does not allow you to return values](https://docs.xlwings.org/en/latest/vba.html#function-arguments-and-return-values). – BigBen May 02 '23 at 13:57
  • Previously: https://stackoverflow.com/questions/39516875/return-result-from-python-to-vba – Tim Williams May 02 '23 at 16:27
  • Could I suggest giving [xlOil](https://xloil.readthedocs.io/) a look? (Disclaimer: i wrote it) There is no need to write the VBA stub as it can call the python directly. That said, xlwings can do a similar trick with the [func](https://docs.xlwings.org/en/latest/udfs.html) decorator. If you really want to call from VBA, then you can call the declared function with `Application.Run`. Appreciate this is a bit brief, let me know if you need clarification. – stevecu May 03 '23 at 12:29
  • Thanks @stevecu, I would like to try but I do not see any doc about the installation. – lulufofo May 03 '23 at 14:16
  • Try [here](https://xloil.readthedocs.io/en/stable/xlOil_Python/GettingStarted.html#getting-started) – stevecu May 03 '23 at 14:18

0 Answers0