Writing code for fast execution

[Luther Cifers]

Are there some techniques for writing code that will make the execution faster?
I'm already trying to minimize the number of lines, I know that helps. But are
there some other techniques that will help as well? Any input is appreciated. I
think most people in this forum can benefit.

Best,

- Luther

[J.E. McGimpsey]

See http://www.cpearson.com/excel/optimize.htm, for starters

In article <3f5801c0e853$ba533a00$9be62ecf@tkmsftngxa03>,
"Luther Cifers" <luther...@cmcsg.com> wrote:

--
J.E. McGimpsey ar...@zptvzcfrl.pbz
ROT13 encoding, decode for real mail

[David McRitchie]

Hi Luther,
Yes, but everybody else here has seen my page on "Slow Response"
http://www.geocities.com/davemcritchie/excel/slowresp.htm

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...

[Dave]

Minimizing the number of lines does not necessarily help. In fact, more efficient
code very often needs a lot more lines.

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
.

[Simon]

Re: Application.ScreenUpdating = False

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...

[Harald Staff]

Nahh... but you really should. It's a good and useful habit to reset what
you set on your way. If you declare an object, set it to nothing after use.
If you switch sheet or location, switch back to the one the user had
originally at the end.

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...

[Luther Cifers]

Thanks, guys. It's fast as lightning now. The screen updating was the biggest
problem. But lots learned from those web pages also.

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

.

[David McRitchie]

Hi Simone,
> Application.ScreenUpdating = False

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

[David J. Braden]

Luther,
Snap out of the hypnotic state <g> and also consider this:

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

[dermot]

The main tips I'd suggest are to

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
.

[Myrna Larson]

I'm not sure I'd agree with your use of byte and integer variables. Long
integers -- 32 bits -- are the "native" data type in a 32-bit operating
system, and functions involving Longs should operate faster than those
involving Bytes or (short) Integers, particularly if, as some have suggested,
"under the hood", the compiler converts the 8 and 16-bit variables to 32 bits,
then converts the result back again.

[Emil Nikolov]

Thanks for the tips,

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...

[Myrna Larson]

The following line of code will get the number of pages required to print the
active sheet.

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

[Desart Eric]

Hi Myrna

| 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...

[R.Venkataraman]

somebody (sorry I forgot) wrote that less number of dots, the code will be
faster. this agrees with Mr. Braden's idea.
---------------------

"Luther Cifers" <luther...@cmcsg.com> wrote in message
news:3f5801c0e853$ba533a00$9be62ecf@tkmsftngxa03...

[Desart Eric]

Hi Myrna

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...

[Dana DeLouis]

Just to add to David's idea (Excellent Web page Dave!) . When one uses
anything related to Page Setup, then this can dramatically slow down a
program.
I think performance is also related to the printer driver. A bad driver can
slow even the best program. I know... a new printer a while back greatly
improved the performance of little things like Headers / Footers. I think
even checking for the number of printed pages can slow a program down.

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...