User-defined function that returns an array doesn't work in a named table

[chx77]

I made a user-defined function in vba that returns an array. It works fine in normal spreadsheets but failed when it is applied to a named table with error "#SPILL!" appears in the first cell. I'm sure there is no other content in the spill range. If I turn the normal spreadsheet range that the UDF works fine into a table structure the function became failed too with the "#SPILL!" error. I'm using Excel 365.

Here is a simple UDF to try (attached):

Public Function AAA(x as Integer) as Variant
Dim z(0 to 1) as Integer
z(0)=x +1
z(1)=x +2
AAA=z
End Function

Thanks for any advice.

chx77

 

[chx77]

sorry Just released I can't attach

an excel file. Here is any image

 

[Alex Blakenburg]

I am afraid that formulas that produce an array are just not going to work inside a table structure.

refer Microsoft Doc: Dynamic array formulas and spilled array behavior

Key points​

• Spilled array formulas are not supported in Excel tables themselves, so you should place them in the grid outside of the Table. Tables are best suited to holding rows and columns of independent data.

 

[Eric W]

You can't use a SPILL function directly in a table, but you can use INDEX to get a specific element from the array and put it in a table. Here are a few examples using your sample UDF:

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	Code	Function		Value	F(v,1)	F(v,2)
2	A	6		1	2	3
3	B	7		2	3	4
4	C	#REF!		3	4	5
5	D	#REF!		4	5	6
6	E	#REF!		5	6	7
7
Sheet27

Cell Formulas
Range	Formula
E2:E6	E2	=INDEX(aaa([@Value]),1)
F2:F6	F2	=INDEX(aaa([@Value]),COLUMN([F(v,2)])-COLUMN([Value]))
B2:B6	B2	=INDEX(aaa(5),ROW([@Function])-ROW(INDEX([Function],1))+1)

 

[chx77]

Hi Eric W
That's magical. Thank you for such an ingenious idea.

Also thanks Alex for the information.
Cheers
chx77

 

[Eric W]

Glad we could help!