UDF Can't return more than 255 chars???

[R Avery]

in the below code, both UDFs work if NUM_CHARS=100, but
the first one fails and the 2nd one succeeds if NUM_CHARS=1000.

The critical limit turns out to be 255. How do i make UDFs that return
arrays (with mixed string/date/double data) with strings that have more
than 255 chars? Is the only way to truncate all the strings to 255
chars?


Private Const NUM_CHARS As Long = 100

Public Function HOHO() As Variant
Dim v
ReDim v(0, 0) As Variant
v(0, 0) = String$(NUM_CHARS, "a")
HOHO = v
End Function

Public Function HOHO2() As Variant
Dim v As Variant
v = String$(NUM_CHARS, "a")

HOHO2 = v
End Function

 

[Ron Rosenfeld]

Private Const NUM_CHARS As Long = 100

Public Function HOHO() As Variant
Dim v
ReDim v(0, 0) As Variant
v(0, 0) = String$(NUM_CHARS, "a")
HOHO = v
End Function

Public Function HOHO2() As Variant
Dim v As Variant
v = String$(NUM_CHARS, "a")

HOHO2 = v
End Function

If you specify the member of the array, it seems to work OK.

In other words, in function1:

HOHO = v(0,0)


--ron

 

[R Avery]

I agree that if i specify the member, then it works, but that is not my
intent. the problem is not returning a single value but rather
returning an array. My real application returns an array with multiple
members, i just chose to use the single-item array for illustration
purposes.

 

[Ron Rosenfeld]

I agree that if i specify the member, then it works, but that is not my
intent. the problem is not returning a single value but rather
returning an array. My real application returns an array with multiple
members, i just chose to use the single-item array for illustration
purposes.

I seem to recall Harlan Grove writing about some obscure limitations that Excel
has in handling strings >255 characters under certain circumstances. It is
clear that, within the UDF, HOHO does, indeed, contain the long string.

Perhaps you could use a Sub to assign elements of the array to various cells.
Or perhaps someone else has a proper solution.
--ron