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.

However, if 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.

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 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)”.

Looking to master Microsoft Excel 2007 VBA? We offer Microsoft Excel 2007 VBA courses in London and all over the UK.

Check out helpful recommendations in the sphere of Free Traffic System – make sure to go through this publication. The times have come when concise info is really at your fingertips, use this chance.