This supplement is designed to be a primer for programming with Visual Basic and Visual Basic for Applications. Upon completion of this supplement, you should be familiar with some basic coding concepts such as:
Functions and Procedures
Visual Basic Editor Environment
COM – Object Models
Examples and exercises will demonstrate basic coding concepts, and will use the VBA programming language.
Event Driven vs. Procedure Based Programming
Visual Basic is a structured programming language derived from the BASIC language. VB is event-driven, in contrast to some other programming languages and architectures that are procedure-based.
In conventional programming, the sequence of operations for an application is determined by a central controlling program (e.g., a main procedure). In event-driven programming, the sequence of operations for an application is determined by the user’s interaction with the application’s interface (forms, menus, buttons, etc.).
For example, rather than having a main procedure that executes an order entry module followed by a data verification module followed by an inventory update module, an event-driven application remains in the background until certain events happen: when a value in a field is modified, a small data verification program is executed; when the user indicates that the order entry is complete, the inventory update module is executed, and so on.
Event-driven programming, graphical user interfaces (GUIs), and object-orientation are all related since forms and the graphical interface objects on the forms serve as the skeleton for the entire application. To create an event-driven application, the programmer creates small programs and attaches them to events associated with objects. In this way, the behavior of the application is determined by the interaction of a number of small manageable programs rather than one large program.
Visual Basic Editor
There are a number of ways to open the Visual Basic Editor. You can access it from the Tools > Macros menu or by pressing Alt-F11. You can also add the Developer toolbar to your Visio user interface to expose the Visual Basic Editor toolbar button.
You will first notice that there are several windows in the VBE: Project, Code, Properties, Immediate, and others. If you do not see these windows, go to the View menu to turn them on.
Figure : The Visual Basic Editor window
The Project window displays a hierarchical list of the projects and all of the items contained in and referenced by each of the projects. From the Project window, you can view the code contained in modules and forms and you can view form objects. From the Properties window, you can view and modify design time properties for objects in a project. From the Immediate window you can execute code. This is extremely useful when debugging to view the values of variables and expressions. It can also be used to set values of variables to test extreme or unusual conditions. To view the value of a variable or expression, place a question mark (?) before the code. To execute a line of code, enter it into the Immediate window and press the Enter key.
Demonstration: Visual Basic Editor
In the Immediate window of VBE, type:
Msgbox "This is my message."
Press Enter. The code will execute displaying a message box with the text.
Create a Visio drawing with a few shapes and select some or all of them.
Return to the Visual Basic for Applications Editor and in the immediate window type:
Press enter. The immediate window returns the number of shapes you have selected in the Visio drawing.
A variable is a part of memory that is given a name by a programmer. This part of memory is used to store important pieces of information that will be used in a program. Variables are containers for information that you wish to store in your application.
The following code shows three variables, one a string of text, the other an integer, and the last a date:
Dim Capitol as String
Dim NumberOfStates as Integer
Dim IndependenceDay as Date
Capitol = “Washington D.C.”
NumberOfStates = 50
IndependenceDay = #7/4/1776#
Variables are empty until you put information in them. Variables are stored in memory, and can be retrieved or replaced with new data at any time during the running of an application.
To create a variable in a program, it is good practice to first declare the variable. Declaring a variable is done by putting the keyword Dim (which stands for Dimension) in front of the variable name, like so:
Dim myFirstVariable as String
Dimensioning a variable sets aside the name and space in memory for the variable. The variable is empty and contains nothing until the variable is assigned a value. The following example assigns the variable a value:
myFirstVariable = “Some String of Text…”
The previous example declared the variable explicitly. It is possible to implicitly declare a variable in your code by simply assigning a value to the variable without declaring it first with the Dim keyword, like so:
myFirstVariable = “Another String of Text…”
It is generally good practice to explicitly declare all of your variables, to avoid creating unnecessary variables and to help debug your programs by using Option Explicit.
Option Explicit is a line of code that you add to ensure that all your variables are explicitly declared. If using Option Explicit, an error will be generated if a variable is implicitly declared. This helps debug your programs because errors will be thrown if you ever misspell a variable name. For ASP scripts that use VBScript, you can add the line Option Explicit to the first line of your code. For VBA you can do this by choosing Options from the VBA Tools menu. This displays a tabbed dialog box. Choose the Editor tab. One of the check boxes on this tab is “Require Variable Declaration.” Checking this option causes the keywords Option Explicit to be inserted at the beginning of any new module or form added to the project. It does not, however, add Option Explicit to already existing forms and modules. Option Explicit forces any variable name used in code to be explicitly defined. This is very helpful at design time in catching any mistyped variable names.