Wednesday, January 5, 2011

Pruning Macros

We may be a ways off from spring pruning but now is a great time to start pruning your macros.  When you record a macro you may notice that, when formatting a cell or group of cells, the resulting code to your recorded macro becomes quite long.  


Take the following recorded macro for example:


If you were to nest this within a loop the user could find themselves watching (and waiting) as Excel slowly chugs along.  Let’s take a look at how these 25 lines of script can be reduced to a more manageable number.
1.       First it asks to change the font of the cell to bold and to a different shade.
2.      Next it asks to change the color of that same cells interior.
3.       Finally, it draws a bottom border
Why then should a few steps turn into 25?  It should not when the same formatting can be accomplished through the following:


Sub Macro1()
    With Selection
        .Font.Bold = True
        .Font.TintAndShade = 0.349986266670736
        .Interior.Color = 65535
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
    End With
End Sub

Since the selection remains the same throughout the script there is never a reason to re-use the selection statement.  Also, when items are being set to 0, xlAutomatic, xlNone you are asking Excel to revert back to its default.  Unless you have an already active sheet with formats altered from the default settings these are useless lines of code. 

With cleaner code, not only does Excel perform faster, but you are better able to apply this code within other scripts.  In addition, it becomes a lot easier to follow which makes for easy adaptation down the road especially when others must step in to maintain it without your support.

No comments: