Spy Journal 3.0 Filtering the web for you

I promised to start writing a series of VBA primers. This is the first one.

For many using Excel is a daily thing. From office secretaries who manage the petty cash through to financial whizzkids who monitor stocks and bonds, Excel is a very versatile tool. It also has a lot of potential for automating repetitive tasks easily by being able to create macros.

A macro is a simply a “recorded set of instructions”. These can be as simple as saving two different sets of print options so you don’t have to continually change them. Basically the concept of a Macro is that you can get the computer to record the key and mouse strokes you use and save them so they can be repeated. While the reality is that the macro that is recorded is written in a language called VBA (visual Basic for Applications) this is irrelevant to people who first start using it.

 In Excel prior to 2007 you will be able to locate from the menu or the tool bar an option to record a macro. Use the Tools Menu to locate the menu options, and to find the toolbar right click the toolbars and turn on the Visual Basic toolbar. The Excel 2007 images are given here also.

Once you click the record button you will be given a dialogue box to name the macro and set up some advance options like short cut keys, where the macro is stored and the comments. For now just leave those all as default and click OK.

Every mouse click and key press you make now while Excel is the active application will be recorded. Try a few of your favourite things; insert a sheet, change the cells borders and colour fill options and enter some text. When you are done press the little Stop button that pops up.

Now it is time to go and see what you did in code. Open the Visual Basic Editor (VBE). There are three ways to do this. From the menu or tool bars listed above, or with a Keyboard shortcut ALT F11. (Hold down the ALT key and press the F11 key).

Inside the editor there are a number of areas that can be used for different functions and display information. for now we will look just at the Project Explorer – the left hand pane listing the workbook, sheets and modules, and the right hand editing pane where you can open up a module. If you ever close the Project Explorer and need to get it back use CTRL R.

Notice in the Project Explorer window that a module will have been created under the Modules folder. Also note all the worksheets in the file are listed. The name of the sheet in the brackets is the actual name of the sheet tab in excel. This can be edited by changing the sheet name back in Excel. However the VBA sheet name are listed sequentially from creation. E.g. if you delete Sheet1 and then create Sheet4, it will be listed as Sheet4 in the VBE, and Sheet1 will be gone as you would expect. Renaming Sheet4 as Sheet1 will change the name in brackets but not the actual sheet name. Try this for yourself and see what I mean. The main benefit of this from a coding perspective is that sheets can be referenced in code by their sheet number regardless of what a user renames it to. However once a sheet is deleted it is gone.

We won’t explore any of the code you created today, though feel free to play with it yourself - make sure you use a workbook that doesn’t have anything you want to keep in it!

Next time we will talk about the code that gets recorded and how to edit it a little.

Excel 2007 options

Click the circle, then the Excel Options button. Now turn on the Developer Tab in the ribbon.

Click Record Macro to record a Macro. Click Visual Basic to open the VBE.