Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

Module 1 - Getting Started in VBA

Lesson 1.4 - How VBA Works

Topic 1.4.3 - Using Parentheses

In the previous part of this lesson, you learnt about parameters and how to pass values to them. A common source of confusion when starting out in VBA is when to use parentheses (round brackets) to enclose the list of values passed to parameters. This part of the lesson attempts to explain the rules of those pesky parentheses!

The short explanation is that you need to use parentheses when you're making use of the value returned by a method or property. The rest of this page attempts to explain what that means!

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 example code.

To demonstrate how to use parentheses, 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:

Feel free to provide a different name for the subroutine.

We won't execute any of the code shown in this part of the lesson, but we should still create a subroutine as a container for the instructions we'll write.

Not Using Parentheses

As you saw in the previous part of this lesson, when you pass values to the parameters of a method or property, a tooltip appears to help you. You may have noticed that the tooltip always displays the parameter list in a set of parentheses (round brackets) but that you don't always write these in your code:

The tooltip shows parentheses around the parameter list but we don't use them here.

If you use parentheses to enclose an argument list, you may encounter a syntax error:

Using parentheses here causes a syntax error when we move the cursor to a new line.

In VBA, you use parentheses to enclose an argument list when you want to return something from the method or property that you're using. In the example above, we're simply asking the PrintOut method to perform its task - we haven't attempted to use any form of result from applying the method. There are many examples of this type of instruction in VBA, some are shown in the image below:

The three instructions shown here don't use parentheses around the argument list. We're not attempting to return a result, we simply want to perform the appropriate action.

If we do attempt to use parentheses in any of the three examples shown above, we'll create syntax errors:

Using parentheses causes syntax errors.

Using Parentheses - Reason 1

If you want to write a single instruction which includes a sequence of methods and properties, you must use parentheses to enclose any argument lists.

In the example below we want to use the Find method to return a reference to a cell containing the text Wise Owl. We then want to apply the Select method to the cell returned by the Find method. As we want to return something from the Find method, we need to use parentheses to enclose the argument list:

Here we're passing a value to the What parameter and enclosing the argument list in a set of parentheses.

We then close the parentheses and apply a method to the object that the Find method returns:

Typing a full stop after the closing parenthesis shows a list of methods and properties that can be applied to the range object returned by the Find method.

Attempting to do this without using parentheses causes a syntax error:

You can't omit the parentheses in this example.

There are many other examples of this type of instruction in VBA:

Each of these lines uses a method or a property whose return value we've used to apply a subsequent method or property.

We can't perform any of the instructions shown above without using a set of parentheses around the argument list:

Omitting the parentheses causes syntax errors.

Using Parentheses - Reason 2

If you're using the result of a method or property to assign a value to something, you must use parentheses to enclose the argument list.

In the example below, we're using the RGB method to assign a fill colour to a cell. As we're using the value returned by the method, we must enclose its argument list in parentheses:

The RGB method returns a number which represents a colour. Here we're using that number to set the value of the Color property.

If we try to do this without using parentheses, we cause a syntax error:

No parentheses = no good.

Spotting Methods and Properties Which Return Things

You can use the tooltip which displays the parameter list to identify which methods return a result. In the tooltip shown below, after the closing parenthesis there is a clue that the method returns something:

The tooltip shows that the RGB method returns a value As Long. Long is a type of number in VBA.

You can see another example in the image below:

After the closing parenthesis in the tooltip, the information indicates that the Find method returns a result As Range.

If you want to make use of the result returned by a method or property, you must enclose the argument list in a set of parentheses.

Methods which don't return a result won't display anything after the closing parenthesis in the tooltip:

There is no information after the closing parenthesis in this tooltip. This shows that the BorderAround method doesn't return a result.

You can see another example of a method which doesn't return a result in the image below:

There is nothing after the closing parenthesis in the tooltip so this method doesn't return a result.

If a method doesn't return a result, you'll never need to use parentheses to enclose the arguments you pass to its parameters.

You'll get lots of practice using the techniques covered in this part of the lesson as you progress through the course.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Not Using Parentheses

Using Parentheses - Reason 1

Using Parentheses - Reason 2

Spotting Methods and Properties Which Return Things

Head office

London

Manchester