VBA Methods

A method is an action to perform on a VBA object

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 to that object. Copying, pasting and selecting are just some examples of VBA methods that can be performed.

Not all 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.

 

Difference Between an Object Reference and a Method

The object reference tells Excel where to look for the data.  The 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 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!