VBA - Efficient Macro

[ajocius]

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

A budding VBA programmer.......

Tony

[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 in speed staggering. If you have recorded a macro, bear in mind that you 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 lines of code, use the With .. End With construct.

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

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

HTH

DominicB

[[email protected]]

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