Excel VBA Courses Wean Users Off The Macro Recorder

Most people's first exposure to Excel VBA (Visual Basic for Applications) involves recording a macro. As the user performs a series of steps (for example opening a workbook going to a particular worksheet, copying some data, etc) Excel faithfully records each step by creating the necessary VBA code. Each time the user runs the macro, the steps are replayed exactly as they were recorded.

Recording macros is ideal for really simple tasks such as producing an extremely strait-forward report. However, there are distinct restrictions on this approach. Firstly, because Excel plays back the steps just as they were originally performed, recorded macros are often painfully slow.

Also, recorded macros will only run normally under the conditions in which they were originally recorded. Thus, for example, if a particular worksheet has to be active at a certain point and it is not active when the macro is played back, the user will get an error.

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.

Admittedly, Excel VBA can be difficult to grasp for those users who have done little or no programming. This is one reason why we prefer to run a five day intensive Excel VBA training course aimed at people new to VBA. We find that this approach gives everyone a chance to gain confidence and familiarity with this o 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 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.

By: Andrew Whiteman

Author is a developer and trainer with Macresource Computer Solutions, an independent computer training company offering Microsoft Excel VBA training courses at their central London training centre.

Article Directory: http://www.articlewisdom.com

Please Rate this Article

5 out of 54 out of 53 out of 52 out of 51 out of 5 

Not yet Rated