10 Ways to Make your VBA Code Better

Web & Code No Comments

1. Better way to write formulas to cells

If using the same formula over the entire row or column (sometimes even uniformly repetitive formulas), its better to update the whole column or row at the same time than each cell.

Range(E4:P4).Formula = "=A$1 "
Is faster than
For k = ActiveCell.Column To 12
j=k-3
ActiveCell.Formula = "=A" & j & ""
ActiveCell.Offset(0, 1).Select
Next k
2. How to effectively copy-paste cell value
Range("E135:AH174") = Range("E135:AH174").Value
Is faster than
Range("E135:AH174").Select
Selection.Copy
Range("E135:AH174").Select
Selection.PasteSpecial Paste:=xlPasteValues
3. Turning off screen refreshing
Use
Application.ScreenUpdating=False
'code
Application.ScreenUpdating=True
At the beginning and end of the subroutine to achive a faster executing code.
4. Not using VBA helps [At times]
There are some complex ways to do conditional formatting through VBA. However, I feel one can easily implement the same using he conditional format button (Format>Conditional Format) in excel main menu.
5. Clean Code, Better Code
Its always better to comment and structure the code. Helps
– in understanding it
– check for errors
6. Flushing the buffer
Set range to nothing and varibles to zero at the end of every subroutine. Not a must but.. helps if you are putting a lot of data into the buffer
7. Cell should have values, VBA should have the formula
There are many advantages of implementing cell formulas through VBA and removing them post execution (saving as values). They include
– Fast Load times (of the workbook)
– Help preserve data integrity, even if cell value is altered rerun of the macro will set it right
– Smaller size of the workbook
8. Make specific functions
Rather than a generic subroutines/functions its better to write specific ones, i.e. for example:
Its better to write a functions to read data for a specific month, year and all years than a function to read all data.
This gives control to the hands of the user, while helping reduce the average execution time
9. Confirm action prior to execution
Always, pop the “Are your sure question box” on a button click. It will help explain the role of the function / sub-routine, and thus help the user make an informed choice.
10.  The Pseudo Speed of VBA
The environment the VBA code runs in has a lot to say on its speed. Essp. if you are reading data from another file, you may want to keep open the file that you are reading from open while the macro runs. This helps access the data from the RAM rather than the hard disk/server. On the hardware side, processing speed and available RAM, addditional hardware cache and buffers offer speed upgrades.

Leave a Reply

© 2009, Nikhil Hullur | Entries RSS Comments RSS