Utilizing Excel Object Methods In VBA Programming

When writing code in Microsoft Excel Visual Basic for Applications (VBA), you need to programmatically manipulate the objects inherent in Excel, such as worksheets, charts and pivot tables. In VBA, each of these objects has properties and methods which can be used to manipulate the object and make it do what you need it to. Properties are the attributes or characteristics that the object possesses, whereas Excel Object Methods are actions associated with the object. For example, a workbook has a “name” property and an “open” method.

Excel Object Methods

Excel Object Methods¬†are a little more sophisticated than properties in that they normally, though not always, require expansion in order to get a particular object to perform a particular action. This expansion¬†is provided through the arguments which the programmer has to supply when using the method. Thus, in order to exit Excel altogether, we would use the “quit” method of the “Application” object, thus: “Application.Quit” without supplying any arguments.

Parameters

If, by contrast, we want to open a workbook, we need to supply at least one parameter: a string specifying the workbook to be opened. As you write your code, Excel will furnish a useful prompt: having entered the method, if you type an opening parenthesis, a “QuickInfo” tool tip will appear with a list of the parameters required by the method. This facility is much the same as the display of parameters when you enter a function in Excel.

Arguments

Any arguments which are displayed in square brackets are optional while those not in square brackets are obligatory. Thus, for example, when using the “Open” method of the “Workbook” object, the “Filename” parameter is obligatory while the “ReadOnly” parameter is optional. The “QuickInfo” tool tip supplies a list of every parameter, separated by commas, which the method can accept in the order that they must be supplied. If you do not wish to supply a given (optional) parameter, then you must still insert a comma to mark the position of the omitted argument.

Thus, for example, if you wanted to use the “Open” method of the “Workbook” object and supply the “Filename” and the “ReadOnly” parameters, you would type “Workbooks.Open(“c:\reports\main.xlsx”,, True)”. Since the “ReadOnly” parameter is the third, a comma is inserted to mark the position of the missing second parameter. VBA offers a very useful alternative method of entering arguments. You can enter the name of each parameter followed by “:=”. Using this technique, the order of parameters becomes unimportant and no reference has to be made to omitted parameters. Thus, in the example above, we could type “Workbooks.Open(FilePath:=”c:\reports\main.xlsx”, ReadOnly:=True)”.

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.