Is ActiveX my boatanchor?

[Wizard]

I have a spreadsheet that has probably over 100 ActiveX buttons (added from the Controls menu, not the Forms menu), spread over a dozen worksheets.

The spreadsheet takes forever to load, save or close (2m30s, 0m45s, & 1m15s respectively), mostly while showing no progress whatsoever in the status bar. While it has a lot of macros in it, the file itself is not that large (4.4Mb) since it is a repository of macros & procedures, not data.

Could all those ActiveX buttons be weighing down the start & shutdown process to that extent? Would switching over to the "Forms" variety of button speed things up?

Thanks!

[Ken Puls]

Hi Wizard,

There's an easy way to test it. Save a copy of the file, nuke the buttons, and check the performance.

I'm curious to hear the outcome...

[Howard Kaikow]

Unless all the code is tightly related, the first thing I would do is to split the code among several workbooks.

Next, I would check whether there are Auto* macros or workbook macros that automatically execute. If so, see what they are doing.

If you have VB 6, re-arrange the code to use classes, then put the classes in ActiveX DLLs.

[Wizard]

Unless all the code is tightly related, the first thing I would do is to split the code among several workbooks..
The code IS tightly related. This is my development copy and while some region-specific pages & modules get removed when the project gets implemented in the field, it doesn't make a great bit of difference in launch/save/close times.


Next, I would check whether there are Auto* macros or workbook macros that automatically execute. If so, see what they are doing..
There is one, but it is very small & runs in under 1 second - it just checks the validity of 5 or 6 directory paths that it uses in the various procedures.


If you have VB 6, re-arrange the code to use classes, then put the classes in ActiveX DLLs.
I don't have VB6.