|
' Example using various list properties
Sub ListInfo()
Dim myWorksheet As Worksheet
Dim lo As ListObject
Dim lc As ListColumn
Set myWorksheet = ThisWorkbook.Worksheets("ListObjects")
Set lo = myWorksheet.ListObjects(1)
For Each lc In lo.ListColumns
Debug.Print lc.Name
Debug.Print lc.Index
Debug.Print lc.Range.Address
Debug.Print GetTotalsCalculation(lc.TotalsCalculation)
Next
Debug.Print lo.HeaderRowRange.Address
Debug.Print lo.DataBodyRange.Address
If Not lo.InsertRowRange Is Nothing Then
Debug.Print lo.InsertRowRange.Address
Else
Debug.Print "N/A"
End If
If lo.ShowTotals Then
Debug.Print lo.TotalsRowRange.Address
Else
Debug.Print "N/A"
End If
Debug.Print lo.Range.Address
Debug.Print lo.ShowTotals
Debug.Print lo.ShowAutoFilter
Set lc = Nothing
Set lo = Nothing
Set myWorksheet = Nothing
End Sub
Function GetTotalsCalculation(xlCalc As XlTotalsCalculation) As String
Select Case xlCalc
Case Is = XlTotalsCalculation.xlTotalsCalculationAverage
GetTotalsCalculation = "Average"
Case Is = XlTotalsCalculation.xlTotalsCalculationCount
GetTotalsCalculation = "Count"
Case Is = XlTotalsCalculation.xlTotalsCalculationCountNums
GetTotalsCalculation = "CountNums"
Case Is = XlTotalsCalculation.xlTotalsCalculationMax
GetTotalsCalculation = "Max"
Case Is = XlTotalsCalculation.xlTotalsCalculationMin
GetTotalsCalculation = "Min"
Case Is = XlTotalsCalculation.xlTotalsCalculationNone
GetTotalsCalculation = "None"
Case Is = XlTotalsCalculation.xlTotalsCalculationStdDev
GetTotalsCalculation = "StdDev"
Case Is = XlTotalsCalculation.xlTotalsCalculationSum
GetTotalsCalculation = "Sum"
Case Is = XlTotalsCalculation.xlTotalsCalculationVar
GetTotalsCalculation = "Var"
Case Else
GetTotalsCalculation = "Unknown"
End Select
End Function
|