Can Excel handle repeatedly creating and destroying controls

  • I am using Excel 2003 with all service packs. I run 6 workbooks with about 120 sheets and over 2500 controls. Recently I have been getting error messages saying that Excel cannot complete the task with available resources. The screen display goes haywire also. So I have written some code to create and destroy the 21 controls (8 Forms buttons, 9 Control buttons, 1 CheckBox, 3 ComboBoxes) as I enter and exit sheets. This will happen daily dozens of times. Will Excel be OK with this, or is it likely to corrupt the files?

  • Re: Can Excel handle repeatedly creating and destroying controls

    OK thanks for your input Rob. I think I will take the plunge and see how it goes. If I get any resource or memory errors I will try your suggestion.

  • Re: Can Excel handle repeatedly creating and destroying controls

    Each sheet is for a different person, and none of the controls are superfluous, they are all used regularly.

  • 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

    The 6 workbooks have about 2500 controls in total. They are all simultaneously open. Thanks Cytop I did not know you could do that! I had Handles 4019, User 259, GDI 9126 for Excel with just the 6 workbooks open. And yes the symptom is that the screen can't be drawn - the lettering gets huge and it's a real mess.

    I looked at the Best Practice article and did not see anything about controls. Clearly I'm hitting the limits though so I will try just creating them on demand and see how it goes.

  • 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

    Sorry for the delay, I have been away. I have been working on this project for 8 years and it has worked all that time so I have no desire to approach it from another direction. As I said each sheet is for a different person and each control is potentially and in most cases actually used. If I was really desperate I could replace the buttons with clickable cells but it would look bad and I am not there yet. Since it is possible for a user to push the GDI count arbitrarily high by opening archive files I do not wish simply increase the GDI limit, but thanks for the suggestion cytops and it is interesting to know why the problem is occurring. When the problem last occurred the GDI count was 9997 so it certainly seems that that is the problem. In addition I have spent 3 days grinding out the code to create and destroy the controls, so I definitely want to try it. I am hopeful that with so few controls a memory leak, if any, will be inconsequential or simply require closing and reopening all files every now and again.

  • 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

    Yes Rob mentioned using a form earlier. It would have to be huge and invisible but I suppose that could work. I will definitely look at that option if what I have done causes problems. Thanks for the heads up on the possible problems, I will just roll this out in the most heavily used location and see how it goes.

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!