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.
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaa

=myUDF_GetBig(A1)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaa


=myUDF_GetBig(myUDF_MakeBig(255))
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaa

=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

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??

--
Cheers
Nigel



"Greg Lovern" wrote in message
ups.com...
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.
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaa

=myUDF_GetBig(A1)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaa


=myUDF_GetBig(myUDF_MakeBig(255))
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaa

=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