| Function Statement |
Named Arguments
No
Syntax
[Public | Private | Friend] [Static] Function name _
[(arglist)] [As type][()]
[statements]
[name = expression]
[Exit Function]
[statements]
[name = expression]
End FunctionPublic
Use: Optional
Type: Keyword
Gives the function scope through all procedures in all modules in the project. If used within a createable class module, the function is also accessible from outside the project. Public, Private, and Friend are mutually exclusive.
Private
Use: Optional
Type: Keyword
Restricts the scope of the function 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 function 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 function between calls to the function.
name
Use: Required
The name of the function.
arglist
Use: Optional
A comma-delimited list of variables to be passed to the function as arguments from the calling procedure.
type
Use: Optional
The return data type of the function.
statements
Use: Optional
Program code to be executed within the function.
expression
Use: Optional
The value to return from the function to the calling 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 function. 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, the 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 function procedure.
Rules at a Glance
If you don't include one of the Public, Private, or Friend keywords, a function is Public by default.
If you declare a function as Public within a module that contains an Option Private directive, the function is treated as Private.
Any number of Exit Function statements can be placed within the function. Execution continues with the line of code immediately following the call to the function. If a value has not been assigned to the function when the Exit Function statement executes, the function returns the default initialization value of the data type specified for the return value of the function. If the data type of the function was an object reference, the exited function returns Nothing.
The return value of a function is passed back to the calling procedure by assigning a value to the function name. This may be done more than once within the function.
To return an object reference from a function, the object must be assigned to the function's return value using the Set statement. For example:
Private Function GetAnObject() As SomeObject Dim oTempObject As SomeObject Set oTempObject = New SomeObject Set GetAnObject = oTempObject Set oTempObject = Nothing End Function
Until Visual Basic Version 6, the return value of a function could not be an array of any data type. One of the major improvements in VB6 is that it allows you to return arrays of any type from a procedure. But to do this, there are two rules to follow. First, you must use parentheses after the data type—which is also mandatory—in the return value of the function declaration. Second, any array in the calling program that is assigned the return value of the function call must be of the same data type as the function.
Here's a quick example showing this in operation. Here, the PopulateArray function is called and is passed a string value. PopulateArray takes this value and concatenates the numbers 1 to 10 to it, assigns each value to an element of an array, then passes this array back to the calling procedure. Note that in the calling procedure, the array that accepts the array returned from the function is declared as a dynamic array. Its size is never explicitly defined in the calling routine; another new feature of VB6 is the ability to assign arrays of any type from one array variable to another in a single assignment statement—as long as the array on the left side of the expression is dynamic:
Private Sub Command3_Click() Dim i As Integer Dim sReturnedArray() As String sReturnedArray() = PopulateArray("A") For i = 1 To UBound(sReturnedArray) Debug.Print sReturnedArray(i) Next i End Sub Private Function PopulateArray(sVal As String) _ As String() Dim sTempArray(10) As String Dim i As Integer For i = 1 To 10 sTempArray(i) = sVal & CStr(i) Next i PopulateArray = sTempArray End Function
If you specify an optional parameter in your function declaration, you can also provide a default value for that parameter. For example:
Private Function ShowMessage(Optional sMsg _ As String = "Not given")
If you're not using VB6, you can still return an array from a function. However, it can be only a variant containing an array. For example:
Private Function MakeArray() As Variant MakeArray = Array(1, 2, 3, 4) End Function Private Sub Form_Load() Dim varArray As Variant varArray = MakeArray() MsgBox UBound(varArray) End Sub
A function can't define a fixed-length string as an argument in arglist; this produces the design-time error, "Expected array."
A calling program can pass a fixed-length string to a function. In most cases, however, this makes little sense, since the string as defined by the function prototype must be either a variable-length string or a variant string. This means that if another string is concatenated with the string, the "extra" portion of the string is lost when the function returns.
A user-defined type can't be included in an argument list as an optional argument.
Another addition to VB6 is the ability to pass a user-defined type (UDT) remotely; that is, you can add a UDT to the parameter list of a public function and as the return value of a public function or method. To enable a public class or code module to expose a UDT, you must declare its type as Public so that clients can "see" the UDT. Here's a simple example of passing a UDT remotely. The first part of the example is a class module in an ActiveX DLL; the second part is a standard EXE project that has a reference to the ActiveX DLL. First, the server code:
Option Explicit 'declare the public user defined type Public Type RemUDT AuID As String LName As String FName As String Phone As String End Type 'declare a local array variable to hold an 'array of the udt Private muRemUDT(1 To 10) As RemUDT Private Function getAuthors() As Boolean 'this function simply populates the udt array 'using the SQL Server pubs test database Dim adoConn As ADODB.Connection Dim adoRecs As ADODB.Recordset Dim i As Integer Dim sSQL As String 'create instances of ADO objects Set adoConn = New ADODB.Connection Set adoRecs = New ADODB.Recordset 'open the ado connection using a test DSN adoConn.Open "Test" 'create a SQL query sSQL = "SELECT *" & vbCrLf __ & "FROM authors" & vbCrLf 'open the recordset adoRecs.Open sSQL, adoConn, adOpenForwardOnly, _ adLockReadOnly 'populate 10 elements of the array For i = 1 To 10 muRemUDT(i).AuID = adoRecs!au_id muRemUDT(i).LName = adoRecs!au_lname muRemUDT(i).FName = adoRecs!au_fname muRemUDT(i).Phone = adoRecs!Phone Next i 'kill the ado recordset Set adoRecs = Nothing End Function Public Function AuthorUDTArray() As RemUDT() 'pass back an array of the udt to the client AuthorUDTArray = muRemUDT End Function Public Function AuthorUDT(iVal As Integer) As RemUDT 'pass back a single element of the 'udt array to the client AuthorUDT = muRemUDT(iVal) End Function
Here's the client code:
Option Explicit 'declare local array for udt Private uUDTArray() As RemUDT 'declare local copy of udt Private uUDT As RemUDT 'declare local udt class object Private oUDT As UDTClass Private Sub Form_Load() 'instantiate the udt class object Set oUDT = New UDTClass End Sub Private Sub cmdUDTArray_Click() Dim i As Integer 'call the remote array function and 'assign the array to the local udt array uUDTArray = oUDT.AuthorUDTArray 'iterate through the array For i = 1 To UBound(uUDTArray) With uUDTArray(i) Debug.Print .AuID Debug.Print .FName Debug.Print .LName Debug.Print .Phone End With Next i End Sub Private Sub cmdSingleUDT_Click() Dim sVal As String Dim i As Integer For i = 1 To 10 'call the single udt function & assign the result 'to the local udt copy uUDT = oUDT.AuthorUDT(i) With uUDT Debug.Print .AuID Debug.Print .FName Debug.Print .LName Debug.Print .Phone End With Next i End Sub Private Sub Form_Unload(Cancel As Integer) 'kill the udt class object reference Set oUDT = Nothing End Sub
The default value for an optional object argument can be only Nothing.
Programming Tips and Gotchas
There is often confusion between the ByRef and ByVal methods of assigning arguments to the function. ByRef assigns a reference to the variable in the calling procedure to the variable in the function; any changes made to the variable from within the function 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 function. Changes made to the variable in the function have no effect on the variable in the calling procedure. In general, ByRef arguments within class modules take longer to perform, since marshalling back and forth between function and calling module must take place; so unless you need to modify a variable's value explicitly within a function, it's best to pass parameters by value.
Functions can return only one value, or can they? Look at the following code:
Sub testTheReturns() Dim iValOne As Integer iValOne = 10 If testValues(iValOne) = True Then Debug.Print iValOne End If End Sub Function testValues(ByRef iVal As Integer) As Boolean iVal = iVal + 5 testValues = True End Function
Because the argument was passed ByRef, the function acted upon the underlying variable iValOne. This means you can use ByRef to obtain several "return" values (although they're not strictly return values) from a single function call. I would go so far as to say that your program can be made much more robust by returning only a Boolean from the actual function call, then testing its value prior to proceeding with the routine in hand.
It's possible to pass an array as the return value for a function by assigning the array to the function in the usual manner—i.e., myFunction = myArray(). However, Microsoft recommends you not use this method for "performance reasons."
What about a performance gain from returning arrays from functions? In many cases you will be simply shifting the creation of the array from one place to another, i.e., in the earlier example, assuming both procedures are within the same project, the performance gain is minimal, since the only benefit is that you are making one function call instead of 10 to populate an array. However when you start to investigate more complex uses for returning arrays, you start to see some substantial benefits.
For example, the following code demonstrates two methods of populating a combo box with the names of authors in the SQL Server sample database "pubs." An Authors object holds a collection of Author objects, which to keep things simple holds only the name of each author. Two methods are used. In the first, Command1_Click uses the traditional method of instantiating an Author object by calling the Authors.Author property for each Author object in the Authors collection and assigning the value of the AuthorName property to the combo box's list. In the second, Command2_Click calls a function, Authors.AuthorsNames, which returns an array of all the author names. Here's the code for the client application:
Option Explicit Private moAuthors As Authors Private Sub Command1_Click() cboAuthors.Clear Dim i As Integer Dim oAuthor As Author 'the traditional method of obtaining the names For i = 1 To moAuthors.Count Set oAuthor = moAuthors.Author(i) cboAuthors.AddItem oAuthor.AuthorName Set oAuthor = Nothing Next i End Sub Private Sub Command2_Click() cboAuthors.Clear Dim i As Integer Dim sAuthors() As String 'the new method of bringing in an array from 'the object sAuthors() = moAuthors.AuthorNames For i = 1 To UBound(sAuthors) cboAuthors.AddItem sAuthors(i) Next i End Sub
The code for the Authors object is shown below. Note that the Author object is prepopulated to save confusing the code here:
Option Explicit Private mcolAuthors As Collection Public Function AuthorNames() As String() Dim oAuthor As Author Dim sTempArray() As String Dim i As Integer ReDim sTempArray(1 To mcolAuthors.Count) For i = 1 To mcolAuthors.Count Set oAuthor = mcolAuthors.Item(i) sTempArray(i) = oAuthor.AuthorName Set oAuthor = Nothing Next i AuthorNames = sTempArray End Function Public Property Get Author(vVal As Variant) As Author Set Author = mcolAuthors.Item(vVal) End Property Public Property Get Count() As Long Count = mcolAuthors.Count End Property
The two projects were then run as a standard client EXE and an ActiveX server DLL. When executed on the same machine, the time taken to populate the combo box is roughly the same, although the array method has a slight advantage. When the DLL was run via Microsoft Transaction Server on a remote machine, the difference in performance is astounding—though not entirely surprising. Below are the figures obtained from taking the average time required to populate the combo box 100 times using each method:
Method Used Average Time (secs.) Returned object .4175 Returned array .0455 Another addition to VB6 goes hand in hand with returning arrays from functions: the ability to assign arrays of any type from one array variable to another in a single assignment expression. There is one condition: the array on the left side of the assignment statement must be dynamic.
Using UDTs remotely—that is, passing a UDT from a DLL to a client or from an EXE to a client—requires you to upgrade OLE on both the client and server machine. If you are running NT5 or Windows 98, you should already have the required upgrade, as should an NT machine with Service Pack 4 applied. Otherwise you need to obtain the latest versions of OLE32.DLL and RPCRT4.DLL. For Windows 95 machines, you should install the latest version of DCOM95.
Optional arguments afford you wonderful flexibility, allowing you to create generic functions that can be used in a variety of scenarios. Until version 5 of VBA, optional arguments could be only of the variant data type. Now, 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 function. If IsMissing returns True, you know immediately that the optional argument wasn't supplied in the function call. Checking to determine whether a strongly typed variable (an integer, for example) is missing is more difficult:
Sub testMissingInt() Dim iVal As Integer Dim iValTwo As Integer iVal = 10 iValTwo = 0 Debug.Print testFunc(iVal, iValTwo) End Sub Function testFunc(ByRef iVal As Integer, _ Optional iValTwo As Integer) As Integer If iValTwo = 0 Then 'perform this if iValTwo is missing testFunc = iVal + 10 Else 'perform this if iValTwo is present testFunc = iVal + iValTwo End If End Function
A missing optional integer argument appears within the function as its initialized value, which is 0. But what happens when you want to pass the value to the function? 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.
A ParamArray must be declared in the function as an array of type 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() Debug.Print testFunc(10, 500, 60) End Sub Function testFunc(ParamArray someArgs() As Variant) _ As Integer Dim iArg As Integer Dim i As Integer Dim vArg As Variant For Each vArg In someArgs iResult = iResult + vArg Next testFunc = iResult End Function
For reasons I haven't quite fathomed yet, you can't use ParamArrays to pass arguments to functions in remote server applications. It's difficult to describe the results you obtain; suffice it to say 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 function 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 function without changing the COM interface, thereby retaining compatibility with a previous version of the server application.
One of the most useful additions to VB5 and VBA5 is the Friend keyword, which allows you to expose a property function or subroutine 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 where you will run into the dreaded (by some!) recursive function call. Recursion occurs when you call a function 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 function concerned, the amount and type of data being passed in, and an infinite number of other variables and unknowns.