Inspecting a ListObject : ListObject « File Path « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. File Path
3. ListObject

Inspecting a ListObject

 
' 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 

 

Related examples in the same category