VBA INTRODUCTION
In this first lesson we will discuss the basics of VBA for Excel. I purposely do not include screen shots or pictures, as I believe by omitting them it forces the student to explore and become more familiar with the environment in which they will be working. This may seem a bit inconvenient at first, but the long-term gain will far outweigh the short-term pain. The basics are probably not very exciting, but definitely necessary so you have a good understanding of the Application (Excel).
VBA is short for Visual Basics for Applications. This is the standard Macro language used in most Microsoft Office products. The word "Applications" can represent any one of the Office products it is used within eg; Excel, Access etc. The main ones being Excel, Word, Access, Powerpoint and is now, in Office 2000, moving into Outlook. The VBA language is a derivative of Visual Basic (VB), which in turn is a derivative of the language Basic. The fundamental difference with VBA from VB is that VBA is (as the name implies) used within an Application. By far the most mature of these Applications when it comes to VBA is Excel. You will find as we delve deeper into VBA for Excel that we can modify the Application so it will behave in almost any way possible.
The purpose of VBA is to enable programmers to customise and extend the functionality of the Application in which it is used. The VBA we will be talking about in all lessons will be VBA for Excel. While the VBA language is generally the same throughout other Applications, their Object model can differ significantly. Unless you are already familiar with a programming language I would recommend not trying to learn too much too soon. I believe it is far better to gain a good understanding of one small aspect of VBA for Excel than to gain a superficial knowledge of a broad aspect. I can honestly say (without hesitation) that I may not know every single aspect of VBA for Excel, but what I do know, I know well. Please at anytime throughout the course never feel that any question is a silly question and if you do not understand an answer I supply for a particular question then it is important you say so. Trust me I have immense patience! One of the most important things about the Basic VBA for Excel course is that you have an understanding of each lesson before moving on to the next lesson.
GETTING STARTED - THE LOOK OF THE VISUAL BASIC EDITOR
To write any VBA code (not record), we need to go into the VBE (b>Visual Basic Editor). There are many ways to do this, but by far the easiest is by pushing the shortcut key Alt + F11 (hold down the Alt key and push F11). You can also access the Visual Basic Editor by going to Tools > Macro > Visual Basic Editor from within Excel.
Below is a summary of arguably the most important components of the Visual Basic Editor. I would like you to work through this, so open a new workbook within Excel. Open the Visual Basic Editor using one of the ways described in the previous paragraph (Alt + F11 is the fastest). It is important that you use a new Workbook so that all names and terms we refer to are the same. The positioning of the windows I refer to may be different in your view, but I will use the headings also so you shouldn't get lost.
At the very top of the VBE you have what is known as the Menu Bar. From this one menu bar it is possible to access most functions of the VBE. If you right click on the grey area just to the right of Help on the Menu Bar a shortcut menu should appear. Select Customize... Here you will see the names of all the Toolbars that can be available to you. As a general default you will have the Menu Bar and the Standard Toolbar showing. To make sure we are looking at the same view, go to View > Project Explorer, then back to View > Properties Window. Or to use the shortcut keys, Ctrl + R for Project Explorer, and F4 for the Properties Window. You should now have visible the Project Explorer and the Properties Window. If you go up to the top Menu bar and click on any menu item you will notice that many of the functions available have their associated short cut keys written next to them. Get to know these well and working from within the VBE will become mush easier.
Project Explorer and Properties Window
Within the Project Explorer (the small window with the heading Project - VBAProject) you will see at the top VBAProject (Book1). This window and it's folders are very similar to the folders you would see in Windows Explorer in that you will be able to expand and collapse folders by clicking on the + or - signs to expand and collapse them. Expand VBAProject (Book1)! Once the VBAProject (Book1) is expanded, you should see a folder called Microsoft Excel Objects. Expand this folder and you will see the Objects that the Workbook contains. In most cases this will be Sheet1 (Sheet1), Sheet2 (Sheet2), Sheet3 (Sheet3) and ThisWorkbook. It is not necessary to know anything more about these at this stage, but I will be showing you how they can be used in a later lesson. But as you have no doubt guessed, the Sheet1, Sheet2 etc refer to the Worksheets in the Workbook, while ThisWorkbook refers to the Workbook itself. If you now click on Sheet1 in the Project Explorer you will see in the Properties Window (the window probably directly beneath) a list of all the Properties for a standard Worksheet. Don't be too concerned with what Properties are at this stage, we will go into the detail later.
COMMON TERMS USED IN EXCEL VBA:
Modules
The grey area of your screen is where your modules (where code is written) are located. As this is a new Workbook we are looking at, there will not be any Modules visible. To insert a Module we go to Insert > Module. You should then see a white background where it used to be grey, this is what's know as a Module, sometimes referred to as a standard Module or code Module in earlier versions. It is within these Modules that we can write VBA code. The code we write is written within a Procedure, a Procedure is a series of statements giving Excel instructions on what to do (a macro). We can have as many Procedures within a Module as we like and we can also have as many Modules within a Workbook as we like, the only restriction is the PC's available memory.
There are 2 types of Modules in the Excel VBE, these are Module and Class Module. The Module (standard Module) is the one we will be using throughout this course. The standard Module can also have what is known as Private or Public modules. By default all standard modules are Public. The Private Module is used in some of Excels other Objects, ie UserForms, Sheets and ThisWorkbook. Again we wont go into any more detail on this yet as we will be covering these later.
One thing you will find yourself doing many times over when writing VBA code is toggling between the VBE and Excels user interface. The simplest way to do this is to either again push Alt + F11 or click on your Microsoft Excel Workbook located on the Task Bar. Let's now look at the Fundamentals of VBA for Excel.
Procedures
Procedures are a named set of statements that are executed as a whole. They tell Excel how to perform a specific task. The task performed can be very simple or very complicated. It is good practice to break long or complicated procedures into smaller sized logically ordered procedures. The two main types of Procedures are Sub and Function. A Sub procedure is what results from recording a macro, while Function procedure must be written manually. We will be looking at Function procedures in a later lesson. All Sub procedures must begin with the word Sub followed by a chosen name and then a set of parenthesis. All Subs must end with End Sub. See example below:
Sub MyMacro()
'
End Sub
Keywords
Keywords in Excel VBA are words that Excel has set aside to use in the execution of code. This means we cannot use them for any other purpose. For example, Select, Active, Sub, End, Function etc are all Keywords that we can only use for their indented purpose.
While working in the Visual Basic Environment we can get help on any keyword by simply placing our mouse insertion point anywhere within the keyword and pressing F1. This will force the Excel help to default to the specific topic and saves a lot of hunting around. I urge all users to use this feature to its fullest.
Comment Text
Comment text is what we can use in a Procedure to help explain what our code is doing, or rather why it is doing it. To identify comment text to Excel we must precede it with a single ' (apostrophe). Excel will ignore any text preceded with a '. I strongly suggest using lots of comment text in procedures as it helps greatly when you come back to it later to edit the code. See example below:
Sub MyMacro()
'Place the value 150 in cell A1 of the active sheet.
Range("A1").Value=150
End Sub
You will notice that comment text is a different color to procedure code. This is so it is easily identified as such.
The default color coding that Excel uses for Comment text, Keyword text, Procedure text etc can be changed by going to Tools>Options and clicking the Editor Format tab. don't get too carried away though :o)
Line Continuation Character
The Line Continuation Character is used to tell Excel that more than one line of code is really a single line. It is represented by _ ( space then an Underscore). So to continue a line of code onto the next line in a module you type a space followed by an Underscore. The reason you would use this is so that you would not need to scroll sideways to read a long line of code. See example below:
Sub Find100()
'
' Find100 Macro
' Finds the value of 100 on the active sheet.
Cells.Find(What:="100", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
End Sub
The above example is really one continuos line of code. As you can imagine this would not fit on most PC monitors if we did not use a Line Continuation. Well, not unless you are lucky enough to have an 80 inch screen!
IMPORTANT DEFINITIONS:
Objects
The word Object in the context that we use it is used to describe just about everything in Excel. You will find as you get deeper and deeper into VBA that there are many Objects. Don't confuse the word with its usual use of referring to something tangible. In Excel, an Object can be a range, a worksheet, a chart or even the Visual Basic Editor (VBE) itself. You will find as you learn more about Excel VBA that there are a lot of Objects!
The very least that you need to know about Objects is that there is what is known as the Object Hierarchy. At the top of this order we have the Application Object, Excel itself in this case. Directly underneath the Application we have the Workbook Object eg; Book1.xls. Directly underneath the Workbook Object comes the Worksheet Object. At the Worksheet Object the Object Hierarchy branches off to incorporate all Objects of the Worksheet. The first one you will most likely encounter will be the Range Object. Branching from the Range Object there are again many other objects such as the Areas Object, the Borders Object, Font Object, Characters, etc. To see a complete description, of the hierarchy of Objects go to the VBE for Excel Help and type Microsoft Excel Objects. Don't let this confuse you, as all you need to know at this stage is the following order:
- Application
- Workbook
- Worksheet
- Range
When you have a group of Objects that are related, this is then known as a "Collection". So when we use the term Workbooks, we are referring to all open Workbooks, and when we use the term Workbook, we are only referring to an individual Workbook (the active Workbook).
You will notice when reading the above paragraph and in the VBE Help, that the Workbook Object is part of the Workbooks Object. In layman's terms, the Workbook is a member of the Workbooks collection. It is also the same for the Worksheet Object, this is a member of the Worksheets collection. Don't worry too much if you don't grasp this concept immediately, it will become apparent as we progress.
Properties
Properties are an attribute of an Object. They are used to define an Objects characteristics, so to use an example; the Worksheet Object has many Properties, one of which would be its name. So by changing it's name, you are directly changing one of its Properties. Also by making the Worksheet hidden, you are again changing one of its Properties, in this case, the Visible Property. To be able to change the Property of any Object we must first identify the Object of whose Property we wish to change. In the example above we would do this by telling Excel which Worksheet we wish to rename. You will see how this can be done later.
Methods
Like Properties, Objects also have Methods. A Method is simply a procedure that acts on a Object. By this I mean it causes the Object do do something, this might be opening a Workbook (which is an Object) or deleting a Worksheet (another Object) or any one of thousands of other things.
Events
An Event in Excel VBA is as the name implies. For example a mouse click is an event as is the closing or opening of a Workbook, in fact there are hundreds of them. The best part about Events is that we can we can programme Objects to respond to Events. This is covered in detail later in lesson 6.
The Macro Recorder
The Macro recorder in Excel is a great tool to use as a VBA learning aid. It will record any steps taken while in Excel and write the code so as they can be performed again. By studying the code written by the Macro recorder we can better understand the VBA language as applied to Excel. Having said this though it is important to realize that it does not write very efficient code. This is because it is only recording the steps you have taken to perform a particular task. Most of these steps are not necessary when writing efficient VBA code. For example you might record a macro going to all Worksheets within your Workbook and changing the Format Properties of the cells. To do this you would need to activate each Worksheet and then scroll to the required cell(s), select them and then format them as required. You would find that the code generated by the Macro recorder would be quite lengthy and most of it would be superfluous. This is because the Macro recorder cannot really write VBA code as it should be written, it can only write the code for ALL the steps you have taken. As a general rule with recorded code you can remove all the words like: Select, Activate, Scroll etc. This is because we very rarely need to actually Select, Activate or Scroll to a Worksheet or Range to perform any action on it. You will see what I mean by this soon!
While it is true that the Macro recorder is a good method for learning VBA there is also another reason why we should use it. This is because it can eliminate typos and save time. Although I can usually write the code for a particular task in Excel, I often record a macro doing it and then go into the VBE and edit it so that it becomes much more efficient.
To activate the Macro recorder go back to Excel (Alt+F11), go to Tools>Macro>Record New Macro. This would display the Record macro dialog box. It is here we can give our macro a one word meaningful name. There is also a drop down arrow asking where we want to store the macro, for all purposes unless stated otherwise, we will use "ThisWorkbook."
Exercise
- Start recording a macro (call itChangeRangeProperty)
- Select CellA1
- Go toFormat > Cells > Number > Currency
- Select any one of the Currency Formats
- ClickOK
- Stop recording the macro
Open the VBE (Alt + F11). In the Project Explorer, you should now see under Microsoft Excel Objects another folder (Object). In this case the Modules Object, and within that you will see Module1, which again is another Object. Modules is a collection of Objects which in this case are the ModuleObject. Double click on Module1 to open the Module. You should see some code as shown below.
Sub ChangeRangeProperty()
Range ("A1").Select
Selection.NumberFormat =
End Sub
Let's go through each bit of this and define what each word means.
Sub
The word Sub is simply letting Excel know that there is a Procedure contained within it. A Procedure is a series of statements giving Excel instructions on what you wish to do. Basically a Procedure is Marco.
()
After each Procedure name is a pair of empty parenthesis. This is reserved in case wish to add arguments to the Procedure. These can be similar to the arguments that are often used in Worksheet formulas. Do not worry about the arguments at this stage as it will only add confusion and we can easily get by without them.
Range
A range as we have discussed above is member of the WorksheetObject. You will notice that the recorded macro does not include the WorksheetObject (the sheet we changed number format of cell A1). This is because the default for the RangeObject on it's own is always the active sheet. So unless the range we are referring to is on a different sheet, there is no need to use the WorksheetObject.
"A1"
This is simply the cell reference address that we selected. It must be enclosed in quotations as Excel sees cell addresses as text or strings (chain of characters that represent the characters themselves rather than their numeric values)
.Select
Select (in this case) becomes a method of the RangeObject. A method is a Procedure that acts on an Object. If we had selected a Worksheet it would be a WorksheetObject, a Chart a ChartObject etc.
Selection
Selection is as it states, simply returning to Excel the Object that has been selected. In this case, the RangeObject. The key word Selection is very generic as it can refer to nearly all Objects within Excel, but as the line Range ("A1").Select is the line of code immediately before it, Excel knows that in this case Selection is referring to a RangeObject only.
.NumberFormat
NumberFormat is a Property of the RangeObject.
=
Is simply the format type that you have chosen.
End Sub
End Sub tells Excel that the Procedure has finished.
While we can certainly find out a lot from recording a macro and studying it's code, you will find as time goes on that the recorded code will include many lines of code, Properties, Methods etc., that are not needed.
As an example we could easily shorten the above-recorded macro to read:
Sub
Range ("A1").NumberFormat =
End Sub
The reason we would do this is
- It means less typing (good!!)
- It means faster execution of code (although barely noticeable in this small example)
- It means there is no need for the active cell to change. So this means that if you ran the Macro immediately above, while any cell except A1 is your active cell, the code will run without the user knowing.
What this means in a nutshell is that in MOST instances there is no need to select or activate an Object to change any one of it's Properties.
Exercise
Record three macro's as follows:
- Selecting any cell, typing a number in and pushing ENTER.
- Selecting a different Worksheet and highlighting the range A1:D10 change the background colour to yellow.
- Selecting any cell, typing a number in and pushing ENTER. Copy the number you just typed and paste it to another cell
When you have recorded these three macros, I would like you to try and modify the code on all of them so that the words "Select" or "Selection" does not appear within the Sub.
Make use of the VBE Help. When you have modified as much as you can so that they still work the same, e:mail them to me. If you get stuck, please do not hesitate to contact me.
I fully realise that a lot of the Object, Property, terms etc. used in VBA can seem daunting, but please don't be deterred at all if you do not understand or grasp the concept. Take my word for it, there are many programmers out there who quite successfully use VBA without fully understanding the concept of what we have described here.
When the next lesson commences is entirely up to you. Just keep in mind that it is important to understand what we have discussed here before moving on.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
Gallery
