560 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 four 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.
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.
|Parts of this blog|
25 Aytoun Street