excel failed to access some objects.

[alanchinese]

I have been maintaining an excel vba program.
In the beginning, I had one large module. It breaks frequently. I found out some suggestions that the exported module size should be below 64k. Therefore, I splited the codes into multiple modules, making sure that each one is smaller than 40k.
However, as more controls were added to the form, it's size gets larger and larger. Now I have 450 controls. For some reason, the vba fails to access some of the newer controls (textboxes). If I commented out those lines, if will still fail in some other new controls.
Does VBA form has the limitation of number of controls? How do I resolve the form size issues?
The message I have always seen says, "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience." "The information you were working on might be lost. Microsoft Office Excel can try to recover it for you". It only appear during run-time and it's really INCONVENIENT!!!! Please help!

[Bob Phillips]

How can you possibly have a form with 450 controls. Nobody can update such a beast. My advice is starta again, with a new design.

[alanchinese]

The client requires the form to include 10 tabs, each tab shows 10 lines of control: 1 label to describe the line, 2 label to display the previous month data, 1 textbox for current month data entry. Adding frames, buttons, the number of control easily reached 400. VBA doesn't support customized controls like VB.NET. What can I do to split the form? I don't want to create 10 forms with repeated controls.

[Bob Phillips]

Well I don't know I am sure, but I would expect that if you have the memory available, a 450 control form is no big issue.

Can you post the workbook (he said hesitantly!)?

[alanchinese]

Actually, what's the limit of # of controls in a form? I did some search from online and came up the # to be 255... Is it true?

[alanchinese]

xld, thanks for your help, but unfortunately, it's probably not a good idea to post client related design... i probably need to recreate a similar form... however, the excel crashes when more controls are added to the form... do you think it's related to memory? i have 4GB and only opened that workbook but it still crashes.

[Bob Phillips]

255 is certainly not the limit. I knocked up a form last night with 10 tabs, 50 controls on each tab, and 2 extra controls. Of course, I didn't have any code to manage those controls, and it is probably the interaction between the two.

The problem that you will have in recreating it is that it won't crash. My test certainly didn't, and that was just with 2Gb.

[Bob Phillips]

Another thought, it might be worth cleaning your app

http://www.appspro.com/Utilities/CodeCleaner.htm

[alanchinese]

tried code cleaner already.
it's quite hard to re-produce the problem.
i have to save the file more than 10 times.
another thing is, if i modify the vba code, it wouldn't break the first time.
here is an easier way to re-produce it: tools -> macro -> securities -> Trusted Publishers, then uncheck the two checkboxes in the bottom.
it won't break during its debug.
it only breaks during run-time.
and when I went through the UserFrom.controls, the troublesome control's name is there. didn't break.
it will break when I tried to reference the object directly. i.e., MyTextbox.Enabled = true; or MsgBox MyTextbox.text
it breaks in both excel 2003 & 2007, with the latest updates
i believe it's memory problem.... this is killing me!