XL2000: Methods to Use Custom Functions with Varying Arguments

View products that this article applies to.

This article was previously published under Q213277

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.

Back to the top

MORE INFORMATION

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/30000104 (https://partner.microsoft.com/global/30000104)

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice (http://support.microsoft.com/gp/advisoryservice)

For 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;CNTACTMS (http://support.microsoft.com/default.aspx?scid=fh;en-us;cntactms)

Back to the top

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

Back to the top

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

Back to the top

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.

Back to the top

REFERENCES

For additional information about getting help with Visual Basic for Applications, click the article number below to view the article in the Microsoft Knowledge Base:

226118 (http://support.microsoft.com/kb/226118/EN-US/ ) OFF2000: Programming Resources for Visual Basic for Applications

For more information about arrays in Visual Basic for Applications, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type using arrays in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Back to the top