To demonstrate how to work with parameters, extract and open the file linked in the Files Needed section above.  In the VBE, insert a new module and begin a new subroutine as shown below:

You can provide a different name for the subroutine if you prefer.

Seeing the Parameter List

The easiest way to see the list of parameters for a method or property is to type a space after the method or property name.  If the method or property has any parameters, these will appear in the form of a tooltip:

The Copy method of a range has a single parameter called Destination.

Some parameter lists are longer than others!  The example below shows the parameter list for the SaveAs method of a workbook:

The SaveAs method has thirteen parameters altogether.  Each parameter is separated from the next using a comma.

The tooltip indicates whether a parameter is optional or compulsory using square brackets.  Parameters whose names are contained in square brackets are optional; those without square brackets are compulsory:

The Find method has nine parameters altogether. The first parameter, What, is compulsory as its name is not contained in square brackets. The remaining eight parameters are optional.

Passing a Value to a Parameter

To make use of a parameter you must pass a value to it.  The value passed to a parameter is referred to as an argument and could be a simple string of text or number, or even a reference to another object.  The example below passes a reference to a Range object to the Destination parameter of the Copy method:

Range("B2") is the argument passed to the Destination parameter.

Executing the code shown above copies cell A1 to cell B2:

The end result of running the code shown above.

Some parameters provide you with a list of values to select from.  The example below uses the PasteSpecial method to paste only the value from a copied cell:

We copy cell A1 without specifying a destination. We then apply the PasteSpecial method to cell A3.

The first parameter of the PasteSpecial method is called Paste and provides a number of options in the IntelliSense list.  In the example below we choose the option which will paste only the value of the copied cell:

You can choose an option from the IntelliSense list in the usual way.

The final instruction should resemble the example shown below:

These two instructions will copy the value of cell A1 to A3.

Running this code has the effect shown below:

The value of cell A1 is copied to cell A3.

Using Multiple Parameters

You'll often need to provide a value to more than one parameter of a method or property.  To do this, separate each argument with a comma.  In the example below we'd like to pass a value to each of the first three parameters of the PrintOut method:

We've passed a value of 1 to the From parameter and would like to pass a value to the To parameter.

Type a comma after the first argument to move to the next parameter:

The To parameter is highlighted in bold in the tooltip to indicate that it is the active parameter.

You can continue typing commas to move along the parameter list:

Typing another comma moves to the Copies parameter.

Skipping Optional Parameters

You'll sometimes want to pass an argument to an optional parameter while ignoring the parameters which precede it in the list.  As long as a parameter is optional, you can simply skip over it as shown in the image below:

Typing in a sequence of commas without entering any values allows you to skip through the parameter list to reach the one you want. Here we've skipped to the Copies parameter and its name is shown in bold in the tooltip.

Naming Parameters

Skipping parameters can make your code difficult to read.  As an alternative, you can name a parameter when you pass an argument to it:

Write the name of the parameter followed by a colon : and equals = sign. You can then write the value you want to pass to the parameter.

If you name one parameter, you must name each one to which you pass a value:

Here we've named three parameters. You must still separate each one using a comma.

Using Multiple Lines

When you're passing values to multiple parameters, you may find your code is easier to ready if you put each argument on a separate line.  To indicate that you're writing a single instruction across multiple lines in VBA you must use the continuation characters which consist of a space followed by an underscore _

Use a space followed by an underscore at the end of each line to continue the same instruction on the next line.

To practise using parameters:

1. Insert a new module in the workbook you've been using in this part of the lesson.
2. Create a new subroutine called Using_Parameters:

3. Write an instruction to add a new worksheet and use the Before parameter to place it to the left of whichever sheet is active when the code is executed:

4. Alter the line to include the name of the parameter:

5. Add an instruction which places a border around the cell which is currently active:

6. Pass a value to the first parameter of the BorderAround method to create a dashed outline:

7. Pass a value to the second parameter of the BorderAround method to create a thin border:

8. Skip the next parameter of the BorderAround method to move to the Color parameter (type two commas) and then pass a value into it:

9. Alter the instruction to use named parameters (don't forget to remove the extra comma):

10. Use continuation characters to place each argument on a separate line:

11. Add two further instructions to copy and paste the formatting of the active cell to cell B3.  Use the Paste parameter of the PasteSpecial method to paste only the formatting of the copied cell:

12. Run the subroutine and check that it performs its task correctly:

You may need to click into a different cell in order to see the results.

13. Save and close the workbook.