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 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.
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.
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.
Thank you for reading CFI’s guide to VBA methods. To learn more, see the following free CFI resources: