Microsoft Excel Power

Lesson 1: The basics of Macros.

The Created Code

So, where did the code go? Well, if you select the Visual basic Editor button (Fig 1.07), then you will be presented with the VBA environment (Fig 1.10).

Fig 1.10 - The Visual basic Editor Environment.

You will notice that our spreadsheet is listed in the Project - VBAProject box and that Module 1 is highlighted. On the right side is the code window, and guess what? There is our code.

Here is our code in all of its glory: All of the code is in the Lesson 1.xls spreadsheet, but I have listed it here to explain what the recorder created. Notice that it created a Sub, or Subroutine called Format_Report (). This is the name that we gave to the macro. The system also places some comments into the macro for you.

Columns("I:I").ColumnWidth = 9.43
Range("B1:I2").Select
Selection.Font.ColorIndex = 5
Selection.Font.Bold = True
Range("A4:N4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With

The first line of code

Columns("I:I").ColumnWidth = 9.43

Sets the width of column "I" large enough to see the data. This uses one of the many properties of the column object, ColumnWidth.

The next few lines of code

Range("B1:I2").Select
Selection.Font.ColorIndex = 5
Selection.Font.Bold = True

Select the range of cells "B12 to I2" and apply the font color Blue and set them to Bold.

The heading row is next.

Range("A4:N4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With

Here we select the range "A4 to N4" and set the property WrapText = True, and then with this same range, we set the fill color to Light green using

.ColorIndex = 35

Notice that there are two With Selection; End With blocks used to apply the formatting properties. This structure allows you to specify a number of options without having to repeat the selection. Also notice that there seem to be a lot of properties set in the first "With" block, especially when we only wanted to set the WrapText property. This is a feature of the record macro process that can sometimes be less desirable, and can lead to very inefficient code. We will address this issue in later lessons, for now it is not a major problem. The rest of the code set the little boxes around each cell

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Running the macro

First reset the spreadsheet as before (copy and paste cells from backup). Select the Run macro button (Fig 1.06) and select the Format_Report macro; then select the Run button (Fig 1.17).

Fig 1.17 - The Run macro Dialog.

In a blink of an eye, your {Unformatted Report} spreadsheet should be transformed in to the same as the {Formatted Report} spreadsheet.

Well, we have created a macro that achieved what we were doing manually. This is just the tip of the Iceberg. In the following lessons I will cover a number of other techniques that I hope you will find a little more useful - but we have to start somewhere. To close out the macro editor you can select File -> Close and return to Microsoft Excel (Fig 1.18).

Fig 1.18 - Exit the VBA editor.

  1  2  3  4  5  6