BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
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.
Writing VBA Code
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.
The Basic Grammar of 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.
Writing Your First Line of Code
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.
Using Intellisense to Complete a Sentence
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:
|Type the word manually.||
The word is typed in and the cursor stays at the end of
|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.
Changing Things in VBA by Using Properties
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:
Collection.Property = SomeValue
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:
- Type in the letter V to jump to the word Validation.
- Press the down arrow key on the keyboard to select Value.
- Press Tab.
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.
Working Out How to do Other Things
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.
Testing and Running a Subroutine
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.