CODEWITHSUNDEEP
xlwings
Jorge
Jorge

Reputation: 83

displaying >255 chars in excel cell

Excel shows #VALUE! when the my UDF returns more than 255 chars string.

xlwings is 0.7.1 and excel is 2007 which, as per Microsoft, can contain up to 32767 chars in a cell.

Where could be the problem?

Upvotes: 0

Views: 449

Answers (2)

Jorge
Jorge

Reputation: 83

Based on @schoolie's suggestion above of converting 2D Variant array to 2D String array, I modified the source of VBA function generation logic in my local xlwings:

In udfs.generate_vba_wrapper()

replace:

vba.write('{fname} = Py.CallUDF("{module_name}", "{fname}", {args_vba}, ThisWorkbook)\n',
                    module_name=module_name,
                    fname=fname,
                    args_vba=args_vba,
                )

with:

vba.write('r = Py.CallUDF("{module_name}", "{fname}", {args_vba}, ThisWorkbook)\n',
                                        module_name=module_name,
                                        fname=fname,
                                        args_vba=args_vba,
                                    )                    
                vba.write('ReDim strarray(UBound(r, 1), UBound(r, 2)) As String\n')
                vba.write('For i = 0 To UBound(r, 1)\n')
                vba.write('  For j = 0 To UBound(r, 2)\n')
                vba.write('    strarray(i, j) = CStr(r(i, j))\n')
                vba.write('  Next\n')
                vba.write('Next\n')
                vba.write('{fname} = strarray\n', fname=fname)

The other option is to patch the generated VB macro in VB editor after doing an 'Import Python UDFs'. However This change will be lost if you reimport. Code is already given above by @schoolie

Upvotes: 2

schoolie
schoolie

Reputation: 462

As best I can tell, Py.CallUDF (used by xlwings udfs) returns a 2D Variant array.

It also appears that for some reason returning a Variant array with string lengths greater than 255 from a pure VBA UDF results in a #VALUE error when called in excel. Placing a watch on the array in the VBA editor shows the data is intact, it's just not getting passed to excel correctly. A little searching returned several questions around max string lengths in VBA, but nothing that specifically addressed this issue.

Returning String arrays or single Strings with > 255 characters appears to work fine though.

Here are a few pure VBA examples showing the problem:

Return Variant Array:

Function variant_long_string(n)
    Dim temp(0 To 0, 0 To 0) As Variant
    temp(0, 0) = String(n, "a")
    variant_long_string = temp
End Function

Calling from Excel, returns (fails for N > 255):

255 aaaaaaaaaaaaa....aaaaaaaaa
256 #VALUE!

Return Element of Variant Array:

Function variant_long_string_element(n)
    Dim temp(0 To 0, 0 To 0) As Variant
    temp(0, 0) = String(n, "a")
    variant_long_string_element = temp(0, 0)
End Function

Calling from Excel, returns (succeeds for N > 255):

255 aaaaaaaaaaaaa....aaaaaaaaa
256 aaaaaaaaaaaaa....aaaaaaaaaa

Return String Array:

Function string_long_string(n)
    Dim temp(0 To 0, 0 To 0) As String
    temp(0, 0) = String(n, "a")
    string_long_string = temp
End Function

Calling from Excel, returns (succeeds for N > 255):

255 aaaaaaaaaaaaa....aaaaaaaaa
256 aaaaaaaaaaaaa....aaaaaaaaaa

Workaround

If your python UDF only returns a single string value, like this:

@xw.func    
def build_long_string(n):
    res = 'a'*int(n)
    return res

xlwings will autogenerate the following VBA Macro in the xlwings_udfs module:

Function build_long_string(n)
        If TypeOf Application.Caller Is Range Then On Error GoTo failed
        build_long_string = Py.CallUDF(PyScriptPath, "build_long_string", Array(n), ThisWorkbook)
        Exit Function
failed:
        build_long_string = Err.Description
End Function

As a quick patch to get your UDF working, changing that macro slightly to this:

Function build_long_string(n)
        If TypeOf Application.Caller Is Range Then On Error GoTo failed
        temp = Py.CallUDF(PyScriptPath, "build_long_string", Array(n), ThisWorkbook)
        build_long_string = temp(0, 0)
        Exit Function
failed:
        build_long_string = Err.Description
End Function

allows string >255 length to make it to Excel successfully. You could do something similar for an array result, you'd just have to convert the Variant array to a String array by looping/reassigning all the values from temp to the result.

Upvotes: 2

Related Questions