# User Defined Array Functions?

This is a discussion on User Defined Array Functions? within the Excel Questions forums, part of the Question Forums category; I'd like to define functions that can be used as array functions, so giving back not just a single value, ...

1. ## User Defined Array Functions?

I'd like to define functions that can be used as array functions, so giving back not just a single value, but a vector or matrix. but i don't know, how to implement such a function. the straightforward solution, returning a variant array, does not seem to work.

example:

Code:
```Function MyFn(R as Range) as Variant
MyFn = Array(1, 2, 3)
End Function```
if I apply it to the range A1:A3 as {=MyFn(B5)}, then all three cells become 1, and not 1, 2, 3 as needed.

2. ## Re: User Defined Array Functions?

I don't think this is possible until Excel 2002 (which is a higher version of VB, sorry don't know which one). But your code looks fine.

Hope this helps!

3. ## Re: User Defined Array Functions?

Hi,

Enter the Formula:

{=TRANSPOSE(MyFn(B5))}

This is because the Formula is entered in a Vertical Range.

4. ## Re: User Defined Array Functions?

Arrays can be returned by *all* versions of XL (that support VBA).

The transpose part is the key to making arrays work right. You can implement the transpose bit in the function itself.

{safety checks missing}
Code:
```with application
if .caller.rows.count>1 and .caller.columns.count=1 then
myFunc= .worksheetfunction.transpose(rsltArr)
else
myFunc=rsltArr
end if
end with```
Alternatively, always create a 2D matrix in your code and simply return that
Code:
```function myFunc(...)
dim rsltArr()
with application
if typeof .caller is range then
redim rsltArr(1 to .caller.rows.count,1 to .caller.columns.count)
else
...
end if
...
myFunc=rsltArr```

5. ## Re: User Defined Array Functions?

Sorry, I read that in a VBA book when I was trying to do it. I took it for truth and never tried to look into it again. (see my signature )

6. ## Re: User Defined Array Functions?

whoa, thanks guys!!

i'm surprised, however, because i thouhgt Excel will bomb me with #N/A if I use an array outside its defined area. in this case, the value was repeated, which is strange to me.

if I modify my code as

Code:
```Function MyFn(R As Range)
Dim X()
ReDim X(1 To 3, 1 To 1)
X(1, 1) = 2
X(2, 1) = 3
X(3, 1) = 4
MyFn = X
End Function```
it works perfectly, which surprises me, because it seems to be the same as before. maybe, excel checks for dimensions from the back? i mean, the last one is the column, the previous (if any) is the row?

7. ## Re: User Defined Array Functions?

jezus, one problem quickly after the other. is there a limitation on the size of the array??? it seems to me that the size should not exceed 5461 values, which is very near to 65536/12, 64K filled with variants.

8. ## Re: User Defined Array Functions?

I've often wondered where the 5461 limit came from and that explanation would be very good -- if a variant used 12 bytes! Unfortunately, it uses 16 bytes for numbers (or 22+ bytes for a string). And, an additional 12 bytes thrown in if the variant contains an array. In addition, an array by itself has an overhead of 20 bytes + 4 bytes per dimension. [See XL VBA help for 'Data Type Summary']

Also, that limit applied only to the XL-VBA interface. Neither product, by itself, has a 5461 limit for array size.

In any case, limit was relaxed with XL 2002.

9. ## Re: User Defined Array Functions?

Uhhh yeah, that's what I was thinking about.

10. ## Re: User Defined Array Functions?

Originally Posted by tusharm
I've often wondered where the 5461 limit came from and that explanation would be very good -- if a variant used 12 bytes!
i should know that variants are 16 bytes, but it was be so nice calculation, i hoped i remembered incorrectly :)

anyway, something is 12 bytes in size, because it can't be coincidence.

whatever it is, such a limitation is lame :(

again, thank you, men!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•