code execution speed question

[Gary Keramidas]

i have code that opens about 12 workbooks and scans for a value in a cell
and then copies that line to the report sheet. all of the usual settings set
to false and my code runs fine, finishes in about 7 or 8 seconds.

but have a question.

if i have the vb window open, click the run button to run the code, it
slows down and i can see all of the code windows for each workbook open.
report takes about 14 or 15 seconds and i can see it scroll through the 56
lines in each sheet in the immediate window. they scroll really fast.

but if i click the control toolbox button on the report sheet itself, the
report takes about a minute to run and i can see the row numbers as they
scroll by very slowly.

what's the difference between hitting the run code button in vb and clicking
on the button on the sheet?

sure is a big difference in code execution time.

 

[Simon Murphy]

Gary
Code often runs slower if the VBA window is open, possible reasons include
screen updating the VBE
The compiled state of your code
If you are saying calling from a button on the worksheet is even slower then
maybe there is some other screen refresh going on there too.
Generally I use the native Excel controls as they seem more reliable than
the activex ones.
Interesting observation though
cheers
Simon