Maximum number of UDFs on one sheet

[Autiokari]

Hi, there seems to be a limit in Excel for the maximum number of UDFs on one sheet, only about 32704 cells can have a UDF. Is there a way to increase this limit?

Timo Autiokari

 

[Joe4]

there seems to be a limit in Excel for the maximum number of UDFs on one sheet, only about 32704 cells can have a UDF

Where are you getting that from? I can seem to use a UDF on every row (65536 cells).

 

[Autiokari]

I have this limit in both 97 and 2000. When I paste an UDF to a large selection (like one entire column) then only some 32704 will be pasted, the rest of the cells will be empty. When I then try to add an UDF to one additional cell then Excel refuses to acknowledge that it is an UDF (gives the #Name? error).

Timo Autiokari

 

[Joe4]

I am using Excel 2000 (U.S. Version) and do not seem to have that problem.

Can you post the UDF and the formula you are using?

You may have some other issues causing problem (did you DIM anything as an Integer that will get over 32,767? If so, you need to change it to Long).

 

[Autiokari]

Aha, these are Array UDFs, they take 3 cells as the input and also output 3 cells. I tried with a normal UDF and I too can use at least 65536 of them. Below is a code sample, the input values need to be in three cells on one row. Before the UDF is entered three cells (for the ouput data) must be selected, then the link to the input cell area is e.g. selected using the mouse and finally Shift+Ctrl+Enter has to be issued so that it will be an array function.

Public Function XYZ2Yxy(CIE_XYZ)
'Colorspace conversion, from XYZ to Yxy

Dim X, Y, Z, Sum As Double
Dim Yxy(1 To 3) As Variant

X = CIE_XYZ(1)
Y = CIE_XYZ(2)
Z = CIE_XYZ(3)

Sum = X + Y + Z
If Sum = 0 Then Sum = 1E-99 'hmmm

 Yxy(1) = Y
 Yxy(2) = X / Sum
 Yxy(3) = Y / Sum

XYZ2Yxy = Yxy

End Function

Timo Autiokari