2.2. The Structure of a VB Program

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.

2.2.1. 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.

2.2.2. Use Event Handlers to Call Functions and Methods

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
        Else
            MsgBox "Details have not been saved", vbCritical
        End If
    
        Exit Sub
cmdSave_Click_Err:
    MsgBox Err.Description & vbCrLf & Err.Number
    
End Sub

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
   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
   
End Sub

The following snippet, which provides the same functionality, demonstrates the power of reducing UI code to a minimum:

Function doSave()

    Set oObj = CreateObject("Business.BusinessObj")
        If oObj.SaveDetails() Then
            doSave = "Details Saved OK"
        Else
            doSave = "Details have not been saved"
        End If
    Set oObj = Nothing

End Function

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!

2.2.3. Writing Custom Procedures

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:

• Functions

• Sub procedures

• Property procedures

2.2.3.1. Functions

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 MinNumber(ByVal iNumber As Integer) _
                 As Integer
    If iNumber <= 500 Then
        MinNumber = iNumber
    Else
        MinNumber = 500
    End If
    
End Function

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. For details of how to pass values into a function, see Chapter 3.

2.2.3.2. Sub procedures

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.

2.2.3.3. Property procedures

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:

Property Let

Assigns a value to a property

Property Get

Retrieves the value of a property

Property Set

Assigns an object reference to a property

For a more in-depth look at using properties and property procedures, see Chapter 4.

2.2.4. Controlling Execution Flow

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?

2.2.4.1. 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.

2.2.4.2. 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:

Call cmdOne_Click()

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.