| Sub Statement |
Named Arguments
No
Syntax
[Public | Private | Friend] [Static] Sub name [(arglist)] [statements] [Exit Sub] [statements] End Sub
Public
Use: Optional
Type: Keyword
Gives the sub procedure scope through all procedures in all modules in the project. If used within a createable class module, the sub procedure is also accessible from outside the project. Public, Private, and Friend are mutually exclusive.
Private
Use: Optional
Type: Keyword
Restricts the scope of the sub procedure to those procedures within the same module. Public, Private, and Friend are mutually exclusive.
Friend
Use: Optional
Type: Keyword
Only valid within a class module; gives the sub procedure scope to all modules within a project, but not to modules outside the project. Public, Private, and Friend are mutually exclusive.
Static
Use: Optional
Type: Keyword
Preserves the value of variables declared inside the sub procedure between calls to the sub procedure.
name
Use: Required
The name of the sub procedure.
arglist
Use: Optional
Data Type: Any
A comma-delimited list of variables to be passed to the sub procedure as arguments from the calling procedure.
statements
Use: Optional
Program code to be executed within the sub procedure.
arglist uses the following syntax and parts:
[Optional] [ByVal | ByRef] [ParamArray] varname[( )] _ [As type] [= defaultvalue]
Optional
Use: Optional
An optional argument is one that need not be supplied when calling the sub. However, all arguments following an optional one must also be optional. A ParamArray argument can't be optional.
ByVal
Use: Optional
The argument is passed by value; that is, a local copy of the variable is assigned the value of the argument.
ByRef
Use: Optional
The argument is passed by reference; that is, the local variable is simply a reference to the argument being passed. All changes made to the local variable are also reflected in the calling argument. ByRef is the default method of passing variables.
ParamArray
Use: Optional
Indicates that the argument is an optional array of variants containing an arbitrary number of elements. It can be used only as the last element of the argument list, and it can't be used with the ByRef, ByVal or Optional keywords.
varname
Use: Required
The name of the local variable containing either the reference or value of the argument.
type
Use: Optional
The data type of the argument.
defaultvalue
Use: Optional
For optional arguments, you can specify a constant default value.
Description
Defines a sub procedure.
Rules at a Glance
If you don't include one of the Public, Private, or Friend keywords, a sub procedure is Public by default.
If you declare a sub procedure as Public within a module that contains an Option Private directive, the sub procedure is treated as Private.
Unlike a Function procedure, a sub procedure doesn't return a value to the calling procedure, and therefore can't be used as part of an expression.
Any number of Exit Sub statements can be placed within the sub procedure. Execution continues with the line of code immediately following the call to the sub procedure.
A sub procedure can't define a fixed-length string as an argument in arglist; this produces the design-time error, "Expected array."
A user-defined type can be passed as an argument only if the argument is required (i.e., not optional). In addition, if a Public sub procedure accepts a user-defined type as an argument, that user-defined type must also be defined as Public within a code module.
The default value for an optional object argument can only be Nothing.
Programming Tips and Gotchas
There is often confusion between the ByRef and ByVal methods of assigning arguments to the sub procedure. ByRef assigns the reference of the variable in the calling procedure to the variable in the sub procedure; that is, it passes a pointer containing the address in memory of the variable in the calling procedure. As a result, any changes made to the variable from within the sub procedure are, in reality, made to the variable in the calling procedure. On the other hand, ByVal assigns the value of the variable in the calling procedure to the variable in the sub procedure; that is, it makes a separate copy of the variable in a separate memory location. Changes made to the variable in the sub procedure have no effect on the variable in the calling procedure. In general, ByRef arguments (Visual Basic's default method of passing parameters) within class modules take longer to perform, since marshalling back and forth between sub procedure and calling module must take place; so unless you explicitly need to modify a variable's value within a sub procedure, it's best to pass parameters by value.
Sub procedures can't return a value, or can they? Look at the following code:
Sub testTheReturns() Dim iValOne As Integer iValOne = 10 Call testValues(iValOne) Debug.Print iValOne End Sub Sub testValues(ByRef iVal As Integer) iVal = iVal + 5 End Sub
Because the argument was passed ByRef, the sub procedure acted upon the underlying variable iValOne. This means that you can use ByRef to obtain a "return" value or values (although they're not strictly return values) from a sub procedure call.
Optional arguments afford wonderful flexibility, allowing you to create generic sub procedures that can be used in a wide variety of scenarios. Until version 5 of VBA, optional arguments could be only of the Variant data type. With the release of VB 5.0, almost any data type can be cast as an optional argument. However, I would still advocate the use of a variant for optional arguments. Why? The variant has a special state called Missing that makes it easy to check the value of an optional argument using the IsMissing sub procedure. If IsMissing returns True, you know immediately that the optional argument was not supplied in the sub procedure call. Checking to determine whether a strongly typed variable (an integer, for example) is missing is much more difficult, as the following code shows:
Sub testMissingInt() Dim iVal As Integer Dim iValTwo As Integer iVal = 10 iValTwo = 0 Call testFunc(iVal, iValTwo) End Sub Sub testFunc(ByRef iVal As Integer, _ Optional iValTwo As Integer) If iValTwo = 0 Then 'perform this if iValTwo is missing glbTest = iVal + 10 Else 'perform this if iValTwo is present glbTest = iVal + iValTwo End If End Sub
A missing optional integer argument appears within the sub procedure as its initialized value, which is 0. But what happens when you want to pass the value to the sub procedure? It's interpreted as being missing. In other words, in a case such as this, you have no way to tell if the argument is really missing. In this case, it's important to take advantage of the defaultvalue argument to replace a missing optional argument either with a meaningful default value or with a value that can be readily identified as missing.
A ParamArray must be declared in the sub procedure as an array of variant. However, the calling procedure doesn't pass the argument explicitly as an array; the individual elements are passed as a comma-delimited list of values or variables, as the following example shows:
Sub testParam() Call testFunc(10, 500, 60) End Sub Sub testFunc(ParamArray someArgs() As Variant) Dim iArg As Integer Dim i As Integer Dim iResult As Integer Dim vArg As Variant For Each vArg In someArgs iResult = iResult + vArg Next End Sub
For reasons that I haven't quite fathomed yet, you can't use ParamArrays to pass arguments to sub procedures in remote server applications. It's difficult to describe the results you obtain; suffice it to say that they don't generate errors, but that, quite simply, the results are little more than garbage. However, you can pass an explicit variant array to a sub procedure in a remote server application. The enormous advantage of this is that you can change both the type and number of arguments passed into the sub procedure without changing the COM interface, thereby retaining compatibility with a previous version of the server application.
One of the most useful additions to VBA (as of Version 5.0) is the Friend keyword, which allows you to expose a property, sub procedure, or function in a class module to the other modules within the same project, but at the same time prevent "the outside world" from having access to the interface. This can be seen as halfway between Private—which prevents the interface from being seen by any module—and Public—which exposes the interface both to modules in the same project and to modules outside the project.
There are many occasions in which you will run into the dreaded (by some!) recursive sub procedure call. Recursion occurs when you call a sub procedure from within itself. Recursion is a legitimate and often essential part of software development; for example, it's the only reliable method of enumerating or iterating a hierarchical structure. However, you must be aware that Microsoft—while never being specific on this point—indicates that recursion can lead to stack overflow. The extent to which you can get away with recursion really depends upon the complexity of the sub procedure concerned, the amount and type of data being passed in, and an infinite number of other variables and unknowns.
Fixed-length strings can't be passed as parameters to a sub procedure. You may think that you can get around this by using code similar to the following:
Private Sub Command1_Click() Dim sFixed As String * 20 sFixed = "Hello" AddSomeStuff sFixed MsgBox sFixed End Sub Private Sub AddSomeStuff(ByRef sStrFx As String) sStrFx = sStrFx & " World" End Sub
In fact, what happens in this case is that the variable sStrFx isn't actually a reference to sFixed in the calling procedure. It's as though you have passed the string by value—sStrFx is a variable-length copy of sFixed. So adding to the string affects only sStrFx ; when control returns to the calling procedure, sFixed is unchanged.