Introduction to VBA
I find that people who are new to VBA have very grandiose goals. They want to take all the work they do in the course of a day, or even a week, and automate every last step of it. While in theory this would be amazing, it’s not always practical, and certainly not the best place for a beginner to start. You’d be surprised how much time you can save by automating small things like applying a custom number format to cells in Excel, or converting text into hyperlinks in a word document.
VBA allows a programmer to access all the core functionality of a piece of software while wrapping it in logic that represents their own custom workflows. Within the realm of MS Office, this logic (or VBA code), can exist in four main areas. The first area, which happens to also be the most common, is called a module. An interesting fact that not too many realize, is when you record a macro it is actually saved into one of these modules and completely viewable and editable by you as VBA code. So in essence then, a macro is nothing more than a chunk of VBA code that gets created by the software program when you record it. You may be asking yourself – Who cares, the macro just works, so why would I ever need to look at the code? The answer to this question is so very important and the most valuable tip I, or anyone for that matter, could give you. As mentioned in my post What Is VBA, it is all about automation of repetitive tasks. So if you record a macro of you carrying out one of these repetitive tasks, you’ve already accomplished the first step in automating it. Now that you have your logic saved as VBA code within module, all you have to do is wrap this logic some sort of looping structure, so that it can be repetitively executed in an automated fashion.
The second most common place for VBA code to reside is in the document itself. Although the concept of the document differs depending on the software program, it is always prefixed by the phrase “this” and represent the file that you are working on. For instance, in Excel it would be called
ThisWorkbook, and in Word it would be
ThisDocument, etc. The advantage of adding code at the file level is that you can listen for different events, and have code automatically executed when these events take place. As an example, let’s suppose that every time you create a new worksheet in excel you want to fill it with some default values. You could listen for the
NewSheet() event and automatically populate it with your default values.
The Last two areas to write VBA code are Class Modules and UserForms. Most people need not dabble with these, but for advanced VBA coders they can certainly be useful. For anyone familiar with Object Orientated Programming (OOP) the Class Module is used to create insatiable objects which can contain properties and functions. In addition, advanced users can take VBA a step even further and actually create a User Interface (UI) to go along with their scripts, giving the appearance of a highly integrated solution. There are several of reasons why a developer might want to do this. Perhaps to ask the user questions before their VBA script runs, or maybe because they want to display feedback to the user as to what is happening.
There you have it, a quick introduction to VBA and explanation of some of the moving parts. For product specific Introductions, please see the following tutorials: