Introduction to Microsoft Visual Basic for Applications

VBA Fundamentals

Introduction

Microsoft Excel is a spreadsheet application that provides simple to advanced means of creating and managing any type of list. To even make it more powerful and production oriented, MS Excel, like all members of the Microsoft Office family, ships with a computer language and a programming environment.

Microsoft Visual Basic for Applications (VBA) is a computer language based on Microsoft Visual Basic. It allows you to write code that can automatically perform actions on a workbook, its worksheets, their columns, their rows, and/or their cells. To write the necessary code, Microsoft Excel also ships with a programming environment called Microsoft Visual Basic that provides all the regular features you need to automate actions on a worksheet.

Before writing VBA code, you must first open Microsoft Excel. To write code, you must open Microsoft Visual Basic. To do that, on the main menu, you can click Tools -> Macro-> Visual Basic Editor. This would open the Microsoft Visual Basic programming environment:

Practical Learning: Starting Microsoft Excel

Using VBA

When it opens, like any regular Windows application, Microsoft Visual Basic displays a title bar in the top section. The title bar follows the same description as Microsoft Excel. Under the title bar, the application displays a menu, followed by a Standard toolbar. They also follow the same descriptions as Microsoft Excel.

By default, Microsoft Visual Basic displays three main windows. In the top-left section, the Project window shows a list of the code segments that are available to your worksheet. It is usually positioned to the top-left section but you can move it to another section of the screen. To move it, click its title bar and  drag it away it from there:

To put the window back where it was previously, you can double-click its title bar.

The Properties window, in the bottom-left section of the screen shows the characteristics of an object that is selected. Like any other window, to move the Properties window from its position, drag its title bar:

The main area of Microsoft Visual Basic uses a gray background. This area is gray because, in reality, MSVB is a multiple document interface (MDI) that can be used to display various windows at the same time. At times, this gray area will be occupied with other windows.

Modules

A module is a blank window that resembles a piece of paper on which you write code. When you use Microsoft Excel and work on a worksheet, a default module is automatically allocated for it, whether you use it or not. You can also create a module that is independent of any worksheet.

To create a module, on the main menu of Visual Basic, you can click Insert -> Module.

Practical Learning: Creating a Module

The Immediate Window

To help you test code, Microsoft Visual Basic provides a special window called the Immediate Window. This window is not automatically available when MSVB opens. If you want to use it, you must request it.

To display the Immediate Window, on the main menu of Microsoft Visual Basic, you can click View -> Immediate Window.

Practical Learning: Displaying the Immediate Window

Fundamentals of Windows Controls

Introduction

A computer application, such as those that run on Microsoft Windows, is equipped with objects called Windows controls. These are the objects that allow a person to interact with the computer.

The Form

The primary control used on most applications is called a form. Because Microsoft Excel is not primarily a programming environment, it doesn't automatically provide a form. If you want to use a form in your application or to associate a form with your workbook, on the main menu of Visual Basic, you can click Insert -> UserForm. This would add a form to your application.

The form is primarily used as a platform on which you add other controls. Such a control is called a container. By itself, a form is not particularly useful: You can add other objects to it.

When you create or add a form, a module is also automatically created for it. To access the module associated with a form, you can right-click the form and click View Code.

Practical Learning: Creating a Form

Introduction to Windows Controls

The main objects used to help a person interact with the computer are Windows controls. There are two main ways you can access these objects when working with Microsoft Excel.

If you are working in Microsoft Excel, besides the columns, rows, and cells, you can add or position some Windows controls on the worksheet. To do that, on the main menu of MS Excel, you can click View -> Toolbars -> Control Toolbox. This would display the Control Toolbox. By default, the Control Toolbox appears in two columns. To change its display, you can click and drag one of its borders:

If you are working on a form, Visual Basic provides its own Toolbox equipped with a few controls.

Practical Learning: Accessing Windows Controls

The Control Toolbox and Additional Objects

The Control Toolbox in Microsoft Excel provides the most regularly used controls. These controls are enough for any normal Microsoft Excel workbook you are developing. Besides the objects in the Control Toolbox, other controls are left out but are still available. To use one or more of these left out controls, on the Control Toolbox, click the More Controls button. This would open a window:

You can scroll up and down in the window to locate the desired control. If you see a control you want to use, click it and click the worksheet.

In Visual Basic, to access more controls, on the main menu, you can click Tools -> Additional Controls...

The Names of Controls

Every control on the tool boxes has a specific name. You may be familiar with some of these controls. If you are not sure, you can position the mouse on a control and a tool tip will come up. In our lessons, we will use the tool tip of a control to name it. The names we will use are:

Control	Name	Control	Name
	Label		CommandButton
	TextBox		TabStrip
	ComboBox		MultiPage
	ListBox		ScrollBar
	CheckBox		SpinButton
	OptionButton		Image
	ToggleButton		RefEdit
	Frame

Control Design

Introduction

To use one of the controls on the Control Toolbox, you can click it. If you then simply click the worksheet, the control would be added where you clicked and with some default dimensions. If you want, instead of clicking and releasing the mouse, you can click and drag. This allows you to "draw" the control and give the dimensions of your choice. If the control has already been added but you want it to assume different dimensions, you can click it to select it and then drag one of its border handles.

The above technique is used to add one control at a time. If you want to add the same control again, you must click it in the Toolbox and click the worksheet or the form again. If you plan to add the same control many times, in the Toolbox, double-click it and click the worksheet or the form as many times as necessary. When you have reached the desired number of copies of that control, to dismiss it, in the Toolbox, click the same control again or another control. If you are working in Visual Basic, to dismiss the control, in the Toolbox, click the Select Objects button.

Practical Learning: Accessing Windows Controls

Control Selection: Single Control Selection

After you have added a control to a worksheet or a form, in order to perform any type of configuration on the control, you must first select it. Sometimes you will need to select a group of controls. To select a control on a worksheet, first, on the Control Toolbox, click the Design Mode button . If you are working in Visual Basic, to select a control, click it on the form.

A control that is selected indicates this by displaying 8 small squares, also called handles, around it. Here is an example:

In Visual Basic, between the border handles, the control is surrounded by dotted rectangles.

Practical Learning: Selecting Controls

Multiple Control Selection

To select more than one control on the form, click the first. If you are working on a worksheet, press and hold Shift. If you are working in Visual Basic, press and hold Ctrl. Then click each of the desired controls. If you click a control that should not be selected, click it again. After selecting the group of controls, release either Shift or Ctrl that you were holding:

If you are working in Visual Basic, another technique you can use to select various controls consists of clicking on an unoccupied area on the form, holding the mouse down, drawing a fake rectangle, and releasing the mouse:

Every control touched by the fake rectangle or included in it would be selected:

Practical Learning: Selecting and Using Various Controls

Control Deletion

If there is a control on your form or your worksheet but you don't need it, you can remove it. To delete a control, first select it and then press Delete. You can also right-click a control and click Cut.

To remove a group of controls, first select them, then press Delete or right-click the selection and click Cut.

Practical Learning: Deleting Controls

Writing Coding

Code Indentation

Indentation is a technique that allows you to write easily readable code. It consists of visually showing the beginning and end of a section of code. Indentation consists of moving your code to the right side.

The easiest and most common way to apply indentation consists of pressing Tab before typing your code. By default, one indentation, done when pressing Tab, corresponds to 4 characters. This can be automatically set using the Tab Width text box of the Editor property page in the Options dialog box. To change it, on the main menu of Microsoft Visual Basic, you can click Tools -> Options and click the Editor tab:

If you don't want the pressing of Tab to be equivalent to 4 characters, change the value of the Tab Width text box to a reasonable integer value and click OK. Otherwise, it is (strongly) suggested that you keep to its default of 4 characters.

Comments

A comment is a piece of text in code that would not be considered when reading your code. As such, a comment can be written any way you want.

In Visual Basic, the line that contains a comment can start with a single quote. Here is an example:

Private Sub Form_Click()
    ' This line will not be considered as part of the code
End Sub

Alternatively, you can start a comment with the Rem keyword. Anything on the right side of rem, Rem, or REM would not be read. Here is an example:

Private Sub Form_Click()
    ' This line will not be considered as part of the code
    Rem I can write anything I want on this line
End Sub

Comments are very useful and you are strongly suggested to use comments regularly.

Line Continuation

You will sometimes need to expand your code on more than two lines. This happens regularly if you are writing an expression that involves many entities that must belong to a group.

To continue a piece of code from one line to the next, type an empty space followed by an underscore symbol, then continue your code on the next line.