XL: Methods to Use Custom Functions with Varying Arguments

ID: Q151582

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for the Power Macintosh, versions 5.0, 5.0a

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, 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/overview/overview.asp

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.

REFERENCES

For more information about custom functions, please see the following article in the Microsoft Knowledge Base:

Q112321 XL5: Macro Fails If Optional Argument Uses Integer Data Type

Additional query words: 5.00a 5.00c 7.00a 8.00 XL97 XL7 XL5

Keywords : kbprg kbdta kbdtacode PgmHowto KbVBA
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0c,7.0,97
Platform : MACINTOSH WINDOWS
Issue type : kbhowto