Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:
Microsoft Certified Partners -
https://partner.microsoft.com/global/30000104Microsoft Advisory Services -
http://support.microsoft.com/gp/advisoryserviceFor more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMSMethod 1: The Paramarray Keyword
Using the Paramarray keyword, the function can accept any number of
arguments, which are all stored in the array that follows the Paramarray
keyword. For example, as shown in the following Sub procedure, Testit, five
arguments are sent to the function, each one being placed into the nums
array with a separate index number. Next, the upper bound of the nums array
is found using the Microsoft Visual Basic for Applications function,
UBOUND, which returns the largest available subscript for an array that is
passed to it. In the following example, because Paramarray is filled with
five arguments, n = 5. Paramarray is always an array of Variants; it
cannot be declared as any other type.
Option Base 1
Function sumit(ParamArray nums() As Variant)
n = UBound(nums()) ' Finds the number of arguments passed to
' the function.
For x = 1 To n
sumit = sumit + nums(x)
Next ' Adds each of the arguments into the total sum.
End Function
Sub testit()
MsgBox sumit(1, 2, 3, 4, 5)
End Sub
Method 2: The Optional Keyword
The Optional keyword should be used in situations where there is an upper
bound on the number of arguments that are going to be passed. For example,
if the maximum number of arguments for a function is not going to exceed
three or four, you can use the Optional keyword in conjunction with the
ISMISSING function. The ISMISSING function returns either TRUE or FALSE,
depending on what arguments were passed into the function. Arguments that
use the Optional keyword must always be declared as Variant.
Function sumit(t1 As Integer, Optional t2 As Variant, Optional t3 As _
Variant) As Integer
Select Case IsMissing(t2) And IsMissing(t3)
' Depending on what arguments were passed, the Select Case returns
' the sum of the arguments that were present.
Case True
sumit = t1 ' Only t1 was passed.
Case False
If IsMissing(t2) Then
sumit = t1 + t3 ' Only t1 and t3 were passed, not t2.
Else
If IsMissing(t3) Then
sumit = t1 + t2 ' Only t1 and t2 were passed, not t3.
Else
sumit = t1 + t2 + t3 ' All of the arguments were passed.
End If
End If
End Select
End Function
Sub test1()
MsgBox sumit(4, 5, 6)
End Sub
Method 3: Using Public Variables
It is also possible, but not recommended, to use variables with a Public
scope as the "arguments" of a user-defined function. Using Public variables
as inputs for a function is not the same as passing arguments; the function
call does not include the arguments themselves; they are accessible to all
functions in the workbook. This method will work, but because all of the
procedures in the workbook have access to these variables, it is possible
that they will have unwanted values.
Public arg1 As Integer
Public arg2 As Integer
Public arg3 As Integer
' These variables use less of the computer's internal memory for storage
' space than the Variant data types created in the preceeding macros
Function sumit()
sumit = arg1 + arg2 + arg3
End Function
Sub test()
arg1 = 1
arg2 = 2
arg3 = 3
MsgBox sumit
End Sub
Each of these methods will work in all cases, but each one will work more
efficiently than the others in certain situations. For example, using an
array in Visual Basic for Applications will always be more efficient than
using individual variables. This must be weighed against the extra code
that must be put into the macro to cycle through the Paramarray. Also,
using the Paramarray to contain your arguments tends to lessen the
readability of your code because it is impossible to tell what arguments
have been passed without cycling through the array.
In terms of memory storage space, Method 3 uses the least amount of
storage. The Variant data type used in Visual Basic for Applications will
accept any of the supported data types and automatically make the
conversion to the correct data type internally. However, because they must
accommodate many different storage sizes, Variant data types use 16 bytes
of storage space in memory. Because both the Optional keyword and the
Paramarray keyword must use Variants as their data type, they will allocate
much more space in memory than simply not using arguments and declaring
public variables of the correct type. For example, if a function must have
as input four or five integers, if you declare four or five Public
variables and assign the numbers to them, the total storage space required
would only be 8 to 10 bytes as opposed to 64 to 80 bytes using the Optional
or Paramarray keywords. Using Public variables as shown in Method 3 is not
recommended because they are not passed to the function; they are only
referenced. Also, using Public variables as inputs to a function will not
enter them into the Function Wizard. The user-defined function will have no
arguments listed in the Function Wizard.