MACRO execution speed

[Guest]

I have a Workbook that is 7.2 MB. It has lots of images, macros, range names, etc. Whenever I try to write data to a cell or cells, or clear cell contents using a macro, it is maddeningly slow. I am currently trying to sort a list of only 44 values using a Userform list box and associated macro, putting the selected values in one column, and the unselected in another column. I could knit a sweater in the time it takes to execute. Are there any settings I need to make to enhance speed? Is the speed (even for such a small list) that much machine dependent? I am using a Pentium III 386 with 512 MB RAM. Is the execution speed being influenced by the size of the workbook? (I actually tried the same sorting routine in a separate Workbook, and the results are almost instantaneous!) Any feedback on this would be greatly appreciated

Eric

 

[Jim Rech]

The obvious question is, is calculation set to automatic? Less
obvious...are there any linked pictures (linked to ranges in particular)?
Is it slow manually entering data, or is it just by macro?
am using a Pentium III 386...

Of course machine speed is an issue. But a 386 and a Pentium are different
machines. I had a 386 in 1988 so I'm hopeful that's not what you have.

 

[Harald Staff]

Hi Eric

My guess is that you have lots of functions. Each time a single cell is
altered then everything recalculates. You can avoid that this way:

Application.Calculation = xlCalculationManual
'code here
Application.Calculation = xlCalculationAutomatic

If your code selects and activates then it's slow per se, and also demanding
on the graphical past of your system. Edit away all those things and try

Application.ScreenUpdating = False
'code here
Application.ScreenUpdating = True

That was the general stuff. Usually, combining the two above will increase
speed a lot. To optimize further we'd have to read the code.
--
HTH. Best wishes Harald
Followup to newsgroup only please

I have a Workbook that is 7.2 MB. It has lots of images, macros, range

names, etc. Whenever I try to write data to a cell or cells, or clear cell
contents using a macro, it is maddeningly slow. I am currently trying to
sort a list of only 44 values using a Userform list box and associated
macro, putting the selected values in one column, and the unselected in
another column. I could knit a sweater in the time it takes to execute.
Are there any settings I need to make to enhance speed? Is the speed (even
for such a small list) that much machine dependent? I am using a Pentium
III 386 with 512 MB RAM. Is the execution speed being influenced by the
size of the workbook? (I actually tried the same sorting routine in a
separate Workbook, and the results are almost instantaneous!) Any feedback
on this would be greatly appreciated.

 

[Guest]

Thanks H. Staff. Adding the Application.Calculation... did wonders. Postponed my going bald by another day!