[VBA] 데이터 편집과 서식 설정 자동화하는 프로그램 만들기 (시간 단축!)

 

# 목표
 - 많은 데이터를 보기 좋게 시각화하는 과정에서 서식을 자동화할 수 있다. (매크로, VBA)
 - 메인 매크로를 통해 2개 이상의 매크로들을 순차적으로 자동 실행할 수 있다. (Call)
  → 서식 변경하는 등의 단순한 작업을 자동화시킴으로써 효율성 ↑
  → 많은 자동 매크로 작업을 한번에 수행하면서 시간 단축!

 

[ 최종 VBA 실행 동작 영상 ]

 

---

 

1. 데이터 편집 및 서식 설정 자동화 매크로 생성

 위의 영상에서는 가상의 데이터셀에 대해서 '제목 만들기', '데이터 행 번호 입력', '전화번호 뒷자리 보호', '셀 서식 설정'으로 총 4가지의 매크로가 실행되며, 이를 각각 자동 실행될 수 있도록 만들어보고 4개의 매크로를 하나로 통합하는 과정(2단계)도 실습해보자.

1-1. 제목 만들기 자동 생성

'제목 자동 생성' 매크로는 표 데이터 상단에 행 3개, 열 1개를 추가한 후, A2셀에 제목을 입력하고 서식을 변경하도록 설정했다. 사용자에 취향과 목적에 따라 다르게 적용할 수 있으며, 매크로 작동하는 것과 VBA 코드를 확인해보자.

 

[ 제목 생성 매크로 실행 결과 ]

 

[ 제목생성 VBA 코드 ]

 

- Rows("1:3").Insert & Columns("A:A").Insert
 : 1~3행 추가, A열 앞에 빈 열 추가(Insert 메서드 사용)
 → Insert 뒤에 인수들을 지정해서 행을 삽입 시, 형태 옵션을 설정할 수도 있다.

- Selection.HorizontalAlignment = xlCenterAcrossSelection
 : 선택한 영역의 가로 맞춤 설정. ( 자동 매크로 사용 시, 변경하지 않은 설정코드들은 제거 )

- With Selection.Font  ~  End With
 : 글꼴에 대한 대화상자 설정 영역으로, 서식 변경할 '맑은 고딕', '굵게', '사이즈' 남기고 삭제
- With ~ End With  : 어떤 문장이 반복적으로 입력될 개체나 속성을 하나로 묶어주는 역할 
 : 위에서는 ' . '으로 시작하는 코드들은 모두 " Selection.Font "라는 개체가 생략됨

1-2. 데이터 행 번호 자동 입력 (Row 함수, End 속성 사용)

'번호 자동입력(순서)' 매크로는 A4셀에 '번호'를 입력하고 데이터가 시작하는 A5셀부터 순차적으로 번호가 자동으로 표기되는 작업이다. Row 함수를 이용하여 순차적으로 번호를 입력하고, 데이터가 업데이트되면 자동으로 추가되는 방식으로 구현했다. (End 속성)

 

[ 행번호 자동입력(순서) 매크로 실행 ]

 

[ 행 번호 자동입력 VBA 코드 ]

 

- Range("A5").FormulaR1 = "=Row()-4"
 : Row 함수를 사용해서 선택한 A5셀부터 1로 표기할 수 있도록 한다.

- Range("A5").AutoFill Destination:=Range("A5:A" & Range("B5").End(xlDown).Row)
 : 실제 데이터가 있는 B열을 참조해서 데이터가 업데이트되면 순번을 자동 채울 수 있도록 설정한다.
  → 데이터가 추가될 경우, 순번이 자동 생성될 수 있도록 B열을 참고 (Destination 설정)
  → 순번은 A5셀부터 A열에 추가하며, AND 연산자(&)를 통해 맨 마지막에 있는 데이터 위치를 알아내는 원리
 → End(xlDown) : 선택된 셀에서 가장 아래 있는 데이터로 이동 (Ctrl +  ↓)

# End 속성 (=Ctrl + 방향키) : 마지막 데이터로 이동하는 역할 수행. 
 → End(xlUp) : 맨 위로 (↑)   /   End(xlDown) : 맨 아래로 (↓)
 → End(xlToLeft) : 맨 왼쪽 (←)   /   End(xlToRight) : 맨 오른쪽 (→)
 → ' xl '의 의미는 Excel 내 VBA 전용이라는 것.

