Dot on i

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 methods are actions associated with the object. For example, a workbook has a “name” property and an “open” method.

Methods are a little more involved 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.

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.

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:\reports\main.xlsx”, , True)”. Since the “ReadOnly” parameter is the third, a comma is inserted to mark the position of the missing second parameter.

There is another, very handy, technique for entering arguments is to 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)”.

For more information on Excel VBA training courses, visit Macresource Computer Training, a UK IT training company offering Excel VBA training courses in London and throughout the UK.

In this tutorial will look at the creation and customisation of a column chart. The first step is to select the data that we want to plot, taking care to include any column and row headings. Row headings will be used as the names of the chart series and will be displayed in the legend. Column headings will be used as category labels. If the selection includes two sets of column headings, Excel will automatically recognize this and create two sets of headings on the category axis for us.

The next step is to click on the Insert ribbon tab and from the Column drop-down menu choose the option that we require. The very first option is the omnipresent scattered column chart. Excel creates our chart and places it in the worksheet as an embedded chart. If we want to change it to a standalone chart, click on Move Chart in the Location section and then choose New Sheet and enter a name for the new chart sheet.

Having chosen a chart, you can of course customise it to suit your requirements. To change the colour of the columns, simply click once on any member of a series to highlight the whole series and then choose a colour from the Shape Fill drop-down menu in the Format contextual tab. As well as Shape Fill, the drop-down also offers Shape Outline and Fill Effects. The Fill Effects includes preset effects such as shadow, glow and bevel.

There are a number of subtypes available within the column chart type. The most basic and probably the most widely used is the clustered column chart type. To change the chart type, go to the Design contextual tab and click on Change Chart Type.

In the Stacked Column type, the overall total of all series within each category takes precedence over the individual value associated with each series. The second type of stacked column is 100% Stacked Column. Here, the height of each column becomes 100% and so all columns have exactly the same height. This type of chart shifts the emphasis away from the number or quantity represented by each series to the percentage split between series.

Excel also offers us 3-D versions of these three different column chart types. However, these are not strictly 3-D charts; they simply have a 3-D effect on the columns. It is the 3-D Column option which gives us an actual three-dimensional chart. Here, Excel adds depth to the chart and places the series along the z axis (the third dimension). The remaining column options are simply variations on these basic themes. For example, if we want to go for a 3-D chart, we might choose to have pyramids instead of rectangular blocks.

You can find out more about Excel and Excel VBA training courses, visit On Site Training Courses . Com, a UK IT training web site offering Excel VBA Classes all over the UK.

If you are going to successfully 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.

Many properties of an object will be 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”.

If you want more information Excel VBA training courses, visit Macresource Computer Training, a UK IT training company offering Excel VBA Classes at their central London training centre.

In this tutorial will discuss how to create and customise a column chart in Microsoft Excel 2007. The first step is to select the data that we want to plot, taking care to include any column and row headings. Row headings will be used as the names of the chart series and will be displayed in the legend. Column headings will be used as category labels. If the selection includes two sets of column headings, Excel will automatically recognize this and create two sets of headings on the category axis for us.

The next step is to click the Insert ribbon tab and from the Column drop-down menu specify the option that we require. The very first option is the default clustered column. Excel creates our chart and places it in the worksheet as an embedded chart. If we want to change it to a standalone chart, click on Move Chart in the Location section and then choose New Sheet and enter a name for the new chart sheet.

Having chosen a chart, you can of course customise it to suit your requirements. To change the colour of the columns, simply click once on any member of a series to highlight the whole series and then choose a colour from the Shape Fill drop-down menu in the Format contextual tab. As well as Shape Fill, the drop-down also offers Shape Outline and Fill Effects. The Fill Effects includes preset effects such as shadow, glow and bevel.

There are quite a few subtypes available within the column chart type. The most basic and probably the most widely used is the clustered column chart type. To change the chart type, go to the Design contextual tab and click on Change Chart Type.

In the Stacked Column chart type, it is the overall total of all series within each category which takes precedence over the individual value of each series. The second type of stacked column is 100% Stacked Column. Here, the height of each column becomes 100% and so all columns have exactly the same height. This type of chart shifts the emphasis away from the number or quantity represented by each series to the percentage split between series.

Excel also offers 3-D versions of its three different column chart types. You will notice that these chart tpes are not strictly 3-D charts; they simply have a 3-D effect on the columns. It is the 3-D Column option which gives us an actual three-dimensional chart. Here, Excel adds depth to the chart and places the series along the z axis (the third dimension). The remaining column options are simply variations on these basic themes. For example, if we want to go for a 3-D chart, we might choose to have pyramids instead of rectangular blocks.

The The writer of this article is a trainer and developer with Macresource Computer Training, an independent computer training company offering Microsoft Excel 2007 training courses at their central London training centre.