How to call an Excel function from xlwings

Question

How do I call an Excel function (like sum, average, product and text) directly from xlwings?

Temporary solution:

The only approach I could come up with is to make a helper Excel book and have the following UDF boilerplate available to xlwings:

'Some extreme workarounds to get excel-like functions working ~95%
'This function is partly generated from a Python script
Public Function xlFunction(fName As String, _
                           Optional args As Collection = Nothing) As Variant
    If args Is Nothing Then
        Set args = New Collection
    End If
    
    'First try with Application.WorksheetFunction object
    On Error GoTo handleWSError
    Select Case args.Count
    
    Case 0
        xlFunction = CallByName(Application.WorksheetFunction, fName, VbGet)
    
    Case 1
        xlFunction = CallByName(Application.WorksheetFunction, fName, VbGet, args(1))

    Case 2
        xlFunction = CallByName(Application.WorksheetFunction, fName, VbGet, args(1), args(2))

    '... Case 3 to 29 left out 

    Case 30
        xlFunction = callByName(application.worksheetFunction, fName, vbGet, args(1), args(2), args(3), args(4), args(5), args(6), args(7), args(8), args(9), args(10), args(11), args(12), args(13), args(14), args(15), args(16), args(17), args(18), args(19), args(20), args(21), args(22), args(23), args(24), args(25), args(26), args(27), args(28), args(29), args(30))
    
    End Select
    
    Exit Function
    
handleWSError:
        'Put some effort into a nice message
        Dim formula As String
        formula = fName & "("
        Dim x As Variant
        
        For Each x In args
            formula = formula & argify(x) & ","
        Next x
        If endsWith(formula, ",") Then
            formula = Mid(formula, 1, Len(formula) - 1)
        End If
        formula = formula & ")"
        
        
        If err.number = 1004 Then
            err.raise err.number, err.source, "Error in Arguments: " & formula, err.helpFile, err.helpContext
        ElseIf err.number = 438 Then
            err.raise err.number, err.source, "No VBA equivalent in Application.WorksheetFunction for " & fName & ": " & formula, err.helpFile, err.helpContext
        Else
            err.raise err.number, err.source, "Error: " & formula & vbCrLf & err.description, err.helpFile, err.helpContext
        End If

End Function

Answer 1

As this is currently not implemented in the native xlwings API, you'd have to use the underlying pywin32 objects via the api attribute, see: https://docs.xlwings.org/en/stable/missing_features.html

In your case, something like this would work (building on the quickstart code):

import xlwings as xw

def main():
    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    sheet["A10"].value = wb.app.api.WorksheetFunction.Min(sheet['A1:B2'].api)