Function Arguments

Keep in mind the following points about Function procedure arguments:

• Arguments can be variables (including arrays), constants, literals, or expressions.

• Some functions do not have arguments.

• Some functions have a fixed number of required arguments (from 1 to 60).

• Some functions have a combination of required and optional arguments.

Note  

If your formula uses a custom worksheet function and it returns #VALUE!, there is an error in your function. The error could be caused by logical errors in your code or by passing incorrect arguments to the function. See "Debugging Functions" later in this chapter.

Reinventing the Wheel

Just for fun, I wrote my own version of Excel's UPPER function (which converts a string to all uppercase) and named it UpCase :

 Function UpCase(InString As String) As String '   Converts its argument to all uppercase.     Dim StringLength As Integer     Dim i As Integer     Dim ASCIIVal As Integer     Dim CharVal As Integer     StringLength = Len(InString)     UpCase = InString     For i = 1 To StringLength         ASCIIVal = Asc(Mid(InString, i, 1))         CharVal = 0         If ASCIIVal >= 97 And ASCIIVal <= 122 Then             CharVal = -32             Mid(UpCase, i, 1) = Chr(ASCIIVal + CharVal)         End If     Next i End Function 

Note  

A workbook that contains this function is on the companion CD-ROM in a file named  upper case.xlsm .

Notice that I resisted the urge to take the easy route - using the VBA UCase function.

I was curious to see how the custom function differed from the built-in function, so I created a worksheet that called the function 20,000 times, using random names . The worksheet took about 40 seconds to calculate. I then substituted Excel's UPPER function and ran the test again. The recalculation time was virtually instantaneous. I don't claim that my UpCase function is the optimal algorithm for this task, but it's safe to say that a custom function will never match the speed of Excel's built-in functions.