Parameters

Up to this point, our procedures have been self-contained. For learning purposes, that is very handy. But in reality, this will rarely happen. Usually, the variables within your procedures will need to get their values from an outside source. To do this, you need to set up parameters in the parentheses after the procedure’s name. In programming terminology, the process of sending information to those variables is called passing parameters.

Let’s start with a simple illustration. We are going to vary the fullName function as shown below:

Function fullName(strFname As String, strLname As String) As String   Dim strFirstName As String   Dim strLastName As String   Dim strFullName As String   strFirstName = strFname   strLastName = strLname   strFullName = strFirstName & " " & strLastName   fullName = strFullName End Function

Let’s take a few minutes to examine what is going on.

Our function now needs the caller to send it two pieces of information before it can do its job: a first name and a last name. It doesn’t care what those names are (as long as they are strings), just that it needs them.

Let’s go to the Immediate window and type the following:

? fullName ("Jane", "Doe")

The call sends two arguments. The first one goes to strFName, and the second goes to strLName.

You may find it curious that I made the parameter names different from the variable names inside the function. This is standard programming practice. You never want a caller to access the variables of a procedure directly. The moment you do, you have lost control of that variable. Many times you may create If…Then structures to test the incoming parameters for validity. As an example, you may want to put the following filter in your procedure:

If strFName = " " Then   strFirstName = "(Name not provided)" Else   strFirstName = strFName End If

On the caller side, it is important that the arguments are sent in the correct order and with the correct data type. The parameters are separated by a comma.

Just to illustrate a point, try typing

?fullName ("Doe", "Jane")

The procedure now thinks “Doe” is the first name and “Jane” is the last name. It has no way of distinguishing what you really want from what you sent.

Let’s set up our module with a second procedure now:

Sub getName()   Dim strFirstName As String   Dim strLastName As String   Dim strFullName As String   strFirstName = InputBox("Enter the First Name", "First Name")   strLastName = InputBox("Enter the Last Name", "Last Name")   strFullName = fullName(strFirstName, strLastName)   MsgBox strFullName, , "Full Name" End Sub

A lot is going on here that we need to look at.

First of all, notice that the same variable names are declared in each procedure. Aren’t they going to clash? These are called local variables. They only exist for the procedure they are in, and once the procedure does its job, they disappear. You could have as many procedures as you want with the same variable names, and they will not see each other or clash.

The procedure getName calls the procedure fullName with the two required parameters. fullName then does the job of concatenation and returns the combined string to the caller, getName. What you don’t see is that when the value is returned, the call

fullName(strFirstName, strLastName)

is converted to

fullName = "Jane Doe"

That is how functions return information. From there on, we used the variable fullName in the message box as we have at other times.

All you need to do is call the sub, getName, from the Immediate window. Remember, because it is not a function, you don’t need to preface it with the question mark.

This is all very neat. However, as you are about to see, things can get somewhat complex now.

Optional Parameters

You want your procedures to be as flexible as possible. However, as you just saw, they expect a certain number of arguments, each of a certain type, and in a certain order. That certainly does not sound very flexible.

As an example, suppose that some of the data going to the fullName function contains a middle name, while other data does not. Based on the preceding description, you could not handle that. However, VBA comes to your aid with a unique programming tool. It is called the optional argument. You could declare the function as follows:

Function fullName (strName as String, Optional strMName As String,_ Optional strLName As String)

The Optional parameters should be the last ones because once one is used, any subsequent parameters must be optional.

You now have four ways of calling this function. You can call it with:

fullName(strFirstName, strMiddleName, strLastName)

Or you can call it with

fullName(strFirstName, , strLastName)

Or you can use

FullName(strFirstName, strMiddleName)

Finally:

FullName(strFirstName)

Notice the placeholder in place of the second argument in the second function call. (Even though it is not shown here, you would need to adjust the code in the fullName procedure to handle the new parameter and include it in the concatenation.) This adds a lot of flexibility to the procedure.

In many instances, you may want to substitute a default value if an optional parameter is not provided. Let’s take a look at the following potential procedure declaration:

Sub account (strFirstName As String, strLastName As String, – Optional intBalance as Integer = 0)

Here, intBalance is an optional parameter. However, rather than just leave it null, a default value of 0 will be set if no other value is provided.

Named Parameters

It is easy to see that all these parameters, with assorted options, can get Byzantine in their declarations. For instance, let’s say you have a scenario such as this:

Function fullname(Optional strFName As String, _ Optional strMName As String, Optional strLName As String) 

As stated in the preceding section, this procedure can now be called in a variety of ways. Just to name a few:

fullName(strFirstName, , )

or

fullName(, strMiddleName, )

or

fullName(, , strLastName)

or

fullName(strFirstName, strMiddleName, strLastName)

Let’s also add a scenario of different callers possibly calling the procedure with the parameters in different order.

Getting the idea? There is an easier way. By simply using the function’s actual parameter names and a colon, many of your problems will be solved.

Again, using the preceding function, you could call it as follows:

fullName(strFName: = "John")

Isn’t that much easier?

As a matter of fact, even without optional parameters, you could have done something like this:

fullName(strMName: = "E", strLName: = "Smith", strFName: = "John")

You can completely rearrange the arguments as needed. Needless to say, this greatly streamlines the process by allowing for a number of different ways of passing parameters without doing additional coding.

As an interesting note, as you will see in Chapter 12, you can use a named parameter in VBA code as a Goto point. In that setting, it is sometimes referred to as a LineLabel. Again, it is a name followed by a colon.