Excel VBA: The Ultimate Intermediate's Guide to Learn VBA Programming Step by Step by A. Williams David

Author:A. Williams, David [A. Williams, David]
Language: eng
Format: epub
Published: 2020-08-06T16:00:00+00:00

Properties and Methods

To access the functionality provided by objects, you work with properties and methods.

Properties

Properties are a set of characteristics of an object. For example, the Worksheet object has properties like Name and Visible.

Methods

Methods are behaviours that an object can exhibit. These are actions that you can carry out with an object. For example, the Worksheet object has methods like ShowAllData, Protect and Calculate.

Tip: To get a detailed list of the methods and properties of an Excel object. Use the format “object name properties” or “object name methods” to search for it in your search engine, where object name is the name of the object for which you want help. One of the top search results will be the official VBA help page for that object.

Setting Properties and Calling Methods

To change the characteristics or attributes of an object, you change its properties by assigning different values to them. Methods, on the other hand, cause an object to carry out an action or a task. Hence, you use methods to perform actions and use properties to set or retrieve attributes of an object.

Setting Properties

To set a property of an object in code, use the following syntax:

Object.Property = Value

The following example assigns True to the Bold property of the Font object of the selected range. This bolds the text in that range.

Selection.Font.Bold = True

To turn off bold you can set the property to False, like this.

Selection.Font.Bold = True

Calling Methods

The way you invoke methods in your code will depend on whether the method returns a value and if that value will be used in your procedure. You can think of methods as verbs. For example, you can open a door. So, if we were to translate that to VBA code, it would be:

Door.Open

Some methods return a value and others don’t. Also, some methods have parameters and others don’t.

Note: A parameter is a variable that is part of a method’s definition or syntax. When you call the method in your code, the values you pass into the parameters are called arguments. These terms are sometimes used interchangeably.

If you don't want to use the value returned by a method, or if the method doesn't return a value, then use the syntax below to call it. Note that the arguments are optional as not all methods have parameters.

Object.Method [arg1, arg2, …]

An example of a method that does not have a parameter or a return value is the Select method of the Range object:

Range("A1:A10").Select

The following statement runs the AutoFit method of the Columns collection to auto-fit the text in the selected columns.

Selection.Columns.AutoFit

If a method returns a value, then you call it by assigning the return value to a variable.

If the method has parameters, then you place parentheses around the arguments when you call it. You generally want to use parentheses any time the method appears to the right of the equal sign.

Use the following syntax to call a method when you want to assign the return value to a variable:

Variable = Object.Method([arg1, arg2, …])

You can also explicitly define a parameter by assigning an argument directly to its name when you call the method.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.