Making Excel Macros

Article ID: 841337
The articles set out below are articles created and/or produced by Future Publishing Limited. Microsoft is not responsible for the content, accuracy or opinions expressed in these articles.
Collapse this imageExpand this image
office97logo
Expand all | Collapse all
The spreadsheet, home of the original macro, is the target for this month?s macro tutorial, as Simon Williams explores what Excel 7 can do for itself.

Let?s start with one of the simplest macros it?s possible to write, but one which many people will find very handy. When you open Excel, you?re likely to want to continue working with the worksheet you were using in your last session. You go to the File menu and click on the top entry in the ?most recently used? list at the bottom of the menu. Why bother, though, when you can get Excel to do it for you?

Excel automatically loads any worksheet or templates in finds in the MSOffice\Excel\XLStart folder when it starts up. You can also create a macro which runs automatically when its parent worksheet is loaded. You just give it the name Auto_Open. If you create this macro in the Personal.xls worksheet, Excel saves it in the XLStart directory by default.

The first walkthrough shows you how to accomplish this self-starting macro. The initial macro recording is done purely to get the right syntax to place the SendKeys command in. When you start a macro recording, you get a small button bar with just one button. Simply click on this button to stop the current recording.

The SendKeys command does just that; it sends the equivalent of key presses to the program, so it responds just as if you had pressed the keys physically. You enclose the key presses in brackets and speech marks and special symbols are used for keys like [Shift], [Ctrl] and [Alt] on the keyboard. So, in this example, the sequence ?%f1? means [Alt]F 1 which, if you try it out, calls up the most recently used file from the list in the file menu.

Shortcuts for text

Text shortcuts are easy to set up and they save time when repeating the same task.

If you find yourself setting up the same headings over and over in your worksheets, you can save yourself considerable typing by creating a macro with text and cell movements in it. For example, to create headings for all months of the year and then move the active cell to the ?January? column, you can use the following macro:

MonthHeadings Macro
Macro recorded 10/12/97 by Simon Williams
Keyboard Shortcut: Ctrl+h
Sub MonthHeadings()

ActiveCell.Select
ActiveCell.FormulaR1C1 = ?Jan?
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = ?Feb?
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = ?Mar?
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = ?Apr?
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = ?May?
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = ?Jun?
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = ?Jul?
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = ?Aug?
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = ?Sep?
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = ?Oct?
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = ?Nov?
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = ?Dec?
Range(ActiveCell.Offset(0, -11), ActiveCell).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = xlHorizontal
End With
Selection.Font.Bold = True
ActiveCell.Offset(1, 0).Activate
End Sub

The same technique could be used with any set of headings, simply by changing the ?Jan, Feb, Mar? etc. text in the FormulaR1C1 lines.

Automatic bar charts

To create a chart in Excel, you would normally select an area of the worksheet and run through the Chart Wizard to select the type and style of the chart, finishing with Excel producing and displaying the graphic you selected.
Collapse this imageExpand this image
52 macros


For many uses, though, where you just want a cheap and cheerful bar chart of your data, the ChartWizard can prove unwieldy. Fortunately, running a Wizard can be automated with a macro too. The new macro starts life as a sequence recorded with the macro recorder, by highlighting a table of figures, dragging out a frame for the chart and selecting typical chart types and styles. The selected area ? which forms the data table from which the chart is created ? is laid out in a ?standard? form, with headings in the left-hand column and top row.

It?s interesting to note that Excel?s macro language creates a shorthand for all the choices you make when using the ChartWizard. It produces a single command, defining all your choices ? like graph type, style and labels as simple parameters.

The newly created macro is assigned to a key sequence and every time that sequence is pressed, the bar chart draws automatically. The selection has to be the same size and in the same position each time the macro is run, as the co-ordinates picked up by the macro recorder are those of the original selection, not of any selection made before running it.

The only way to change this is to get in there and edit the macro by hand. The original macro looks like this:

BarChart Macro
Macro recorded 09/12/97 by Simon Williams
Keyboard Shortcut: Ctrl+b
Sub BarChart()
ActiveSheet.ChartObjects.Add(336.75, 51, 287.25, 204.75).Select
Application.CutCopyMode = False
ActiveChart.ChartWizard Source:=Range(?B5:F9?), Gallery:=xl3DColumn, Format:=4, PlotBy:=xlRows, CategoryLabels:=1, SeriesLabels:=1,
HasLegend:=1
End Sub

If you look at the line beginning ActiveChart. ChartWizard you can see that the source range the Wizard uses is B5:F9, the range originally highlighted when the macro was recorded. To make the macro act on the selection current at the time it?s run, the Source part of the command has to take account of the new selection.

To make this happen, a new variable is defined to take the range of the selection. The command Dim myRange As Range sets up this variable, while the command below, Set myRange = Selection, copies the co-ordinates of the cells in the selection to the variable. It?s then used in the ActiveChart.ChartWizard line so that the Wizard works with whatever area has been selected.

With the changes made, the macro now looks like this:
Sub BarChart()
Dim myRange As Range
Set myRange = Selection
ActiveSheet.ChartObjects.Add(336.75, 51, 287.25, 204.75).Select
Application.CutCopyMode = False
ActiveChart.ChartWizard Source:=myRange, Gallery:=xl3DColumn, _ Format:=4, PlotBy:=xlRows, CategoryLabels:=1, _
SeriesLabels:=1, HasLegend:=1
End Sub

If you want to try this macro out, you can load up the sample worksheet Excel macros.xls from the cover CD, highlight either of the tables of data and press [Ctrl]B to draw the bar chart. You?ll notice how the chart changes from showing sixteen columns with data from the top table to eight with data from the bottom one. Bear in mind that each time you press [Ctrl]B a new chart is drawn on top of the one before, without deleting it. The automated month headings macro is included in the same worksheet. Press [Ctrl]H to run it.

The bar chart macro will work with any number of columns and rows, though the bars gets increasingly cramped with more data. What it doesn?t do at the moment is to draw the bar chart at a position relative to the selected data table. This facility is made hard to implement by Microsoft?s way of allocating space on a worksheet for graphics. It does this by specifying co-ordinates in points (1/72nd of an inch), without providing any tie in with the dimensions of a cell.

You can?t easily write into a macro that a chart area should start two columns over from the right-hand edge of a table and on the same row as the top row of its headings. It?s not a trivial exercise to position graphic elements relative to the position of cells in a worksheet.
This material is the copyright material of or licensed to Future Publishing Limited , a Future Network plc group company, UK 2004. All rights reserved.

Properties

Article ID: 841337 - Last Review: 10 August 2006 - Revision: 1.3
  • Microsoft Office 97 Standard Edition
  • Microsoft Excel 97 Standard Edition

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com