엑셀 VBA 강좌(9): 엑셀 VBA How-To

이 글은 새로운 블로그로 옮겼습니다. 5초후 자동으로 이동합니다.

▶ 새로운 블로그 주소: https://prodskill.com/

▶ 새로운 글 주소: https://prodskill.com/excel-vba-lecture-9-how-to/

이번 글은 엑셀 VBA 코딩시 알아두면 좋은 How-To에 대해 살펴본다.

 

이전 글에서 이어지는 내용이다.

엑셀 VBA 강좌(8): 엑셀 VBA 언어 기본-자료형(Data type), 자료구조(Data structure)

엑셀 VBA 강좌(8): 엑셀 VBA 언어 기본-자료형(Data type), 자료구조(Data structure)

 

목차

 

4. 엑셀 VBA How-To

 

4.1. UI(User Interface) 구성 방법

엑셀 VBA에서 사용자에게 값을 입력받거나 진행상태를 보여주는 등의 UI를 구성하는 방법은 세가지 정도가 있다.

4.1.1. 엑셀 Sheet를 사용한 UI

가장 일반적인 방법이다. 버튼, 텍스트 상자, 체크 박스 등의 컨트롤을 엑셀 Sheet에 추가하고 이벤트를 처리한다. 데이터를 살펴보면서 버튼 클릭을 실행할 수 있어 가장 쉽게 구현할 수 있고 사용하기에도 편리하다.

엑셀 Sheet를 사용한 UI

이 방법은 한가지 단점이 있다. 화면의 해상도가 변경된 다음 버튼을 클릭하면 그 버튼의 크기가 계속 커지거나 글씨의 크기가 작아지는 현상이 발생한다. 주로 외부 모니터를 변경하거나 빔프로젝트에 연결할 때 화면 해상도가 변경되고 그 때 컨트롤이 포함되어 있는 엑셀 Sheet가 열려 있었다면 거의 이 현상이 발생한다. 이 현상은 ActiveX 컨트롤을 사용했을 경우에 발생하고, 양식 컨트롤을 사용하면 발생하지 않는다.

양식 컨트롤과 ActiveX 컨트롤

 

4.1.2. User Form을 사용한 UI

별도의 User Form 개체를 만들고 그 위에 컨트롤을 추가하여 이벤트를 처리하는 방식이다.

User Form을 사용한 UI

 

입력값의 목록 등 데이터를 입력/수정하면서 작업하기에는 화면을 가릴 수 있어서 적합하지 않을 수 있다. 아래 그림처럼 ProgressBar등 정보를 표현하는 용도로는 적합하다.

Progress Bar UI

 

4.1.2. Ribbon Bar를 사용한 UI

가장 손이 많이 가고 복잡한 방법이다. 엑셀 2007부터 변경된 Ribbon Bar interface에 사용자 정의 탭과 버튼을 추가하고 이벤트를 처리한다. 몇 년 전 후배 직원이 만들어서 내게 보내준 사용자 정의 Ribbon Bar 예시는 다음과 같다.

Ribbon Bar를 사용한 UI

