Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making code more efficient
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making code more efficient
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 "Tommi" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making code more efficient
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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Tommi" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making code more efficient
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. -- Jim Rech Excel MVP |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making code more efficient
Just a few more references for optimzing code.
Chip Pearson http://www.cpearson.com/excel/optimize.htm Optimizing for Size and Speed http://www.microsoft.com/officedev/a...pg/013/013.htm http://www.google.com/groups?hl=en&l...28cc66c&rnum=1 HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making code more efficient
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 "Tommi" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select or Not Select?
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 OPTION suggested: Range("A1").Font.Bold = True but what if your macro wants to delete certain row or something, not change its properties? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select or Not Select?
Sheets(2).Rows(18).Delete
Run it from anywhere in any sheet. -- HTH. Best wishes Harald Followup to newsgroup only please. "Cecilia Reyes" wrote in message ... but what if your macro wants to delete certain row or something, not change its properties? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making code more efficient
[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 In article , says... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making code less column-specific | Excel Worksheet Functions | |||
making code more efficient | Excel Discussion (Misc queries) | |||
Making A Code | Excel Worksheet Functions | |||
Book on how to write VBA code in a fast/efficient manner? | Excel Programming | |||
More efficient code | Excel Programming |