Working With Object Properties In Excel Vba

When you first start learning Excel VBA, one of the first concepts you need to grasp is that in order to automate Excel, you have to refer to the various components and features which the program contains. In order to do this successfully, you need to know the name VBA name assigned to each element and the correct syntax to use in order to accomplish a certain task. Each component which forms part of Excel is an object with a specific name, usually a fairly obvious name, such as workbook, worksheet or range.

Excel offers a kind of dictionary to check the correct syntax to use when working with Excel objects; it's called the Object Browser. Simply choose View - Object Browser in the Visual Basic Editor. Choose "Excel" from the drop-down menu in the top left of the Object Browser window which is initially set to "All Libraries". The Object browser will then display a list of all the objects within Excel. Clicking on the name of an object will display the members relating to it; in other words, the syntax which can be used when working with that particular object.

The chief elements of syntax used to manipulate Excel objects are properties and methods. Properties are attributes which the object possesses while methods are actions which can be performed on the object. If we compare this syntax to English grammar, you could say that properties are like nouns and that methods resemble verbs.

Some object properties are read-only; you can check to see what they are but you cannot change them. For example, we could check the version of Excel being used with the syntax "Application.Version"; but we could not set the version. By contrast, we can both read and alter other properties. Thus we could verify the users preferred number of sheets in each new workbook with the code "Application.SheetsInNewWorkbook" and we can alter alter this number with the statement "Application.SheetsInNewWorkbook = 12", for example. Properties which can be modified in this way are referred to as read/write.

When setting object properties, it is important to provide a value of the correct data type. Thus, in the example above, an integer has to be supplied. Some Excel VBA objects also have properties which require an enumeration, one of a fixed number of set keywords beginning with "xl". For example, if you want to set the location of a chart, you would use one of the three built-in "xlChartLocation" enumerations: "xlLocationAsNewSheet", "xlLocationAsObject", or "xlAutomatic".