데이터 표준점검 도구(https://prodtool.tistory.com/23)는 UI를 엑셀 Sheet에 제공했는데, Ribbon bar UI로 변경한다면 위와 비슷할 것이다.

데이터 표준점검 도구 설명글 목록, 목차, 다운로드

 

Ribbon bar에 대한 내용은 이 자체로도 상당히 방대하여, 나중에 별도의 글로 따로 정리하겠다. 아래 내용을 참고한다.

https://docs.microsoft.com/en-us/archive/msdn-magazine/2007/february/extend-office-2007-with-your-own-ribbon-tabs-and-controls

Extend Office 2007 With Your Own Ribbon Tabs And Controls

 

https://docs.microsoft.com/en-us/office/vba/library-reference/concepts/overview-of-the-office-fluent-ribbon

Overview of the Office Fluent ribbon

 

 

4.2. VBA 코드를 실행하는 방법

VBA 코드를 실행하는 방법은 세 가지 정도가 있다.

4.2.1. 버튼 클릭 이벤트에서 실행

버튼 클릭 이벤트에서 VBA 코드 실행

위 그림과 같이 버튼을 만들고, 그 버튼의 클릭 이벤트에 VBA 코드를 실행하는 구문을 작성하는 방법이다. UI에서 실행하는 방법을 제공할 때 적합하다.

 

4.2.2. 실행할 소스코드에 커서 위치시키고 단축키로 실행

커서 위치에서 VBA 코드 실행

VBE(Visual Basic Editor)의 Sub 또는 Function 프로시저 코드에 커서를 위치시키고 단축키 F5 로 실행한다. 툴바의 실행 버튼(▶️) 을 클릭해도 된다. 이 방법은 parameter가 없는 Sub, Function 프로시저에서만 동작한다.

실행 방법을 외부로 제공할 필요가 없는 경우, 간단히 테스트하는 경우 적합한 방법이다.

 

4.2.3. 직접 실행 창에서 실행

직접 실행 창에서 VBA 코드 실행

Sub, Function 프로시저명과 parameter를 직접 실행 창에서 입력하고 엔터키를 눌러 실행하는 방법이다. parameter가 있는 Sub, Function 프로시저를 간단히 테스트할 때 적합한 방법이다.

 

 

4.3. 직접 실행 창 도구 활용 방법

직접 실행 창은 위에서 살펴 보았던 매크로 실행 용도외에 실행시 변수값을 출력해 보는 디버깅 용도, 메시지 출력 등의 다양한 용도로 활용할 수 있다.

직접 실행 창 도구 활용 방법

1. 매크로 실행
   • Module내의 Sub, Function 프로시저명을 입력하여 실행
   • Parameter가 있는 프로시저도 실행 가능
2. 실행시 변수값 확인
   • 중단점(break point)에 멈춘 상태에서 사용
   • Print 또는 ? 뒤에 변수명 입력후 엔터키로 값 확인
3. 출력 메시지 확인
   • Debug.Print로 출력되는 내용 확인

 

 

4.4. 개체 찾아보기 도구 활용 방법

이전 글 엑셀 VBA 강좌(3): 엑셀 Object Model 에서 개체 찾아보기 도구를 잠깐 살펴보았었다.

엑셀 VBA 강좌(3): 엑셀 Object Model

 

VBE '보기' 메뉴에서 '개체 찾아보기'를 실행한다. 단축키는 F2 이다.

개체 찾아보기 메뉴

 

개체 찾아보기 도구는 다음과 같이 구성되어 있다.

개체 찾아보기 도구 구성

1. Filter & Search
   • 엑셀에서 기본적으로 제공되는 라이브러리와 참조 추가한 라이브러리 전체 목록을 확인하고 특정 라이브러리를 선택할 수 있다.
   • 전체 라이브러리 목록을 대상으로 클래스명 또는 구성원명으로 검색할 수 있다.
2. 클래스 목록
   • 선택한 라이브러리 하위의 모든 클래스, 모듈, Enum 목록을 보여준다.
3. 구성원 목록
   • 클래스 목록에서 선택한 클래스, 모듈의 구성원인 프로시저, 함수, 속성 등의 목록을 보여준다.
4. 구성원 정보
   • 구성원 목록에서 선택한 프로시저, 함수, 속성등에 대한 상세 내용을 보여준다.

 

개체 찾아보기 도구에서 표시되는 아이콘의 종류와 그 의미는 다음과 같다.

개체 찾아보기 도구에서 표시되는 아이콘의 종류와 의미

(출처: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/icons-used-in-the-object-browser-and-code-windows)

 

개체 찾아보기 도구에 대한 더 자세한 내용은 아래 URL을 참고한다.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/object-browser

Object Browser (Visual Basic for Applications)

 

 

 

4.5. VBA 성능 향상 방법

VBA 성능 향상 방법은 별도의 글로 작성해 두었다.

VBA 코딩 패턴: 성능 향상

VBA 코딩 패턴: 성능 향상

 

위 글을 요약하면, 처리할 코드에 진입하기 전 전역변수를 성능향상값으로 설정하고, 처리 코드 종료 후 원래의 값으로 변경한다. 다음 코드를 참고하기 바란다.

'코드 진입 부분
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False
 
'실행할 코드가 들어 갈 부분
...
 
'코드 종료 부분
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True

 

 

4.6. 매크로 기록기 활용 방법

엑셀의 매크로 기록기는 매우 유용하다. 예를 들어, 피벗 테이블을 새로 만들거나, 차트를 새로 만들거나, 반복되는 작업을 어떻게 VBA 코드로 작성할지 모르는 경우 등에 매크로 기록기가 크게 도움이 될 수 있다.

매크로 기록기를 시작하고 키보드나 마우스등을 이용하여 작업을 처리한 다음 매크로 기록기를 중지하면, 그 동안 처리한 작업 내역이 VBA 코드로 작성되어 있는 것을 확인할 수 있다. 처음부터 VBA 코드를 작성하는 것보다 이렇게 만들어진 코드를 수정하여 사용하면 훨씬 빠르고 정확하게 코딩이 가능하다.

매크로 기록은 다음과 같이 개발 도구 탭 또는 하단 상태바의 '매크로 기록' 버튼을 클릭하여 시작한다.

매크로 기록 시작 버튼

'매크로 기록' 버튼을 클릭하면 다음과 같이 매크로 이름, 바로 가기 키, 저장 위치, 설명을 입력하는 창이 보여진다.

매크로 이름, 바로 가기 키, 저장 위치 설정

 

매크로 정보를 입력 후 '확인' 버튼을 클릭하면 다음과 같이 기록중 상태로 변경된다.

매크로 기록 중

 

시트에 붙여넣은 데이터의 테두리 지정, Header 설정(배경색, 글꼴, 가운데 정렬), 눈금선 감추기, 틀고정 기능을 매크로 기록기로 기록해 보자. 매크로 기록을 시작하고 키보드, 마우스 등을 이용하여 기능을 실행하면 아래 그림의 우측과 같은 결과가 된다.

매크로 기록 예시

 

매크로 기록을 중지하고 생성된 VBA 코드를 확인해 보면 다음과 같다.

Sub 매크로1()
'
' 매크로1 매크로
'
    Columns("A:B").Select
    Columns("A:B").EntireColumn.AutoFit
    Range("A1:B11").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("A1:B1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    Range("A2").Select
    ActiveWindow.FreezePanes = True

    ActiveWindow.DisplayGridlines = False
End Sub

 

이 코드를 분석해 보면 다음과 같다.

매크로 기록기로 생성된 코드 분석

 

엑셀 Object Model을 잘 모르더라도, VBA 문법을 잘 모르더라도 이렇게 만들어진 VBA 코드를 잘 활용하면 얼마든지 유용한 매크로를 만들 수 있다. 매크로 기록기를 잘 활용하면 VBA 코딩도 쉬워진다.

참고로, Ms-Office 제품군중에서 엑셀이 유일하게 매크로 기록기가 제공된다.( 파워포인트, 워드 등에서도 매크로 기록기가 제공된다면 훨씬 다양한 시도를 해볼 수 있을 것 같은데, 제공되지 않는 점은 개인적으로 많이 아쉽다.

 

4.7. 코딩시 지키면 좋을 기본 원칙

엑셀 VBA를 이용하여 코딩할 때 다음과 같은 기본 원칙을 지키는 것을 권장한다.

• 변경되는 값은 내부에 두지말고 외부(UI)에서 입력받도록 한다.
• 처음부터 Module/Class로 구조화하지 말고 처음에는 구현의 목적에만 집중한다.
  • 구조화하는데는 추가적인 시간이 소요되는데 이 시간 때문에 본래 업무 일정에 영향을 주면 안됨.
  • VBA는 수단(도구)이지 목적이 아님. 주객전도되지 않도록 주의.
• 3번 이상 사용되는 경우 또는 향후 계속 사용될 것이 분명한 경우에는 재사용성을 고려하여 Module/Class로 리팩토링 및 구조화한다. (Make --> Copy --> Refactoring)

 

---

이번 글에서는 엑셀 VBA 코딩시 알아두면 좋은 How-To에 대해 살펴보았다. 다음에는 엑셀 VBA로 개발하여 사용하고 있는 도구의 사례를 살펴보겠다. 엑셀 VBA 강좌 마지막 글이 될 예정이다.