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 excellent for really simple operations such as producing a simple report. However, there are definite limitations to this approach. Firstly, because Excel plays back the steps just as they were originally performed by the user, recorded macros are notoriously slow to play back.
Secondly, recorded macros will only run properly under the conditions in which they were originally recorded. For example, if a particular worksheet needs to be active at a certain point and it is not active when the macro is played back, an error will occur.
This means, effectively, that recorded macros can only really be utilised by the person who records them. They cannot be given out 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.
Naturally, there is a bit of a steep learning curve for any users who have done little or no programming. For this reason, we run a five day crash-course in Excel VBA for fledgling macro programmers. We have found that this provides everyone with a chance to gain some degree of confidence with the challenging VBA environment.
After weaning users off the macro recorder, we explain to them the syntax and structure of VBA. They learn how to use variables to store both data and references to Excel objects, how to code logical and iterative structures and how to allow the person running a macro to choose between different options.
Getting some training on Excel VBA is always worth the effort. It can take the productivity of an Excel user to a new level very rapidly. Monthly procedures and reports which used to take long hours can suddenly be accomplished with astounding ease and lightening speed.
Getting trained on Excel VBA is a great way to wean oneself off the macro recorder. However, the recorder will still always have its uses. For example, when you are working with an Excel object or a series of steps with some degree of programmatic complexity, recording a few steps then looking at the code generated is a great way to learn new syntax.
Leave a Reply