Monday, November 28, 2011

Advanced Excel: Macro Examples

Creating Excel spreadsheet macros is a great way to save time and reduce the possibility of human error by automating repetitive processes. Other reasons for learning how to write VBA macros include: standardization, improve efficiency, expand capabilities, and streamline tasks. Macros in Excel are created through two primary methods: the macro recorder or by writing custom code with the macro editor (or even with notepad). Recording is a great way to figure out syntax or a specific, simple task if you are stuck but writing custom code is far superior, especially if you are dealing with multiple Excel files. Here are a few basic, easy lines of code which are helpful to learn when beginning to create macros. Most of these deal with layout or formatting of your spreadsheet. I typically would add these types of commands to the end of my macro after all the data is where I would like it.

To insert a row above an active cell use this code:


Conversely, to insert a  row below the active cell:


To insert a new, blank column to the left of the active, selected cell, use this:


Or, to insert a column to the right of the active cell, change to this:

ActiveCell.EntireColumn.Offset(0, 1).Insert

To delete a column, specify the column number (A being 1, B = 2, and so on):


To add a diagonal line through a range of cells with a macro use the following code:

Range(insert desired range , ie A2:B2).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    With Selection.Borders(xlDiagonalUp)
        .LineStyle = xlContinuous
        .ColorIndex = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With

To change the background color to white, select a range of cells then type this:

With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

To create a thick border on the right of a cell:

Selection.Borders(xlEdgeRight).Weight = xlThick

Or, make the bottom border thick:

Selection.Borders(xlEdgeBottom).Weight = xlThick

To Autofit a columns width, specify a range:

Read more about creating Excel spreadsheetmacros.