Working with the ListColumn Poperties of the List Object in Excel 2010
Working with Tables in Microsoft Office 2010: Learn how to work with the ListColumn object to add totals and formatting to a table in Microsoft Excel 2010.
Applies to: Microsoft Excel 2010
Published: June 2011
Provided by: Frank Rice, Microsoft Corporation
The ListObject object displays data in a series of rows and columns. Its ListColumn object represents a column in a ListObject object. In this topic, you programmatically work with various members of the ListColumn object to display totals and data formatting in a table in Microsoft Excel 2010. To complete this task, you must do the following:
Add the Code to the Visual Basic Editor
In this task, you add programming code to the Visual Basic Editor.
To add code to the Visual Basic Editor
Start Excel 2010.
On the Developer tab, click Visual Basic to open the Visual Basic Editor.
If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.
In the Projects pane, click Sheet1.
Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.
Sub ListColumnDemo() ' Step over this procedure. It's not terribly interesting. FillRandomData ' Set up the list object. Dim lo As ListObject Set lo = ListObjects.Add( _ SourceType:=xlSrcRange, _ Source:=Range("A1:F13"), _ XlListObjectHasHeaders:=xlYes) lo.Name = "SampleData" lo.ShowTotals = True ' Retrieve a reference to the second column in the ListObject: Dim lc As ListColumn Set lc = lo.ListColumns(2) ' Retrieve a reference to the ListColumn's data, excluding ' headers and footers. Dim rng As Range Set rng = lc.DataBodyRange ' Do some things with the data body range: rng.Borders.Color = vbRed rng.FormatConditions.AddDatabar ' Retrieve the total for the column and ' display it in the totals row. ' Obviously, there are other ways to do this: Dim totalValue As Long totalValue = WorksheetFunction.Sum(rng) ' You should verify that the Total property isn't ' Nothing--it will be, if the list object isn't ' displaying its totals row. In this case, the totals ' row is definitely visible, so this check is extraneous. If Not lc.Total Is Nothing Then lc.Total.Value = totalValue End If End Sub Sub FillRandomData() ' No need to stop through this procedure. Range("A1", "F1").Value = Array("Month", "North", "South", "East", "West", "Total") ' Fill in twelve rows with random data. Dim i As Integer Dim j As Integer For i = 1 To 12 Cells(i + 1, 1).Value = MonthName(i, True) For j = 2 To 5 Cells(i + 1, j) = Round(Rnd * 100) Next j Next i Range("F2", "F13").Formula = "=SUM(B2:E2)" End Sub
Test the Solution
In this task, you add a table to a worksheet and then step through the VBA code to see the effects on the table.
To step through the code
Drag the Visual Basic Editor window to the right side of your monitor.
Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.
Now, place the cursor in ListColumnDemo procedure, press F8 to get started debugging, and then press Shift+F8 to step through the code line-by-line (and bypass external procedures) and watch the code behavior.