What is the maximum number of controls that a VBA form can hold?

Question

I'm currently building an Excel 2003 app that requires a horribly complex form and am worried about limitations on the number of controls. It currently has 154 controls (counted using Me.Controls.Count - this should be accurate, right?) but is probably only about a third complete. The workflow really fits a single form, but I guess I can split it up if I really have to.

I see evidence in a Google search that VB6 (this usually includes VBA) has a hard limit of 254 controls in a form. However, I created a dummy form with well over 1200 controls which still loaded and appeared to work just fine.

I did get some 'out of memory' errors when trying to add specific combinations of controls though, say 800 buttons and 150 labels, leading me to think that any limit might be affected by the memory requirements of each type of control.

Does anyone have any information that might help ensure that I or, more importantly, other users with differing environments don't run into any memory issues with such a large form?

Answer 1

Most MSForms controls are windowless (as in they are not true windows, rather they are drawn directly by the VBA Forms engine as graphical objects) which are "light-weight" by comparison. This means you can dump more onto a Userform than you could using equivalent non-MSForms controls on a VB6 form.

I don't know what the upper limit is, but it will either be an absolute limit or a limit imposed by available resources, so if you can add 1,200 without encountering either of those & excel is behaving itself in terms of memory use you should be ok.

That said, that number of controls still seems an awful lot to present to the user at once!