Making code more efficient

  • Thread starter Thread starter Tommi
  • Start date Start date
T

Tommi

Hello!
I have code, which manages lists (so the code will make heavy use of
for-loops, comparisons, sorting, filtering and that kind of stuff = quite
basic, but time-consuming).
Running my code will in some cases take quite a lot of time. In the
beginning of every procedure, I put Application.ScreenUpdating = False ( in
the end of procedure again True). I put also Application.Calculation =
xlCalculationManual in the beginning (and in the end again to Automatic).

Is there any other things, I should take into account, if I want my code run
more efficiently?

BR, Tommi
 
Tommi,

One thing that can slow down code considerably is using Select
and Selection. With rare exception, it is never necessary to
Select an item or work with the Selection object. Instead of
code like

Range("A1").Select
Selection.Font.Bold = True

reference the range directly:

Range("A1").Font.Bold = True


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi Tommi,
What you have already done are the two things that generally make
the most difference.

Take a look at my page and then follow the links to Charles Williams
site as well. His site is best viewed in Netscape (Mozilla), so if you
have that I would switch browsers before going there for intense reading.

Slow Response and Memory Problems
Speeding up Excel, Enhancing Performance
http://www.mvps.org/dmcritchie/excel/slowresp.htm

Chip Pearson has some pages on optimization
http://www.cpearson.com/excel/topic.htm
http://www.cpearson.com/excel/optimize.htm

If you have some specific concerns, include more information
for more specific answers.
 
The only other generality that comes to mind is to make sure page breaks are
not visible. Depending on what you're doing this can really drag
performance as Excel may have to hit the printer driver to recalculate them.

We'd need to see your code to determine if you could use faster techniques.
 
Tommi,
A few thoughts:

1. You will see a wide list of topics fro perfromance improvement in the
links provided in other responses . Of these, (after doing the basics like
no selects, etc), personal experience suggests that you should look at
clearing .emf files from you temp folder as a key step.

2. If you are not doing so, consider reading all your lists into VBA as
arrays (using single read technique). Do all the manipulations in VBA, and
then do array writes (single write technique) back to the sheets. While
cell-by-cell reads are slow, cell-by-cell writes or other on-sheet
manipulations are much slower (I think 8-10 times slower (?)).

3. Consider using collections to index your lists - I have found this to be
effective to avoid a large number of array search functions.

Hope this adds to the body of wisdom you will collect from your post.

Alex J
 
Somebody in another thread on efficient code said that the
using of the select and selection object can slow down the
exec. and perhaps (we could) reference the range directly


with SELECT
Range("A1").Select
Selection.Font.Bold = True
Click to expand...

OPTION suggested:
Range("A1").Font.Bold = True
Click to expand...

but what if your macro wants to delete certain row or
something, not change its properties?
 
[This followup was posted to microsoft.public.excel.programming with an
email copy to Tommi.
Please use the newsgroup for further discussion.]

In addition to the comments you've already received, consider
leveraging the XL object model whenever possible. Rather than use
loops to do whatever you are doing, use XL's objects/properties/methods
to speed up the process. In some cases, the results will be dramatic.

However, this is not easily explained in the abstract. If you were to
share some relevant code snippets...

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top