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

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.

0 0

Post a comment