Get Excel VBA Training And Forget The Macro Recorder
submitted: Jul 30th 2008 |
by: AndrewWhiteman |
Total views: 1 |
Word Count: 429 |
| 
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 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.
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.
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.
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 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 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
Author is a developer and trainer with Macresource Computer Solutions, a UK IT training company offering Microsoft Excel VBA Classes in London and throughout the UK.
Comments
No comments posted.
You do not have permission to comment. If you log in, you may be able to comment.
