# Maximum number of UDFs on one sheet

#### Autiokari

##### New Member
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

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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).

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

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

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.

Code:
``````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

Replies
1
Views
67
Replies
9
Views
160
Replies
5
Views
301
Replies
1
Views
207
Replies
5
Views
175

1,203,696
Messages
6,056,764
Members
444,891
Latest member
MelissaBr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back