Microsoft Office Tutorials and References
In Depth Information
Keep in mind the following points about Function procedure arguments:
h Arguments can be variables (including arrays), constants, literals, or expressions.
h Some functions don’t have arguments.
h Some functions have a fixed number of required arguments (from 1 to 60).
h Some functions have a combination of required and optional arguments.
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)
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 20 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.
For another example of reinventing the wheel, see “Emulating Excel’s SUM Function,” later in