[VBA] 엑셀 매크로와 VBA에 꼭 필요한 기초 자동화 (초급)
업무 중 단순, 반복적인 작업을 자동화하거나 함수로 실행할 수 없는 작업들을 쉽게 구현하기 위해 매크로와 VBA를 공부해보자. VBA 기초 문법부터 학습하기보다 실무에 최대한 바로 적용할 수 있도록 자동 매크로로 코드를 생성하고, 그 코드를 해석 및 수정하면서 VBA를 익히는 식으로 학습을 진행하면 효율적!!
1. 자동 매크로와 VBA 편집기
# 매크로(Macro)
: 기존 MS 응용프로그램(Word, Excel, Powerpoint 등)에 없는 기능을 사용자가 추가해서 사용할 수
있도록 하는 작업.
→ 반복적인 업무와 실행방법이 매우 편리하며, 본인이 만든 엑셀 매크로 및 VBA 프로그램을
다른 사용자에게 베포 하더라도 바로 사용할 수 있다는 장점이 있다.
→ 이 때 구성되는 MS 내장 언어가 Visual Basic Application(VBA)이다.
→ 주의점!
: 매크로가 포함되어 있는 경우, 파일 형식을 'Excel 매크로 사용 통합 문서(*.xlsm)'으로 저장한다.
그렇지 않을경우(xlsx), 매크로 기능이 모두 삭제되어 저장될 수 있다.
1-1. 자동 매크로 실습
자동 매크로 기록기는 코드의 녹화기능으로 엑셀 화면에서 키보드와 마우스 조작만으로 동작이 자동으로 VBA 코드 생성되는 원리로 작동된다.
아주 간단한 실습으로 셀에 1과 2를 입력하고, 아래로 숫자 5까지 생성되는 것을 진행해보자.
1) '매크로 기록(개발도구 - 매크로)'을 누르고 매크로 이름 지정 (단축키, 설명은 선택사항)
2) 작업하고자 하는 동작을 수행하고, '기록 중지' 클릭
3) 설정한 단축키 or 실행하고자 하는 매크로 선택 후 실행 (매크로 - 등록한 매크로 중 하나 선택)
1-2. Visual Basic 편집기
자동으로 생성된 VBA 코드를 확인하고, 약간의 수정으로 동작원리를 확인해보자.
(불필요한 코드 삭제는 아래 참고)
→ Visual Basic 편집기는 '개발도구 - Visual Basic' 혹은 '개발도구 - 매크로 - 편집'을 클릭해서 확인!
※ 위의 코드 중, A5까지 자동 생성이 아니라 A500으로 변경한다면? ('A1:A5' → 'A1:A500')
→ 1~500까지 숫자 자동생성 매크로가 된다.
# VBA의 구조
: VBA 코드는 개체지향적 언어이다. 즉, 개체를 중심으로 프로그램이 실행된다.
기록되는 모든 문장에는 개체가 제일 먼저 나오며, 그 다음으로 속성이나 메서드가 입력된다.
1) 개체.속성 = 속성값 2) 개체.메서드 = 인수1, 인수2 ...
→ 구분기호 : 마침표( . ) → 구분기호 : 쉼표( , )
2. 동일한 작업을 모든 Sheet에 반복 실행하기 (For Each, 절대참조)
주어진 Sheet 내 데이터를 새롭게 입력하고, 텍스트를 분리하는 매크로를 만들어보자
그리고 하나의 Sheet가 아닌 Excel 파일 내 모든 Sheet에서 동일한 작업이 수행될 수 있도록 해보자
위 실습 자료에는 날짜, 온도, 습도의 데이터들이 각 sheet마다 다른 형식으로 저장되어있다. 우리는
자동 매크로를 통해서 범주명을 새롭게 입력하고 텍스트를 분리해보자.
→ 날짜 데이터는 '데이터 - 텍스트 나누기'를 통해서 텍스트 분리!
위 작업에서 자동 생성된 VBA 코드에서 불필요한 코드(Select, ActiveCell)를 삭제하고, 사용된 인자를 분석해보자.
- Destination:=Range("A1") : 대상
- DataType:=xlFixedWidth : 너비를 일정하게 할 것인지, 구분 기호로 분리할 것인지 설정
→ 만약 문장이 아직 끝나지 않았음에도 단순히 줄만 바꾸기 위해서는 ' _ ' 를 추가하면 된다.
- FieldInfo:=Array(Array(0,1), Array(10,9)) : 2, 3단계의 데이터 서식을 한번에 지정하는 코드
→ Array(시작위치, 데이터 서식)
- TrailingMinusNumbers:=True : 고급 설정에서 '음수일 경우' 마이너스로 표시 설정
이젠, 파일 내 모든 Sheet에 대해 해당 작업을 동일하게 수행될 수 있도록 코드를 추가해보자!
위 사진처럼 'For Each ~ Next' 코드를 추가하면, 모든 Sheet에 동일한 작업 수행된 것 확인!
# For Each 코드 구조
" For Each (변수) In Worksheets
반복 실행할 문장
Next "
→ 시트 내 반복 사용할 때,
현재 파일에 시트가 있는 만큼 혹은 현재 cell 범위에 데이터가 있는 만큼 반복할 수 있다.
→ For Each ~ Next 구문은 엑셀에서 주어진 개체만큼 셀, 시트, 차트, 피벗의 개수나 어떠한 개체의
개수만큼 반복을 하는 문장으로써 많이 사용된다.
3. 셀 데이터 이동 및 삭제 자동 반복하기 (Do Loop, 상대참조)
셀의 여러 데이터값 이동 및 삭제를 자동 반복하는 코드를 구현해보자.
많은 양의 데이터일 경우, 동일한 매크로 작업이라도 여러 번 하는 것은 비효율적이므로 'Do Loop'
반복문을 통해 매크로 실행 위치가 상대적으로 변경되는 상대 참조 매크로를 실습해보자.
위의 실습 자료는 어떤 품목, 거래처, 날짜와 수량이 있는 데이터셋이며 수량에 해당하는 데이터의 위치를 보기 좋게 이동하는 동작을 구현한 것이다. 위 작업은 하나의 셀 데이터값을 이동 및 삭제하는 것이며, 먼저 셀 이동에 필요한 Offset에 대한 개념을 공부해보자.
# Offset 속성
Offset을 통해 출발할 셀에서부터 지정한 행수와 열수만큼 이동된 셀을 지정할 수 있다.
→ 형식 : 출발셀.Offset(이동 행수, 이동 열수)
→ 이동할 행과 열 수는 0을 기준으로 양, 음의 정수로 입력할 수 있다.
여기까지는 하나의 셀에 대한 이동이며, 아래 많은 데이터에 대해서 일일이 VBA를 실행시키는 것은 매우 비효율적이다. 따라서 이를 한번에 모든 데이터 셀에 반복적으로 실행될 수 있도록 'Do ~ Loop' 반복문으로 구현해보자.
- Do while ActiveCell.Value <> "" ~ Loop : ActiveCell의 값이 빈 셀일 때까지 반복!
→ 구조 : Do while (조건식) ~~~ Loop
→ 즉, 셀에 데이터가 있는 동안, 이동할 데이터가 있을 경우 해당 반복문이 실행된다.
→ 그리고 반복문을 시작하기 전에는, 꼭 Start Cell을 지정해야 한다.
ex) Range("D3").Select
- Selection.Cut Destination:=ActiveCell.Offset(-1,1).Range("A1")
→ Selection은 드래그로 범위를 지정한 것 ( 2개 이상의 셀 데이터를 선택한 경우 )
→ ActiveCell은 선택한 하나의 셀을 의미
→ Cut 메서드는 뒤에 Destination 이라는 인수가 오면, 잘라내지 않고 셀 이동을 한다.
위의 사진을 통해 2가지 개념을 비교할 수 있으며, 하나의 셀을 이동할 경우에는
ActiveCell이 Selection으로 설정되어도 문제는 없다.
- ActiveCell.Rows("1:1").EntireRow.Select
→ 현재 클릭되어 있는 셀을 기준으로 해당 셀이 속한 행 전체를 의미한다.
A3이 클릭되어 있으면, 3행 전체를 뜻한다. (Rows("1:1")은 없어도 된다. - 가상셀)
- Selection.Delete Shift:=xlUp : 선택한 행 삭제
- ActiveCell.Offset(1,3).Range("A1").Select : 선택된 셀인 A3에서 D4로 이동
위의 동작에서 확인할 수 있듯이 Do Loop를 적용하면, 조건이 만족할 때까지 동일한 작업을 반복할 수
있으며 실행될 매크로의 위치가 상대적으로 다른(상대 참조 매크로)의 경우에도 쉽게 구현할 수 있다.
'Data Analyst > Excel VBA' 카테고리의 다른 글
| [VBA] 데이터 편집과 서식 설정 자동화하는 프로그램 만들기 (시간 단축!) (0) | 2022.07.24 |
|---|---|
| [VBA] 양식 컨트롤을 이용한 데이터 추출 프로그램 만들기(고급 필터) (0) | 2022.07.20 |
| [VBA] 엑셀 Raw Data 자동 편집 프로그램 만들기 (업무 효율↑) (0) | 2022.07.19 |
| [VBA] 많은 표 데이터 중 "보고 싶은 데이터"만 추출하는 방법(매크로) (0) | 2022.07.18 |
댓글