VBA Methods: The Complete Guide To Working With Methods In Excel

If you're in the process of learning Visual Basic for Applications, there are a few basic topics you need to thoroughly understand. I have explained some of these in other blog posts that you can find at the Power Spreadsheets Archive.

However, at the most basic level, the following 3 topics are perhaps the most basic things you need to learn and understand in order to start making useful things with VBA:

1. Objects.
2. Object properties.
3. Object methods.

You can read some of the tutorials I've written about the first 2 topics here, here (for objects), and here (for properties). This blog post focuses on the third item in the list above:

Methods.

More precisely, I explain the most important aspects you need to know in order to start working with object methods in VBA. You can use the following table of contents to navigate directly to any section you're most interested in. Make sure to read the full post in any case 😉 .

OK. Let's go straight into the topic of today's post and take a closer look at…

What Are Object Methods In VBA And Why Are They Important

There's no question that knowing about VBA objects is extremely important. To achieve something of practical value, you must do something with/to the object(s) you work with.

Therefore, you must know about VBA objects in order to become a great Excel VBA programmer. However, this isn't enough on its own…

You also need to know and understand how you can manipulate those objects. And this is where object methods and object properties come in:

Methods are the actions or operations you perform with an object (by or on the object). Properties, on the other hand, are the attributes or characteristics you can use to describe the object.

According to a common analogy (between VBA and the parts of speech in the English language), you can think of:

• Objects as akin to nouns.
• Properties as analogous to adjectives.
• Methods as corresponding to verbs.

It's not possible to communicate fluently in English by just using nouns (without also using adjectives and verbs). Similarly, you can't build appropriate VBA applications by just using objects (without also including properties and methods).

The ability of specifying the action to be applied to an object allows you to use methods for the following 2 main purposes:

