| CityLinkPCs » Computers » Get Excel VBA Training And Forget The Macro Recorder |
Get Excel VBA Training And Forget The Macro Recorder
|
View PDF | Print View |
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 find we have to do on the Excel VBA training courses that we run at our London training centre is to steer users away from the macro recorder. We offer them a good grasp of the Excel object model, a way of programmatically encoding each of the elements within the Excel environment such as the application itself, workbooks, worksheets and cell ranges.
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 reliance on the macro recorder. However, the recorder should not be disregarded altogether. For example, if you find yourself working with an Excel object or procedure you find difficult to code, recording a few steps then examining code Excel generates is an excellent way to learn new VBA syntax.
About the Author
The writer of this article is a developer and trainer with Macresource Computer Solutions, a UK IT training company offering Microsoft Excel VBA training courses at their central London training centre.
Rating: Pending (21)
