UDF functions cannot return df as table in VBA function

[adebouille-mercuria]

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

[adebouille-mercuria]

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 Best regards Antoine de Bouillé | Gas data scientist intern | Mercuria Energy Trading SA | www.mercuria.com<http://www.mercuria.com/> T: +33 6 64 16 92 15 | M: +33 6 64 16 92 15 | 50 Rue du Rhône | Geneva 1204, Switzerland From: Felix Zumstein ***@***.***> Sent: Friday, May 17, 2024 5:12 PM To: xlwings/xlwings ***@***.***> Cc: Antoine de Bouillé ***@***.***>; Author ***@***.***> Subject: Re: [xlwings/xlwings] UDF functions cannot return df as table in VBA function (Issue #2438) 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<https://urldefense.com/v3/__https:/docs.xlwings.org/en/latest/quickstart.html*macros-call-python-from-excel__;Iw!!A41YHp6c7w!rEoFMtWmh5SoFAOtAtIkTUWOTu6ipzx-ilLBwzYqJ762opryhI0iqQisAm5TuBY8JsYJJJcYbghfYAMq76BWNI8aDA$> — Reply to this email directly, view it on GitHub<https://urldefense.com/v3/__https:/github.com/xlwings/xlwings/issues/2438*issuecomment-2117816912__;Iw!!A41YHp6c7w!rEoFMtWmh5SoFAOtAtIkTUWOTu6ipzx-ilLBwzYqJ762opryhI0iqQisAm5TuBY8JsYJJJcYbghfYAMq76AbvteK-Q$>, or unsubscribe<https://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/BGIMT67ACIXJKAFT5X2DQULZCYM4LAVCNFSM6AAAAABH2N23COVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMJXHAYTMOJRGI__;!!A41YHp6c7w!rEoFMtWmh5SoFAOtAtIkTUWOTu6ipzx-ilLBwzYqJ762opryhI0iqQisAm5TuBY8JsYJJJcYbghfYAMq76A4ySAv4A$>. You are receiving this because you authored the thread.Message ID: ***@***.******@***.***>> Please consider the environment before printing this email!

[fzumstein]

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