VBA 테이블과 ListObjects
In this Article
이 튜토리얼에서는 VBA에서 테이블 및 ListObjects로 작업하는 방법을 보여드립니다.
VBA 테이블과 ListObjects
테이블은 Excel의 가장 유용하고 강력한 기능 중 하나이며, 이 튜토리얼에서는 VBA를 사용하여 테이블을 만들고, 테이블에 간단한 정렬을 추가하고, 테이블을 필터링하고, 기타 테이블 관련 작업을 수행하는 방법을 살펴 보겠습니다.
VBA로 테이블 만들기
ListObjects.Add 메서드는 워크시트의 범위를 기반으로 워크시트에 테이블을 추가할 수 있습니다. Sheet1 워크시트에 ($A$1:$B$8)에 표시된 범위가 있습니다.
다음 코드는 기본 테이블 스타일을 사용하여 ($A$1:$B$8) 범위를 기준으로 워크시트에 Table1 테이블을 추가합니다:
Sub CreateTableInExcel()
ActiveWorkbook.Sheets("Sheet1").ListObjects.Add(xlSrcRange, Range("$A$1:$B$8"), , xlYes).Name = _
"Table1"
End Sub결과는 다음과 같습니다:
VBA로 테이블 끝에 열 삽입하기
ListColumns.Add 메서드를 사용하여 테이블 끝에 열을 추가할 수 있습니다. 아래와 같이 Table1 테이블이 있습니다.
다음 코드를 사용하여 테이블에 열을 추가할 수 있으며, 항상 테이블 끝에 열이 추가됩니다:
Sub AddColumnToTheEndOfTheTable()
ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1").ListColumns.Add
End Sub결과는 다음과 같습니다:
VBA를 사용하여 테이블 하단에 행 삽입하기
ListRows.Add 메서드를 사용하여 테이블 하단에 행을 추가할 수 있습니다. 아래와 같이 Table1 테이블이 있습니다.
다음 코드는 항상 테이블 하단에 행을 추가합니다.
Sub AddRowToTheBottomOfTheTable()
ActiveSheet.ListObjects("Table1").ListRows.Add
End Sub결과는 다음과 같습니다:
VBA로 간단한 정렬 추가하기
VBA로 테이블을 정렬할 수 있습니다. 아래에 표시된 Table1 테이블은, VBA를 사용하여 Sales 열을 가장 낮은 순서에서 가장 높은 순서로 정렬할 수 있습니다.
다음 코드는 Sales 열을 오름차순으로 정렬합니다.
Sub SimpleSortOnTheTable()
Range("Table1[[#Headers],[Sales]]").Select
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Add _
Key:=Range("Table1[[#All],[Sales]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub결과는 다음과 같습니다:
VBA로 테이블 필터링하기
VBA를 사용하여 Excel 테이블을 필터링할 수도 있습니다. Table1 테이블이 있는데 1500보다 큰 매출만 표시되도록 테이블을 필터링할 경우 아래 예제를 참고해 주세요.
5개의 선택적 매개 변수가 있는 자동 필터 방법을 사용할 수 있습니다. 두 번째 열인 Sales 열을 필터링하고 싶으므로 필드를 2로 설정하고 날짜와 숫자에 사용되는 xlAnd 연산자 매개 변수를 사용합니다.
Sub SimpleFilter()
ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
">1500", Operator:=xlAnd
End Sub결과는 다음과 같습니다:
VBA의 ShowAllData 메서드로 필터 지우기
워크시트 클래스의 ShowAllData 메서드에 액세스하여 필터를 지울 수 있습니다. 테이블의 필터를 지우려는 경우 먼저 테이블에서 셀을 선택해야 하며, 이는 VBA에서 수행할 수 있습니다.
워크시트에 필터가 적용되었는지 확인하기 위해 조건부 논리를 사용하지 않으면 ShowAllData 메서드가 오류를 생성합니다. 다음 코드는 이를 수행하는 방법을 보여줍니다:
Sub ClearingTheFilter()
Range("Table1[[#Headers],[Sales]]").Select
If ActiveWorkbook.Worksheets("Sheet1").FilterMode = True Then
ActiveSheet.ShowAllData
End If
End SubExcel 테이블에서 모든 필터 지우기
테이블에서 셀을 먼저 선택하지 않고도 ListObject 클래스의 ShowAllData 메서드에 액세스할 수 있습니다. 다음 코드는 이를 수행하는 방법을 보여줍니다:
Sub ClearAllTableFilters()
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").AutoFilter.ShowAllData
End SubVBA로 행 삭제하기
ListRows.Delete 메서드를 사용하여 테이블의 데이터 본문에서 행을 삭제할 수 있습니다. 행 번호를 사용하여 어떤 행을 삭제할지 지정해야 합니다. 아래와 같이 Table1 테이블이 있습니다.
테이블의 데이터 본문에서 두 번째 행을 삭제하고 싶을 경우 다음 코드를 사용하면 삭제할 수 있습니다:
Sub DeleteARow()
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").ListRows(2).Delete
End Sub결과는 다음과 같습니다:
VBA로 열 삭제하기
ListColumns.Delete 메서드를 사용하여 테이블에서 열을 삭제할 수 있습니다. 아래와 같이 Table1 테이블이 있습니다:
첫 번째 열을 삭제하려면 다음 코드를 사용합니다:
Sub DeleteAColumn()
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").ListColumns(1).Delete
End Sub결과는 다음과 같습니다:
VBA에서 테이블을 다시 일반 셀로 변환하기
VBA를 사용하여 테이블을 일반 셀로 다시 변환할 수 있습니다. 다음 코드는 Table1 테이블을 셀로 다시 변환하는 방법을 보여줍니다:
Sub ConvertingATableBackToANormalRange()
ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1").Unlist
End SubVBA를 사용하여 워크시트의 모든 테이블에 줄무니 열 추가 및 서식 지정
ListObjects 컬렉션을 사용하여 워크시트의 모든 테이블에 액세스할 수 있습니다. 아래 시트에는 두 개의 테이블이 있으며 VBA를 사용하여 두 테이블에 한 번에 줄무니 열을 추가하고 두 테이블의 데이터 섹션 글꼴을 굵게 변경하고 싶다면 아래 예제를 참고해 주세요.
Sub AddingBandedColumns()
Dim tbl As ListObject
Dim sht As Worksheet
Set sht = ThisWorkbook.ActiveSheet
For Each tbl In sht.ListObjects
tbl.ShowTableStyleColumnStripes = True
tbl.DataBodyRange.Font.Bold = True
Next tbl
End Sub결과는 다음과 같습니다:
DoCmd.RunSQL을 사용하여 Access VBA에서 테이블 만들기
VBA에서 Access에서 테이블을 만드는 주요 방법 중 하나는 DoCmd.RunSQL 메서드를 사용하여 SQL 문으로 작업 쿼리를 실행하는 것입니다.
샘플 양식에 버튼이 있고 이 버튼을 클릭하면 두 개의 필드(또는 열)가 있는 ProductsTable 테이블을 만들고자 하는데, 하나는 ProductsID라는 기본 키 필드이고 다른 하나는 Sales라는 필드입니다.
이 테이블을 생성하기 위해 다음 코드를 사용합니다:
Private Sub cmdCreateProductsTable_Click()
DoCmd.RunSQL "CREATE TABLE ProductsTable " _
& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"
End Sub결과는 다음과 같습니다:
VBA를 사용하여 Access에서 테이블 필터링하기
DoCmd.ApplyFilter 메서드를 사용하여 Access에서 테이블을 필터링할 수도 있습니다. 아래에는 Access에 ProductsTable로 이름지어진 간단한 테이블이 있습니다.
폼에서 이 버튼을 누르면 1500보다 큰 매출만 표시하려고 합니다.
다음 코드를 사용하여 이를 수행할 수 있습니다:
Private Sub cmdFilter_Click()
DoCmd.OpenTable "ProductsTable"
DoCmd.ApplyFilter , "[Sales]>1500"
End Sub결과는 다음과 같습니다:
