IV - Procedures
Visual Basic for Applications (VBA) code is grouped into procedures that are used to perform one or more tasks or calculations, either in response to events or when called by other procedures. There are three types of procedures in VBA, each of which is used for a different purpose.
Subroutines
A Sub procedure (also called a method or subroutine) is the most common procedure type. It's a group of statements intended to perform one or more tasks relating to the program's operation. Subroutines are used to handle events in Access although they can be created independently of specific events and called as needed. Subroutines are declared with the Sub keyword.
In code samples like the one below, you'll sometimes see lines preceded by an apostrophe and usually in green text. These are code comments which are ignored by the compiler and help to clarify what's being done. Code commenting is a good practice and will be discussed more later.
Private Sub cmdLabels_Enter()
On Error GoTo cmdLabels_Enter_Err
'Open the mailing labels report.
DoCmd.OpenReport "rptMailingLabels", acViewPreview
Exit Sub
cmdLabels_Enter_Err:
MsgBox Err.Number & " - " & Err.Description
End Sub
This example from the Welcome form in the demonstration database is a simple procedure used to open the mailing labels report when the user clicks on the appropriate command button. On the first line, you can see the procedure declaration with the Sub keyword and then the name of the procedure. For events, the name is always the name of the form or report control with an underscore and then the name of the event (i.e. cmdLabels_Enter). In VBA, this is the only way that the system has to associate a procedure to a specific event.
The last line of the procedure, "End Sub", terminates the procedure's operation. In the middle of the procedure, notice the "Exit Sub" command which is another way of exiting the procedure. In this case, it's used to leave the procedure before the code reaches a portion that is only supposed to run if there's an error. It's good practice to have as few exit points as possible in a procedure and this procedure example is the ideal, with only one exit point other than the End statement and a clear path through the procedure to the exit. Having too many exit points makes a procedure difficult to follow when debugging or modifying the code and can lead to errors that are hard to find.
Going back to the first line, the parentheses at the end of the line indicate where the routine can accept input parameters for use in calculations. Here's another example from the chapter on variables where the name of a form is passed in.
Public Sub FormOpen(strFormName As
String)
'Declare variables for use in procedure.
Dim dbsCurrent As CurrentProject
Dim frmCurrent As AccessObject
Dim blnExist As Boolean
blnExist = False
'Reference current project.
Set dbsCurrent = Application.CurrentProject
'Check if requested form exists.
For Each frmCurrent In dbsCurrent.AllForms
If frmCurrent.Name = strFormName Then
blnExist = True
End If
Next
'If form exists, open it. Otherwise, notify
'user.
If blnExist Then
DoCmd.OpenForm strFormName, acNormal
Else
MsgBox "Sorry, that form does not exist.",
vbOKOnly, "Form not found ..."
End If
End Sub
This procedure is a little longer but still pretty straightforward. It accepts the name of a form as an input parameter and then matches that parameter against every form in the database in the For ... Each statement. If it finds a match, it sets the blnExist variable to True. The If .. Then decision checks the blnExist value. If the variable equals True, the program opens the requested form. Otherwise, it notifies the user that the form doesn't exist.
You would decide on the name of the input parameter when writing the function and it can be anything but it should follow the rules for variable names and be something that indicates the purpose of the value as it will show up as a prompt when you call the function from elsewhere in the code.
Functions
Functions are like subroutines except they are used to calculate and return a value. They can be placed either in form and report modules (also called class modules) or standalone modules.
Public
Function formLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
If Forms(strFormName).CurrentView <> 0 Then
formLoaded = -1
End If
End If
End Function
Although some of the commands in the above example may look complicated, this is actually a simple function that shows the main features of a function in VBA. The function accepts the name of a form and determines if it is currently loaded.
The first line declares this as a function rather than a subroutine. It also accepts an input parameter for use by the function (ByVal strFormName as String). In this case, it means that the function will accept the name of the form in question. Finally, the first line states what type of value will be returned by the function ("As Integer"). This function is actually used to return a True / False value (the form is loaded or it isn't) so it could return a Boolean but an integer of -1 (True) or 0 (False) can be used in place of a Boolean value.
In the body of the function, you can see the decision process that it uses to come to the final result. The If ... Then statements test two different conditions, referencing the form name that was supplied to the function. If both conditions pass, it returns the result by assigning the result to the function name itself. Then when calling this function from any other procedure, you would simply write:
If formLoaded("<InsertFormName>") Then ...
or
If Not formLoaded("<InsertFormName>") Then ...
When a function is used this way in code, the function call represents the value or object it returns as a result and is treated that way by the code. This is why functions can also be referenced in Access queries and formulas. In this case, the function name followed by the form name in parenthesis automatically represents either a True or False value to the code. The Not keyword tests for a False value. It could also be written this way:
If formLoaded("<InsertFormName>") = False Then ...
Using the first method in the case of a True / False value makes for cleaner code.
Properties
The third type of procedure is the Property procedure which is only used in form or report modules.
The purpose of Property procedures is to create custom properties for forms or reports and to set and read those values as needed. These property settings can then be used to affect the program's operation. Properties make use of module-level variables, variables stored at the top of the module outside of any procedure, to store their values.
A custom property ShowPicture declared in a form module.
The example above is from the frmEmployeeMain form in the demo database. The ShowPicture property is used to determine whether the employee pictures should be displayed on the Employee form whenever the user switches to a new record. It's set when the user clicks a checkbox on the form.
A few lines down in the screenshot, you can see the mblnShowPicture boolean variable declared at the top of the module. This variable is also referred to as a field and is being used by the ShowPicture property below it to store a boolean value.
After mblnShowPicture is declared in the example above, you can see a pair of declarations for the ShowPicture property. Unlike subroutines and functions, properties can have two declarations; the Let declaration sets the value of the module field while the Get declaration retrieves it. Notice that the Let declaration accepts an input parameter for which the type matches the type of the field and then uses it to set the value of mblnShowPicture. When setting this property from code, you might type the following:
frmEmployeeMain.ShowPicture = True
There are a couple of reasons for having both a module-level field and a property to set and read that field. The main reason is to limit access to the field value. You've probably noticed the Public and Private keywords in procedure and variable declarations. These keywords affect which code can access the item and this is referred to as scope. The Private keyword limits access to the immediate area where the item is declared.
Since mblnShowPicture is declared as private in the module header, this means that only procedures within this module can access the variable's value. If it was declared inside a procedure, then it would be limited to operations within that procedure. The ShowPicture property is declared as Public which means that procedures from other forms or modules can access the property. The property can then act as a gateway to the mblnShowPicture field, controlling conditions under which the value can be changed. Using the public property and private field combination, you could also test a value that the property receives before it's actually written to the field. The public property can also be used to perform additional tasks each time a property is changed.
Properties don't need to have both declarations. A property that only has a Get declaration is called read-only, meaning that the property can only be read and not changed from outside the module. The module in which it's declared can write directly to the private field since the module is within the scope of that field. Less commonly, you might have a write-only property that only has a Let statement. It could be written from outside but the module where it's declared would need to read the private field.
A third type of property declaration is the Set statement. This is the same as the Let statement in that it accepts a value on behalf of a field. The difference is that the Let statement is used for value fields (numbers, dates, strings, boolean, etc..) and Set is used for object fields (controls, forms and other database objects when used in code).
Intellisense
One of the features of VBA, along with other programming languages, is that the interface will detect a call to a function and assist you in entering it properly.
This screenshot shows the formLoaded function from the above example being called from a subroutine. As soon as I entered the function name and typed the opening parenthesis (parentheses can be used with all function names, even if there are no variables passed), the compiler recognized the function name and prompted me for the value to be passed in by showing me the function declaration and the current argument needed in bold letters. If there was more than one argument to be passed in, I could enter the values separated by commas and the prompt would change as I entered each one.
This is a good reason to use self-explanatory function and argument names when declaring functions.
Optional and Default Parameters
In some cases, you might want to include a parameter in a function or subroutine declaration that has a default value. This means that a specific value will be supplied to the function even if the parameter is not specified in the call to the function.
Public Function AddTax(Price As Double, Optional taxRate As Double = 0.06) As
Double
AddTax = Price + (Price * taxRate)
End Function
In this example, the first parameter, Price, is required but the second parameter does not need to be specified. If it isn't, the function will supply a default tax rate of 6%.
Here you can see the function being called. The variable TotalSale is supplied for the first parameter and Intellisense shows that the second parameter has the default value of 0.06 or 6%. This default could be overridden by supplying another value:
TotalWithTax = AddTax(TotalSale, 0.08)
It could also be left as is:
TotalWithTax = AddTax(TotalSale)
There are a couple of rules for optional and default parameters:
- An optional parameter can be declared without a default value but parameters with defaults must always use the Optional keyword.
- If a parameter is declared as optional, all parameters that follow it must be optional as well. In other words, any optional parameters must be at the end of the declaration.
Parameter Arrays
In the chapter on variables, I talked about the dangers of using the Variant data type. I stated that since it will accept any value, it can lead to errors and should be avoided. An exception to this is the parameter array. Sometimes, you might want to pass a list of items to a function or procedure but you might not know in advance how many items that list will hold. In that case, you could use the ParamArray keyword in the procedure declaration. Parameter Arrays are required by VBA to be of the Variant type.
Public Function AverageItems(ParamArray NumericValues() As
Variant)
Dim varItem As Variant
Dim dblItem As Double
Dim dblTotal As Double
Dim intCount As Integer
dblTotal = 0
intCount = 0
'Iterate through parameter array. Add and count numeric values.
For Each varItem In NumericValues()
If IsNumeric(varItem) Then
dblItem = CDbl(varItem)
dblTotal = dblTotal + dblItem
intCount = intCount + 1
End If
Next
'If numeric values were found, average them for result.
If dblTotal > 0 Then
AverageItems = dblTotal / intCount
Else
AverageItems = 0
End If
End Function
The above example is a function that accepts a parameter array and averages any numeric values it finds in that array. Notice the use of the ParamArray keyword in the procedure declaration. If a parameter array is used, it must be the last argument in the declaration to indicate that it is optional.
In the middle section of the function, you'll notice the For Each loop that iterates through each item in the array using the varItem variable as a placeholder. I'm using VBA's IsNumeric function to verify that each value is a number before using CDbl to convert it to a Double value and then adding it to the sum of numeric values. I also keep a count of the numbers found. In the final section, if any numeric values have been found, I average them and return the result as the result of the function.
Although I avoid using Variants (or the generic Object type), this is an example of a place where they would need to be used and the precautions you would need to take when using them.
When I started programming using an older version of the BASIC language, I learned to write programs as one long code listing that ran from start to finish. It was quite a different programming experience from the VBA style of separate procedures and functions. The advantage of VBA's style is that it encourages you to break the project down into pieces and analyze each part of the problem. This is an important skill when designing a solution to a real-life problem.
In the next chapter, I'll go into more detail about arrays and how you can use them to manage groups of items in your code.
