Microsoft KB Archive/151582

= Methods to Use Custom Functions with Varying Arguments =

Article ID: 151582

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q151582





SUMMARY
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.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements.

Method 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.

