For anything beyond simple tasks, Excel VBA training is required. Excel VBA training involves a much deeper understanding of the Excel Object Model than a user ever sees when simply recording macros.
For most users, the first exposure to Excel Visual Basic for Applications (VBA) involves recording a macro. Just like a voice recorder, when the Excel macro recorder is started, as the user carries out a series of steps (for example opening a workbook going to a particular worksheet, copying some data, and so on) Excel faithfully records each step by generating the necessary Visual Basic code. Each time the user plays back the macro, these same steps are performed exactly as they were recorded.
When Recorded Macros Are Appropriate
Recording macros is ideal for really simple tasks such as producing an extremely strait-forward report. However, there are distinct restrictions on this approach. Because Excel plays back the steps just as they were originally performed, recorded macros are often painfully slow.
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 that recorded macros are only of use to the person who records them. It is unlikely that they could be distributed to work colleagues.
Formal Excel VBA Training
One of the first things we do when we run an Excel VBA training course at our London training centre is to attempt to wean users off the recorder. We provide them with a good grasp of the Excel object model, a way of programmatically representing all of the elements that make up the Excel environment such as cells, workbook and worksheets. 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.
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.
Despite its initial complexity, attending an Excel VBA training course is a good investment. It can greatly enhance the productivity of an Excel user. Monthly operations and reports which used to take hours can suddenly be streamlined and accomplished with astounding rapidity.
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.
By: Andrew Whiteman
Article Directory: http://www.articledashboard.com
The writer of this article is a developer and trainer with Macresource Computer Solutions, an independent computer training company offering Microsoft Excel VBA training courses in London and throughout the UK.
Excel VBA Made Easy And Visual Basic 6 Made Easy are E-books written to help you learn Excel VBA And Visual Basic 6 easily. The e-books are popular among the students of both online tutorials.