Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Returning strings longer than 255 characters from UDFs results in #VALUE error #456

Open
schoolie opened this Issue Apr 29, 2016 · 6 comments

Comments

Projects
None yet
6 participants
@schoolie
Copy link
Contributor

schoolie commented Apr 29, 2016

Referenced from this stackoverflow question. My answer detailing my understanding of the issue and a workaround for the specific example asked about is copied below:

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.

@fzumstein fzumstein added this to the short list milestone Apr 30, 2016

@fzumstein fzumstein added the bug label Apr 30, 2016

@DougAJ4

This comment has been minimized.

Copy link

DougAJ4 commented May 4, 2016

Playing with this I found that:

  1. The old ExcelWings code will transfer the string as a string, so this works (rtnstring just returns N copies of txt as a long string):
Function Rtnstring(txt As String, N As Long) As Variant
    On Error GoTo rtnerr:
    Rtnstring = Py.Var(Py.Call(Py.Module("xlwDict"), "rtnstring", Py.Tuple(txt, N)))
    Exit Function
rtnerr:
    Rtnstring = Err.Description
End Function
  1. It seems that either that way, or using the new Py.UDF function and extracting the string from the returned array, there is still a limit of 32,767 characters, which is 2^15-1. Much longer strings are returned to VBA, so presumably it is an Excel UDF limit.
@DougAJ4

This comment has been minimized.

Copy link

DougAJ4 commented May 4, 2016

Yes, it seems that 32k characters is the maximum that will fit in a cell (for 2007 and later):
http://superuser.com/questions/182721/excel-cell-length-limit

@thinker007

This comment has been minimized.

Copy link

thinker007 commented Oct 16, 2016

confirmed this is a bug

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

@su79eu7k

This comment has been minimized.

Copy link

su79eu7k commented May 15, 2017

Maybe my experience is related to this bug. Not only just 255+ char cell, when I return some DataFrame, #VALUE ERROR randomly(maybe it contains lots of characters?) occurred. But after I added .fillna('') to the code, the problem solved. Just for your information.

@fzumstein

This comment has been minimized.

Copy link
Member

fzumstein commented May 15, 2017

@su79eu7k can you please add some sample code to replicate the issue? thanks.

@spectereye

This comment has been minimized.

Copy link

spectereye commented Mar 14, 2018

thanks for the great tool!
met this issue again in xlwings 0.11.7, @thinker007 's solutions works, just little changes:
(@fzumstein you can easily replicate this issue, anyhow, my function at end which just join several cell contents)

In udfs.generate_vba_wrapper() from line 336
from

...
                else:
                    vba.writeln('{fname} = Py.CallUDF("{module_name}", "{fname}", {args_vba}, ThisWorkbook, Application.Caller)',
                        module_name=module_name,
                        fname=fname,
                        args_vba=args_vba,
                    )

to

...
                else:
                    vba.writeln('r = Py.CallUDF("{module_name}", "{fname}", {args_vba}, ThisWorkbook, Application.Caller)',
                        module_name=module_name,
                        fname=fname,
                        args_vba=args_vba,
                    )
                vba.writeln('ReDim strarray(UBound(r, 1), UBound(r, 2)) As String')
                vba.writeln('For i = 0 To UBound(r, 1)')
                vba.writeln('  For j = 0 To UBound(r, 2)')
                vba.writeln('    strarray(i, j) = CStr(r(i, j))')
                vba.writeln('  Next')
                vba.writeln('Next')
                vba.writeln('{fname} = strarray', fname=fname)

my udf

@xw.func
def tc(rng, delimit='/', hyphen=';'):
    """concatenate items id mainly for TeamCenter search keywords
    ";" is separator character of Teamcenter search, "/" is symbol before version number
    """
    rng = [str(item).split('.')[0].split(delimit)[0] for item in rng]
    rng = [x.replace('[', '').replace(']', '').strip() for item in rng for x in item.split(',')]
    rng = [item[:item.index('-')] if item.startswith('PV') else item for item in rng]
    rng = list(set(rng))  # remove duplication
    return hyphen.join(rng)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.