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
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.
This material is the copyright material of or licensed to Future Publishing Limited
(http://www.futurenet.co.uk/), a Future Network plc group company, UK 2004. All rights reserved.
Article ID: 841337 - Last Review: 10 August 2006 - Revision: 1.3