Additional Information:
The above video was made in Excel 2003. In Excel 2007 and 2010, the button for recording a new macro can be found under Developer->Code->Record Macro.
Most users learn Visual Basic by recording macros. This is essential because Microsoft Excel allows a user to perform many different functions and they should all have VBA equivalents. These equivalents are not always obvious and would be almost impossible to find in the Visual Basic help file. For example, to shade a cell a standard red requires the instruction Cells.Interior.ColorIndex = 3. You cannot guess the code. Recording macros lets you extract the language you need to develop your own knowledge.
This tutorial demonstrates that the record function produces a lot of superfluous code. Therefore it is best to write your own sub routines using the language learned from recorded macros, as opposed to developing recorded macros further.
Excel refers to all cells, sheets, and ranges by their spoken name e.g. Sheets("Data").Range("A1:A5") where you are referencing the first five rows of data, in the first column on a sheet named Data. If you are planning to rename sheets or move cells in future, it is better to use more permanent labels. The sheet name should be assigned a code name in the Properties window such as "shtData" and this should be used to replace references to Sheets("Data") in the recorded code.
Earlier modules stress the importance of using cells notation to refer to ranges, so Range("A1") becomes Cells(1, 1). Where a range refers to a group of cells, the range can be defined by the cells in the top left-hand corner, and bottom right-hand corner of the group. Therefore Range("A1:A5") becomes Range(Cells(1, 1), Cells(5, 1)). Although it looks less efficient, it makes it easier to replace the numbers with references to range names in the Excel spreadsheet. This means we can write code that will continue to operate on the correct cells, even if we insert new rows and columns.
Another practice Excel adopts when recording code is that of using "With" statements. "With" statements refer to a specific range or object to which you are making changes. The benefit of a "With" statement is that between the initial instruction, "With Range/Object", and the end of statement, "End With", you do not need to re-state the range/object to which changes are being applied. So it can be an efficient way of writing code.
However, when you are recording a macro, the "With" statement is used because Excel stores data in such a way that it cannot always identify exactly how a spreadsheet has changed. The Record Macro function gets round this problem by re-stating the value of everything that might have changed to ensure it covers the changes that have been made. So if you have changed the appearance of some text, the recorded code will state its font, its size and whether it is bold. Generally you will have only made a single change, so it is best to rationalize "With" statements as shown in the video.
