Excel VBA Macro Programming
An Excel macro is a set of instructions that will perform an operation on an Excel worksheet. It is a great way of seeing how specific actions on the spreadsheet are interpreted in code and how the Excel object model is used to do this. If you ever get stuck as to what object or what collection you should be using, try recording a macro and see how the recorder uses the object model. It is very useful to see what parameters are being passed for specific methods and what properties and methods belong to various objects. You can then use this as the basis for building your own code.
When you record a macro, a module is created (if it is not already available) and the code is written into it. You can view this code and see exactly how Excel has tackled the problem in VBA. You can modify this code or use it in other procedures.
To record a macro, select Tools Macro Record New Macro from the Excel menu. Your screen will look like Figure 12-4.
Figure 12-4: Recording a macro
You can give the macro another name and a shortcut key if needed. The naming conventions are that there must be no spaces or illegal characters such as a slash. The rules are the same as for names of functions or subroutines in VBA, because all you are doing is using the macro recorder to create the subroutine. Click OK and a small Stop Recording window appears.
From now until you click the Stop Recording button, everything that you do on the spreadsheet is translated into VBA code using the Excel object model. For example, select a range of cells C8 to F16 on Sheet1 (it does not matter if these are empty). Right-click the selected range and choose Copy. Go to Sheet2 and click cell D10. Use Edit Paste from the Excel menu. Now click Tools Macro Stop Recording, and your macro is complete.
Look in the code window by pressing Alt-F11 , and you will find that a new module has been inserted that contains the code for the macro you recorded:
Sub Macro1() ' ' Macro1 Macro ' Macro recorded 17-03-03 by Richard Shepherd ' ' Range("C8:F16").Select Selection.Copy Sheets("Sheet2").Select Range("D10").Select ActiveSheet.Paste End Sub
Comments are automatically added to show who recorded it and on what date. The first statement selects the range C8 to F16. The Selection object's copy method is then used. The Selection object represents the currently selected range. Next, Sheet2 is selected, followed by the range on that sheet, D10, being selected. Finally, the ActiveSheet object's Paste method is used.
You can see how Excel uses the Excel object model to carry out your commands. However, Excel does make some assumptions because when this particular macro was recorded, only one workbook was loaded. If more than one workbook is loaded, the macro will be run against whichever is the active workbook, which will be the one visible in Excel. This is not always the result that you want to achieve.
Excel assumes that this is the only workbook loaded. If there are several workbooks loaded and the macro runs while in one of them, it will produce different results and even possible errors if there was not a ‚“Sheet2 ‚½ in that workbook.
If it was run in the wrong workbook, it could also run the risk of overwriting valid data in the spreadsheet, with terrible results. This is because the macro does not explicitly refer to a specific workbook. It assumes you want the macro run on the currently active workbook.
The Workbook tier is ignored, and the workbook to use is not defined, nor is the sheet the macro should start off in.
The lesson is that you should not assume that a recorded macro will work under every circumstance. You may find that you need to modify it to make it totally secure ‚ by including a reference to the workbook being used, for example.
Recording macros is a great way to see your way around problems. After all, if you can do it manually on the spreadsheet, then Excel can record a macro to do it. However these macros do have limitations and should only be used as a guide in professional Excel development. After all, the whole purpose of this book is to get you to the stage where you can just sit down and write the code right off the top of your head.