Forget The Macro Recorder With Excel VBA Classes

Most peoples first exposure to kExcel VBA 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 great for really simple tasks such as producing a strait-forward report. However, there are distinct limitations to this approach. Firstly, because Excel plays back the steps just as they were originally performed, recorded macros are notoriously slow.

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 that recorded macros are only of use to the person who records them. They cannot be distributed to work colleagues without the originator being constantly barracked because his or her macros never seem to work properly.

One of the first things we do on the Excel VBA classes 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.

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. It also offer them plenty of opportunities to practice the skills being discussed by creating and debugging small Excel VBA applications.

Next we teach them the syntax and structure of VBA. How to use variables to store both data and references to Excel objects, how to create logical and iterative structures and how to allow the user of a macro to make choices.

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 the only real way of losing ones 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.

Fetch handy advice about home business – check out quoted web page.