VBA - Efficient Macro

[ajocius]

Group,
What are examples of VBA macros that are efficient and no
efficient. I'm learning now that as I do more and more error detectio
on spreadsheets that have less than 2000 rows and less than 52 columns
speed is an issue. Presently my test spreadsheet with all th
comparing and looking for errors takes about 6 minutes to completel
run. I'm a beginner at VBA and therefore believe many of my bits o
code are probably poorly written. Examples of code optimization I'
sure can reduce my macro cycle-time significantly.

A budding VBA programmer.......

Ton

 

[dominicb]

Good afternoon Ajocius

Make this your first line of your code:

Application.ScreenUpdating=False

and this your last:

Application.ScreenUpdating=True

If you haven't already used this technique, you'll find the increase i
speed staggering. If you have recorded a macro, bear in mind that yo
don't have to select a cell to do something to it, for example:

Range("A1").Select
ActiveCell.FormulaR1C1 = "hello"

is what the macro recorder will give you but can be achieved with :

Range("A1").FormulaR1C1 = "hello"

Also, if you are manipulating a single object across successive line
of code, use the With .. End With construct.

Also, any variables should be declared (use Option Explicit at th
start of your code to force you to do so) and typed (eg, Dim i A
Integer).

Hopefully, following these should lead to slicker, quicker code.

HTH

Dominic

 

[hanjohn]

Macros that work on the worksheet cells are slow.
You can greatly increase speed if you set up an array of the variant
type from you worksheet cells then use code that works with the array.
eg Dim MySheetArray as variant (in the declarations) then MySheetArray
= Range(A1:AZ2000) then code that works with the elements of this 2
dimensional array. mySheetArray(1,1) is the value in A1 on the
worksheet, mySheetArray(2,2) is the value in B2 on the worksheet etc.
One book I use suggests this results in code that runs up to 50 times
faster!
Ken Johnson

 

[ajocius]

DominicB and Hanjohn,
Whoa, I can't believe how much faster my very large macro
executes. Thanks for the advice. Take care and God Bless.

Tony