Any VB program—whether a hosted VBA application or a VB executable—is a collection of modules containing code, graphical user interface objects, and classes. This book concentrates on the language elements of VBA as they relate to both hosted VBA and the retail version of VB. The VBA and VB user interfaces— whether Word, Excel, Project, or a VB form—all fire events that are handled by the code you create using the VBA language. Therefore the code modules within your program are of greatest concern to us here.
Visual Basic code can be split into three categories:
• Code you write to handle events such as a button being clicked by the user; these procedures are called event handlers
• Custom procedures, where you create the main functionality of your application
• Property procedures, used in form and class modules
All Visual Basic language elements work equally well in all three types of procedure. For example, there are no restrictions placed on the type of code you can write within a particular type of procedure. It's left to you as the developer to decide what code goes where.
Events: The Starting Point
An event is always the starting point for your procedure. It can be a system-generated event, such as the Form Load event or a Timer control event, or it can be a user-generated event, such as the Click event on a command button.
To code an event handler for a control event, open the form's code window and select the control from the drop-down list of the available objects. Next, select the required event from the drop-down list of available events for that control. The Event handler definition is then automatically placed in the code window, and you can start coding the event handler.
If you are writing a small and simple application, you could program the whole thing within event handlers. However, the more complex your program becomes, the more you find yourself repeating code within these event handlers, and at this point you should start moving related blocks of code into their own separate functions.
I would recommend that you keep the code in your event handlers to an absolute minimum, using them simply to call methods within a class or to call functions within the project. You will find that your code becomes easier to follow, code reuse is maximized, and maintenance time for the project is reduced.
The following Click event from a command button called cmdSave demonstrates this minimalist approach to event handling:
Private Sub cmdSave_Click()
On Error GoTo cmdSave_Click_Err
If SaveDetails() Then
MsgBox "Details Saved OK", vbInformation
MsgBox "Details have not been saved", vbCritical End If
Exit Sub cmdSave_Click_Err:
MsgBox Err.Description & vbCrLf & Err.Number
Because all the code to actually save the details is held within the SaveDetails function, this function can be called from anywhere in the form or project.
The move towards removing functional code from the user interface has been spawned by the n-tier client-server model, in which the user interface is purely a graphical device for displaying information and collecting user input. The middle tier or tiers enforce business rules and provide the main functionality of the application. Here's another example of the same Click event, this time using a SaveDetails method stored in a class module:
Private Sub cmdSave_Click()
On Error GoTo cmdSave_Click_Err
Dim oObj As Business.BusinessObj Set oObj = New Business.BusinessObj
20 Chapter 2 - Program Structure
If oObj.SaveDetails() Then
MsgBox "Details Saved OK", vbInformation Else
MsgBox "Details have not been saved", vbCritical End If Set oObj = Nothing
Exit Sub cmdSave_Click_Err:
MsgBox Err.Description & vbCrLf & Err.Number & vbCrLf _ & Err.Source
The following snippet, which provides the same functionality, demonstrates the power of reducing UI code to a minimum:
Set oObj = CreateObject(": If oObj.SaveDetails() doSave = "Details
Else doSave = "Details End If Set oObj = Nothing iusiness.BusinessObj") Then
have not been saved"
So what's so special about this function? Well, this function is calling exactly the same method as the previous Click event, only this code has been taken from an Active Server Page used in a corporate intranet. Because the vast majority of code has been moved away from the front end of the application, the task of porting the application to an HTML/ASP user interface is made extremely easy. In this simple example, the SaveDetails method could care less who or what has called it; it doesn't matter whether it was a Win32 application or an ASP web server application—or both!
Custom procedures can be written in any type of VB module. As a general rule, form modules should only contain procedures that need to refer to properties of the controls contained within the Form. Therefore, a procedure that doesn't refer to any form control properties should be placed in a code module.
To create a new procedure, you can use either the Add Procedure dialog, which is accessed from the Add Procedure option of the Tools menu, or you can move to the bottom of the code window and start typing the Function or Sub definition.
There are three types of custom procedures in Visual Basic:
• Sub procedures
• Property procedures
The Structure of a VB Program 21
A function is a collection of related statements and expressions that perform a particular task. When it completes execution, the function returns a value to the calling statement. If you don't specify an explicit return value for the function, the default value of the return data type is returned. If you write a custom function in a class module and declare it as Public, it becomes a method of the class.
Here's a quick example of a function that's used to provide a minimum number:
Private Function MiriNumber(ByVal iNumber As Integer) _ As Integer If iNumber <= 500 Then MinNumber = iNumber
MinNumber = 500 End If
Because functions return a value, you can use them as part of an expression in place of a value. In the following snippet, the string passed to the VB Instr function is a custom function that returns a customer name given a customers code:
If InStr(1, GetCustomerName(sCustCode), "P") > 0 Then
For full details on the syntax and use of functions, see the Private, Public, and Friend statements in Chapter 7, The Language Reference. For details of how to pass values into a function, see Chapter 3, VBA Variables and Data Types.
A sub procedure is used exactly the same way as a function, the only difference being that it doesn't return a value and therefore can't be used as part of an argument. Sub procedures are used by Visual Basic to provide event handling.
In general you should use functions rather than subs to create custom procedures. Functions allow you to return a value, which at a minimum could be a Boolean True or False to inform the calling statement that the function has succeeded or failed. I have done some testing to determine whether there is a performance hit for using a function instead of a sub, and there is no appreciable difference between the two, even though the function has to return a value to the calling statement, and a sub procedure doesn't.
Like a function, if you write a custom sub in a class module and declare it as Public, it becomes a method of the class.
For full details of the syntax and use of Sub procedures, see the Private, Public, and Friend statements in Chapter 7. For details of how to pass values into a sub procedure, see Chapter 3.
Property procedures are specialized procedures that assign and retrieve values of custom properties. They can be included only within object modules such as form or class modules. There are three types of property procedures:
22 Chapter 2 - Program Structure
Assigns a value to a property
Retrieves the value of a property
Assigns an object reference to a property
For a more in-depth look at using properties and property procedures, see Chapter 4, Class Modules.
So you've got your event handlers that spring into life when the user clicks a button, or a form loads, or a Timer control fires its Timer event. You've written some neat functions to do all the work behind the scenes. How do you link the two?
Calling sub and function procedures
Sub procedures can be called in one of two ways. First, you can use the Call statement, like this:
Call DoSomething(sSomeString, iSomeInteger)
If you use the Call statement, you must enclose the argument list in parentheses. The other method of calling a sub is by simply using its name, but if you don't use the Call statement, don't put parentheses around the argument list:
DoSomething sSomeString, iSomeInteger
If you aren't going to use the return value of a function, you can use either of the above methods to call the function. Otherwise, use the function name as part of an expression. For example:
If GetItNow(sSomeStuff) = 10 Then
Like the Call statement, when you use a function call as part of an expression, the argument list must be enclosed within parentheses.
For more information, see the entry for the Call statement in Chapter 7. Explicitly calling event procedures
It's also possible to call an event handler from within your code. For example, to replicate the user clicking on a button called cmdOne, you can use the code:
Because event handlers are private to the form in which they are defined, you can only explicitly call an event handler from code within the same form.
Was this article helpful?