ID: Q151582
The information in this article applies to:
Using Microsoft Excel, you can create user-defined functions that can be called from a worksheet. These functions accept arguments in their function calls that can be of any type. Often, you need to be able to pass a varying amount of arguments to the function, depending on the situation.
Function calls in Microsoft Visual Basic for Applications can accept a varying number of arguments, using one of three methods. Each method has its own limitations and uses and can be applied only in certain situations.
The following macros and functions demonstrate each method using a common task--summing a varying quantity of numbers and returning the results.
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 the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/refguide/
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
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
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
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.
For more information about custom functions, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q112321
ARTICLE TITLE: XL5: Macro Fails If Optional Argument Uses Integer
Data Type
Keywords : kbprg kbdta kbdtacode PgmHowto KbVBA
Version : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a,98
Platform : MACINTOSH WINDOWS
Issue type : kbhowtoLast Reviewed: May 18, 1999