Use The Macro Recorder

Really. USE IT! It's not just a tool for dummies, the macro recorder can save valuable time. For instance, what's the fastest way to find the name of the property to make a text box scale automatically when a chart is re-sized? First try right-clicking the text box and selecting Format Object, then click the Properties tab. This dialog appears:

Searching Help for Object Positioning, Text Boxes, positioning, Size, and Chart turns up a big goose egg. Ah, what a productive morning! But, wait a minute. Because it's interactive, the macro recorder can write code faster than trying to look it up in Help. Turn it on, and in five seconds, the answer: set the Placement property to xlMoveAndSize.

Here's an example of code the macro recorder produces and how to adapt it to an existing macro. To follow along, select a chart and then start the macro recorder to change the font size on the axis. The recorded code:

ActiveChart.Axes(xlValue).Select

With Selection.TickLabels.Font

.Size = 8

End With

becomes...

With DialogSheets("Wiz4").DrawingObjects(GWiz4ChartName)

.Chart.Axes(xlValue).TickLabels.Font.Size = 8

End With

In this case, it's not a good idea to have the chart active while working with it because the user would see the changes the macro was making. So, replace the selection code and references to the selection with a With...End With block that has an explicit reference to the chart. Writing the code this way also makes it independent of what is currently selected.

The macro recorder can record using absolute or relative references. Absolute references are useful when code needs to work with the exact same cells every time it runs. Relative references are useful to write code that could work with cells anywhere on a worksheet. For example, code that creates a list of the worksheet names in a workbook would place a sheet name in a cell, then move the selection down a row and insert the next name. The row number is not important, just the fact that the selection must move down one row.

To record this example using relative references, mark the position in code where the move operation should happen with the menu option Tools/Record Macro/Mark Position for Recording, then select the option Tools/Record Macro/Use Relative References. Now switch back to the worksheet, place the cursor in the list, and select Tools/Record Macro/Record at Mark. Use the arrow key to move the selection down a row, then click the Stop Recording button on the floating toolbar. The recorder will generate a relative reference in the code, as shown below:

Sub Main()

Dim ws As Object

For Each ws In ActiveWorkbook.Sheets

ActiveCell.Value = ws.Name

'The following line was recorded with relative references:

ActiveCell.Offset(1, 0).Range("A1").Select

Next ws

End Sub