Please note javascript is required for full website functionality.
MVP

Blog

VBA Blog: 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.

Newsletter