Introduction to Excel Macros


« Back to videos

Additional Information:

This video shows you how to use macros in Excel. This may require you to lower the security settings on your machine. The video was created in Excel 2003. If you are using Excel 2007 or 2010, Macro Security Settings can be found under the Start button in the top-left hand corner of Excel->Excel Options->Trust Center->Trust Center Settings->Macro Settings. You should set this to "Disable all Macros with Notification".

Similarly, the instructions for accessing the Visual Basic Editor have now changed. You can still use the shortcut (Alt+F11) but it now has a dedicated button on the Developer ribbon. If you cannot see the Developer menu, go to the Start button->Excel Options->Popular and check the box "Show Developer Tab in Ribbon".

The way in which you create a sub routine, is to enter the code Sub ModuleName(), which then gives you space to write a script above the line End Sub. It should be noted that "Sub" is an abbreviated form of "Public Sub" which means the script can be accessed universally. Later modules in the training course will introduce you to private sub-routines. Private Subs are used when the trigger for a macro creates some ambiguity as to whether it should be running; it does not affect whether other users can see your work.

You will notice the tutorial introduces you to the Cells(RowNum, ColumnNum) notation for referring to cells. If you have ever tried recording a macro, you will have seen Excel refer to cell A1 as e.g. Range("A1"). The argument for re-writing the reference as Cells(1, 1) is that, should we wish to process a number of cells, we would look to replace the numbers with variable names. Whereas the Cells notation allows us to write Cells(RowNum, 1), the Range notation would need to be written as Range("A" & RowNum) which is somewhat more cumbersome. The fact the column reference is a letter adds a further layer of complexity as soon as you look to use variables to process e.g. columns A->Z in turn.

The Play button within the Visual Basic Editor takes a familiar form. You will see it features alongside pause and stop buttons. These are of less use than the Play button because you would typically look to pause or stop a macro at a specific point. Therefore, if you wish to pause a routine, it makes more sense to introduce a break point in the code which, once again, is covered in later modules.