Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andrew Gould
In this tutorial
Visual Basic for Applications, or VBA for short, is a powerful programming language that is embedded in every Microsoft Office product. Whether you are using Word, Excel or PowerPoint, you are only ever a few mouse clicks away from starting to write your own programs.
Because we use VBA in so many of our blogs, we thought it would be useful to have an article that introduces the uninitiated to the most fundamental techniques you'll need to start working with VBA. So even if you've never written a line of code, or recorded a single macro, fire up your favourite Office application and read on to take your first steps on the way to becoming a VBA programmer!
Obviously another way to learn VBA is to attend one of our VBA training courses.
Whichever Microsoft Office application you happen to be using, you can be sure that embedded within it is another application called the Visual Basic Editor. This is the application that you use to write your VBA code. There are various menu or Ribbon options that will take you to the VB Editor, depending on which application and which version of Office you are using, but you can always get to the VB Editor with a keyboard shortcut. To do this, hold down the ALT key on the keyboard, and then press F11.
This is how the VB Editor should look the very first time you open it.
In the above diagram, the two windows that we've highlighted are:
1. The Project Explorer
2. The Properties window
The VB Editor application is the same regardless of which Office application you are in when you open it. You will see slightly different things in the Project Explorer and Properties window depending on which application you are using - the diagram above is using Excel 2007.
You can use the shortcut ALT + F11 at any time to toggle between the VB Editor and the Office application you are working in. Much quicker than clicking with the mouse!
If you can't see the two windows that we've highlighted in the image above, you can go to the View menu at the top of the VB Editor to display them.
You can click the options shown here to show the two relevant windows, or use the keyboard shortcuts that are listed next to the options in the menu.
A project is the name for the collection of VBA objects that are part of the file you are working on. A project is created automatically when you open a new Office file (such as an Excel workbook, Word document, or PowerPoint presentation), and you can only have one project associated with one file.
The only useful thing you can really do with a project at this point is to rename it. To do this:
1. Click on the project in the Project Explorer.
Although you can only have one VBA project for each Office file, you might also see other projects listed when you go into the VB Editor. These extra projects include things like the Excel Personal Macro Workbook, the Word Normal template, Excel Add-Ins, and the Project Global template.
The VB Editor in Microsoft Project always contains the ProjectGlobal VBA project. This project is attached to the Global.MPT template file. | In Microsoft Word you will always see a VBA project associated with the template your document is based on - usually this will be the Normal template. |
Before you can start writing any code, you need to insert a module. A module is the electronic equivalent of a blank piece of paper. Each project can contain multiple modules - the exact number you have will depend on the size and complexity of the system you are developing, as well as your personal preference on how to organise your code.
To insert a new module, right-click on the VBA project you want to insert the module into, and from the menu choose: Insert -> Module
You can actually right-click anywhere in the project you are working on to do this, but it's easier to spot the bold text of the project name when you have several files open.
When you've done this, you'll see a new item in the Project Explorer window, and the great white expanse of the module you've just created taking up most of the right hand side of your screen.
You can rename a module in the same way you renamed a project earlier: select the module in the Project Explorer, and type in a new name in the Properties window. Again, you can't use spaces or most of the punctuation characters - stick to text and numbers to be safe!
Now that you've inserted and renamed a module, you can click into it with the mouse and start typing your VBA code. Read the next part of this series to find out exactly what you need to type!
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.
If you've been following this blog series so far you should have a newly created subroutine with a flashing text cursor poised and ready to write some real code. This article will explain some of the basic rules of writing VBA and show you a couple of tricks to get you creating code as quickly as possible.
if you want to hear and see a human (well, wise owl) delivering this information, have a look at the courses we offer in VBA.
VBA is a language, and like any language it has grammatical rules that you need to follow if you want to make sense when you're "speaking" it. Generally when you're writing VBA programs, most of the time you'll be attempting to perform some kind of action on some sort of object. The structure of a line of code that performs an action on an object is very consistent in VBA - you always start the sentence by referring to the thing you want to do something to, followed by the action you want to perform, and you use a full stop to separate the two. So, very generally speaking, a basic sentence in VBA would look like this:
In VBA terms, the Thing part of the above sentence would technically be called either a collection or an object. The Action part of the sentence would be referred to as a method. So, the technical way of representing the above sentence would look like this:
Bearing this in mind, we're going to write a line of code that will apply the Add method to the Workbooks collection.
In our first line of code, the collection part of the sentence is the word Workbooks. Workbooks is the name for the collection of all of the currently open Excel files. Type it into your code and follow it immediately with a full stop.
Typing in a full stop after a word that VBA recognises presents you with a list of other words you can use to finish the sentence.
After typing in the full stop you should see a list of keywords appear automatically. This feature is referred to as Intellisense - horrible name, useful feature! The next section describes several ways to use Intellisense to save you as much typing as possible.
After the Intellisense list appears we can complete our sentence in a number of ways. The method part of our line of code is the word Add - to get this word into your code you could do any of the following:
Option | Effect |
---|---|
Type the word manually. |
The word is typed in and the cursor stays at the end of
the line. |
Use the mouse to scroll to the word you want and double-click on it. | The word is inserted automatically and the cursor appears immediately after the word. |
Use the arrow keys or start typing the first letters of the word to highlight it in the list, then press Tab on the keyboard. | The word is inserted automatically and the cursor appears immediately after the word. |
Use the arrow keys or start typing the first letters of the word to highlight it in the list, then press Enter on the keyboard. | The word is inserted automatically and the cursor appears on a new line below the previous one. |
Probably the quickest technique to use in this example is to type in the letter A which will automatically select the word Add in the list, and then press Enter.
Congratulations, you've finally written your first line of code! When we get around to running our subroutine, this line will create a new workbook.
Adding items to a collection is a standard way to create new objects in VBA. The screenshots below show examples of doing this in Word and PowerPoint.
In Word, the collection of open files is called Documents.
In PowerPoint, the collection of open files is called Presentations.
So far we've seen how to create a new workbook in Excel VBA by applying the Add method to the Workbooks collection. Now that we've done this we need to add some text to some of the cells in the file that we've just created. We're going to do this by modifying a property of an object. Properties are like methods in that they always appear after the object or collection in a VBA sentence. Unlike methods, you can assign a value to a property to change it in some way. The basic grammar of a line of code to do this would look like this:
The object we are interested in is a cell, which is referred to as a Range in Excel VBA, and the property we want to change is called Value. Type the following into your code:
You can identify the properties in the list by their "finger pointing at a piece of paper" symbol.
Referring to a Range object is slightly more complicated than referring to the Workbooks collection because we have to say which cell we are interested in. We do this by typing a cell reference (here it is A1) inside a set of round brackets and double-quotes. Next, we can type in a full stop to display the list of properties and methods.
The quickest way to select the Value property from the list is to do the following:
This should leave you with a subroutine looking like this:
The only remaining thing is to say what we want to change the value of the cell to.
We can now say what text we want to appear in the cell. To do this we need to type in an equals sign, = followed by the text. All literal text in VBA must be enclosed in a set of double-quotes. Type in the following and press Enter at the end of the line.
When you press Enter at the end of the line you should see a space appear on either side of the equals sign.
As a final flourish in our very basic program, we're going to write a line that will put today's date into another cell on the spreadsheet. This line will look very similar to the one we've just created, so type in the following code. Try to remember the quickest way to use the Intellisense list - hint: it doesn't involve the mouse!
Press Enter at the end of the line and you should see the word date becomes capitalised.
Rather than putting in the date as a string of literal text, we've used a built-in VBA function called Date. This function calculates what today's date is each time the code is run (based on your computer's clock) and puts the result of the calculation into the cell.
Although we've barely scratched the surface of what you can do with VBA, you've learnt a couple of basic rules of grammar that will help you as you learn how to do new things. One fairly good way to find out how to do other things in VBA is to record a macro. Recording a macro means that you can do a bunch of things in an Office application, formatting some cells in Excel for instance, and have the application write out the VBA code for you as you are doing it. This technique has some limitations, but it is a neat way to discover new methods and properties.
So you've written a program, but you don't have any idea if it's going to work! The next article in this series shows you how to run a program from a developer's point of view and explains a few useful techniques for letting other people run the code you've written.
If you've been following the rest of this blog series you should be at the stage where you've written a (very) simple subroutine, but you haven't actually tested it yet. This article explains how to run your code as a developer to test that it works.
Trust us when we say that you always want to save your file before you run any subroutines! The main reasons for doing this are:
Once you've run a macro, you can't use the Undo tool to reverse the actions that it has performed.
There is always the possibility that your subroutine will crash the application when you run it so that you lose the code you've written.
You can choose to save your code either in the VB Editor, or in the Office application you are working in. Technically you are only ever saving an Office document - the VBA project simply sits inside the file in the same way that a worksheet sits inside a workbook.
Saving in the VB Editor | Saving in Excel |
If you are working in Office 2007 or later you need to make sure that you use one of the macro-enabled file types when you save your file. If you try to use a normal file type when you save a file that contains VBA code, you'll see a message like this one:
If this message appears, don't click the Yes button! If you do that your macros will be deleted and the file will be saved. Click No instead and choose a different file type, as explained below.
Each Office application has a set of file types that can be used to save your code. If you clicked No on the message shown above, you'll be taken to the Save As dialog box where you can choose one of these file types. The diagram below shows part of the list of file types you can choose from in Excel 2007.
Click on the Save as type: option to select a different file type.
Now that you've saved the file, you're ready to run your subroutine. Although there are many ways to do this, we're going to run our code from within the VB Editor. To do this, click anywhere in the subroutine you want to run, and then either:
Press F5 on the keyboard.
From the menu, select: Run -> Run Sub/UserForm.
Click the Run Sub/UserForm tool on the toolbar, as shown below.
The Run tool looks like a green triangle pointing to the right, like the play button on a DVD player.
Wise Owl does not recommend the use of crossed fingers, lucky four-leaf clover or prayer when running subroutines. In our experience it doesn't help and encourages sloppy programming.
If nothing went horribly wrong and you didn't see any error messages you can now switch back into the Office application (ALT + F11) to see if your macro worked.
Success! It's surprising how satisfying it can be when even a simple macro works first time.
If you were unlucky enough to have something go wrong when you tried to run your code, the next article in this series will give you a few pointers about how to go about identifying and fixing the problem.
When you run a subroutine using the methods described above you'll find that the code is executed as quickly as the Office application can carry out the instructions. As a developer it's nice to be able to slow down the running of your code so that you can watch what's happening as each line is executed. This technique is called stepping through code. Before stepping through a subroutine it's useful to shrink down, or restore, the VB Editor window so that you can see the Office application in the background. The diagram below shows how you can do this:
At the top right hand corner of the VB Editor is a set of three buttons. Click the middle one of these to shrink the window down so that you can see the Office application in the background.
Once you've restored the VB Editor window, you can click and drag on the border of the window to change its size, and click and drag on the title bar of the window to move it around the screen.
When you've got your screen set up, you can start stepping through your code and watch what happens in the background. To do this:
Click into the subroutine you want to run in the VB Editor.
Press F8 on the keyboard to start the subroutine.
Press F8 to run the line of code that is highlighted in yellow - continue doing this until the subroutine ends.
Press F8 to run the line that is currently highlighted in yellow. Carry on doing this until you have run the End Sub line.
Take care that you don't keep pressing F8 after you have run the End Sub line. If you do, your subroutine will start again at the beginning!
If you get bored of stepping through a long subroutine, you can always press F5 at any stage to run the rest of the code, or click the Reset tool (it's the one that looks like a little blue square) on the toolbar to stop the code running.
If you're writing a set of subroutines that you want other people to be able to use, you don't want them to have to go into the VB Editor and find the right macro. There are many other ways to run macros, including assigning a shortcut key to them, or attaching them to buttons, pictures or toolbars. These separate Wise Owl blogs explain how to do this in more detail.
In the words of the famous guide book, "Don't panic!" There could be a huge number of things that have gone wrong when running a subroutine. The next article in this series talks about some of the most common mistakes people make when writing and running VBA code.
Lots of things can go wrong when you try to run a subroutine, and not all of them are down to coding errors. This article explains a few of the common mistakes people make when running VBA code and how to get around them.
You might think that this is a silly mistake to make, but it's surprisingly common, especially on training courses! All of the Microsoft Office applications have a number of macro security levels and the level that is set determines when macros are allowed to run. If you try to run a macro when they are not enabled, you'll see a message like this:
This is an example of the type of message you'll see when macros are disabled but you try to run one anyway.
Making sure that macros are enabled works slightly differently depending on which version of Office you are using, but in essence there are two steps involved:
Choosing an appropriate level of security.
Choosing to enable macros each time you open a file that contains them.
To choose the appropriate level of security in Office 2003 applications:
From the menu, choose: Tools -> Macro -> Security...
On the dialog box that appears, select Medium, and then click OK.
Medium is probably the most sensible option to choose. A Low security level means that your code will always be allowed to run, but so will everyone else's, including potentially dangerous code.
To choose the appropriate level of security in Office 2007 or 2010 applications:
From the Ribbon, select: Developer -> Macro Security.
On the dialog box that appears, choose the option shown in the diagram below.
The selected option in this diagram is the equivalent of the Medium security level in Office 2003.
Once you have done this, close down the application and then reopen it. The next time you open a file that contains macros you must choose to enable them. This works differently in the different versions of Microsoft Office.
In Office 2003 applications when you open a file containing macros you will see a dialog box like this one:
Click Enable Macros to make sure your code will run.
In Office 2007 you will see a small message appear below the Ribbon when you open a file containing macros:
Click the Options... button to choose to enable macros.
On the dialog box that appears you can then choose to enable macros:
Choose this option and then click OK to make sure your code can run.
In Office 2010 you will see a message appear under the Ribbon - simply click the Enable Content button to make sure your macros can run.
Click the button shown here to make sure you can run your subroutines.
When you are sure that macros are enabled there are still plenty of other potential sources of error!
If you are running your code from within the VB Editor, you have to select the subroutine you want to run before you try to execute it.
If the text cursor isn't within the subroutine you want to run, the VB Editor won't know which one to run.
If you don't have a subroutine selected at all you will see a dialog box appear to ask you which one you want to run.
You could always select the macro at this point and click Run, but it's much easier to just click in the macro you want before you try to run it!
Syntax errors are mistakes in the punctuation of your VBA sentences. These are very easy to spot because as soon as you make a syntax error and try to move the cursor to a different line of code, the offending line will be highlighted in red.
With the default settings in the VB Editor, you will also see a dialog box attempting to explain the problem.
To solve this issue, click OK on the message that appears and then try to amend the line of code that is flagged in red. Here the mistake we've made is to miss the double-quotes after the cell reference A2. When you have edited the line of code, move the cursor to a different line to check that it doesn't get highlighted in red again.
The dialog box that pops up to warn you about syntax errors is never particularly useful, and it's quite annoying to have to click OK before you can fix the problem. You can turn off this warning message (but still have syntax errors highlighted in red) by selecting: Tools -> Options... and then completing the dialog box that appears as shown below:
Make sure the Auto Syntax Check box is unchecked and then click OK to make sure you don't see the annoying pop-ups every time you make a mistake.
When you choose to run a program that you've written in VBA, the VB Editor first of all compiles your code to make sure that all the words make sense in the context you've used them. A compile error is a problem that occurs at the compile stage but before your code has actually started running.
In this example we've misspelt the name of the Add method. The VB Editor has helpfully highlighted this for us so our job of fixing it is as easy as possible.
To fix a compile error, click the OK button on the message that appears and try to identify what is wrong with the part of the code that has been highlighted. Compile error messages are often quite descriptive, like the one shown below:
It's fairly obvious what this error message means, and as if it wasn't obvious enough the offending part of the code is highlighted again.
You can ask the VB Editor to compile your project without trying to run a subroutine by selecting: Debug -> Compile ProjectName
Run-time errors are errors that occur when your program has successfully passed all of its syntax checking and compiling and is in the process of running. Any executable line of code has the potential to generate a run-time error and it's not always obvious why one has occurred. The diagram below shows an example of a run-time error:
It's not immediately obvious what's gone wrong here as run-time errors don't highlight any of your code immediately. You can click the Debug button to see which line of code has failed to run.
When you click the Debug button on a run-time error dialog box, the line of code that has failed will be highlighted in yellow. In the example below you can see that it's the second line of code that has gone wrong.
Closer inspection should reveal a mistake in the cell reference we've typed in.
This is just one of many examples of run-time errors that you'll experience when running VBA code. Run-time errors are often the most difficult to resolve and there's really no substitute for experience when things get tricky. The best way to learn is to try something, get it wrong, and then fix it!
If you've been following this blog series from the beginning and you'd never even used VBA before, congratulations! You've come a long way from where you started, but there's still an awful lot to learn before you become a fully fledged programmer. The key now is to start experimenting with new techniques: try recording some macros to see the code that gets written for you; edit some recorded macros to see what happens; read more Wise Owl blogs to give you more ideas. Above all, learning to program in VBA can be immensely satisfying, so have fun with it!
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.