Can Excel handle repeatedly creating and destroying controls

Re: Can Excel handle repeatedly creating and destroying controls

One would like to think it would be ok. My guess is that there is a chance there will be a memory leak somewhere which will eventually chew up resources.
I would suggest you make a UserForm with your controls on and run it vbModeless.

Re: Can Excel handle repeatedly creating and destroying controls

Quote

I run 6 workbooks with about 120 sheets and over 2500 controls.

Far too many sheets and controls

Re: Can Excel handle repeatedly creating and destroying controls

Excel Best Practices

Re: Can Excel handle repeatedly creating and destroying controls

It's funny how the meaning of things change every time you read them... is that 6 workbooks with 2,500 controls per workbook or 2,500 spread over the 6 workbooks?

If the former, and you open more than 2 or 3 ot them, then you are hitting Windows resource limts on the number of handles, GDI objects and USER objects on a system wide basis - if the latter, you might be... depending on what other applications are open at the time.

One of the symptoms of this is that Windows (and under the bonnet, everything is an individual 'Window' - labels, textboxes, userforms - absolutely everything) can't be drawn on the screen...

I could go on and on about this, but it may not be your problem... a quick check though. Start Task manager and use the View/Select Columns menu option to add column for Handles, USER Objects and GDI Objects. View the entry for EXCEL on the Process tab and see if the USER and GDI counts are hitting 1,000's. Handles are less of an issue, but useful to know if you're delving deeper...

Re: Can Excel handle repeatedly creating and destroying controls

As this seems to be a Windows System issue rather than just Excel, I hope the Mods bear with me... after all, the affected system is Excel, but that's a little tenuous

There is a theoretical limit of 65,536 GDI handles per session (Windows session!), but the available max is lower as it is affected by available memory. There is also per process limit of 10,000 (or so). So it looks like you're bumping against that...

To make sure - have a look at the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Windows\GDIProcessHandleQuota in the registry using RegEdit (Usual comments about viewing the registry applies).

The good news is that if it is set to the default of 10,000, you can change it, up to a max of 65,536. But remember, that's per process and you have to trade off against other process' requirements. Based on your post figure of 9,126 I'd suggest trying 15,000 and see what happens. I'd consider that the max to ensure everything remains on an even keel - That's a personal preference based on experience rather than any concrete fact.

Remember that everything is a compromise. Depending on your processor, available memory and whether or not there’s an ‘R’ in the month changing a system setting can have unintended consequences. If you do change, stress test and validate everything to make sure other apps are not adversely affected.

Good sample code for reading/writing the registry here...

If you do change the registry, remember you'll have to re-boot the machine for the changes to take effect. Can't remember the number of times people change a system setting in the registry and then complain it still doesn't work...

Re: Can Excel handle repeatedly creating and destroying controls

Going to add a couple of points as observation on all of the above:

1) I understand you consider all the Controls and Worksheets necessary and certainly they are what you have become used to, but I would concur with Dave's somewhat direct comment and say that it is far too many sheets and controls for Excel or Windows to remain stable. The approach of creating and destroying controls may buy you some breathing space but is more likely to just test Excel's garbage memory collection to the limit.

2) If you follow Cytop's advice, be sure to back-up the registry before making any changes. While the change he proposes should not have any adverse effect, changing the Registry always carries some risk, if only from human error. Broken registry = Hours of grief. However again, if this does work for you, it is probably only buying you time.

3) I think you really need to reconsider how you are carrying out the larger task. Not the process of opening and closing Workbooks and Worksheets in Excel or editing the data on them, but the task that that process achieves. To have that many worksheets and require that number of controls to me suggests a task that needs to be approached from a new direction.

Re: Can Excel handle repeatedly creating and destroying controls

It sounds like these controls do the same function, just on a different sheet. Would one userform, that could be used (and perhaps customized) for each of the sheets possibly work? (Excel 2003 supports modeless userforms)

The title asks whether Excel can "handle" repeatedly creating and destroying controls. My experience is that heavy creation and deletion of controls (or shapes) makes for an unstable workbook. It can pass through testing and early use and then fall over for no apparent reason.

Re: Can Excel handle repeatedly creating and destroying controls

There are ways to create multi-page UserForms, however they cannot be 'invisible'. The whole purpose of a UserForm is to contain a set of controls that are used to modify your Workbook/Worksheets. My suggestion was to make it 'Modeless' that means you can have the UserForm open and move around Excel virtually as if the UserForm was not there. That means you can: select cells, select different Worksheets, perform almost any actions you require on the Workbook (although you need to be aware that the UserForm is there and that any of its events could be triggered by changes you make to the Workbook).

I can understand that with 8 years invested in a project that you are reluctant to 'take a different approach' as I phrased it. However you may possibly have to accept that it has outgrown Excel. Some things Excel does exceptionally well, and in my opinion it is one of the most polished of the Office applictions, but handling very huge amounts of data for many different users (which is what this sounds like) is not one of them.