Excel VBA Courses Wean Users Off The Macro Recorder
Most people’s first knowledge of Excel VBA (or Visual Basic for Applications) is recording a macro. As a user performs a series of operations (e.g. opening a workbook activating a particular worksheet, copying some data, etc) Excel will faithfully record each step and create the necessary VBA code. Each time the user runs the macro, these steps are replayed exactly as they were originally recorded.
Recording macros is great for really simple tasks such as producing a strait-forward report. However, there are distinct limitations to this approach. Firstly, because Excel plays back the steps just as they were originally performed, recorded macros are notoriously slow.
Secondly, recorded macros will only run correctly under the conditions in which they were first recorded. For example, if a certain worksheet needs to be active at a given point and it is not active when the macro is run, there will be an error and the macro will stop running.
This means, effectively, that recorded macros can only really be used by the person who recorded them. They cannot be distributed to one’s work colleagues.
One of the first things we do on the Excel VBA training courses that we run at our London training centre is to wean users off the recorder. We give them a good grasp of the Excel object model, a way of programmatically representing each of the elements within the Excel environment such as workbooks, worksheets and cells.
True, there is a bit of a steep learning curve for those users who have done little or no programming. This is one reason why we run a five day intensive Excel VBA training course for new users. We find that it gives everyone a chance to gain confidence and familiarity with this challenging environment.
Once we have put the recorder on a back-burner, we teach delegates the syntax and structure of VBA. We show them how to use variables to store both data and references to Excel objects (such as workbooks), how to create logical and iterative coding structures and how to allow the person running your macro to make choices.
Getting some training on Excel VBA is definitely worth the effort. It can take greatly increase one’s productivity. For example, monthly procedures and reports which used to take ages to complete can suddenly be accomplished at the click of a button.
Getting trained on Excel VBA is the only real way of losing one’s initial dependency on the macro recorder. However, the recorder will always have its uses. For example, when one is working with an Excel object or procedure which is programmatically complex, recording a few steps then examining code generated is an excellent way to learn new syntax.
Leave a Reply