The biggest thing is turning off calculation and screen updating.
But Charles Williams (links on my page) probably has more tips
on actually speeding up your actual coding..
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
"Luther Cifers" <luther...@cmcsg.com> wrote in message
Here are some guidelines :-
1. Avoid code that selects or activates. This is rarely necessary.
2. Try to avoid loops. They can often be avoided by the use of, for example,
worksheet functions in the code(e.g. Filter, Find, LookUp, etc.). In fact, where
possible, loops should be avoided like the plague!)
3. Reduce the number of "stops" (i.e. "." ) in your code by declaring and
defining variables, and by using code such as "With....End With"
5. Where possible, store objects in arrays so as to use one command on the array
rather than a command on each item in the array.
4. At the start of macros add the line Application.ScreenUpdating=False (although
not normally necessary if the code avoids selecting, etc.)
There are other techniques - experience is the best teacher.
The main factors that cause snail-pace code are :
-Failure to reduce code by efficient use of declaring variables
-Failure to avoid repetition of code - i.e. failure to reduce number of stops
-Failure to use arrays where appropriate.
Wow, that'll solve a million performance problems of mine. Do I need to
reset this switch to True at the end of the procedure?
(This is my first post so please let me know if anything odd happens)
"Dave" <Da...@MacKay.com> wrote in message
And sooner or later you will encounter things that don't reset itself on the
end of the code, like Calculation. And poof, Excel doesn't calculate anymore
and you get a lot of phonecalls ;-)
Best wishes Harald
"Simon" <amigo_...@yahoo.com.au> wrote in message
To anyone else who is not familiar with these methods, I suggest you try what
these guys have suggested. Works wonders for speed.
I ahve another program which switches from sheet to sheet about a jillion times
dunring execution. Once it almost hypnotized me I think! It is so bad I was
thinking of selling it as a recreational supplement! Could go nicely with some
colorful 70's tunes...
I'll have to work on that one, too. :)
If that really fixes a lot of performance problems then you probably
specifically have code that is cycling through in loops and changing
the activecell which is not good. (but it's less likely to be noticed)
If you find yourself coding with .Select and .Activate, seriously consider
modifying it, as in this example:
Selection.Value = 1
Selection.Font.Bold = True
you can use
Range("A1").value = 1
Range("A1")..Font.Bold = True
or better yet
.value = 1
.Font.Bold = True
Same idea with .Activate; avoid it where possible (almost always the case if
you want zippy code)
Declare all your variables as small as possible, ie byte or integer rather than
long or single. If you don't define them they will be variants which are the
slowest of all.
Read from and write back to worksheets all in one go. Don't read/write one cell
at a time
Don't use worksheet formulae in VBA, or use VBA in worksheet cells, if you want
real speed. There is a performance penalty
.. and experiment with different options to find bottlenexks in your code.
Extend Excel's abilities with free web and encryption code....
Do you know what can I use insetad of .Select or .Activate to force Excel to
recalcuta it's page breaks? I want to monitor if my output goes to more than
"David J. Braden" <dbr...@rochester.rr.com> wrote in message
NumPages = ExecuteExcel4Macro("Get.document(50,"")")
Note: Excel 4 was limited to workbooks with only one sheet; that's why the
function only works with the active sheet.
On Wed, 30 May 2001 11:17:30 -0400, "Emil Nikolov" <emil_n...@hotmail.com>
>Thanks for the tips,
>Do you know what can I use instead of .Select or .Activate to force Excel to
| NumPages = ExecuteExcel4Macro("Get.document(50,"")")
This doesn't work for me (gives error in Excel 5)
NumPages = ExecuteExcel4Macro("Get.document(50)")
will return the number of pages of the active sheet.
| Note: Excel 4 was limited to workbooks with only one sheet; that's why
| function only works with the active sheet.
=Get.document(50,"[Book2]Sheet3") - works even when book not active.
=Get.document(50,"Sheet3") - works even when sheet not active.
Both of the above do work in the MS Excel4Macro language.
While they still call it Excel4Macro language, the language was strongly
upgraded with MS Excel 5 and can do a lot of things typical new for
I only don't know how to get it working via VBA (but that's probably
because I don't know VBA).
NumPages = ExecuteExcel4Macro("Get.document(50,"Sheet3")") doesn't work.
NumPages = ExecuteExcel4Macro("Get.document(50,"[Book2]Sheet3")")
Maybe you can solve this by giving it a name in VBA (how to do so?)
If this can be solved, this then can apply for a lot more Excel4Macro
I assume that VBA can use/activate the latest version (Excel 5) of the
"Myrna Larson" <myrna...@home.com> schreef in bericht
Found the solution.
Its a seldom event (as non-VBAer) that I can contribute something in the
NumPages1 = ExecuteExcel4Macro("Get.document(50,"")")
Does Not work = gives Error
NumPages2 = ExecuteExcel4Macro("Get.document(50)")
Returns number of pages to be printed of active sheet.
NumPages3 = ExecuteExcel4Macro("Get.document(50,""Sheet2"")")
Returns number of pages to be printed of Sheet 2 even if not active.
NumPages4 = ExecuteExcel4Macro("Get.document(50,""[TEST.XLS]Sheet3"")")
Returns number of pages to be printed of File TEST.XLS Sheet 3 even if
The same approach can be used for lots of other Excel4Macro functions,
which allow the use of references.
"Desart Eric" <af...@belgacom.net> schreef in bericht
Depending on what you are doing, you may want to also consider using
You could have a template set up with most of the work already done.
(headers, footers, title rows, formatting, etc)
Suppose you are importing a large text file, and want to clean it up in
When you open a text file, the Text Import wizard puts the data on a new
sheet. This new sheet is not based on any Templates on your system, but
rather a default Template built into Excel. After the data is cleaned up,
you can spend a lot of programming time adding title rows, adding
Font.Bold=True to many cells, Adding Color, etc.
Or, you can also add a new sheet with all this already done. Then, just
copy the data to this new sheet.
Help says that this is one way to add a sheet to a workbook..
Sheets.Add after:=ActiveSheet, Type:=xlWorksheet
What is not documented is that "Type" can also be the name of a Template.
Sheets.Add after:=ActiveSheet, Type:="C:\MyTemplates\AllTheWorkIsDone.xlt"
This has not been talked about that I am aware of. I found this out by
Excel 2000 is even better because you can import external data directly onto
a specific sheet.
Another thing to consider is the use of Styles.
Instead of this...
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Name = "Arial"
.FontStyle = "Bold Italic"
.Size = 12
.ColorIndex = 5
I use this: MyRange.Style = "Headings"
I don't know if it is any faster, but it does make the code smaller. I
personally keep a list of Styles in Personal.xls. If a workbook doesn't
have what I need, it is just 1 line of code to Merge the Styles from
Personal.xls into the new workbook.
HTH. Dana DeLouis.
"David J. Braden" <dbr...@rochester.rr.com> wrote in message