VBA Methods

A method is an action to perform on a VBA object

Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.

What is a VBA Method?

A VBA method is a piece of code attached to a VBA object, variable, or data reference that tells Excel what action to perform in relation to that object. Copying, pasting, and selecting are just some examples of VBA methods that can be performed. (VBA stands for “Visual Basic for Applications”)

Not all VBA methods can be used with all types of references. For example, the “Value” method tells Excel to store a value in a relevant cell, or an array of values in a relevant range. If you stored a range of values in a variable, you cannot use the value method to attach that array to a single cell.

Object Reference vs. VBA Method

The object reference tells Excel where to look for the data. A method tells Excel what to do with the data.

VBA Methods

VBA Methods for Financial Modeling

Not all methods need to be used when it comes to writing VBA code for financial modeling. In fact, strong and efficient macros can be designed even with just the simplest and most basic of the available methods.

Methods are attached after objects and with a period symbol. Some VBA methods also need additional information (known as parameters) after the method itself.

vba method example

For example, when using the .Copy method, simply attaching .Copy to an object will tell Excel to copy but not paste the data. However, the .Copy method has the ability to directly paste the data in a new location if the user tells Excel where to do so. This is done by following the .Copy method with the Destination parameters.

Range(“A2:A3”).Copy _

    destination:= Range(“B2”)

Performing this action will copy the contents of A2:A3 to B2. Doing so will populate B2:B3.

The syntax above using the _ symbol is a way to separate parameters from the method for readability. However, that is optional and the parameter can be placed on the same line as the method.

Learn More!

Thank you for reading CFI’s guide to VBA methods. To learn more, see the following free CFI resources:

0 search results for ‘