Writing Functions

Remember that a function's name acts like a variable. The final value of this variable is the value returned by the function. To demonstrate, examine the following function, which returns the user's first name:

Function FirstName()

Dim FullName As String

Dim FirstSpace As Integer

FullName = Application.UserName

FirstSpace = InStr(FullName, " ")

If FirstSpace = 0 Then

FirstName = FullName

Else

FirstName = Left(FullName, FirstSpace -

■ 1)

End If

End Function

This function starts by assigning the UserName property of the Application object to a variable named FullName. Next, it uses the VBA InStr function to locate the first space in the name. If there is no space, FirstSpace is equal to 0 and FirstName is equal to the entire name. If FullName does have a space, the Left function extracts the text to the left of the space and assigns it to FirstName.

Notice that FirstName is the name of the function and is also used as a variable name in the function. The final value of FirstName is the value that's returned by the function. Several intermediate calculations may be going on in the function, but the function always returns the last value assigned to the variable that is the same as the function's name.

All of the examples in this chapter are available at this book's Web site.

0 0

Post a comment