Time Lag before macro code executes

[Stretchwickster]

I've been developing a VBA macro for Excel 2000 and when I execute it the usual macro warning form appears. After clicking the "Enable Macros" button it takes over 1 minute before it begins executing my code. Once it gets to this stage, code execution is completed very swiftly as I would expect. By the way, I am running Win2000 OS on a 400MHz PC with 128Mb RAM. I upgraded the memory to 256Mb RAM but this seemed to make very little difference.

I've run the same macro on a different system (650MHz PC with 128Mb RAM Win2000 and Excel97) and there is a lag of only 10 seconds between the macro warning form and code execution.

Can anyone suggest why there is this long delay on the 400MHz machine.

Does anyone have any tips for speeding things up on the 400MHz machine without upgrading e.g. changing virtual memory settings or something?
Clive

 

[m3bhp]

Sounds like you may have alot running in the background besides Windows and Excel. Is there Virus software and system utilities running. Lots of icons in the system tray usually means lots of backgound overhead for the CPU, the memory also gets filled with data that isn't necessary. Shut-down all unnecessary software and see the difference.

 

[Stretchwickster]

Thanks for the suggestion. I've tried getting rid of everything in the background and it makes a slight difference (maybe a second or two) but there is still this 60 second lag.

I wrote a function to log the system time at the start of my macro code, the end of it and various points in between.
My macro code takes only 6 seconds of the 68 seconds it takes from clicking "Enable macros" to the end of my macro.

There is something happening before it even accesses the VBA script.

I also tested on other systems and, interestingly, the lengthy lag only seems to occur on PCs running Excel 2000. Excel 97 executes everything in a respectable time, even on a slower processor. Clive

 

[MatthewGB]

Just a question Clive. Do you have any "control toolbox" controls on worksheets (not userforms)? I am asking because I have a problem which sounds very similar to yours. My spreadsheet takes about 1 minute to load up before running any code. I narrowed the problem down to the OLEObjects (controls) on my worksheets. The workbook takes about two seconds to load up per OLEObject. ie. there is about 30 OLEObjects all up, hence 60 sec load time.

If you have a similar set-up i would be interested in knowing.

Thanks

Matt

 

[Stretchwickster]

I have no "control toolbox" controls embedded on any of my sheets. I do create a command bar containing several buttons, to offer various options to the user. But I have no OLE Objects anywhere! Clive

 

[MatthewGB]

Even though you have no controls on your worksheets, your problem sounds suspiciously similar to mine. Yesterday I even tried a total reinstall of excel 2000 but with no success.

As you have previously mentioned, my program also works on Excel 97. It also works on an older computer on which i installed only excel 2000, but the same problem does not occur. Therefore I feel it may be due to corruption of certain excel-required files. I am unable to determine exactly which ones.

I am still looking into the problem and will keep u updated.

Cheers

Matt

 

[Stretchwickster]

I had a brain-wave: what would happen if I selected VB Editor -> Tools -> References and deselected OLE automation?

Absolutely nothing - still the same delay.

If anyone can help please let me know. Clive