Excel VBA Courses Wean Users Off The Macro Recorder
If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!
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.
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 that recorded macros are only of use to the person who records them. They cannot be distributed to 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.
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.
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.
Popularity: 3% [?]
