아이엑셀러 닷컴

완성 예

새해 첫 번째 강의입니다(두둥~~). 만사형통 하시고 부귀영화가 함께 하는 2023년이 되길 기원합니다. ㅎㅎ

얼마 전 'VBA 기초 강의' 시간에 이벤트의 개념과 종류, 작성 방법 등에 대해 살펴보았습니다. 생소하거나 가물가물한 분들은 아래 강의를 참고하시기 바랍니다.

이벤트를 사용하면 여러 가지 유용한 것들을 만들 수 있습니다. 선택한 범위에 색칠하는 것을 넘어서 실무에 적용 가능한 것을 하나 만들어 보도록 하죠. 날짜별 품목별 실적 데이터가 있습니다. B열에 있는 품목을 선택하면 그 품목의 요약 실적 테이블이 자동으로 만들어 지는 코드를 작성해 보겠습니다.

완성 예

이벤트 프로시저 만들기

첫 번째 단계는 어떤 이벤트를 사용할 것인지 결정하고 그에 맞는 이벤트 프로시저를 생성하는 겁니다. 특정 셀(여기서는 '품목')을 선택하면 어떤 작업이 실행되도록 하는 것이므로 Worksheet 개체의 SelectionChange 이벤트가 적합하겠습니다.

워크시트에서 ALT + F11 키를 눌러서 VB Editor ^{Visual Basic Editor}를 열고 접근하는 방법도 있습니다만 오늘은 다르게 해 보도록 하죠. '연습' 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 [코드 보기] 메뉴를 선택합니다.

'개체' 드롭다운 버튼을 클릭하고 'Worksheet'를 선택하면 Worksheet_Selection 이벤트 프로시저가 자동으로 생성됩니다. 만약 그렇지 않다면 '프로시저' 드롭다운 버튼을 클릭하고 'SelectionChange'를 선택합니다. 이곳에 코드를 작성하면 되겠습니다.

Worksheet_SelectionChange 이벤트 생성

코드와 해설

SelectionChange 프로시저는 하나의 인수(Target)를 넘겨 받아서 실행합니다. Target은 선택된 셀이나 셀 범위를 뜻합니다.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ''' 변수 선언
    Dim rTbl As Range             ''' 실적 테이블에 접근할 변수
    Dim rTblItem As Range         ''' 실적 테이블 중 품목에 접근할 변수
    Dim rSumTbl As Range          ''' 요약 테이블에 접근할 변수
    
    ''' 오류 처리 및 변수에 값 할당
    On Error Resume Next                      ''' 에러가 발생해도 계속 진행
    Set rTbl = Me.Range("A1").CurrentRegion   ''' 현재 워크시트의 A1셀 인접 영역을 rTbl 변수에 할당
    Set rTblItem = rTbl.Columns(2)            ''' rTbl 영역의 두 번째 열을 rTblItem 변수에 할당
    Set rTblItem = rTblItem.Offset(1).Resize(rTblItem.Rows.Count - 1)   ''' 제목을 제외한 영역에 접근
    If Target = "" Then Exit Sub              ''' 빈 셀이 선택되었다면 실행 종료
    If Target.Cells.Count > 1 Then Set Target = Target.Cells(1)         ''' 여러 셀이 선택되었다면 첫 번째 셀을 Target으로 설정
    If Intersect(Target, rTblItem) Is Nothing Then Exit Sub             ''' B열이 아닌 열이 선택되었다면 실행 종료
    Set rSumTbl = Target.Offset(, rTbl.Columns.Count).Resize(, 4)       ''' 요약 테이블을 표시할 위치와 크기 지정
    rSumTbl.EntireColumn.Clear                ''' 요약 테이블을 표시할 자리에 데이터가 있다면 지우기
    
    ''' 엑셀 기본 함수들을 이용하여 선택된 항목의 금액/횟수/평균을 구한 다음 지정한 위치에 표시
    ''' Array 함수를 이용하여 각 결과값을 한꺼번에 화면에 표시
    With rSumTbl
        .Cells(1).Offset(-1) = "[" & Target & "] 실적 요약"
        .Value = Array("총금액", "총횟수", "평균수량", "평균금액")
        .Offset(1) = Array(Application.SumIf(rTblItem, Target, rTblItem.Offset(, 2)), _       ''' 총금액
                        Application.CountIf(rTblItem, Target), _                              ''' 총횟수
                        Application.AverageIf(rTblItem, Target, rTblItem.Offset(, 1)), _      ''' 평균수량
                        Application.AverageIf(rTblItem, Target, rTblItem.Offset(, 2)))        ''' 평균금액

        ''' 요약 테이블 서식 설정 
        With .CurrentRegion.Offset(1).Resize(.CurrentRegion.Rows.Count - 1)
            .Rows(1).Interior.ColorIndex = 15   ''' 제목에 음영 설정
            .NumberFormat = "#,###"             ''' 콤마 서식 지정
            .Borders.LineStyle = xlSolid        ''' 괘선 그리기
            .Columns.AutoFit                    ''' 열 너비 자동 조절
        End With
    End With
End Sub

CODE

오늘 코드 중에서 가장 중요한 부분은 이곳입니다. 엑셀 함수(워크시트 함수)를 VBA에서 사용할 때에는 다음 중 한 가지 방법을 이용하면 됩니다.

방법 1: Application.WorksheetFunction.함수이름
방법 2: Application.함수이름
방법 3: WorksheetFunction.함수이름

With rSumTbl
    ..........
    .Cells(1).Offset(-1) = "[" & Target & "] 실적 요약"
    .Value = Array("총금액", "총횟수", "평균수량", "평균금액")
    .Offset(1) = Array(Application.SumIf(rTblItem, Target, rTblItem.Offset(, 2)), _
                    Application.CountIf(rTblItem, Target), _
                    Application.AverageIf(rTblItem, Target, rTblItem.Offset(, 1)), _
                    Application.AverageIf(rTblItem, Target, rTblItem.Offset(, 2)))
    ..........
End With

엑셀의 기본 함수들을 사용하였습니다. 함수의 인수가 낯설어 그렇지 사용 방법은 워크시트에서와 다를 것이 없습니다. 엑셀의 기본 기능에 대해 많이 알수록 VBA를 배우는데 유리하다고 하는 것은 그래서입니다. 자신 있게 쓸 수 있는 함수가 많다면 요약 테이블을 더욱 풍성하게(?) 구성할 수 있습니다.

조건 분기 처리나 반복문이 없어 크게 까다롭지는 않으리라 생각합니다. 수작업으로 진행할 때와 같은 논리적 순서에 따라 차근차근 코딩하면 되겠습니다.

들어가기 전에 ㅡ 나쁜 일만 안 생겨도 감사한 일이다

완성 예

이벤트 프로시저 만들기

코드와 해설