15 Visual Basic for applications
,15.1 Why learn VBA?
As you have already seen, Excel is a very powerful tool that can be used to analyse, manipulate and present data in itself. Why then, would one use VBA? Despite all the features found in Excel you may still search for an easier way to solve a task, you may want to create a friendlier user interface, or perhaps you need to add some new capabilities into Excel. VBA gives you the ability to do this.
Many people find it quite intimidating to learn how to program just to use Excel efficiently. And the truth is, it takes some time to learn how to program, but once the effort is made you’ll see a new world of possibilities. In this chapter we’ll teach you just enough to get you going. As you walk along you’ll discover that it actually isn’t that difficult – it may even be fun.
Here are some of the things you can do with VBA.
• You can change the user interface (UI) by manipulating user interface features such as menus and toolbars and use customized user forms or dialogue boxes.
• You can create custom commands that can be executed by clicking on a button or pressing a key combination.
• You can simplify the UI by placing components such as list boxes, buttons and menus directly into the worksheet.
• You can automate mundane, repetitive tasks.
• You can develop new worksheet functions.
• You can create custom add-ins for Excel. Excel comes with a few add-ins already installed, such as the Analysis ToolPak (for statistical and technical calculations) and the Solver Add-in (for optimization and solving complex linear and non-linear problems).
VBA controls Microsoft Excel by means of macros which are also called subroutines.
15.2 The Visual Basic Editor
The Visual Basic Editor (VBE) provides a user-friendly environment for writing your VBA programs. An editor is a program used to write program code, but the VBE is much more than that. From here you can write, debug, compile, and run your code. Another term used to describe such an environment is Integrated Development Environment (IDE). This section will teach you the most important features of the VBE.
Excel 2007 and Excel 2010 don’t display the Developer tab on the Ribbon. You will need to access this tab while you are developing VBA applications. To display the Developer tab in Excel 2010 follow these steps:
1 Choose Options from the File menu to open the Excel Options dialogue.
2 Choose Customize Ribbon in the categories pane.
3 In the list of Main Tabs, choose Developer.
4 Click the OK button to close the dialogue.
Figure 15.1 The Developer tab on the ribbon.
Notice the Visual Basic button in the Code group.
To display the Developer tab in Excel 2007 follow these steps:
1 Choose Microsoft Office button.
2 Choose Excel Options.
3 Choose Popular in the categories pane.
4 Select the Show Developer tab in the Ribbon check box.
5 Click the OK button to close the dialogue.
To get into the VBE just click on the Visual Basic button in the Code group. For earlier versions of Excel you can choose Macro from the Tools menu, and then Visual Basic Editor. The fastest way to enter the VBE is by pressing Alt+F11. To go back to the worksheet just press Alt+F11 again.
The first time you enter the VBE it will look similar to the display in figure 15.2. There are usually several smaller windows shown within the VBE. In figure 15.2 we have a Project Explorer window in the upper left corner, a Properties window in the lower left corner, and an Immediate window down to the right. In the grey area a code window is usually shown. If you close a window, or if the window is not visible, you can select the window name from the View menu to open it again. Keyboard shortcuts are F7 to open the Code window, Ctrl+R to open the Project Explorer window, and F4 to open the Properties window.
The Project Explorer window, the Properties window, and the Immediate window can be docked to the edges of the frame in the IDE, or can be made to float in front of the main IDE window. Double-click on the title bar on one of the windows to try this. Double-click to dock it again. Note that it may be necessary to right-click on the title bar and then select Dockable for this to work. The grey area can contain several windows that are not dockable. They can be arranged by choosing Tile Horizontally, Tile Vertically, or Cascade from the Window menu. In figure 15.3 the Project Explorer window is docked to the bottom of the frame, the Properties window is floating, the Immediate window is hidden, and two code windows are cascaded in the grey area.
Figure 15.2 The VBE.
Figure 15.3 The VBE.
The Project Explorer window shows a list of all open projects. There is one project for every opened workbook. We can think of a project as a collection of objects. There is one object for the workbook (ThisWorkbook) and one object for each worksheet (Sheet1, Sheet2, etc.). If you create a code module (see the Code window section) another object will be added. Figure 15.4 shows a Project Explorer window with two open projects.
Figure 15.4 The Project Explorer window.
Figure 15.5 The Properties window.
The Project Explorer window has a toolbar with three buttons. What will happen when you click on a button depends on what is currently selected in the window. For example, if Sheet1 is selected, and you click on the middle button (View Object), Sheet1 will be displayed on the screen. Clicking on the first button (View Code) will open a code window for this object. The last button (Toggle Folders) will toggle between a tree structure showing folders and objects and a structure showing only the objects.
The Properties window
The objects shown in the Project Explorer window have a set of properties. When you select an object in the Project Explorer window a list of properties for that object is displayed in the Properties window. Objects and properties will be explained later in this chapter. The title bar shows the name of the selected object, for example Sheet1. A property has a name and a value. The name is shown in the left column and the value in the right. It is possible to change the value in the right column, either by typing in a new value or by choosing one from a dropdown list.
Example 15.1
In this example we’ll show you how you can change the value of a property in a worksheet.
Start Excel and press Alt+F11 to open the VBE window. Click on the name of the first worksheet (Sheet1) in the Project Explorer window. First, we will change the text on the worksheet tab from Sheet1 to Q1. Locate the property Name (not (Name)) in the properties window. Change the value from Sheet1 to Q1 (double-click in the right column and type Q1). Press Enter and then Alt+F11. Notice that the text on the tab has changed.
You may wonder why there are two properties labelled (Name) and Name. The latter property refers to the text displayed on the window tab. The former is the name of the object. We will use this name when we want to do something with the worksheet in code.
Let’s try to change another property. We will hide the second worksheet so that it does not show on the screen. Select Sheet2 in the Project Explorer window. Change the value for the Visible property to xlSheetHidden. Press Alt+F11 and notice that the sheet is hidden. To make the sheet visible again you can:
1 Set the property value to xlSheetVisible
2 Right-click on a spreadsheet tab, choose Unhide and then Sheet2. Click OK.
The first method will always work. The second will only work if the Visible property is set to xlSheetHidden. If it is set to xlSheetVeryHidden it cannot be made visible with this method.
The Code window
Every object in a project has an associated code module. It can be opened in a window by double-clicking on the object in the Project Explorer window, or by right-clicking on the object and choosing View Code. The code module is where we place our VBA code. It will be empty when we open the window for the first time.
A code module contains one or more subroutines. A simple subroutine may look like this:
Sub Test()
' VBA code is written here
End Sub
If you double-click on one of the spreadsheet objects (Sheet1, Sheet2, etc.) or the ThisWorkbook object you open a module that is specific to a worksheet or workbook. We can also create code modules that are not attached to a sheet or workbook. Such a module is called a standard module. Most of the code that we’ll be writing in this book is placed in standard modules.
A new project does not have any standard modules. We have to create the modules we need as follows:
1 Choose Module from the Insert menu, or…
2 Right-click in the Project Explorer window and click on Insert. Choose Module from the sub-menu.
A new module is created with the name ModuleN where N starts with 1 and is incremented by 1 for each new module we create. It is a good idea to change the name of the module to something more meaningful. This can be done by setting the (Name) property in the Properties window.
Figure 15.6 The Project Explorer window with two modules.
In a bigger project there will be many modules. They will all be shown in the Modules folder in the Project Explorer window. Figure 15.6 shows a project with two standard modules.
A module can be deleted by right-clicking the name and then choosing Remove ModuleN … You will then be prompted if you want to export the module (see next paragraph).
Modules can be exported and saved on disk so that they can be used in other VBA projects. To export a module right-click the module name and choose Export File … It will then be saved in a file with extension .bas. It can be imported into another project by right-clicking in the Project Explorer window and choosing Import File …
After a standard module is created we are ready to write some code, i.e. some subroutines. A subroutine can be created in two ways:
1 By writing a subroutine in the code window for the module, or …
2 By using the Macro Recorder to generate a subroutine.
Macro recording is described in the next section. We’ll begin by writing a procedure in the editor. As you start typing you’ll notice that you are getting some help from VBA. After a line has been written and the Enter key pressed, VBA will check the line for errors. If none are found the line will be reformatted and written into the editor. This reformatting will remove extra spaces, one space will be added before and after an operator, variables may change case, and reserved words will be coloured.
Example 15.2
To give you a little taste of this, write the following code exactly as it stands. Notice what happens after you press the Enter key.
Create a new standard module if you haven’t done so already (Insert → Module)
If necessary, double-click the module name to open the code window.
Type the following (press Tab at the start of the second line):
sub Example
' This is a comment
dim value as int
VALUE=9
if value = 6 then value= value+1
You’ll notice the code has changed to this:
Figure 15.7 Automatic formatting in VBE.
Reserved words are words that have a special meaning in VBA. Examples are Sub, Dim, As, Integer, If, Then, and End. They all begin with a capital letter and have a blue colour. A line starting with a single quote is a comment line. It has a green colour. Notice also that the names VALUE and Value have been changed to value, and spaces have been removed in some places and added before and after an operator such as = or +.
If there is an error in a line it will be coloured red. Let’s try this. Delete the word Then (double-click on Then and then press the Del key) and click on another line. The line will now be painted red. An error message will also be displayed.*
Figure 15.8 Compiler (syntax) error detected.
Press Ctrl+Z to undo the deletion.
*If no error message is shown you may need to choose “Options” from the “Tools” menu, and then click on the “Editor” tab. Select “Auto Syntax Check” and click OK.
There are many shortcuts and key combinations you may use in the editor. Press F1 in the code window and then search for “Code Editing Keys”, “Code Window Keyboard Shortcuts”, or “Code Window General Use Keys”.
15.3 Macros and subroutines
In daily life we’re used to recording music and video with different types of recorders. Excel has a tool for recording the keystrokes and mouse clicks we do while we’re working. Such a recording is called a macro, and the tool a macro recorder. What actually happens is that as we’re performing a task Excel generates code in a standard module. The code is written inside a subroutine. For this reason the terms macro and subroutine are used interchangeably. We can take a look at the generated code by opening the module in a code window.
Using the macro recorder to generate VBA code is a very easy and fast way. There are drawbacks, though. The generated code isn’t always the most efficient. Some things cannot be done with a macro recorder, such as loops and control logic. The main reasons for using it are that it’s fast and we can use it to get a first draft that we can later change in the editor. Also, it can be used as a tool for learning to do things in VBA code that aren’t quite obvious.
As a first example of using the macro recorder we will use it to generate a list of all the students that have scored above average on a test. A list of all the students and their score is shown in figure 15.10a. Our macro will generate a new list as shown in figure 15.10b. The necessary steps are as follows:
1 Click the Record Macro button in the Code group under the Developer tab. The dialogue box shown in figure 15.9 opens.
Figure 15.9 The Record Macro dialogue.
2 Type in a macro name, for example AboveAverage.
3 Optionally register a shortcut key by typing in a capital T in the text box. This will register Ctrl+Shift+T as a key combination that can be used to run the macro. Type in a lower-case t if you want to use the key combination Ctrl+t.
4 Optionally add a macro description.
5 Click OK to start recording. From now on all keystrokes and mouse clicks will be recorded.
6 Click in cell A1.
7 Click the Filter button in the Sort & Filter group on the Data tab.
8 Click on the arrow to the right of Score in cell B1.
9 Choose Number Filters and Above Average.
10 Click on the symbol to the right of Score in cell B1 once more.
11 Select Sort Largest to Smallest.
12 Click on the Stop Recording button in the Code group on the Developer tab.
To get the original list back you can click in cell A1 and then on the Filter button in the Sort & Filter group on the Data tab.
The macro can be run in the worksheet in several ways:
Method 1
1 Press Alt+F8 or click the Run Macro button in the Code group on the Developer tab.
2 Select the name of the macro from the list and click the Run button
Method 2
1 Press the shortcut key that you assigned to the macro (Ctrl+Shift+t)
Click Alt+F11 to enter the VBE.
Notice that a new standard module (Module1) has been added to the project. Open this module in a code window (double-click on the name). VBA has created a new subroutine
Sub AboveAverage()
'
' AboveAverage Macro
' List students with scores above average
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$28").AutoFilter Field:=2, Criteria1:= _ xlFilterAboveAverage, Operator:=xlFilterDynamic
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add _ Key:=Range("B1:B28"),SortOn:=xlSortOnValues, Order:=xlDescending,_ DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Don’t worry if you do not understand all the statements in the subroutine. You may take a look at it again after you have read all the VBA chapters. Hopefully it will be much clearer to you then. All you need to understand now is that recording a macro generates a new subroutine, and this subroutine is stored in a code module.
We are now free to change this subroutine if we like. In this case we’ll leave it as it is, though.
The macro/subroutine can also be run from within the VBE. The easiest way to do it is to click in a line within the subroutine and then press F5. We can also click on the 
button on the toolbar or select Run Sub/UserForm from the Run menu.
To see the effect of the macro we must go back to the spreadsheet. The easiest way to do this is to press Alt+F11. Alt+F11 will toggle between the IDE and the worksheet.
Figure 15.10 (a) List of students with scores. (b) List after running the AboveAverage macro.
In Excel you can select the workbook you want to store the macros that you record. The options are:
• Personal Macro Workbook
• New Workbook
• This Workbook.
Figure 15.11 Select macro storage.
If you create a macro that you use frequently you should store it in the Personal Macro Workbook. It will then always be available to you. If it is a macro that you will use only in a particular workbook, then you should store it there. In Excel 2010 the personal macro workbook is located at C:UsersUserNameAppDataRoamingMicrosoftExcelXLSTARTPESONAL.xslb. It will automatically be created when you save a macro into it.
Relative and Absolute Cell References
When macros are recorded, absolute cell references are used. If a formula in cell A1 is referring to cell C1 then the cell reference will not change if you copy the formula to another cell. To use relative cell references in a macro recording you must click on the button Use Relative References under the Code group in the Developer tab. The button will then get a yellow background. To change back to absolute cell references click it again. This can be done repeatedly during a macro recording. To show you the need for relative cell references let’s record a macro that will insert a list of names in a column starting in the active cell.
In our first go we will use absolute cell references. We’ll start typing in names in cells A1:A5 as we record. Here’s the resulting code with absolute cell references.
ActiveCell.FormulaR1C1 = "Janet Dyson"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Bill Hudson"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Kenny Stoltze"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Cathy Carr"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Linda Rodriguez"
Range("A6").Select
The problem with this macro is that the list of names will always start in cell A1. We want it to start in the active cell. With relative cell references activated the result is:
ActiveCell.FormulaR1C1 = "Janet Dyson"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Bill Hudson"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Kenny Stoltze"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Cathy Carr"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Linda Rodriguez"
ActiveCell.Offset(1, 0).Range("A1").Select
This macro will put the first name in the active cell and shift one row down for each new name. If you later need to add names to the list you can edit the macro in a code window.
Macro security settings
Security is an important issue when working with Excel. Since macros have the potential to do harmful things to your computer, you want to protect yourself from that. Excel protects you from running macros in the first place, but if you’re creating macros of your own you’ll naturally want to be able to run them. To control which macros run you can set the security level by clicking on the Macro Security button in the Code group under the Developer tab (see figure 15.12).
The default value is Disable all macros with notification. If this setting is chosen a security warning will be displayed when a workbook with macros opens (see figure 15.13).
Click Enable Content to make this a trusted workbook if you know the content is from a reliable source. Disable all macros without notification will only allow you to run macros that are stored in a trusted location. To see which locations are trusted, or to add a trusted location, click on Trusted Locations in the left pane. A third option is Disable all macros except digitally signed macros. This allows you to run macros that are digitally signed.1 If you run Excel in a trusted environment you may select Enable all macros. All macros will run without notification.
To save a workbook with macros you must save it as type Excel Macro-Enabled Workbook (*.xlsm) (see figure 15.14).
Figure 15.12 Macro settings.
Figure 15.13 Security warning displayed when opening a workbook with macros.
Figure 15.14 Select Excel Macro-Enabled Workbook when saving a workbook containing macros.
If you forget to do this, Excel will display the following message box:
Figure 15.15 Trying to save a workbook with a wrong type.
Click No and set the correct type.
Run a macro from a Command Button
To invoke the macro in an easy way you can add a command button to the worksheet. Just follow the steps below:
1 Choose Insert from the Controls group under the Developer tab.
2 Select the button icon in the upper left corner.
3 Click in the worksheet and drag to draw a button.
4 When you release the mouse button the Assign Macro dialogue pops up.
5 Select which macro should run when you click the button, and click OK.
6 Right-click on the command button, choose Edit Text. Change the text and click anywhere outside the button.
Add a button to the Quick Access Toolbar
If you want to quickly run the macro from another workbook you may opt to add a button to the Quick Access Toolbar. This is how you do it:
1 Right-click somewhere on the ribbon and choose Customize Quick Access Toolbar.
2 Choose Macros from the Choose commands from combo box.
3 Click on the macro name and then on the Add >> button.
4 Click on the Modify button to change the text and select another icon.
5 Click OK.
Problems
15-1. Record a macro where you add a frame around the cell and change the background colour to yellow. Assign the key combination Ctrl+y to the macro.
15-2. Record a macro where you move the content of a cell to the cell on the right. Hint: Use relative references. Assign a shortcut key to the macro.
15-3. Record a macro that adds a worksheet to a new workbook and then change the names of the four worksheets to Q1, Q2, Q3 and Q4.
Note
1 A digital signature is an electronic, encrypted, secure stamp of authentication obtained from a commercial certification authority.