Module 1 - Getting Started in VBA
Lesson 1.4 - How VBA Works
Topic 1.4.2 - Parameters and Arguments

Some methods and properties require extra information in order to perform a task.  You provide this information by passing a value (referred to as an argument) into a parameter of the method or property.

Files Needed

You can click here to download the file used for this page.

Completed Code

You can click here to download a file containing the sample code.

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:

New sub

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:

Copy method

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:

SaveAs method

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:

Find method

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:

Copy range

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

 

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

End result

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:

Copy and pastespecial

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:

Paste value

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

The final instruction should resemble the example shown below:

Final code

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

 

Running this code has the effect shown below:

Copied cell

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:

First parameter

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:

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

Third parameter

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:

Skip parameters

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.

 

You can only skip a parameter if it is optional, i.e. its name is shown in square brackets 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:

Name parameter

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:

Naming multiple parameters

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

 

When you're using named parameters you can put them in any order.

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 _

Multi lines

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:

Sub Using_Parameters()

 

End Sub

  1. 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:

Sub Using_Parameters()

 

Worksheets.Add ActiveSheet

 

End Sub

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

Sub Using_Parameters()

 

Worksheets.Add Before:=ActiveSheet

 

End Sub

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

Sub Using_Parameters()

 

Worksheets.Add Before:=ActiveSheet

 

ActiveCell.BorderAround

 

End Sub

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

Sub Using_Parameters()

 

Worksheets.Add Before:=ActiveSheet

 

ActiveCell.BorderAround xlDash

 

End Sub

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

Sub Using_Parameters()

 

Worksheets.Add Before:=ActiveSheet

 

ActiveCell.BorderAround xlDash, xlThin

 

End Sub

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

Sub Using_Parameters()

 

Worksheets.Add Before:=ActiveSheet

 

ActiveCell.BorderAround xlDash, xlThin, , rgbRed

 

End Sub

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

Sub Using_Parameters()

 

Worksheets.Add Before:=ActiveSheet

 

ActiveCell.BorderAround LineStyle:=xlDash, Weight:=xlThin, Color:=rgbRed

 

End Sub

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

Sub Using_Parameters()

 

Worksheets.Add Before:=ActiveSheet

 

ActiveCell.BorderAround _

LineStyle:=xlDash, _

Weight:=xlThin, _

Color:=rgbRed

 

End Sub

  1. 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:

Sub Using_Parameters()

 

Worksheets.Add Before:=ActiveSheet

 

ActiveCell.BorderAround _

LineStyle:=xlDash, _

Weight:=xlThin, _

Color:=rgbRed

 

ActiveCell.Copy

 

Range("B3").PasteSpecial _

Paste:=xlPasteFormats

 

End Sub

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

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

 
  1. Save and close the workbook.
This page has 0 threads Add post