본 문서는 프로그래밍을 20여년 해온 본인이 VBA(Visual Basic for Application) 스크립트를 처음 사용해보면서 알게된 기본적인 내용들을 기록하기 위해 작성 되었습니다. 변수는 무엇인지 또 클래스는 무엇인지 등의 기본 개념들에 대해서는 언급하지 않고 변수를 선하고 사용하는법, 클래스를 만드는 법 등의 '사용법'을 위주로 다룹니다. 물론 프로그래밍에 대해 아무것도 모르고 엑셀을 사용하면서 '매크로'라는 기능을 사용해보고 싶은 사람도 이 포스트를 보고 공부를 할 수는 있지만 근본적인 궁금증은 해결하기 어려울 것입니다. 하지만 아직 뭐가 궁금한지도 모르는 사람이라면 이 포스트가 하나의 시작점이 될 수 있을 것이라 생각합니다.
시작
비주얼베이직 프로젝트 창 띄우기
VBA를 사용하는데 사실 어떻게 해야 할지 난감합니다. 엑셀 어디를 살펴봐도 프로그래밍 코드를 입력 할 수 있는 부분은 보이지 않습니다. 간단하게 엑셀을 실행 시킨 상태에서 'Alt + F11'을 누르면 비쥬얼베이직 프로젝트 창이 뜹니다.
만일 메뉴를 통해 프로젝트 창을 띄우고 싶으시다면 '개발 도구' 메뉴를 활성화 시켜야 합니다. 이미 활성화 되어 있는 상태라면 하지 않으셔도 됩니다. '파일 > 옵션 > 리본 사용자 지정 > 개발 도구'를 체크해주세요. 그럼 이제 부터 상단 메뉴에 '개발 도구'가 보일 것입니다.
VBA 프로젝트 항목 설명
위와 비슷한 화면이 보인다면 성공입니다. 각자 구동환경이 다를 수 있기 때문에 위와 똑같은 화면이 아니어도 신경 쓰지 않으셔도 됩니다. 지금 중요한 것은 붉은 박스 부분입니다.
'VBAProject 통합문서' 라고 되어 있는 창의 아무 곳이나 우클릭하면 아래와 같은 메뉴들이 나옵니다.
여기서 중요한 것은 'Sheet1', '현재_통합_문서', '사용자 정의 폼', '모듈', '클래스 모듈' 입니다. VBA 프로그래밍은 이 다섯 영역에서 모두 이루어 집니다.
- 워크시트(Worksheet) - "Sheet1"
엑셀 문서에 워크시트가 하나 추가 될 때 마다 Sheet1, Sheet2...이런식으로 같이 추가 됩니다. 특정 워크시트에 대한 이벤트 처리 프로그래밍을 여기에 합니다. - 워크북(Workbook) - "현재_통합_문서"
엑셀 문서 전체 관한 이벤트 처리 프로그래밍은 여기서 합니다. - 폼 - "UserForm1"
여기는 특이하게 타이핑으로 프로그래밍을 하는 것이 아닌 컨트롤 UI들을 드래그 해서 추가하는 곳입니다.
- 모듈 - "Module1"
모든 워크시트나, 워크북에서 공통적으로 사용되는 코드를 여기에 코딩 합니다. - 클래스 모듈 - "Class 1"
클래스들을 정의합니다.
VBA 작성
주석
첫글자에 따옴표 또는 Rem을 라인 맨 앞에 쓰고 한칸 띄우면 주석으로 처리
' This is Comment
Rem This is Comment, tooOn Error 문
VBA 스크립트 실행 중 에러가 발생 했을 때 처리 방식 지정
On Error Resume Next
'On Error Resume Next' 이후 라인 부터는 오류가 발생하더라도 그냥 계속 진행
Sub OnErrorResultNext()
On Error Resume Next
Sheet(999).Delete
If Err Then MsgBox "can't find sheet"
End Sub3라인의 999째 시트가 없는 경우 런타임 오류를 발생 시켜야하지만 On Error Resume Next로 인해 아무 오류도 발생하지 않음. On Error Resume Next문은 프로시져가 종료 되면 원래대로 초기화 된다. 즉, 다른 프로세스가 가동 될 때는 오류 발생 시 정상적으로 오류 메시지를 볼 수 있다는 것이다.
On Error GoTo 0
On Error Resume Next로 오류 창이 뜨는 것을 막았다면, On Error Go To 0으로 다시 원래대로 되돌린다. 'On Error Go To 0'문 이후 부터는 오류가 발생하면 정상적으로 오류창이 뜨게 된다.
On Error GoTo <Label>
오류가 발생하면 특정 레이블로 분기 한다.
Sub OnErrorGoToLabel()
Sheets("SomeSheet").Select
On Error GoTo e1
ActiveSheet.Shapes("NotExistSomeThing").Delete
e1 :
'Resume Next
End Sub5 라인에서 "NotExistSomeThing"의 뭔가를 찾지 못하면 에러 발생. 4라인에서 e1 레이블로 분기 하라고 했으므로 e1 부터 코드를 다시 실행한다.
변수
변수 타입
| 종류 | 테이터 타입 |
| 논리 값 | Boolean |
| 수 | Integer, Long, Longptr, LongLong Double, Currency(화폐) |
| 문자 | String |
| 개체 | Object, Collection |
라이프 스팬에 따른 변수 구분
- 지역 변수
- 선언 된 프로시져 내에서만 접근 가능, 프로시져 종료 시 해제
- 프로시져 내에서 'Dim 변수명 As 변수 타입' 으로 선언.
- 모듈 변수
- 선언한 해당 모듈에서만 사용 가능, 프로그램이 종료 될 때까지 유지
- 'Dim 또는 Private 변수명 As 변수 타입'으로 선언
- 전역 변수
- 전체 프로젝트에서 접근 가능하며 프로그램이 종료 될 때까지 유지
- 모듈 개체에서만 선언 가능
- 'Public 변수명 As 변수 타입'으로 선언
- 정적 변수
- 선언 된 프로시저 내에서만 유효. 지역 변수와 접근 스코프는 같지만 프로그램이 종료 될 때까지 유지
- 'Static 변수명 As 변수 타입'으로 선언
변수 선언
Public globalVar As Integer '전역 변수
Dim moduleVar As Integer '모듈 변수
Sub Procedure()
Dim i As Integer '지역 변수 선언
Static s As Integer '정적 변수 선언
End Sub※ Dim : Declare in Memory
개체 타입 변수
Primitive 타입 변수를 제외한 변수. 예를들어 Collection, Workbook, Worksheet, Range, Cell 같은 것들이 개체 타입 변수임. 개체 변수는 선언은 Primitive 타입 일반 변수와 같지만 New 연산자를 통해 메모리를 할당 하는 과정이 필요함.
일반 변수의 경우 '변수 = 대입 값' 과 같이 사용하지만 개체 변수의 경우는 'Set 변수 = 대입 값'과 같이 사용함.
Dim coll1 as Collection 'Collection 개체 변수 선언
Set coll = New Collection※ 개체 변수에 값을 할당 할 때 'Set' 키워드를 빼면, '컴파일 오류입니다. 선택적 인수가 아닙니다' 오류가 발생 한다.
※ 일반 변수에 값을 할당 할 때는 그냥 쓰는데 왜 개체 변수에 값을 할당 할 때는 'Set'을 사용해야만 하는가? - 일반 변수 앞에는 Let이라는 키워드를 써줘야하지만 Let이 기본값이기에 키워드를 생략해도 사용한 것으로 간주한다.
대표적인 개체 타입
- Worksheet : 엑셀 문서의 시트
- Workbook : 엑셀 문서(파일)
- Application : 엑셀 어플리케이션
- Collection : 다이나믹 배열. key-value 형태도 가능
- Cell, Range : 워크시트 셀을 참조하는 개체
클래스
VBA에서 클래스는 class 같은 키워드로 선언하는 것이 아니라 '프로젝트 탐색기에서 마우스 우클릭 > Insert > Class Module'을 선택 함으로써 생성된다.
클래스의 선언은 일반 변수와 동일 하게 'Dim 변수이름 As 클래스타입' 포멧이다. 클래스는 개체 변수이므로 생성 New 키워드로 메모리를 할당 해주지 않으면 오류가 발생한다. 'Dim 변수이름 As New 클래스타입' 처럼 선언과 할당을 동시에 해줄 수 있다.
Dim 변수이름 As 클래스타입
Set 변수이름 = New 클래스타입
' or
Dim 변수이름 as New 클래스타입워크북
- 내용이 너무 길어 별도의 포스트로 작성 되었습니다 - [Excel] VBA - Workbook 완벽 가이드
워크시트
- 내용이 너무 길어 별도의 포스트로 작성 되었습니다 - [Excel] VBA - Worksheet 완벽 가이드
엑셀 오브젝트 단위 이벤트 처리
엑셀 오브젝트란 엑셀 어플리케이션 그 자체(Application으로 참조) 또는 엑셀 문서(Workbook), 엑셀 시트(Worksheet), 차트(chart) 등을 말하며 각 오브젝트들은 다양한 이벤트 함수들을 가지고 있다. 여기서 이벤트란, 엑셀 시트를 열거나, 셀의 값을 변경하거나, 엑셀 윈도우 사이즈를 변경하거나 등등의 각종 유저가 발생 시키는 변경사항들을 말한다. 하나 하나 설명하긴 너무 많고 여기에선 엑셀에서 제공하는 이벤트 처리 함수 추가 방법을 알아 보도록 한다.
Worksheet 이벤트
- 이벤트 함수를 작성할 워크시트 개체를 선택한다.
- 워크시트를 선택한다
- 사용할 이벤트 함수를 선택하면 코드에 자동으로 이벤트 프로시져가 작성 되어진다.
Workbook 이벤트
워크시트 개체를 선택하는 대신 워크북 개체를 선택(위 이미지에서 '현재_통합_문서')를 선택하는 것 외에는 동일한 과정을 거친다. 대신 처리 할 수 있는 이벤트의 종류와 이름은 다르다.
디버깅하기
- 내용이 너무 길어 별도의 포스트로 작성 되었습니다 - [Excel] VBA - 디버깅(Debugging)
유용한 예제들
워크시트에서 특정 값을 가진 Cell 찾기
Cells.Find(What:="<찾으려고하는 Cell 값>").Column셀에 드롭다운 리스트 달기
Set Cell = Range("A1")
Cell.Validation.Delete '이전 드롭다운 리스트 삭제
Cell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="A,B,C,D"
