ABOUT US
V EN ENUES
501 T ES ES TI TI MO MONI AL ALS
RESOURCES
01457 858877
CONTACT
OUR COURSES
Wise Owl Training
GO
Blogs Blo gs
Introdu Int roduct ction ion to Visual Visual Basic Basic for Applic Applicati ations ons (VBA (VBA))
Writi Wr iting ng VBA VBA Code (Visual Basic for for Applc Applciat iations) ions)
This page has 0 threads threads | Add post
If you found this blog useful and you’d like to say thanks you can click click here here to to make a
Introduction to Visual Basic for Applications (VBA) (VBA)
contribution. Thanks for looking at our blogs!
BLOGS BY BLOGS BY TOPIC TOPIC
Part three 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
Blog home page (346)
clear. It's an introduction to the most basic skills you'll need to start
Excel + (122)
creating your own programs in any of the Microsoft Office applications.
SQL Server (148) Programming (138) General (22) Archived Archi ved (70)
SIGN IN
1.
Introdu ntroduction ction to VBA (Visual Basic Basi c for Applications) Applic ations)
2.
Creating Your Your First VBA Program P rogram
3.
Writing VBA Code (Visual Basic for Applciations) Applciati ons) (this blog)
4.
Running a Subroutine in VBA macros
BLOGS BY AUTHOR
Andrew Gould (81)
Posted by Andrew Gould on 01 July 2011
Andy Brown (249) David Wakefield (3) Jenny Brown (1) Michael Allsop (12)
BLOGS BY YEAR
2010 (2) 2011 (76) 2012 (100) 2013 (45) 2014 (38) 2015 (23)
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.
2016 (52) 2017 (10)
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: Thing.Action
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: Object.Method or Collection.Method
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
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:
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.
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: Object.Property = SomeValue or 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: 1. Type in the letter
V
to jump to the word Validation.
2. Press the down arrow key on the keyboard to select Value. 3. 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 VBAfunction 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.
1.
Introduction to VBA (Visual Basic for Applications)
2.
Creating Your First VBA Program
3.
Writing VBA Code (Visual Basic for Applciations) (this blog)
4.
Running a Subroutine in VBA macros
5.
Problems When Running VBA Code
This blog has 0 threads
Add post
© Wise Owl Business Solutions Ltd 2017. All Rights Reserved
S it ema p
Te rms & C on di ti on s
P ri va cy p ol ic y
