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.
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, by passing incorrect arguments to the function, or by performing an illegal action (such as attempting to change the formatting of a cell). 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
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 an argument that was 26 characters long. The worksheet took about nine 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.
Was this article helpful?