Microsoft Excel은 매크로와 VBA(Visual Basic for Applications) 스크립팅을 활용해 다양한 기능과 명령을 자동화할 수 있는 강력한 도구입니다. VBA는 엑셀에서 매크로를 만들 때 사용하는 프로그래밍 언어로, 특정 조건에 따라 명령이 자동으로 실행되도록 설정할 수도 있습니다.
매크로란 무엇인가?
매크로는 미리 기록해 둔 일련의 명령 모음으로, 정해진 명령을 내리면 자동으로 실행됩니다. 엑셀에서 회계 처리, 프로젝트 관리, 급여 계산처럼 반복적으로 수행하는 작업이 있다면, 이를 자동화함으로써 상당한 시간을 절약할 수 있습니다.
리본 메뉴의 개발 도구 탭에서는 마우스 클릭과 키 입력을 기록하는 '매크로 기록' 기능을 사용할 수 있습니다. 하지만 일부 기능은 단순히 기록만으로는 구현하기 어렵기 때문에, 보다 복잡한 스크립트를 직접 작성할 수 있는 VBA가 큰 장점으로 작용합니다.
이 글에서는 다음 내용을 다룹니다.
- 스크립트 및 매크로 사용 설정
- 엑셀에서 매크로 만드는 방법
- 매크로 실전 예제
- VBA 더 깊이 알아보기
- VBA 시작을 위한 버튼 만들기
- 버튼에 코드를 추가해 기능 부여하기
- 정상 작동 확인하기
스크립트 및 매크로 사용 설정하기
엑셀에서 매크로나 VBA 스크립트를 만들기 전에 먼저 리본 메뉴에 개발 도구 탭을 활성화해야 합니다. 개발 도구 탭은 기본적으로 비활성화되어 있습니다.
- 엑셀 워크시트를 엽니다.
- 파일 > 옵션 > 리본 사용자 지정을 클릭합니다.
- 개발 도구 항목 옆의 체크박스에 체크합니다.
- 리본 메뉴에서 개발 도구 탭을 클릭합니다.
- 이어서 매크로 보안을 클릭하고 '모든 매크로 사용(권장하지 않음. 잠재적으로 위험한 코드가 실행될 수 있음)' 항목을 선택한 뒤 확인을 누릅니다.
매크로가 기본적으로 비활성화되어 있고 경고 문구와 함께 제공되는 이유는, 매크로가 악성코드를 포함할 수 있는 컴퓨터 프로그램이기 때문입니다.
공유 프로젝트로 엑셀 등 Microsoft 프로그램 파일을 함께 작업할 때는 반드시 신뢰할 수 있는 출처의 문서인지 확인하세요.
그리고 매크로와 스크립트를 다 사용했다면, 잠재적인 악성 코드가 다른 문서로 퍼지지 않도록 반드시 매크로 설정을 다시 비활성화하는 것이 좋습니다.
엑셀에서 매크로 만들기
매크로 기록 중에 엑셀에서 수행하는 모든 동작이 그대로 매크로에 저장됩니다.
- 개발 도구 탭에서 매크로 기록을 클릭합니다.
- 매크로 이름, 바로 가기 키, 설명을 입력합니다. 매크로 이름은 반드시 문자로 시작해야 하며 공백을 포함할 수 없습니다. 바로 가기 키는 문자 하나여야 합니다.
다음 세 가지 옵션 중 어디에 매크로를 저장할지 결정합니다.
- 개인용 매크로 통합 문서: 숨겨진 엑셀 문서에 매크로를 저장하여 어떤 엑셀 문서에서든 사용할 수 있습니다.
- 새 통합 문서: 새 엑셀 문서를 만들어 해당 문서에 매크로를 저장합니다.
- 현재 통합 문서: 현재 편집 중인 문서에만 적용됩니다.
설정이 끝나면 확인을 클릭합니다.
- 자동화하려는 작업을 차례대로 수행하고, 완료되면 기록 중지를 클릭합니다.
- 이후에는 지정한 키보드 단축키로 언제든 매크로를 실행할 수 있습니다.
매크로 실전 예제
고객명과 미수금을 정리한 간단한 스프레드시트를 예로 들어 보겠습니다. 먼저 워크시트 서식을 자동으로 지정하는 매크로를 만들어 보겠습니다.
예를 들어 이름과 성을 각각 별도 열로 분리하는 것처럼 모든 스프레드시트에 새로운 서식을 적용하고 싶다고 가정해 봅시다.
물론 일일이 손으로 수정할 수도 있지만, 매크로를 활용해 프로그램을 만들면 올바른 서식이 자동으로 적용되도록 할 수 있습니다.
매크로 기록하기
- 매크로 기록을 클릭하고 매크로 이름을 Format_Customer_Data로 지정한 후 확인을 누릅니다.
- 첫 번째 열의 제목을 First Name(이름)으로 변경합니다.
- A열 옆에 새 열을 삽입하고 Last Name(성)이라고 입력합니다.
- 첫 번째 열의 모든 이름(아직 성과 이름이 함께 있음)을 드래그로 선택한 뒤, 리본 메뉴에서 데이터를 클릭합니다.
- 텍스트 나누기를 클릭합니다.
- 분리 기호로 나눔 선택 > 다음 > 공백 체크 > 다음 > 마침을 차례로 진행합니다. 아래 스크린샷에서 위 과정을 통해 이름과 성이 분리된 결과를 확인할 수 있습니다.
- 미수금 필드의 서식을 지정하려면 금액 셀들을 선택한 후 홈 > 조건부 서식 > 셀 강조 규칙 > 보다 큼 > 0을 차례로 클릭합니다.
이렇게 하면 미수금이 있는 셀이 강조 표시됩니다. 서식 효과를 더 잘 보여 주기 위해 미수금이 없는 고객 몇 명을 추가했습니다.
- 개발 도구 탭으로 돌아가 기록 중지를 클릭합니다.
매크로 적용하기
매크로를 기록하기 전의 원본 스프레드시트로 돌아가 보겠습니다. 매크로를 클릭한 뒤 방금 만든 매크로를 선택하고 실행을 누르면 됩니다.
매크로를 실행하면 모든 서식이 자동으로 적용됩니다. 방금 만든 이 매크로는 Visual Basic 편집기에 저장되어 있습니다.
매크로는 여러 가지 방법으로 실행할 수 있으니, 필요에 따라 '매크로 실행' 관련 자료를 참고해 보세요.
VBA 더 깊이 알아보기
VBA를 학습하려면 개발 도구 탭에서 매크로를 클릭하고, 이미 만들어 둔 매크로를 선택한 후 편집을 누릅니다.
위 박스에 표시된 코드가 바로 매크로를 기록할 때 자동으로 생성된 내용입니다.
동일한 방식으로 다른 고객 결재 스프레드시트의 서식을 지정할 때도 이 코드가 실행됩니다.
VBA 시작을 위한 버튼 만들기
앞서 사용한 고객별 미수금 스프레드시트를 활용해 이번에는 환율 변환기를 만들어 보겠습니다.
- 버튼 요소를 삽입하려면 개발 도구 탭으로 이동합니다.
- 컨트롤 섹션의 삽입 옆 드롭다운에서 ActiveX 명령 단추를 선택합니다.
- 버튼을 스프레드시트 원하는 위치로 드래그하여 배치합니다. 나중에 쉽게 찾아 수정할 수 있는 곳에 두는 것이 좋습니다.
- 코드를 연결하려면 버튼을 마우스 오른쪽 버튼으로 클릭하고 속성을 선택합니다. 여기서는 Name은 CommandButton으로 유지하고, Caption(버튼에 표시되는 텍스트)은 Convert로 지정합니다.
버튼에 코드를 추가해 기능 부여하기
VBA 코딩은 엑셀 화면 안에서 이루어지지 않고 별도의 환경에서 진행됩니다.
- 개발 도구 탭에서 디자인 모드가 활성화되어 있는지 확인합니다.
- 방금 만든 버튼의 코드에 접근하려면 버튼을 마우스 오른쪽 버튼으로 클릭하고 코드 보기를 선택합니다.
- 아래 스크린샷의 코드를 보면 시작(Private Sub)과 끝(End Sub)이 이미 작성되어 있는 것을 알 수 있습니다.
- 다음 코드가 환율 변환 절차를 담당합니다.
ActiveCell.Value = (ActiveCell * 1.28)
이 섹션의 목표는 스프레드시트의 통화를 변환하는 것입니다. 위 스크립트는 GBP(영국 파운드)에서 USD(미국 달러)로의 환율을 반영한 것으로, 셀의 새 값은 현재 값에 1.28을 곱한 결과가 됩니다.
아래 스크린샷은 코드를 입력한 후 VBA 창에서 어떻게 표시되는지 보여 줍니다.
- 상단 메뉴에서 파일을 클릭한 후 닫기 및 Microsoft Excel로 돌아가기를 선택해 엑셀 기본 화면으로 돌아갑니다.
정상 작동 확인하기
코드를 테스트하기 전에 먼저 디자인 모드를 비활성화(클릭하여 해제)해야 추가 수정을 막고 버튼 기능을 정상적으로 사용할 수 있습니다.
- 스프레드시트에 아무 숫자나 입력한 뒤 Convert 버튼을 클릭합니다. 값이 약 28% 증가한다면 정상적으로 작동한 것입니다.
이 예제에서는 셀에 숫자 4를 입력했습니다. Convert 버튼을 클릭하니 값이 5.12로 변경되었는데, 4 × 1.28 = 5.12이므로 코드가 올바르게 실행된 것입니다.
이제 엑셀에서 매크로와 스크립트를 만드는 방법을 이해했으니, 다양한 반복 작업을 자동화하여 업무 효율을 크게 높일 수 있습니다.