UDF functions cannot return df as table in VBA function

[antoinedeb]

OS: Windows 10

Versions: xlwings 0.31.1, Python 3.10

Hello, I have defined a UDF py_return_df which works great as an Excel function. However, I want to use it in a VBA code linked to a button. In this case I get error, while it is the same function. Moreover, I managed to use simple UDF in VBA, but the ones returning tables don’t seem to work in VBA.

Thanks in advance for your help

Python UDF function

@xw.func
@xw.ret(header=True, index=True, expand='table')
def py_return_df():
    return pd.DataFrame(np.random.randn(5, 3), index=pd.date_range('1/1/2000', periods=5), columns=['A', 'B', 'C'])

VBA Sub

Sub Button4_Click()
    Dim ws As Worksheet
    Dim resultCell As Range
    Dim printDateResult As Range

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Define the target cell where the result will be placed
    Set resultCell = ws.Range("D60")

    ' Call the Python function to print the date
    Set printDateResult = py_return_df()

    ' Check if the result is not empty
    If Not printDateResult Is Nothing Then
        ' Place the result in the target cell
        printDateResult.Offset(1, 0).Resize(printDateResult.Rows.Count - 1, printDateResult.Columns.Count).Copy
        resultCell.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    Else
        ' If the result is empty, display an error message
        MsgBox "Error: The result is empty."
    End If
End Sub

Error message in Excel

[fzumstein]

What's your use case to do this instead of using RunPython? See https://docs.xlwings.org/en/latest/quickstart.html#macros-call-python-from-excel

[antoinedeb]

I have very big functions with a Gurobi optimization. I need to give several parameters from the excel I have very big functions with a Gurobi optimization. I need to give several parameters from the excel to run the optim, which doesn’t seem possible with RunPython

[fzumstein]

Please provide (pseudo) code that reflects your actual use case.