Visual Basic for Applications differentiates three types of subprograms: GoSub routines, subroutines, and functions. GoSub routines are open subprograms, having no argument or temporary variables of their own. Subroutines are closed subprograms, with named arguments and their own temporary variables, but lacking a return value; and functions are closed subprograms having a return value.
GoSub routines are artifacts of the original Dartmouth BASIC language, and are largely avoided. However, the syntax for calling a GoSub routine is:
where label is a line number or statement label. The syntax for defining a GoSub routine is to simply put statements at the appropriate line number or statement label. Execution of a Return statement ends the GoSub routine and returns control to the statement following the GoSub statement.
Subroutines are called by one of the statements:
Call routinename (argument,...)
where routinename is the name of the subroutine, and argument is a parameter to the subroutine. The comma-ellipsis (,...) indicates the preceding syntax element — in this case, the argument — may be repeated as many times as necessary, with commas separating the elements. Also note that the first form of the statement — the form that does not use the Call keyword — does not use parentheses around the argument list, while the second form does.
routinename argumentname := argumentvalue,...
The form of routine invocations used is a matter of taste, but should be consistent within a project. The primary disadvantage of named parameters is that it exports the names of parameters to the users of the routine: the names of the actual parameters then become part of the interface to the routine. This means such names must be meaningful, and should not contain potentially confusing data type prefixes. The primary advantages to named parameters, of course, are clarity and maintainability.
Subroutines are defined by a block of statements starting with the Sub statement and ending with the End Sub statement. The format of the Sub statement is:
[Private | Public] [Static] Sub routinename (argdecl,...)
where routinename is the name of the subprogram, and argdecl is the declaration of the argument. Again, the comma-ellipsis indicates many argument declarations, separated by commas, may be present. Each argdecl is a phrase of the form:
[Optional] [ByVal | ByRef] [ParamArray] varname[( )] _ [As type] [= defaultvalue]
where varname is the name by which the parameter will be known in the body of the subprogram, type is the datatype of the parameter, and defaultvalue is a default value for the parameter. In this description, syntax elements enclosed in braces are optional; and syntax elements separated by vertical bars are alternatives to one another. This phrase is split across lines in this description, but need not be split across lines in the Visual Basic for Applications program. However, recall any VBA statment may be split across multiple text lines, by appending the underscore to all text lines but the last.
As you may note from the syntax descriptions, Visual Basic for Applications routine declarations can be quite complicated. This is because modern Basics are quite powerful and expressive languages. Despite Basic’s poor reputation among self-congratulatory “power” programmers, few Basic implementations more recent than the Apple ][’s Applesoft can be regarded as “toy” languages.
Private and Public are scope keywords. They determine whether the routine can be referenced outside the module in which it is defined. A Public routine can be so referenced; a Private routine cannot. It is considered good form to declare routines Private unless they need to be Public. Limiting the clients of a routine to the same module also permits routines to be less defensive about the parameters supplied by the caller.
The optional keyword Static indicates that variables defined in the routine are to retain their values between routine invocations. By default, variables are created (and initialized to zero) every time the routine is entered. Individual variables may be declared Static, and this is generally preferred.
The Optional keyword indicates that the argument is optional, that is, it need not be specified at the call site. If the argument is omitted at the call site and a default value is specified, the call site acts as if the default value were specified for the argument. Otherwise, the argument is missing, which the subprogram can detect with the IsMissing function applied to the argument name.
ByVal and ByRef are parameter passing mechanism keywords. These determine whether the subprogram can produce side effects on the parameters passed to it. Modifications made in the subprogram to variables passed ByRef have effect outside the subprogram. Modifications made in the subprogram to values passed ByVal are discarded when the subprogram exits.
The ParamArray keyword indicates that the subprogram takes a variable number of arguments; the parameter with the ParmArray keyword, which must last in the parameter list, is used to reference all trailing parameters. Variable argument list routines are highly specialized and will not be further mentioned here.
The paired parentheses () optionally following the parameter name indicate that the argument is to be an entire array, rather than a single value. Omitting the paired parentheses indicates the argument is to be a single value.
If the As type phrase is omitted, the argument is of Variant type, a type that can hold any value. Otherwise, the type of the actual parameter is converted at the call site to the type of the argument expected.
The format of the End Sub statement that terminates a subprogram definition is:
If the subprogram has not already returned, this statement also forces a return to the call site.
The subprogram can return before its lexical end with the statement:
This statement ends the execution of the subprogram, but does not terminate the subprogram definition.
The following sample subroutine demonstrates the use of GoSub routines, a subprogram with no parameters, and the Exit Sub statement.
Private Sub Gackt() Dim lArgument As Long Dim lTemporary As Long Dim lResult As Long lArgument = 10 GoSub DoFactorial Debug.Print "DoFactorial [lArgument = "; lArgument; _ "] -> lResult = "; lResult Exit Sub DoFactorial: lTemporary = lArgument lResult = 1 Do While lTemporary > 0 lResult = lResult * lTemporary lTemporary = lTemporary - 1 Loop Return End Sub
Finally, Visual Basic for Applications also defines functions. A function is a subprogram that returns a value which may be directly used in expressions. Functions are defined by a block of code starting with a Function statement and ending with an End Function statement. The syntax of the Function statement is:
[Private | Public] [Static] Function routinename _ (argdecl,...) [As type]
where the effect of keywords and the format of argument declarations are as in the Sub statement. In addition, however, the Function statement includes an optional As type clause, which specifies the return type of the function. If this clause is omitted, the function returns the Variant type.
Private Function Factorial(ByVal n As Long) As Double Dim lIndex As Long Dim dProduct As Double dProduct = 1 For lIndex = 1 To n dProduct = dProduct * lIndex Next lIndex Factorial = dProduct End Function
Copyright © 2001 Brian Hetrick
Page last updated 30 December 2001.
Building Blocks I
Control Flow II
A First Program
Data Structures I
Building Blocks II
Data Structures II