Excel VBA 기초 요약

기타 사항

• 매크로(Macro): 일련의 반복되는 엑셀 작업을 기록하여 재사용 가능하도록 하는 기능
• VBA: Visual Basic Application의 약어로 MS오피스의 기본적인 기능을 뛰어넘는 작업을 할 수 있도록 해주는 프로그램
• 양식컨트롤 단추와 ActiveX 단추 차이: 매크로 단순 실행(특정 매크로 연결) 대비 버튼에 대해 직접 코딩이 필요한 차이가 있음
• ActiveX 단추 버튼을 만들고 더블 클릭 시 VBA 편집 모드로 이동됨, 실행하고 싶을 땐 디자인 모드 해제 필요
• call 매크로이름으로 매크로 실행 가능
• 언더바 _는 한 문장이 길어 두 줄로 나눠서 쓸 때 입력
• '은 주석(comment) 처리용 특수기호

VB 편집기(Editor) 구성

• 메뉴바(Menu Bar): 맨 상단 메뉴
• 툴바(Toos Bar): 자주 쓰는 툴 모음 (보통 메뉴바 바로 밑)
• 프로젝트 탐색기(Project Explorer)
• 속성창(Properties Window) (F4)
• 코드창(Code Window) (F7)
• 직접 실행창(Immediate Window)
• 편집 도구바(주석 버튼 사용): 메뉴바의 보기(View) → 도구 모음(Toolbars) → 편집(Edit)
• 메뉴바의 도구 → 옵션 → 편집기 형식: 폰트 등 설정 가능

프로그램의 구성 요소

1. 프로젝트(Project): 파일에 포함된 모든 코딩 내용
2. 모듈(Module): Procedure의 집합 (레고 블록 처럼 갖다 쓸 수 있음)
3. 프로시져(Procedure): 일련의 동작을 위한 단위 프로그램
4. 코드(Code): 프로그램 구성의 최소 단위 (Method, 속성 등)

• 이벤트(Event): 마우스 클릭, 활성 시트 변화 등  (개체의 이벤트에 따른 프로시져 코딩)
• 개체(Object): 작업 대상 (워크시트, 범위, 버튼, 애플리케이션 등)
• 속성(Property): 개체가 갖는 고유한 특성 (폰트의 색상 등)
• 메소드(Method): 개체가 행하는 동작 (select, activate 등)

주요 개체

• Workbook: 엑셀 파일
• ThisWorkbook : 이 코드가 포함된 엑셀 파일
• ActiveWorkbook : 활성 엑셀 파일
• Worksheets :활성 파일의 모든 워크시트
• Worksheets(" "): 특정 워크시트
• ActiveSheet : 활성 시트
• ActiveCell: 활성 셀
• Range: 엑셀에서 맨 하위 개체

주요 메소드

• .FillDown: 엑셀에서 Ctrl + D와 동일
• .AutoFill 채울 영역, 형식: 두개의 데이터 범위를 참조하여 동일 패턴으로 채움 ※ 형식: xlFillDays, xlFillSeries, xlFillMonths, xlFillDefault
• .Activate: 셀의 경우 범위로 지정해도 단일 셀만 활성화됨
• .Rows/Columns.Count: 행열의 개수
• .Clear (Content/Format/..?)
• .Copy Range("..")/Sheets("..").Range(".."): 복사해서 원하는 범위에 붙여넣기
• .End(xlDown..): 연속된 범위의 끝 데이터로 이동
• .PasteSpecial(xlPasteAll/xlPasteFormats/xlPasteValues, xlPasteSpecialOperationAdd/Multiply, Transpose:= True)
• WorkSheets.Add [after/before:=지정시트]: 시트 추가 (현재 시트의 before가 default임)
• WorkSheets("xx").Move/Copy After/Before:=WorkSheets("yy")

주요 속성

