Functions

Since we have already had some experience with sub procedures, let’s concentrate for now on functions. Let’s assume you either start, or open, an existing module. A simple function might look as follows:

Function fullName() As String   Dim strFirstName As String   Dim strLastName As String   Dim strFullName As String   strFirstName = "John"   strLastName = "Smith"   strFullName = strFirstName & " " & strLastName End Function

Seems pretty straightforward! Well, since we are not using a GUI, you will have to use the Immediate window to test it.

Testing a function in the Immediate window is a little different from testing a sub. Remember, you are asking the function to return a value, in this case, a string. You are asking, “What is the full name?” In order to do that, you type the following in the Immediate window:

?fullName

Then press ENTER.

Whoops! Nothing. Not even an error!

Wait, you were asking the function, fullName, to return a string. You just forgot one little part: what string? Do you want it to return strFirstName, strLastName, or strFullName?

It may be obvious to you that you want it to return strFullName, but it is not so obvious to VBA. (Chapter 3 discussed the importance of using the proper sequence.) Here is how it works: A function is like a variable in that it stores a value to a name that will be returned to whatever is calling that name. However, the value returned by the function must be assigned to a variable with the same name as the function. To illustrate this, let’s add one more line of code after the concatenation:

fullName = strFullName 

Go ahead and test it now. It should work fine.

Whatever you assigned to the function name will be the return value of the function. Also remember that if you do not assign a value, nothing will be returned.

Notice that unlike subs, you don’t need to place the Debug.Print statement in your code to show an output in the Immediate window because a function is already returning a value that will show in the window.

A little later in this chapter, you will see how to find functions prebuilt within the VBA library.