• Purpose #1: Make an object do something.
• Purpose #2: Modify the properties of an object. Consider (for ex.) the Range.ClearContents method. The Range.ClearContents method:
  • Clears values and formulas from the applicable cell range.
  • (Generally) Results in a change to the applicable Range.Value property (the cell range's value).

This applies to object collections as well. The reason for this, as explained here, is that collections are themselves objects.

However, in order to ensure that you do a great job with the object methods you'll need to use, let's take a look at…

How To Work With Object Methods In VBA

Let's start to take a closer look at how you structure a statement that refers to (or calls/access) object methods. For these purposes, we'll examine the following macro (called “Delete_Inactive_Worksheets”). The purpose of this Sub procedure is to delete all worksheets except for the one that's currently active.

Sub Delete_Inactive_Worksheets()
    Dim my_Worksheet As Worksheet
    For Each my_Worksheet In ThisWorkbook.Worksheets
        If my_Worksheet.Name <> ThisWorkbook.ActiveSheet.Name Then
            Application.DisplayAlerts = False
            my_Worksheet.Delete
            Application.DisplayAlerts = True
        End If
    Next my_Worksheet
End Sub

This Excel VBA Methods Tutorial is accompanied by an Excel workbook containing the data and macros I use (including the macro above). You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.

The following screenshot shows how the VBA code of the Delete_Inactive_Worksheets macro looks like in the Visual Basic Editor:

I won't explain this particular macro line-by-line. For purposes of this Excel tutorial, we'll focus on the following statement:

my_Worksheet.Delete

This statement is within the If…Then statement in the macro above. The relevant row of code is underlined in the following screenshot:

This simple statement is enough to illustrate the basic rule to create references to object properties in Visual Basic for Applications:

You refer to (or call/access) a method by putting together the following 3 items:

• Item #1: Name of the relevant VBA object. In the case above, this is the object variable my_Worksheet.
• Item #2: A dot (.).
• Item #3: Name of the method. In the case above, this is “Delete” or, more precisely, the Worksheet.Delete method.

In fact, most procedures have a number of lines following this basic structure:

Object.Method

Under this (basic) statement structure, the item after the dot (Method in the case above) acts on/with the item before the dot (Object in the case above).

One final note before we move on to the next section:

Note how, above, I mention specifically the Worksheet.Delete method instead of simply referring to the Delete method. This is important because a method may behave differently depending on the object it works with.

For example, when it comes to the Delete method, its syntax and precise behavior can be slightly different depending on whether it is applied to a Worksheet object (Worksheet.Delete method) or a Range object (Range.Delete method). Yes, in both cases the end result is that Excel deletes the relevant object. However:

• When applied to a Range object, the Delete method has an extra argument (I explain method arguments below) which allows you to determine how Excel shifts the cells that replace any deleted cells.
• When working with a Worksheet object, the method displays (by default) a dialog box asking you to confirm the worksheet deletion.

As you continue to work with Excel and Visual Basic for Applications, these nuances will become clearer. However, you may want to confirm that your choice of VBA object methods actually carry out the actions you want them to when working with the VBA objects you're working with.

How To Work With The Arguments (Or Parameters) Of Object Methods In VBA

Arguments (also known as parameters) are, generally, what allows you to further determine the action that a method performs with an object.

In other words: Parameters allow you to determine “how” the action/method is carried out.

Going back to the parallel between VBA and the parts of speech of regular English, you can think of method arguments as being comparable to adverbs.

Some, but not all, object methods in VBA take arguments. Arguments can be mandatory or optional.

The topic of arguments is, actually, not as easy as you may think at first glance. In this section, we take a look at the most important aspects you must know in connection with this topic.

For purposes of illustrating how to work with the arguments of object methods in VBA, I make reference to the following simple macro (called “Copy_Main_Worksheet”). The Copy_Main_Worksheet macro (i) copies the worksheet called “Object Methods in VBA” and (ii) pastes the copy after the worksheet named “Sheet1”.

Sub Copy_Main_Worksheet()
    Worksheets("Object Methods In VBA").Copy After:=Worksheets("Sheet1")
End Sub

This Excel VBA Methods Tutorial is accompanied by an Excel workbook containing the data and macros I use (including the macro above). You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.

The following image shows the VBA code of the Copy_Main_Worksheet within the Visual Basic Editor:

Let's focus on the relevant statement of this macro, which is underlined in the image below:

You already know how to refer to object methods, the topic I explain above. Therefore, you probably identify the first part of the statement (“Worksheets(“Object Methods In VBA”).Copy”) as a reference that is divided in the following 3 items:

• Item #1: “Worksheets(“Object Methods In VBA”)” makes reference to the Worksheet object named “Object Methods In VBA”.
• Item #2: A dot (.) separates part #1 above from part #3 below.
• Item #3: “Copy” makes reference to the Worksheet.Copy method.

The second part of the statement (“After:=Worksheets(“Sheet1″)”) is the part that sets the relevant method parameters. More precisely, it determines that the copied worksheet is placed after the sheet called “Sheet1”.

The Worksheet.Copy method has 2 optional but exclusive (you can only specify one of them) parameters:

• Optional Parameter #1: “Before”, which determines which is the sheet in front of (or before) which the copied worksheet is placed.
• Optional Parameter #2: “After”, which sets what is the sheet after which the copied worksheet is placed.
  • This is the parameter I use in this example. In other words: The copied worksheet is placed after “Sheet1”.

The following image breaks down the statement in the parts and items I explain above:

This way of setting method parameters (which I explain below) isn't the only syntax you can use. More precisely:

There are a few ways in which you can work with the arguments of object methods. In order to understand this better, let's take a look at the most important ways in which you can specify the arguments for an object method in VBA:

Option #1: Basic Syntax To Specify Object Method Arguments

The Copy_Main_Worksheet macro that appears above doesn't use the most basic form of syntax to specify method arguments. Although…

After going through this section you'll probably:

• Think that the basic syntax isn't really that basic.
• Understand why I use another syntax (which I explain below) in the version of the Copy_Main_Worksheet macro that appears above.

The following version of the Copy_Main_Worksheet macro uses the basic syntax to set the arguments of an object method. Notice the difference between both versions when it comes to referring to the method parameters.

I explain why there's a comma (,) at the beginning of this part of the statement below. Excluding that comma, the basic syntax to specify arguments is relative straightforward:

Object.Method Argument_Value

In other words, to determine an argument for a VBA object method, you must:

• Step #1: Refer to the object method appropriately by following the basic syntax explained above.
  • In the case above, this is “Worksheets(“Object Methods In VBA”).Copy”.
• Step #2: Place a space ( ) after the name of the method.
• Step #3: Place the relevant argument values.
  • In the example above, this is “, Worksheets(“Sheet1″)”.

You already know what “Worksheets(“Sheet1″)” does: It's the After parameter that determines that the copied worksheet is placed after “Sheet1”.

That leaves the comma (,) at the beginning.

This comma (,) appears because of the following 3 syntax rules:

• When using more than 1 argument, use commas (,) to separate them.
• When working with unnamed arguments, enter arguments in their default order.
• When working with optional (and unnamed) arguments, use commas (,) to create blank placeholders for omitted arguments.
  • There are some cases where you don't need to create placeholders for omitted arguments (for ex., when the argument you omit is at the end of the argument list).

In this case, the Worksheet.Copy method has 2 optional but exclusive arguments: Before and After. The Copy_Main_Worksheet uses only the second argument (After). Therefore, the VBA code that appears above includes a comma (,) and a blank placeholder for the unused Before argument.

As you see, the “basic” syntax to specify the arguments of a method isn't that readable. And this was a single (and quite straightforward statement)…

Just imagine how it would be to work in a large and complex VBA application that uses this syntax and has plenty of methods with optional arguments and blank placeholders 😕 .

Fortunately, there is a second way in which you can set the parameters that clarify the action to be taken by a particular method. This is the syntax used in the original version of the Copy_Main_Worksheet macro above. Let's take a look at it:

Option #2: Named Arguments

As implied by the description of this way of referring to the arguments of VBA object properties, you use the actual name of the argument when specifying it. More precisely, you use the following syntax:

Object.Method Argument_Name:=Argument_Value

In other words, when using named arguments you proceed as follows:

• Step #1: Repeat the first 2 steps described above where I describe the basic syntax to specify the arguments of object methods.
  • In other words: (i) refer to the object method appropriately, and (ii) place a space after the name of the method.
• Step #2: Write the (official) name of the argument.
• Step #3: Place a colon and an equal sign (:=).
• Step #4: Set the relevant argument value.

This is the syntax that I use in the first version of the Copy_Main_Worksheet macro that appears above.

When you use named arguments for object methods in VBA, you don't have to use any placeholder for the optional arguments that you don't use. Therefore, in this case, there's no placeholder at all for the missing Before argument of the Worksheets.Copy method.

You don't need to use named arguments (is optional) and can get away with using the basic syntax described above. However, using named arguments has a few advantages over not using them, particularly the fact that named arguments improve the readability of your VBA code.

You can (theoretically):

• Start specifying arguments using the basic syntax described above; and
• Switch to named arguments at some point in the middle of the argument list. Once you include a named method argument, the remaining method arguments must (also) be named.

However, I don't necessarily recommend this syntax.

Even though you can switch from non-named arguments to named arguments in the middle of a statement, you can't do the opposite. In other words, you can't start specifying parameters using named arguments and then switch to the basic syntax. Once you use named arguments once, you must continue working with named parameters for that particular line.

After comparing both syntax options for setting VBA object method parameters, you'll probably agree that using named arguments is (as a general rule) a better practice.

Before we finish the topic of specifying arguments for object methods in VBA, let's take a look at 2 additional clarifications regarding the syntax you use when setting VBA method arguments:

Referring To The Arguments Of Methods That Return A Value And Working With Default Argument Values

When working with methods that return a value or create new objects, you must (generally) wrap the arguments in parentheses. In such cases, as long as the arguments are within the parentheses, you can continue to use either of the 2 syntax options that I describe above (unnamed or named arguments).

Finally, note that several objects within Visual Basic for Applications have a particular default method value. You don't necessarily have to provide a method value in such cases. This also applies to those methods that take more than 1 argument: some arguments may have default values while other don't.

How To Access The Methods Of A Particular Object

A single object can have more than 1 associated method. In fact:

• Some VBA objects have several dozen methods.
• The Excel VBA Object Model contains thousands of methods.

But don't feel overwhelmed!

In practice, you're likely to work with a relatively small amount of VBA object methods over and over again. This is partly due to the fact that a single method can be associated to several VBA objects.

In other words: You're unlikely to (in practice) work with certain methods.

However, it's quite likely that (from time to time) you'll need to search for a particular method while working with macros. Therefore, let's take a look at some of the most common strategies you can use to find out the best VBA object method to achieve your goals in a particular situation.

In addition to the 3 strategies I explain below, you can also use the macro recorder to get some guidance about the objects, properties and methods you need.

Strategy #1: How To Make The Visual Basic Editor Display A List Of Methods

This is probably the easiest and most convenient way to explore the properties available for a particular VBA object. However, it only works if the Auto List Members setting of your Visual Basic Editor is enabled. In case you don't have this setting enabled, I explain how to do it in this Excel tutorial.

In order to understand how you can make the VBE display a list of available methods, let's go back to the code for the Delete_Inactive_Worksheets. More particularly, let's assume that you're typing the statement “my_Worksheet.Delete” (which I explain above) and you're standing at the point where we must type the dot.

Once you type the dot, the VBE displays a list of items that can be associated with that particular object. Therefore, in the case above, once you type the dot that follows my_Worksheet, the following list is shown:

This gives you a great idea of the properties and methods that you can use when working with a particular object. For example, if you scroll down enough, you can find the Delete method. This is the appropriate method to complete the statement above.

In order to be able to take advantage of this setting, your Visual Basic Editor must have the Auto List Members setting option enabled.

Strategy #2: Use The Object Browser

As explained by Chandoo (in the webpage I link to above), the Object Browser is a “helpful screen” that allows you to browse and understand VBA objects, properties and methods.

You can use the Object Browser to obtain all the available properties and methods for a particular object in 3 simple steps:

Step #1: Open The Object Browser

There are a few ways to access the Object Browser from within the Visual Basic Editor:

• Option #1: Click on the Object Browser button that appears in the Standard toolbar of the VBE.
  
• Option #2: Click on “Object Browser” within the View menu.
  
• Option #3: Press the F2 key.

Step #2: Select The Excel Library

The drop-down list that appears on the top left corner of the Visual Basic Editor (“<All Libraries>”) displays all the object libraries that are available. Click on this drop-down menu and select Excel.

Step #3: Select The Relevant Object Class

The Object Browser is divided in 2 sections:

• Left section: Lists all the Excel objects.
• Right section: Displays all the properties and methods that are available for the object that is currently selected on the left.

Therefore, in order to access the VBA methods of a particular object, search the object you want to explore on the left side of the Object Browser and click on it.

For example, in the case of sample macros used throughout this Excel tutorial, it's interesting to see what happens when clicking on “Worksheet”. As you can see in the image below, the Object Browser displays a list of its members. This list includes both the Copy and Delete methods used by the Copy_Main_Worksheet and Delete_Inactive_Worksheets macro (respectively).

Strategy #3: Visit Microsoft's Official Documentation

Microsoft's Official Documentation has a massive amount of content regarding VBA objects and methods (among others).

One of the things you can use Microsoft's Official Documentation for, is to get a list of all the methods that are available for a particular VBA object. To do this, simply follow these 4 easy steps:

Step #1: Type The Name Of An Object In The Visual Basic Editor

As an example, let's go back to the Copy_Main_Worksheets used as an example above. Assume that you're typing the only statement of this Sub procedure. Therefore, you type the relevant object which, in this case, is “Worksheets(“Object Methods In VBA”)”.

However, now that you've identified the VBA object, you want to see which methods can be used on that particular object.

Step #2: Place The Cursor On The Object

In the case above you can, for example, place the cursor within the word “Worksheets”, as shown below:

Step #3: Press The F1 Key

F1 is the keyboard shortcut for opening the help system. In other words, this keyboard shortcut generally should either:

• Take you to the relevant page within Microsoft's Official Documentation; or
• Ask you to confirm what the topic you want to help on is. This is the case, generally, when the Visual Basic Editor finds multiple instances of the word that you've selected.

In this particular case, once I press the F1 key, I'm led to the page about the Application.Worksheets property within the Microsoft Dev Center.

Step #4: Navigate To The Methods Of The Relevant Object

In some cases (such as the example above), you may need to do some navigation before you actually get to the appropriate object. The reason for this is that, most objects within VBA are accessed by using a property.

For example in the case above, the Application.Worksheets property is used to return a Worksheet object. Therefore, we're originally taken to the webpage belonging to this particular property.

However, this navigation is usually not very complicated. In the case of the Application.Worksheets property, notice how Microsoft's Official Documentation immediately explains that the property returns a collection (which is an object itself) representing worksheets. It also includes a link leading to the Sheet object (see image below).

In turn, the page covering the Sheets object explains how this collection “can contain Chart or Worksheet objects”. The website, again, displays a conspicuous link that you can click to get to the Worksheets object.

In the case above, it took a little bit longer than usual to get to the appropriate page. However, once you're in the page that makes reference to the relevant object, you can get Microsoft's Official Documentation to show you all the available methods by expanding the list that appears on the left side of the page.

In the case above, one of those available methods is indeed the Worksheet.Copy method used by the Copy_Main_Worksheet sample macro.

Conclusion

I mentioned at the beginning of this blog post that having a good understanding about VBA objects, properties and methods is essential in order to do any meaningful work with Visual Basic for Applications. This Excel tutorial has provided you with the fundamental knowledge you need in order to start using methods when coding in VBA. Among other topics, we've covered:

• What are object methods in VBA, and why are they important.
• How do you create references for a method.
• How can you set the parameters or arguments of a particular method.
• 3 different strategies you can use to find out what are the properties you can use when working with a particular VBA object.