Best,
- Luther
In article <3f5801c0e853$ba533a00$9be62ecf@tkmsftngxa03>,
"Luther Cifers" <luther...@cmcsg.com> wrote:
--
J.E. McGimpsey ar...@zptvzcfrl.pbz
ROT13 encoding, decode for real mail
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..
HTH,
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
news:3f5801c0e853$ba533a00$9be62ecf@tkmsftngxa03...
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 :
-Loops
-Selecting/activating
-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.
Best,
- Luther
.
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
news:22e301c0e85b$c457cbd0$9ae62ecf@tkmsftngxa02...
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
news:yRQQ6.2490$Yr1.1...@ozemail.com.au...
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. :)
- Luther
.
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)
"Slow Response"
http://www.geocities.com/davemcritchie/excel/slowresp.htm
If you find yourself coding with .Select and .Activate, seriously consider
modifying it, as in this example:
Instead of
Range("A1").Select
Selection.Value = 1
Selection.Font.Bold = True
you can use
Range("A1").value = 1
Range("A1")..Font.Bold = True
or better yet
With Range("A1")
.value = 1
.Font.Bold = True
End With
Same idea with .Activate; avoid it where possible (almost always the case if
you want zippy code)
HTH
Dave BRaden
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.
Dermot Balson
Extend Excel's abilities with free web and encryption code....
http://www.webace.com.au/~balson/InsaneExcel/Default.html
Best,
- Luther
.
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
one page.
e.
"David J. Braden" <dbr...@rochester.rr.com> wrote in message
news:B7396B31.16CEF%dbr...@rochester.rr.com...
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>
wrote:
>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
the
| 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
Excel 5.
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")")
doesn't work
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
Functions.
I assume that VBA can use/activate the latest version (Excel 5) of the
Excel4Macro language?
Kind regards
Eric
"Myrna Larson" <myrna...@home.com> schreef in bericht
news:tlmaht46imoc58a0s...@4ax.com...
Found the solution.
Its a seldom event (as non-VBAer) that I can contribute something in the
group.
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
not active.
The same approach can be used for lots of other Excel4Macro functions,
which allow the use of references.
Kind regards
Eric
"Desart Eric" <af...@belgacom.net> schreef in bericht
news:OiExOHb6AHA.2176@tkmsftngp07...
Depending on what you are doing, you may want to also consider using
Templates.
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
Excel.
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
accident.
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...
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold Italic"
.Size = 12
.ColorIndex = 5
End With
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
news:B7396B31.16CEF%dbr...@rochester.rr.com...