멤버십 회원님들은 다음 동영상 강의와 함께 보시면 학습효과가 높아집니다(멤버십 전용 영상).
멤버십 전용
완성 예
[품목 업데이트] 버튼을 클릭하면 G4:H4 영역에 콤보 박스가 자동으로 만들어집니다. 콤보 박스에서 '품목'을 선택하면 그에 해당하는 데이터가 추출됩니다.
B열의 품목이 변경되었다면 [품목 업데이트] 버튼을 누르면 반영되며, 오름차순으로 표시됩니다. 콤보 박스에서 품목을 선택하면 그에 해당하는 자료와 요약표가 만들어집니다.
완성 예
상황을 단순화시켜기 위해서 '합계'만 표시했지만 필요에 따라 얼마든지 지표를 추가할 수 있습니다. 필요한 것은 VBA 역량과 문제 의식입니다.
Step 1: 콤보 박스에 품목 채우기
콤보 박스를 채우는 코드를 작성합니다. 이 때 B열에 있는 품목 중에서 중복되지 않게 고유 값만 추출하고 오름차순으로 채웁니다.
지금까지와는 달리, 프로시저를 작성하기 전에 변수를 선언합니다. 여러 프로시저에서 공통적으로 사용되는 변수는 모듈 시작 부분(Option Explicit 문 아래)에 선언합니다. 이런 변수를 '전역 public 변수'라고 합니다. 반면에 특정 프로시저 내부에서만 영향력을 가진 변수는 '로컬 local 변수'라고 부릅니다.
드롭다운 버튼에 '품목'을 오름차순으로 추가하기 위한 코드이므로 이름을 'fillDropDownByAscendingOrder'라고 지정했습니다. 조금 길지만 이름만 보고도 무슨 일을 하는 프로시저인지 알 수 있도록 하는 것이 좋습니다. 등장 인물과 배역을 지정하는(변수 선언, 값 할당) 것으로 코딩을 시작합니다.
시트에 이미 만들어진 콤보 박스가 있다면 지우고 새로 만듭니다.
고유 항목을 추출하여 컬렉션 개체 변수 oItems에 담습니다. 그런 다음, 각 항목을 오름차순으로 정렬하기 위한 준비를 합니다.
배열 변수 sItems에 들어 있는 항목을 오름차순으로 정렬합니다. For ~ Next 안에 또 For ~ Next가 들어 있는 중첩 구조를 사용했습니다.
이제 배열 변수 sItems에는 중복되지 않는 고유한 값이 담겨 있습니다. 이 값들을 콤보 박스에 추가합니다.
프로시저 내부에 커서를 두고 F5 키를 눌러 프로시저를 실행하면 B의 폼목 중에서 고유한 값들이 오름차순 정렬되어 콤보 박스에 표시됩니다.
Dim shtData As Worksheet
Dim rTbl As Range
Dim oDropDown As DropDownCODE 1
Sub fillDropDownByAscendingOrder()
Dim oItems As New Collection ''' 품목을 담을 컬렉션 개체
Dim rX As Range ''' 각 품목 셀에 접근하기 위한 순환 변수
Dim sItems() As String ''' 품목을 담을 동적 배열
Dim sX As String ''' 품목을 정렬할 때 사용
Dim i As Integer, j As Integer ''' 품목을 정렬할 때 사용
On Error Resume Next
Set shtData = Worksheets("연습")
Set rTbl = shtData.Range("A1").CurrentRegion
Set rTbl = rTbl.Offset(1).Resize(rTbl.Rows.Count - 1)CODE 2
VBA 강의를 하다 보면 다음 두 줄에서 헷갈려 하는 분들이 많습니다.
원하는 범위에 접근하기 위해 개체 변수를 재정의하는 과정입니다. 이유는? A1 셀 인접 영역에서 제목(1행)을 제외한 영역을 rTbl 변수에 담기 위해서입니다. 빈번하게 사용되는 패턴이며, 거의 공식처럼 사용됩니다.
shtData.DropDowns("myDropDown").Delete
With shtData.Range("G4") ''' G4셀을 기준으로 드롭다운 박스 삽입
Set oDropDown = shtData.DropDowns.Add(.Left, .Top, .Width * 2, .Height)
With oDropDown
.Name = "myDropDown"
.OnAction = "filterDropDownItem"
End With
End WithCODE 3
콤보 박스를 포함한 모든 도형은 '왼쪽/위쪽/너비/높이'를 지정하는 방식으로 삽입할 수 있습니다. 이에 해당하는 속성은 각각 Left, Top, Width, Height입니다.
드롭다운 개체의 OnAction 속성을 이용하면 항목을 선택했을 때 특정한 프로시저가 실행되도록 설정할 수 있습니다.
For Each rX In rTbl.Columns(2).Cells
oItems.Add rX, rX
Next
ReDim sItems(oItems.Count) ''' 동적 배열 변수의 크기 지정
For i = 1 To oItems.Count
sItems(i) = oItems(i)
NextCODE 4
컬렉션 개체 이용
RiDim 문은 동적 배열 변수의 크기를 재할당할 때 사용합니다. oItems 컬렉션 개체의 항목 수만큼 공간을 확보한 다음, 배열 변수 sItems에 하나씩 값을 전달합니다. 컬렉션 개체의 항목 그대로는 정렬할 수 없기 때문에 동적 배열 변수를 이용합니다.
For i = UBound(sItems) To LBound(sItems) Step -1
For j = 1 To i
If sItems(j) > sItems(j + 1) Then
sX = sItems(j)
sItems(j) = sItems(j + 1)
sItems(j + 1) = sX
End If
Next
NextCODE 5
바깥쪽 For ~ Next에서는 배열 변수의 가장 큰 요소(즉 마지막 값)부터 작은 요소로 접근한 점에 유의하세요. Bound 앞에 붙어 있는 U와 L은 각각 Upper, Lower를 뜻합니다.
뒤에 있는 값을 바로 앞에 있는 값과 비교해서 뒤의 값이 앞의 값보다 크다면 서로 위치를 바꿉니다. 이 과정을 모든 요소들에 대해 진행합니다.
For i = LBound(sItems) To UBound(sItems)
If Len(sItems(i)) <> 0 Then
oDropDown.AddItem sItems(i)
End If
Next
End SubCODE 6
배열은 0부터 시작합니다. 따라서 빈 값이 들어 있는 배열 요소를 콤보 박스에 표시하지 않도록 하기 위해 If 문으로 걸러주었습니다. If와 End If를 빼고 실행시켜 보면 차이를 아실 수 있습니다.
프로시저 실행 결과
코드를 자세히 설명하려고 과정을 나눠서 소개했더니 되려 어수선해 보이는 듯도 합니다. 코드에 넘버링을 해 두었으니 찬찬히 뜯어서 보시기 바랍니다(멤버십 회원님들은 예제 파일의 코드 참고). 앞으로 계속 반복해서 나오니까 지금 모르겠다고 걱정할 필요는 없습니다.
Step 2: 선택한 품목 요약표 만들기
콤보 박스에서 품목을 선택하면 그에 해당하는 요약표를 만드는 코드를 작성합니다. oDropDown 개체의 OnAction 속성에서 호출하는 프로시저이므로 이름이 같아야 합니다.
등장 인물과 배역을 설정합니다.
콤보 박스에서 선택된 값(항목)과 rRow(rTbl 영역의 각 행)의 2번째 셀 값이 일치하는 지 조건 비교해서 같은 경우 처리를 합니다.
가독성을 높이기 위해 요약표의 제목과 서식을 지정합니다.
요약표 맨 아래쪽에 '합계'를 표시하고 서식을 지정합니다. 여기서는 두 가지에 유의하시면 됩니다.
Sub filterDropDownItem()
Dim rWrite As Range ''' 결과를 출력할 위치
Dim rRow As Range ''' rTbl 영역의 각 행에 접근
Dim sItem As String ''' 콤보 박스에서 선택한 품목 저장
Dim iRow As Integer
On Error Resume Next
Set shtData = Worksheets("연습")
Set rTbl = shtData.Range("A1").CurrentRegion
Set rTbl = rTbl.Offset(1).Resize(rTbl.Rows.Count - 1)
Set oDropDown = shtData.DropDowns("myDropDown")
Set rWrite = oDropDown.TopLeftCell.Offset(3)
rWrite.CurrentRegion.Clear ''' 결과를 표시할 영역 청소CODE 1
콤보 박스가 있는 위치를 기준으로 출력 위치를 결정하고자 합니다. 그러려면 당연히 콤보 박스가 어디 있는 지 알아야겠죠? 이럴 때 사용할 수 있는 것이 TopLeftCell 속성입니다. 말 그대로, 지정한 개체의 윗쪽/왼쪽 위치를 알 수 있습니다. 현재 콤보 박스가 G4:H4 영역에 있으므로 TopLeftCell은 G4 셀이 됩니다.
sItem = oDropDown.List(oDropDown.ListIndex)
rWrite.Offset(-1).Resize(, 4) = Array("분류", "품목", "날짜", "실적")
For Each rRow In rTbl.Rows
If rRow.Cells(2) = sItem Then
rRow.Copy rWrite.Offset(iRow)
iRow = iRow + 1
End If
NextCODE 2
콤보 박스에서 어떤 값이 선택되었는 지 확인해서 변수에 저장할 때, ListIndex 속성으로 위치값을 파악한 다음, List 속성으로 그 값을 가져온다는 점을 눈여겨 보시기 바랍니다.
''' 요약표의 타이틀 작성
With rWrite.Offset(-5)
.Value = sItem & " 품목 집계표"
.Font.Bold = True
.Font.ColorIndex = 3
End With
''' 요약표 각종 서식 지정
With rWrite.CurrentRegion
.Borders.LineStyle = xlSolid
.Rows(1).HorizontalAlignment = xlCenter
.Rows(1).Interior.ColorIndex = 15
.Columns(3).ShrinkToFit = True
.Columns(4).EntireColumn.NumberFormat = "#,###"
End WithCODE 3
직설적(?)인 내용이라 단어만 잘 해석해도 무슨 기능을 하는 건지 알 수 있을 겁니다. 표 내부에 있는 Range 개체에 어떻게 접근하는 지를 봐두시면 되겠습니다.
With rWrite.Offset(rWrite.CurrentRegion.Rows.Count - 1, 2).Resize(, 2)
.Value = Array("합계", Application.SumIf(rTbl.Columns(2), sItem, rTbl.Columns(4)))
.Borders.LineStyle = xlSolid
.Borders.Weight = xlThick
.Interior.ColorIndex = 6
.Font.Bold = True
End With
End SubCODE 4
서식을 지정하려면 대상 영역(Range 개체)에 접근하는 것이 먼저입니다. 표의 맨 마지막에 어떻게 접근하려면 어떻게 해야 할까요? rWrite 인접 영역의 행 수를 센 다음, 오른쪽으로 2열 만큼 이동합니다(I열부터 표시하기 위해). Offset과 Resize 속성에 대해 잘 익혀 두면 수많은 변주가 가능합니다.
다른 계산 작업이 필요할 경우, 워크시트 함수를 이용하면 편리합니다. 여기서는 집계표 마지막에 품목의 합계를 표시했고, SUMIF 함수를 사용했습니다.
함수 앞에 Application이나 WorksheetFunction 중 하나(또는 둘 다)를 붙여주면 되며, 사용 방법은 워크시트에서 사용할 때와 같습니다. '엑셀에 대해 많이 알수록 VBA에 더 쉽게 다가갈 수 있다'고 Exceller가 노래를 부르다시피 하는 건 그래서입니다.
비교적 간단한 로직이지만 이 정도만 하더라도 함수나 엑셀 기본 기능으로는 처리하기 힘듭니다. 아, Filter 함수를 이용하여 간단한 요약표 만드는 것은 서두에 소개해 드린 영상에 있으니 참고하세요. 멤버십 회원이 아닌 분들은 Filter 함수(Microsoft 365용 함수)를 검색해 보시면 되겠습니다.