UDF -> 256-Char -> UDF = "#VALUE!"

[Greg Lovern]

If I create a UDF that returns a string longer than 255 characters, and
another UDF that accepts and returns a string, they work fine together
in separate worksheet cells, but if I nest the former in the latter in
a single formula, it returns "#VALUE!".

I've searched newsgroups and Microsoft's support site, and although I
see many articles that discuss various limitations of 255 with Excel,
none seem to address this particular problem.


Here are the test UDFs:

Function myUDF_MakeBig(length As Long) As String
myUDF_MakeBig = String(length, "a")
End Function

Function myUDF_GetBig(text As String) As String
myUDF_GetBig = text
End Function


Here are the worksheet formulas, with their return values:

=myUDF_MakeBig(256) <--This is in cell A1.
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

=myUDF_GetBig(A1)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


=myUDF_GetBig(myUDF_MakeBig(255))
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

=myUDF_GetBig(myUDF_MakeBig(256))
#VALUE!


Is this a bug or limitation? Is it documented anywhere?


Thanks,

Greg Lovern
http://PrecisionCalc.com
More Power In Excel

 

[Nigel]

Hi Greg
I experience the same as you - no surprises! (XP / Excel 2003)

I can pass a 32767 (integer max) string as a parameter into a udf, and
create the same length string into a cell (documented max). But not nested
in the way you describe - very curious - must be a limit somewhere??