Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
You can click here to download the file used for this page.
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:
You can provide a different name for the subroutine if you prefer.
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.
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.
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.
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.
You can only skip a parameter if it is optional, i.e. its name is shown in square brackets in the tooltip.
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.
When you're using named parameters you can put them in any order.
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:
Sub Using_Parameters()
End Sub
Sub Using_Parameters()
Worksheets.Add ActiveSheet
End Sub
Sub Using_Parameters()
Worksheets.Add Before:=ActiveSheet
End Sub
Sub Using_Parameters()
Worksheets.Add Before:=ActiveSheet
ActiveCell.BorderAround
End Sub
Sub Using_Parameters()
Worksheets.Add Before:=ActiveSheet
ActiveCell.BorderAround xlDash
End Sub
Sub Using_Parameters()
Worksheets.Add Before:=ActiveSheet
ActiveCell.BorderAround xlDash, xlThin
End Sub
Sub Using_Parameters()
Worksheets.Add Before:=ActiveSheet
ActiveCell.BorderAround xlDash, xlThin, , rgbRed
End Sub
Sub Using_Parameters()
Worksheets.Add Before:=ActiveSheet
ActiveCell.BorderAround LineStyle:=xlDash, Weight:=xlThin, Color:=rgbRed
End Sub
Sub Using_Parameters()
Worksheets.Add Before:=ActiveSheet
ActiveCell.BorderAround _
LineStyle:=xlDash, _
Weight:=xlThin, _
Color:=rgbRed
End Sub
Sub Using_Parameters()
Worksheets.Add Before:=ActiveSheet
ActiveCell.BorderAround _
LineStyle:=xlDash, _
Weight:=xlThin, _
Color:=rgbRed
ActiveCell.Copy
Range("B3").PasteSpecial _
Paste:=xlPasteFormats
End Sub
You may need to click into a different cell in order to see the results.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.