Understanding Object Methods In Excel VBA

Automating Microsoft Excel with Visual Basic for Applications (VBA) involves the programmatic manipulation of 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 methods are actions associated with the object. For example, a workbook has a “name” property and an “open” method.

Methods are a little more sophisticated than properties in that they normally, though not always, require amplification in order to get a particular object to perform a particular action. This amplification 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 supply any arguments.

Compare this with the situation where we want to open a workbook; here, 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.

Parameters 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 lists all of the parameters, 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:reportsmain.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:reportsmain.xlsx”, ReadOnly:=True)”.

To learn more about Excel VBA training courses, visit Macresource Computer Training, an independent computer training company offering Excel VBA training courses at their central London training centre.

Support Us:

Microsoft Office 2008 for Mac Home & Student Edition

Norton Internet Security 2010 1 User/3 PC [DOWNLOAD]

Microsoft Office Home and Student 2007

Related posts:

1. Working With Object Properties In Excel VBA
2. Understanding The Anatomy Of A Microsoft Excel Chart
3. Getting A Handle On Excel 2007 Column Charts
4. UnderstandingColumn Charts in Excel 2007
5. Getting The Most From Excel’s Paste Special Command
6. Understanding The Properties And Events Of ASP.NET Server Control
7. Setting Font Attributes In MS Word
8. Guarding Against Computer Spying Software
9. Fixing Your Xbox 360
10. Microsoft Dynamics Software Solutions For World Wide Businesses