1-3. 전화번호 보호 처리 ( * 표시 )

'번호보호처리' 매크로는 집/사무실/휴대폰 전화번호의 맨 뒤 4자리를  ' * ' 표시함으로써 개인정보 보호하도록 작동된다. LEFT 함수를 사용하여 번호의 앞자리를 가져 온 후, 뒤에 별표(*)를 추가해서 빈 열에 표시하고 변경된 번호값으로 수정하는 방식으로 수행했다. 

 

[ 전화번호 보호처리 매크로 실행 ]

 

[ 번호보호처리 VBA 코드 ]

 

- Range("I5").FormulaR1C1 = "=LEFT(RC[-2],9)&""****"""
 : I5셀(현재 셀)을 기준으로 왼쪽으로 2칸 떨어진 값을 가져와서 LEFT 함수 적용한다는 의미
  → LEFT (값을 가져올 셀, 얼마나 가져올지 지정)
  → 앞자리가 02 or 031인 경우, LEFT 함수만으로 가져올 수 없으므로 LEN 함수 적용!!
  → 즉, LEN 함수로 뒤에 4자리를 뺀 숫자값들을 모두 그대로 가져올 수 있도록 설정함.

# FormulaR1C1 형식 (상대 참조)
 : 현재 셀을 기준으로 행, 열의 수 증감 표시
 ex) R[1]C[2] : 행은 1증가, 열은 2증가한 셀 참조 
       RC[-2] : 행은 변화 없고, 열만 2칸 감소한(왼쪽) 셀 참조

- PasteSpecial Paste:=xlPasteValues  : "값 붙여넣기" 인수 추가 
- Delete 메서드
: 뒤에 인수가 필요하지 않지만, 삭제 후 왼/오른쪽 정렬을 할 것인지 지정할 수는 있다.

1-4. 전체 셀 서식 자동 변경

위에서 생성한 제목을 제외한 본문 데이터의 서식을 자동 변경하는 매크로를 만들어보자. 자동매크로를 이용하여 기록 후 코드를 삭제할 경우, 홈 메뉴에서 사용하지 않는 것이 좋다. (본문 데이터 전체 범위를 지정하고, 셀서식에서 변경하는 방식)

 

[ 전체 셀 서식변경 매크로 실행 ]

 

[ 셀 서식 변경 VBA 코드 ]

 

- Range("A4").CurrentRegion.Select  : 데이터셀 전체 선택 ( 'Ctrl + A'와 동일한 역할 )
 → 데이터가 추가/감소됨에 따라 셀번호가 달라질 수 있으므로, CurrentRegion 속성을 이용하자

- Selection.Borders.LineStyle = xlContinuous  : 전체 테두리를 '실선'으로 표시

 

2. 생성한 매크로 한번에 실행시키기 (Call로 호출)

여기까지 총 4가지의 데이터 편집 및 서식 변경하는 매크로를 생성하였으며, 이 매크로들은 순차적으로 실행되어야 데이터 오류없이 동작할 수 있다. 따라서 "메인 매크로"를 만들어서 한번에 순서대로 실행될 수 있도록 하고, 한번 편집완료된 것은 더이상 동작하지 않도록 해보자.

 

[ 최종 메인 매크로 구현 - 통합 매크로 한번에 돌리기 ]

 

[ 메인 매크로 VBA 코드 ]

 

- ' Call '를 통해서 매크로를 호출할 수 있다. (호출한 순서대로 매크로가 실행될 수 있도록 한다.)
- ' Private '은 Call을 통해 실행될 매크로에 한해서 추가해주면 된다.
 ( 그러면 실행할 때, 매크로 목록에서 보이지 않는다. 단축키 설정해서 사용하면 용이하다. )
- If ~ End If 
 : 편집 완료된 Sheet에 대해서는 데이터 손상을 방지하기 위해 중복실행되지 않기 위한 조건문으로,
 A1셀이 빈셀일 경우, 작업이 완료되었다고 판단하여 매크로가 실행되지 않게 된다. (msg 팝업)