Macros: The Return

Prepare to speed up your workflow as we continue our WINDOWS guide by examining how macros work in Excel. We also take a look at Visual Basic for Applications (VBA) and explain how this program can help you alter macros to make them yet more valuable

  • E-Mail
By  Matthew Wade Published  January 3, 2005

Macros: The Return|~|Feb-05-Workshop-image---m.jpg|~|Speed up Excel by either using the built-in macro recorder or heading into Visual Basic for Applications (VBA)|~|About macros Macros are wonderful little tools for speeding up your work. If you have a pesky task that you regularly carry out in Excel for example, such as formatting text a certain way or inserting a particular table, this can almost certainly be automated with ease. Once you have recorded or scripted the relevant macro, one click of a shortcut button will be all it takes to run the task, saving you time and possibly also stopping you from slumping, snoring onto your desk. Applications such as Word, Excel, Outlook, Visio and Projects, to name but five, all feature fully functioning built-in macro recorders. There are also several non-Microsoft applications available that both support VBA and include recorders, such as Corel Draw and WordPerfect Office. EXCEL: MACRO BASICS As we outlined in the January issue of WINDOWS, the recorders built into applications are a good starting point for the beginner and certainly more reliable than the general purpose recorders that Microsoft included with the first versions of Windows. When it comes to automating tasks in Excel then, let's kick off with a quick reminder of how to record a macro: 1 - Practice the task you want to automate so that you don't make mistakes and record these into your macro. Remember - mistakes made here will lengthen the entire task and be repeated every time you run your macro. Where possible, try to use keyboard commands rather than mouse moves, because if your PC's configuration changes causing application windows to open in a different screen location, recorded mouse clicks will miss their recorded targets, rendering your macro pretty useless. 2 - Click Tools/Macros/ Record New Macro and choose an obvious name for your macro. Choosing a shortcut key will also help you run the macro quickly. 3 - Click OK to start recording. Slowly and carefully work through the steps of the task you want to automate. Once you’re done recording, click the cassette style Stop button. 4 - Check your Macro works by heading to Tools/Macro/Macros (shortcut Alt/F8), choosing it and clicking Run. If it's not working correctly, delete it and start over. 5 - To create a toolbar button from which to run your macro, go to Tools/Customize and click Commands. Under Categories, click Macros then drag the custom button to the desired location on the toolbar. In the Customize dialog, click Modify Selection and choose Assign Macro. Then simply choose your macro. To change how the button itself looks, head for Modify Selection again, pick Change Button Image and go for one of the available images. Alternatively, opt for Edit Button Image and use the built-in Button Editor to create your own image. Pros & Cons Though recorders certainly have their uses, this doesn't mean they are always 100% reliable. For example, your recorded macro might not fully record your actions in a particular dialog box. Another possible drawback of using recorders relates to how you use your PC. It may be that most of a regular task is the same each time you run it, but there may also be some slight differences each time. You might want to apply standard formatting to text or use a particular calculation, but the location of the text or the cells in question may change. There are a couple of solutions then. The first is to think ahead in terms of carefully considering where you start and stop your macro recording. If your task starts with a number of steps that remain constant then start your recording from the first step and stop the recorder before the step that changes each time. You could even break a task into multiple macros, though of course the more you do this, the less time saving macros will be. The alternative is to edit your own macro code. Introducing VBA This editing approach isn't as worrying a proposition as it sounds in that you can use one of your own recorded macros as the starting point for your own edited version. Click Alt/F8 to view your list of macros, select one of these and then hit Edit. This opens Visual Basic. The window on the right (the code window) displays the code relevant to your macro. Should you have problems with permissions, make sure your security is set to Medium. Change this setting by clicking Tools/Macros/Security. Even if you have never examined code before, if you look closely you'll probably be able to work out what you need to change to customise it, such as the filename or the cell range for example. Editing recorded macros can be an effective way of getting to grips with simple programming. One warning here though: be sure to back up your workbook before tinkering with macro settings as a small mistake can quickly transform your macro into nonsense. It could even damage your files if you've got it really wrong. A good starting point is to add extra functions to a macro you have already recorded. Say you have a macro set-up that formats cells, but now you want to also change the background colour of these cells. If you record a macro that changes the background colour, you can then copy the additional script instructions from this macro to your original macro. A clear example of how to go about this is provided on Microsoft's support site - just head to http://office.microsoft.com and search for 'Adding steps to an existing Excel macro.' QUICKLY DOES IT These common Excel shortcuts will help you whiz around workbooks: CTRL/P, CTRL/S, CTRL/F4, ALT/F4 - Print, save, close, close excel CTRL/Arrow - Move to edge of region CTRL/Home CTRL/end - Select A1, Select last cell in used range CTRL/Shift/End - Select from active cell to last cell in used range CTRL/Shift/Home - Select active cell to A1 CTRL/Page Down CTRL/Page Up - Move to next/previous sheet CTRL/Tab - Move to next open workbook Shift/F11 - Insert new worksheet Shift/F3 - Paste function window into sheet Alt/F11 - Open Visual Basic Editor (VBE) CTRL/F3 - Define name CTRL/; CTRL/Shift/: - Insert current date/current time CTRL/Space - Select (highlight) column Shift/Space - Select (highlight) row More On Macros * http://office.microsoft.com - contains numerous workshops and guides to using macros and VB * www.vba-programmer.com - includes downloadable reference documents and numerous code snippets for Microsoft applications such as Word, Excel, Access, Outlook etc. * www.exceltip.com - includes code examples and a multitude of valuable Excel pointers. ||**||

Add a Comment

Your display name This field is mandatory

Your e-mail address This field is mandatory (Your e-mail address won't be published)

Security code