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.

  1. Introduction to VBA (Visual Basic for Applications)
  2. Creating Your First VBA Program (this blog)
  3. Writing VBA Code (Visual Basic for Applciations)
  4. Running a Subroutine in VBA macros
  5. Problems When Running VBA Code

Posted by Andrew Gould on 01 July 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating Your First VBA Program

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!

Creating a Subroutine

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).

Starting a subroutine

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.

Naming a subroutine

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!

Finishing creating a subroutine

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:

  • The word sub is capitalised to Sub.
  • The word Sub is coloured in dark blue.
  • A set of parentheses is added to the end of the subroutine name.
  • The words End Sub appear on the line below your text cursor.

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.

An error message

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.

The Importance of Neat Code Layout

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.

Without indenting With indenting
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!).

Code without layout Code with layout
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.

Adding Comments to Your Code

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.

Creating a comment The finished 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. 

This blog has 0 threads Add post