562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Introduction to Visual Basic for Applications (VBA)
Part two of a five-part series of blogs
If you've heard people in your office talking about macros or VBA but don't understand what they mean, this blog series will make everything clear. It's an introduction to the most basic skills you'll need to start creating your own programs in any of the Microsoft Office applications.
In the first part of this blog series we saw how to get into the VB Editor application and insert a module. You're now ready to start writing your first VBA program!
The most common type of program you're likely to write in VBA is called a subroutine, or sub for short. You can start writing a subroutine by typing the word sub at the top of the module you created earlier (we're using Excel for this demonstration).
Type in the word sub, followed by a space to get started with your subroutine.
You'll hear lots of words used to refer to a subroutine - program, procedure, macro, and sub for instance. They all mean essentially the same thing - a set of instructions that an application will follow when you run the subroutine.
The next thing you need to do is type in a name for your subroutine. Names for subroutines follow the same rules as names for projects and modules: don't use spaces and try to avoid punctuation characters. If you can make the name of your program unique and descriptive then all the better! The program we're going to write in this first example will simply create a new Excel workbook and write a title into cell A1 on the first worksheet. We'll call our subroutine CreateAndLabelNewFile. Type this into your module after the word sub.
Notice we've typed in the word sub in lowercase letters, but used uppercase letters to start each word in the name of the subroutine. This is a common convention used in VBA programming to make it easier to read the names of things.
The only other thing you need to do to create your subroutine is to press Enter on the keyboard, so do just that!
Congratulations, you've created a subroutine! Obviously it doesn't actually do anything yet, but we're getting to that part.
Assuming that you didn't see an error message, several things should happen automatically when you press the Enter key:
All of these things are important, but the VB Editor does the work for you - it's like a more helpful and fancy version of the Notepad application.
If something went wrong and you saw an error message when you pressed Enter, the two most likely reasons are:
1. You put a space or other disallowed character in the name.
2. You typed in a name that is reserved by the VBA language.
If you see this error message, just click OK and make sure that your sub's name doesn't contain any spaces or punctuation characters.
So far, the code that we've written doesn't actually perform any useful actions. That's something we'll solve in the next article in this series, but first you should learn a little about how to lay out your code neatly.
Once you've created your subroutine, you can start typing the VBA code on the very next line. The problem with doing this is that when you come back to look at your code later you'll find yourself staring at a horrible looking chunk of text that's very difficult to read.
You can make your life much easier by using blank lines to separate parts of your code, and indenting text within a subroutine to make it easier to see where it starts and ends. To do this, once you've created your subroutine by pressing Enter, press Enter once more to create a blank like, and then press Tab on the keyboard to indent your code.
|Your text cursor should start here...||...and end here.|
It may seem like a pedantic thing to do, and technically speaking it's not even necessary, but it will definitely make your life easier in the long run. Trust us! You can hopefully see the benefits of this approach by comparing the two screenshots below (even if you don't understand the actual code!).
|Without a neat layout to the code it's extremely difficult to work out what's going on in this subroutine.||The simple addition of a few blank lines and indents makes the whole subroutine much easier to read.|
Another thing that will help you enormously when looking at your code later is a series of comments that explain what your program does. You can add a comment either on a separate line or at the end of a line of code by typing an apostrophe. You can then type whatever you like and press Enter at the end of the line to create the comment.
|After the apostrophe, type in your comment.||Press Enter and the text will turn dark green to indicate that it's a comment.|
You can see more information on the art of commenting code here. It might seem like a lot of effort, but you'll thank yourself for doing it later on!
Now that we've created a subroutine and laid out our code properly, we're ready to start writing some real code. Read the next part of this series to learn how.
|Parts of this blog|
25 Aytoun Street