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:
- Object properties.
- Object 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. In fact, in Excel VBA Programming for Dummies, Excel guru John Walkenbach states that:
After all, Excel programming really boils down to manipulating the objects that make up Excel.
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.
I've always liked the analogy between Excel VBA and English parts of speech made in books such as Excel 2013 In Depth and Excel 2013 VBA and Macros. According to this classification, 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.
One example of such case, mentioned by Walkenbach in Excel VBA Programming for Dummies, is the ClearContents method which, when applied to a Range object, changes the Value property of that Range.
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.
Dim my_Worksheet As Worksheet
For Each my_Worksheet In ThisWorkbook.Worksheets
If my_Worksheet.Name <> ThisWorkbook.ActiveSheet.Name Then
Application.DisplayAlerts = False
Application.DisplayAlerts = True
The basic structure of this VBA code is suggested by Excel expert Michael Alexander (in both 101 Ready-To-Use Excel Macros and Excel Macros for Dummies).
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. You can find a complete description here.
For purposes of this Excel tutorial, we'll focus on the following statement:
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, as explained by Excel expert Bill Jelen and Tracy Syrstad in Excel 2013 VBA and Macros, this is the basic structure of Visual Basic for Applications:
(…) a bunch of lines of code in which you have.
As further explained in Excel 2013 VBA and Macros, within Visual Basic for Applications, the usual consequence of this statement structure is that the item after the dot (Method in the case above) performs on the item before the dot (in the statement above, this is Method).
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, as explained in Mastering VBA for Microsoft Office 2013, a particular method may behave differently depending on the object it's paired 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.
As explained in Excel 2013 VBA and Macros, these parameters allow you to determine “how” the action 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. For example, in Excel 2013 Power Programming with VBA, John Walkenbach explains that:
An issue that often leads to confusion among new VBA programmers concerns arguments for methods and properties.
Therefore, in this section, we take a look at the most important 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”.
Worksheets(“Object Methods In VBA”).Copy After:=Worksheets(“Sheet1”)
The basic structure of this Sub procedure is suggested at the Microsoft Dev Center.
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”.
As explained at the Microsoft Dev Center, 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:
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.
The reason why this comma (,) appears is the following syntax rule:
In case you're using more than 1 argument, you use commas (,) to separate them.
In Excel 2013 Power Programming with VBA, John Walkenbach explains a second rule that's important for purposes of understanding what's going on above:
When you're working with a method that has optional arguments, you can use blank placeholders for the optional arguments you aren't using.
And here is where things get complicated. Bill Jelen and Tracy Syrstad explain (in Excel 2013 VBA and Macros) that the arguments for every object method within VBA need to be listed in the appropriate order.
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:
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 (:=).
As explained in Excel 2013 VBA and Macros, whenever you find this combination (:=) within a piece of VBA code, you're likely looking at a method argument.
- 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.
As explained in Excel 2013 VBA and Macros, you can actually start specifying arguments using the basic syntax described above and switch to named arguments “when you hit one that doesn't match the default order”. However, I don't necessarily recommend this syntax (nor do the authors of the cited book).
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 with the following statement by John Walkenbach in Excel 2013 Power Programming with VBA:
Using named arguments is a good idea, especially for methods that have many optional arguments and also when you need to use only a few of them.
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
In Excel 2013 Power Programming with VBA, Walkenbach explains that whenever you're working with methods that return a value you must place the arguments within 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 (basic 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
As explained by Richard Mansfield in Mastering VBA for Microsoft Office 2013, a single object can have more than 1 associated method. In fact, according to Mansfield, some have dozens of methods. In Excel VBA Programming for Dummies, John Walkenbach talks about “thousands of properties and 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 the words of Walkenbach:
You'll never need to use most of the available properties and 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.
According to Excel blogger Chandoo:
Dot (.) is your best friend when dealing with objects.
As you see above, this is quite true when it comes to the topic of object methods in VBA.
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 The Microsoft Developer Network
The Microsoft Dev Center has a massive amount of content regarding VBA objects and methods (among others).
One of the things you can use the Microsoft Developer Network 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 the Microsoft Developer website, 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 the Microsoft Developer Network 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 the Microsoft Developer Network 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.
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.
Books Referenced In This Excel Tutorial
- Alexander, Michael (2012). 101 Ready-To-Use Excel Macros. Hoboken, NJ: John Wiley & Sons Inc.
- Alexander, Michael (2015). Excel Macros for Dummies. Hoboken, NJ: John Wiley & Sons Inc.
- Jelen, Bill (2013). Excel 2013 in Depth. United States of America: Que Publishing.
- Jelen, Bill and Syrstad, Tracy (2013). Excel 2013 VBA and Macros. United States of America: Pearson Education, Inc.
- Mansfield, Richard (2013). Mastering VBA for Microsoft Office 2013. Indianapolis, IN: John Wiley & Sons Inc.
- Walkenbach, John (2013). Excel VBA Programming for Dummies. Hoboken, NJ: John Wiley & Sons Inc.
- Walkenbach, John (2013). Excel 2013 Power Programming with VBA. Hoboken, NJ: John Wiley & Sons Inc.