# 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*.