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

On 30 Jun 2006 09:53:06 -0700, "R Avery" wrote:

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

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.

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

In other words, in function1:

HOHO = v(0,0)


--ron

On 30 Jun 2006 10:50:23 -0700, "R Avery" wrote:

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.

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

In other words, in function1:

HOHO = v(0,0)


--ron

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