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).
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