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

No hard n soft rule...

---

There is no definite number of controls that VBA will limit you to.
It is entirely dependent on the system you run it on.

As a general rule of thumb:

• Reduce the number of controls.
• Use grid controls instead of arrays of buttons.
• Split it into logical forms for further simplicity.
• Use lightweight controls (handle-less controls) as they consume less memory.

Apart from this, if you still are using more than 100 controls on the screen (as you say you are) then its time you hired a new UI designer for the project.

GoodLUCK!!

PS: Do try and split the form up, if possible.
I can't imagine using any software that throws up 154 controls in one screen.
(MS-WORD comes pretty close ;-) )

---

UPDATE: Some stuff for your reference below...

• http://support.microsoft.com/kb/q177842/

Answer 2

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!

Answer 3

There is no hard limit for the maximum number of controls on a form. As mentioned in another answer, this will vary based on your hardware, Excel version, and operating system.

Sadly, I have had too much experience at building VBA forms with too many controls. :(

I can say, that once you get above about 200 controls, you may start noticing some strange/intermittent occurrences/errors.

One thing I have found, completely by trial and error, is that the Frame control, typically used to hold groups of radio buttons, seems to cause more problems than any other control. If I create forms without any Frame controls, I can get more controls on that form before running into trouble.

I have found that no matter how many controls you need, they can usually by categorized into different groups. If a particular group or category is going to contain more than a dozen controls (including labels) it is almost always better to have button for that category that will launch a sub-form. This really helps to reduce the complexity of the main form.

Answer 4

I try to stay away from userforms that are this big. I much prefer to use the spreadsheet to handle the data collection for things this big. If there are dependencies, then I hide/unhide rows as necessary, or use multiple sheets. I find it is an easier solution (sometimes).