Microsoft Excel —

The Macro Recorder

Excel offers a macro recorder that is about 90% perfect. Unfortunately, the last 10% is frustrating. Code that you record to work with one dataset will be hard-coded to work only with that dataset. This might work fine if your transactional database occupies cells A1:K41550 every single day, but if you are pulling in a new invoice register every day, it is unlikely that you will have the same number of rows each day. Given that you might need to work with other data, it would be a lot better if Excel could record selecting cells using the End key. This is one of the shortcomings of the macro recorder.

In reality, Excel pros will use the macro recorder to record code, but then expect to have to clean up the recorded code.


Understanding Object-Oriented Code

If you took a class in BASIC a long time ago, the recorded code in VBA is going to appear rather foreign to you. Whereas BASIC is a procedural language, VBA is an object-oriented language. Most lines of VBA code follow the Noun.Verb syntax. Except, in VBA, it is called Object.Method. Objects can be workbooks, worksheets, cells, or ranges of cells. Methods can be typical Excel actions, such as .Copy, .Paste, .PasteSpecial. Many methods allow adverbsparameters you use to specify how to perform the method. If you see a construct with a colon/equal sign, you know that the macro recorder is describing how the method should work. The final type of code that you might see is where you assign a value to the adjectives of an object. In VBA, adjectives are called properties. If you set ActiveCell.Font.ColorIndex = 3, you are setting the font color of the active cell to red. Note that when you are dealing with properties, there is only an equal sign, not a colon/equal sign.