VBA Blogs: Total Eclipse of the Heart
5 January 2018
The seventh in a series about using ListObjects to manipulate Tables within an Excel workbook in VBA featuring the Totals Row.
“It’s a Heartache” when a table doesn’t have a totals row. Let’s look at the following table:
“Loving You’s a Dirty Job” but displaying the Totals Row of a table is not. The Totals Row is easily found in the Table Menu here (or Ctrl + Shift + T for you keyboard shortcut enthusiasts):
However, how can we do this in VBA? It is simply the ShowTotals property of the ListObject. This is a Boolean setting, if it is TRUE then the Total Row is displayed (and switch it off by setting it to FALSE).
“Faster Than the Speed of Night” the Totals Row appears
Notice how that it has put a formula in the last column which is the default setting of showing the totals row.
=SUBTOTAL(103,[Album Length])
“The Reason Why?” Excel makes a rough judgement about which of the SUBTOTAL functions it would like to use and in this case has chosen 103 – COUNT. “It’s a Heartache” sometimes because it doesn’t always use the right one.
To edit the Totals Row, one could very easily edit it by using the TotalsRowRange property of ListObject. Let’s delete the word “Total” in the row.
And it comes out like this as expected:
But “I’m A Fool” if this is all one wanted to do with a table. One would want to populate the Totals Row with calculations. This is done using the ListColumns method of ListObject. Though ListColumns hasn’t been covered in detail previous articles, let’s ”Take A Chance” and muddle through the syntax. Columns in a table can be referred to by the ListColumns property by using the index or by the header. Then we use the TotalsCalculation method to change the calculation in the row. The following calculations may be used:
| Subtotal Number | Excel Function | Function | VBA Syntax |
|---|---|---|---|
| 101 | AVERAGE | Average | xlTotalsCalculationAverage |
| 102 | COUNTA | Count Numbers | xlTotalsCalculationCountNums |
| 103 | COUNT | Count | xlTotalsCalculationCount |
| 104 | MAX | Max | xlTotalsCalculationMax |
| 105 | MIN | Min | xlTotalsCalculationMin |
| 106 | PRODUCT | Product | |
| 107 | STDEV.S/STDEV | Standard Deviation Sample | xlTotalsCalculationStdDev |
| 108 | STDEV.P | Standard Deviation Population | |
| 109 | SUM | Sum | xlTotalsCalculationSum |
| 110 | VAR | Variance | xlTotalsCalculationVar |
With enough info, “My Guns Are Loaded” so let’s give it a go.
Notice that SUBTOTAL functions 106 and 108 are not available in the VBA Syntax. “Save Up All Your Tears”, presumably this is because these are not often used. However, there are two further TotalsCalculations that are available: xlTotalsCalculationNone which is identical to clearing the cell and xlTotalsCalculationCustom which doesn’t do much at all.
xlTotalsCalculationCustom just puts a =0 in the formula which is not very helpful.
But what if for example one wanted to calculate the average song length? Let’s use an Array Formula using the AVERAGE function as follows:
{=AVERAGE(Table_BTDisco[Album Length]/Table_BTDisco[Number Of Songs])}
So how could this be achieved?
The TotalsRowRange could be used as above, but ListColumns also has a method Total which allows access to the Totals Range for that particular column. Let’s use ArrayFormula to put the formula in the [Album Length] column and change the number format to show minutes and seconds.
“Bye Bye Now My Sweet Love”, next week will be a closer examination of ListColumns.
