Starting Out With Excel 2007 VBA Using The Macro Recorder.

This article introduces you to using Excel 2007 VBA from a complete beginner’s point of view. The good news is that you can use Excel 2007 VBA right from within the programme – all the tools are there. But we’ll begin first by covering how to set the macro security level as macros can contain malicious code. Excel 2007 is much more security conscious than previous Excel versions and prefers to work macro-free by default.

We’ll then describe an exercise using the Macro Recorder to create VBA code. Then we’ll cover editing the code using the VBA editor and re-run the macro to test the edited version. We’ll also cover assigning the macro to a shortcut key and finally we’ll describe how Excel 2007 manages macro security.

Why get involved in macros and VBA anyway you might be wondering? VBA can be used for example to create custom functions, carry out repetitive tasks, work with user forms to input data and much more. We’ll look at an example of using VBA to create a table we want to use frequently. Our table will have headings, some data and a calculation. You could also build a form using VBA to allow the user to add data to our table each time the macro is run, but for this article we’ll let the macro add the data.

Before starting to work with VBA we need to set the default Excel 2007 security level. With Excel open, click the Office Button then the Excel Options button at the bottom right. In Excel Options select “Trust Center” then click “Trust Center Settings” on the right of the panel. Under “Macro Settings” choose the option second from the top, “Disable all macros with notification”. Click OK, and OK again to complete. Now Excel 2007 will prompt you if a file is opened containing macro(s). This is safe setting to ensure you are always prompted when Excel 2007 opens a file containing macros.

To begin our exercise, ensure you have a new workbook open in Excel 2007 with cell A1 selected. We’re going to start the macro recorder, then type in the table and data, then stop the macro recorder. On the View tab click the down arrow on the Macro button and select “Record Macro”. In the Record Macro box give the macro a name such as Table1, leave the macro location as “This Workbook” and click OK to continue.

Then select cell D4, type the word without the quotes “Item” and press Enter. Then in the same way add “Apples”, “Oranges”, “Grapes” and “Total” in successive cells under the heading “Item”. Now select cell E4 and type in “Number”, press Enter, then type in “12”, then “10” then “15” in successive cells under “Number” so you end up with a two column table. Ensure the cursor is now in the cell E8 which is immediately under the last number. On the Home Tab click Autosum, and press Enter to complete. You should have a table with headings, data and a total calculation.

Stop the Macro Recorder by choosing the View Tab, click the down arrow on the Macro button, and choose “Stop Recording”. We have now recorded a macro in VBA code to create this table automatically whenever the macro is run. By default the macro code is stored within the file you’re currently working with, but you can change the default to make the macro available to all your Excel 2007 files.

Now we’ll test the macro. First delete the table on the worksheet then select any cell except D4. In the View Tab click on the Macro button to show the Macro panel listing all current macros. Select Table1, then click Run – and the macro creates the table, starting at cell D4.

Next we’ll edit the VBA created in the macro. Again clear the table in the workbook. Then in the View Tab click the down arrow on the Macro button and choose “View Macros”. Click once on Table1 to select it, then click Edit. Excel’s VBA editor opens showing you the VBA code for your Table1 macro.

In the right hand code panel carefully edit the VBA by clicking where you see “item” and change the text to “product” but keep the original quote marks. Change “number” to “sales” in the same way. You could also change some of the numbers if you wish but ensure the original quotes are kept. Then click on the upper right hand cross to close the VBA editor and return to Excel. The VBA is updated automatically.

Now we’ll save the file with an appropriate name. If you choose Save, Excel 2007 alerts you that the file contains a macro and advises you to change the file type. This is because by default Excel 2007 saves macro-free files only. So in the Save panel click No, then Cancel to end the Save operation. Instead click the Office Button, hover over “Save As” and choose “Excel Macro Enabled Workbook”, give the file an appropriate name and click Save. The file is now saved with the filename extension .XLSM and the M tells Excel the file contains macros..

With the file still open try running the macro again by choosing Macro in the View Tab, select Table1 and click Run. Your table should now appear with the edited headings. Once you’re happy with this, again delete the table from the worksheet so we can test the macro via a keyboard shortcut.

To assign your Table1 to a keyboard shortcut key, on the View Tab click the Macro button, and click once on the Table1 macro. Then click Options. In the “Macro Options” box type a capital T to set the shortcut as CTRL + SHIFT + T then click OK and Cancel to complete. Ensure the workbook is empty, and select any cell except D4, then type the keyboard combination CTRL+SHIFT+T. The macro runs again and creates the table. Then close the file.

To see how Excel 2007 manages macro security try opening the same file (remember you just closed it). You’ll see that Excel 2007 shows a Security Warning alert saying “macros have been disabled”. Click the Options button. Choose “Enable this content” then OK to allow macros in this file. Then you can run the macro again quite happily.

To gain a really good insight into the world of macros and VBA in Excel 2007 consider attending an Excel 2007 VBA introductory course. There are many around so find one that is best for you – and start learning much more about VBA in Excel 2007.

---

---