Microsoft Office Tutorials and References

In Depth Information

**Function Arguments**

Function Arguments

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)

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 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

this chapter.