• .Name
• .Borders.Weight: 테두리 두께 (ex xlMedium, xlThin, xlThick...)
• .Borders.LineStyle: 테두리 스타일(ex xlContinuous, xlDash ..)
• .Formula = "=수식"
• .Interior.Color: 채우기색
• .ColorIndex: 0~56까지 기본 색상
• .Rows/Columns: 특정 범위 내의 행/열 범위
• .Rows(#)/Columns(#): 특정 범위 내의 상대적인 행/열 범위
• .Row/Column: 행/열 번호
• .EntireRow/EntireColumn: 전체 행/열 범위
• .RowHeight/ColumnWidth: 높이와 폭
• .Address: 주소 표시 (옵션 없으면 절대 주소로 표시됨) ※ 옵션(행,열) 1:Absolute, 0:Relative
• .offset(행,열)

프로시져

• 생성: 메뉴바의 삽입 → 프로시져: 이름 입력 및 형식, 범위 선택
• public: 프로젝트의 다른 곳에서 모두 호출 가능
• private: 해당 모듈 또는 워크시트 내에서만 호출 가능

단축키

• Alt + F11로 편집기 ↔ 엑셀 스위칭
• Alt + F8로 매크로 실행창 열기
• F4: 속성창 보이게 하기
• F8: 디버깅을 위해 한 줄씩 실행 

변수(Variable)

• 선언(declaration): 변수명과 변수타입을 선언함으로써 유지보수 및 메모리를 효율적으로 운영할 수 있도록 함.
• 변수 범위
  • dim(지역변수): 단일 function, procedure에서만 사용
  • static(정적변수): 프로시져 내부에서만 사용 가능, 프로그램 종료 시까지 값을 유지 (변수에 값을 누적시키면서 반복하여 매크로 사용시에 활용 가능, 프로그램 새로 시작하면 초기값으로 시작)
  • public(전역변수): 프로젝트 내 모든 개체에서 사용할 수 있음, 프로그램 종료시까지 그 값을 유지
• Option Explicit: 변수 선언을 필수로 요구하며 변수 선언이 누락되면 오류 발생토록 함 (도구 → 옵션 → 편집기탭에서 default로 설정 가능)
• 변수 타입
  • variant: 타입 미지정 (dim 변수이름만으로 선언)
  • single, double: 실수값(소수점 표시) ※ 계산값의 소수점 표시가 이상할 때 double로 지정하면 됨
  • bite(0 ~ 255), integer(~32767), long: 정수
  • date
  • object
  • string
  • currency
  • 개체변수: worksheet, range 등

변화 감지하여 실행

• Worksheet_Activate(): 시트가 활성화될 때 실행
• Worksheet_SelectionChange(ByVal Target As Range): 셀 선택이 될 때 실행

자동필터 다루기

텍스트를 포함하는 조건으로 필터링

ActiveSheet.Range("~").AutoFilter Field:=2, Criterial1:="=*XX*", _
Operator:=xlOr, Criteria2:="=*ZZ*"

축약형

ActiveSheet.Range("~").AutoFilter 2, "*XX*", xlOr, "*ZZ*"

복수 조건 Array 활용

ActiveSheet.Range("~").AutoFilter Field:=2, Criteria1:=Array("XX", "ZZ", "YY"), Operator:=xlFilterValues

반복문

For / Do Until(While)

Dim i, j As Integer
For i = 1 to 5
    For j = 2 to 7
        실행문
    Next j
Next i

DO UNTIL(WHILE) 조건식
	실행문
LOOP

 For Each

For Each ws In 참조개체 '예, Worksheets, range("~~")'
	실행문
Next (변수)

※ 만족 조건을 찾아 더 이상 for 반복이 필요 없는 경우 for를 빠져나오도록 한다. → exit for 명령 추가

With 구문

With 대상개체명
	.속성1=값
	.속성2=값
End With

Select Case

수식 조건

Select Case Range("D4")
	Case Is >= 4
		Range("D4").Interior.Color=RGB(#,#,#)
	Case 1 To 3
		...
	Case Else
		...
End Select

텍스트 조건

Select Case #
	Case 1:
		...
	Case 2:
		...
End Select

상대 참조 수식 입력 방법

rng를 기준으로 수식을 입력함

rng.FormulaR1C1="=sum(RC[-2]:RC[-1])"

문자열 주요 함수

• Instr(시작위치, 원본문자열,찾을문자): find와 동일 ※ 없으면 0 반환
• InStrRev(원본문자열,찾을문자,[시작1 or 마지막-1(생략가능)]): find와 동일하나 마지막으로 찾은 위치도 반환 가능 (Reverse)
• Lcase : 소문자로 변환
• Ucase : 대문자로 변환
• Ltrim : 왼쪽 공백 제거
• Rtrim : 오른쪽 공백 제거
• Trim : 좌우 공백 제거
• Len : 문자열의 길이 반환
• Left, Right
• STR: 숫자를 문자로 변환
• Strcomp(str1, str2,(vbtextcompare)): 문자 비교 후 결과 반환 (동일 0, 1, -1) ※ text, binary(생략 시)에 따라 소문자 대문자 구분 차이

산술 함수

• Int(값): 정수로 표시
• Abs(값): 절대값으로 표시 
• Rnd() : 난수(random 값) 0 ~ 1 표시 ※ Int(Rnd * 100) + 1
• Round(값,자리수): 반올림
• Val(문자열): 문자열을 숫자로 변환
• Isnumeric(문자열): 숫자로 표현가능 여부

날짜/시간 함수

• Date: 오늘 날짜 ※ Year(Date): 올해 표시
• Year/Month/Day(날짜): 연월일만 추출하여 표시
• DateAdd(구분,숫자,날짜): 일월년을 선택해서 원하는 숫자만큼 더해서 표시 ※ 구분: "y"/"m"/"d"
• DateSerial(Y,M,D): 정수로 입력 받아 날짜로 표시 ※ DateSerial(Year(Date),Month(Date)+1,0): 이 달 말일 표시
• DateDiff(구분,시작일,기준일): 구분(연,분기,월,분,초 등)별로 날짜/시간 차이 계산 (구분 참조)
• DatePart("간격",날짜): 날짜의 위치 (오늘은 몇 분기?) ※ DatePart("q",Date)
• Weekday(날짜): 요일을 숫자로 표시
• Time:현재 시간
• Hour/Minute(시간): 시/분만 표시
• IsDate(값): 날짜로 표시 가능 여부

 기타 Is 함수

• IsNull: lookup 등 시도 시 해당 값이 없는 경우
• IsEmpty: Null 또는 빈셀인지 여부 ※ ex) 빈 셀이면 0을 입력하라
• IsBlank: Null, 빈셀 또는 스페이스만 채워진 건지 여부

배열

하나의 변수에 여러 데이터 값을 저장하기 위해 사용

• 1차원 배열 형식: 변수(첨자), 변수(첨자1 to 첨자2) ※ Dim A(5) as Integer: A변수에 0~4까지 공간 할당
• 다차원 배열 형식: 변수(R, C), 변수(R1 to R2, C1 to C2)
• 동적 배열 형식: 변수() -> ReDim  변수(첨자)
  ※ ReDim 시 변수에 저장된 값이 사라지며, 유지 시에는 Preserve를 사용 (ReDim Preserve 변수(첨자)

※ 첨자를 0이 아닌 1부터 할당하고자 하면, Option Explicit 아래나 위에 Option Base 1을 추가해준다.

array(값1,값2,...): 배열로 한 번에 할당 ※ 첨자 미할당 시에도 자동으로 됨

Dim arr As Variant
arr = Array("값1", "값2",...)
range("A3").Resize(1,3).Value = arr

arr = Array("일","월","화","수","목",...)
Msgbox (arr(Weekday (date) - 1))

배열 크기 계산

• Lbound(배열명,[차원]): 배열의 첨자를 확인
• Ubound(배열명,[차원]): 배열의 첨자를 확인

arr = Array("값1", "값2", ...)
For i = 1 to UBound(arr)
	Cells(3+i,3).Value = arr(i)
Next i

에러 처리

• On Error Resume Next: 에러 무시
• On Error Goto err_handle: 에러 시 점프하여 진행
  Exit Sub
  err_handle:

 

사용자 정의함수

용어

• 인수(매개변수, Argument)

함수 형식

Public (또는 Private) Function 함수명(인수1, 인수2..)
	명령어
	함수명 = 결과값
End Function

함수 생성

메뉴바 → 삽입 → 모듈 클릭 후 코드 직접 입력

기타 유용한 코드

연속된 데이터의 마지막 셀

특정 데이터 범위 시작셀에서 아래쪽 끝까지 연속된 셀을 찾은 뒤 행 번호 확인

Range("B4").End(xlDown).row

 

관련 포스팅

[Excel/VBA] 셀과 범위 선택하기 총정리!

 

Excel 함수와 VBA 내장 함수 비교