Macro & VBA Page 1 1. Macro & VBA ~ 1) Macro 매크로란일련명령어의조합으로자주사용하는일련의명령어를기록해두었다가단축키나버튼클릭만으로반복재생할수있게한다. 매크로를사용함으로반복적인작업을간단하게실행시킬수있고, 복잡한계산과정및작업과정을단순화할수있

Transcription

1 Macro & VBA Page 1 1. Macro & VBA 1) Macro 매크로란일련명령어의조합으로자주사용하는일련의명령어를기록해두었다가단축키나버튼클릭만으로반복재생할수있게한다. 매크로를사용함으로반복적인작업을간단하게실행시킬수있고, 복잡한계산과정및작업과정을단순화할수있다. 2) VBA(Visual Basic Application) 매크로는내부적으로어플리케이션을위한비주얼베이직언어로작성된다. 매크로를기록하는 언어가 VBA 이므로매크로와 VBA 는결국같은것이라고할수있다. 3) 매크로가필요한경우 반복적이거나복잡한일련의엑셀작업을하나로묶어서재사용할때 엑셀기본함수로지원되지않는계산이나복잡한계산식을쉽게사용가능하도록새로운함수를정의할때 엑셀의기본기능으로처리할수없는새로운기능을만들어사용할때 엑셀을기본으로하는전문적인프로그램을개발할때 2. VBA 프로그램의구성 1) 애플리케이션 (Application) 애플리케이션은액셀, 워드, 액세스, 파워포인트와같은응용프로그램을말하며, 액셀에서는현재실행중인프로그램인액셀자체를의미한다. VBA 코드의최상위계층을말하며, 액셀에서는액셀자체를의미하기때문에액셀의기능이나정보를확인할때사용한다. 2) 프로젝트 (Project) 프로그램을구성하는모듈과폼, 클래스모듈의집합을의미하는것으로액셀 VBA 에서는하나 의통합문서에작성되는모든 VBA 코드내용을하나의프로젝트라보면된다. 3) 모듈 (Module) 프로시저의집합으로, 프로젝트를구성하는기본단위이다. 모듈은표준모듈과폼모듈, 클래스 모듈등으로구분된다. 표준모듈 폼모듈 클래스모듈 워크시트모듈 (Sheet 로표시되는모듈 ) 과 ThisWorkbook 모듈, 공용모듈 ( 일반적으로사용하는모듈 ) 이있다. 워크시트모듈은각각하나씩만들어지며, ThisWorkbook 모듈은통합문서즉, 액셀파일하나에하나씩만들어진다. 사용자정의폼을정의하고사용자정의폼의컨트롤에이벤트프로시저를작성하는모듈 개체를새롭게정의해서사용할수있도록작성하는모듈로개체의속성, 매서드, 이벤트를정의하는모둘

2 Macro & VBA Page 2 4) 프로시저 (Procedure) 특정기능을실행하기위해모여진명령문집합으로, 실행방법에따라 Sub, Function, Property 로구분된다. 매크로기록기를이용해작성한 VBA 코드는 Sub 프로시저를사용하는것이고, 액셀의함수라고불리는기능은 Function 프로시저를, 개체의속성을새로정의할때는 Property 프로시저를사용한다. 5) 사용자정의폼 (User Form) 자료의입출력을효과적으로하기위한대화상자로, 액셀 VBA를이용해사용자가직접설계한대화상자를만들때사용한다. 폼은하나의정보를입출력할수있는단위인컨트롤개체로구성된다. 3. 프로시저의구성 1) 개체 (Object) 개체란분리될수있는하나하나의작업단위이다. 액셀에서는통합문서도하나의개체이고, 통합문서를구성하는시트, 셀, 도형들도개체이다. 한마디로개체란어떤작업의대상이될수있는것을말한다. 2) 컬렉션 (Collection) 관련있는한개이상의개체집합을말하는것으로한꺼번에특정개체들에대한작업을처리 할때사용하는개념으로일반적으로개체이름에 S 를붙여사용한다. Sheet worksheet Activecell Sheets worksheets Selection 3) 속성 (Property) 개체의크기, 색, 모양등과같은개체의특성이나상태를말하는것으로 VBA 코드에서는개체 와속성사이에마침표를찍어구분한다. 지정된속성을표시할때 새로운속성을지정할때 개체. 속성 MsgBox Sheet1.Name 개체. 속성 = 새로운속성값 Sheet1.Name= 일사분기 4) 매서드 (Method) 개체가실행할수있는동작, 행동을의미한다. 개체. 매서드 로사용

3 Macro & VBA Page 3 5) 이벤트 (Event) 개체가어떤행동을할때발생하는사건을말한다. 즉, 액셀에서는통합문서를열때나닫을때, 키보드를누를때등통합문서개체의이벤트이다. 6) 클래스 (Class) 개체의형식적인정의를의미한다. 개체의속성과동작등을제어하는방법을정의함으로써개 체의모습을완성시킨다. 4. VBA 연산자 1) 연산자의종류와우선순위 우선순위 산술 비교 논리 높음 지수 (^) 같다 (=) Not 부정 (-) 같지않다 (<>) And 곱셈 (*), 나눗셈 (/) 작다 (<) Or 정수나눗셈 (\) 크다 (>) Xor 나머지연산 (Mod) 작거나같다 (<=) Eqv 덧셈, 뺄셈 (+, -) 크거나같다 (>=) 문자열연결 (&) Like Is 낮음높음 낮음 2) VBA 추가연산자사용법 연산자 기능 사용예 \ 정수나눗셈 나눗셈의몫을구함? 11\4 Mod 나머지연산 나눗셈의나머지를구함? 11 Mod 4 Like Is 문자열의일부포함된값비교? : 한문자 * : 0 개이상의문자 # : 한숫자 (09) [charlist] : charlist 안의한문자 [!charlist] : charlist 안에없는문자 두개체가같은지를비교할때사용하며, 일반적인숫자, 문자열등의비교에는사용하지않는다.? horizons like hor*? 신림1동 like 신? 동? F like [AZ]? F like [!AZ]? Sheets(1) is ActiveSheet

4 Macro & VBA Page 4 5. 변수 & 상수 1) 변수프로그램처리과정에서중간계산값이나결과값들을잠시보관해야할필요가있는데, 이런경우변수를이용하여자료를보관한다. 변수는컴퓨터의메모리중일부에이름을정의하여사용하는것을말하며, 이렇게정의된메모리, 즉변수이름에값을기억시킬때는 변수 = 값 형태로사용된다. 2) 변수선언 Dim 변수이름 [As 데이터유형 ] 변수선언문설명 Dim int나이 As Integer Dim str성명 As String Dim rng시작 As Range Dim sht기초 As Worksheet 정수만기억하는변수선언텍스트를기억하는변수선언셀영역을기억하는변수선언워크시트를기억하는변수선언 3) VBA 데이터형식종류 데이터형식저장용량데이터형식저장용량 Byte 1 바이트 Currency 8 바이트고정십진소수 Integer 2 바이트정수 String 문자열 Long 4 바이트정수 Variant 16 바이트 Single 4 바이트실수 Boolean 2 바이트 Double 8 바이트실수 Object 4 바이트 Date 8 바이트사용자정의형식 type 문으로선언된요소들이가진크기 4) 줄변경에사용되는내장상수 vbcr, vbcrlf 등은 MsgBox 나셀에내용을입력할때줄을변경하기위해사용하는상수이다. 내장상수설명 vbcr vblf vbcrlf Carrage Return( 줄변경 ) 기능으로, MsgBox 에서는줄이변경되지만, 워크시트셀에서는줄변경되지않는다. LineFeed( 줄이동 ) 으로일반적인경우 vbcr 과별차이가없으나, 셀에서의줄변경시에이용된다. 줄변경과줄이동을동시에사용

5 Macro & VBA Page 5 6. MsgBox 함수 1) 형식 간단한메시지내용을출력할때주로사용. 사용방법에따라단순한출력용도로만사용할수 도있고, 몇개의단추를표시한후선택한단추종류에따라다른작업을처리할수도있다. 메시지만출력 MsgBox 메시지내용 [, 버튼종류 ] [, 제목 ] 선택한단추값반환변수 =MsgBox( 메시지내용, 버튼종류 [+ 아이콘종류, 제목 ] ) 2) MsgBox 버튼종류 상수 값 설명 vbokonly 0 [ 확인 ] 단추만 vbokcancel 1 [ 확인 ] [ 취소 ] vbabortretryignore 2 [ 중단 ] [ 재시도 ] [ 무시 ] vbyesnocancel 3 [ 예 ] [ 아니오 ] [ 취소 ] vbyesno 4 [ 예 ] [ 아니오 ] vbretrycancel 5 [ 재시도 ] [ 취소 ] 3) MsgBox 아이콘종류 상수 값 아이콘 설명 vbcritical 0 중대메시지 vbquestion 1 질의경고 vbexclamation 2 메시지경고 vbinformationl 3 메시지정보 7. InputBox 함수 1) 형식 MsgBox 와반대로데이터하나를입력받을때사용한다. 반환되는값은항상문자열형태이므로 범위를반환받거나날짜와같은특별한형태의자료를반환할때는사용할수없다. 변수 = InputBox( 메시지내용 [, 제목 ] [, 기본값 ] [, 가로위치 ] [, 세로위치 ] )

6 Macro & VBA Page 6 2) 실습예제 8. InputBox 매서드 1) 형식 InputBox 함수는문자열로모든자료를입력받기때문에셀영역등의값은입력받을수없다. 셀영역을개체형태로입력받을때는반드시 Application.InputBox 매서드를사용해야한다. 형식과사용법은 InputBox함수와유사하지만반환데이터형식을 type이란인수를이용하여지정할수있다. Application.InputBox( 메시지내용, 제목, Type:=8 ) 2) 사용방법 Dim 변수명 As Range Set 변수 = Application.InputBox( 메시지내용, 제목, Type:=8 ) 3) 실습예제

7 Macro & VBA Page 7 9. Range 개체 1) Range 속성 - 작업영역지정 개체.Range( 시작셀 [, 종료셀 ] ) Range 속성셀참조예제 Range("A1").Select Range("A1:A10").Select Range("A1", "A10").Select Range("A1, A10").Select Range("Myrange").Select 실행결과 A1 셀을선택 A1:A10 셀범위를선택 A1 A1셀과 A10셀을선택 Myrange' 로정의된이름범위를선택 Ex) Range("A1").Value = 100 Range("A1","A10") = "Excel"

8 Macro & VBA Page 8 2) Cells 속성 - 행열좌표에의한셀지정 개체.Cells( 행번호 [, 열번호 ] ) 개체영역에서행과열번호에해당하는위치에있는한셀을반환한다. 이속성은숫자를이용 해한셀을지정하기때문에 For 문등과같은반복문에서동적으로셀위치를지정할때자주 사용한다. 3) Offset 속성 상대적위치의작업범위지정 Range 개체.Offset( 이동행수, 이동열수 ) 기준셀영역으로부터행과열에지정된숫자만큼상대적으로이동한셀영역을반환한다. 행과열의이동에사용하는숫자는음수, 0, 양수모두사용할수있는데, 양수이면행 / 열방향으로, 음수이면반대방향으로이동한다. Cells 속성은하나의셀을반환하지만, Offset속성은기준셀영역을어떻게지정하는지에따라하나이상의영역을반환한다. Offset속성도 Cells 속성과마찬가지로숫자를이용해상대적인위치를지정하므로 For문과같은반복문에서자주사용한다. 4) CurrentRegion / End 속성 - 연속영역지정 Range 개체.CurrentRegion Range 개체.End( 방향 ) Sheet 개체.UsedRange 상하좌우연속된데이터영역 특정방향으로연속된데이터영역 특정시트에서사용된셀영역 정확한행과열의크기를알지못하는상태에서, 현재셀부터데이터가입력된연속셀영역을 블록으로설정해야하는경우처럼연속셀설정에사용한다.

9 Macro & VBA Page 9 Range 개체.CurrentRegion Range 개체.End( 방향 ) 이동방향 End(xlUp) End(xlDown) End(xlToLeft) End(xlToRight) 기능연속데이터의위쪽끝셀연속데이터의아래쪽끝셀연속데이터의왼쪽끝셀연속데이터의오른쪽끝셀 Sheet 개체.UsedRange 5) Columns / Rows / EntireColumns / EntireRows - 행 / 열단위의작업범위지정 개체.Columns( 열범위 ) 개체.Rows( 열범위 ) Range개체.EntireColumn Range개체.EntireRow 지정한개체에서해당열전체영역반환지정한개체에서해당행전체영역반환지정된셀영역을포함하는열전체반환지정된셀영역을포함하는행전체반환

10 Macro & VBA Page 10 Columns, Rows 속성 EntireColumn, EntireRow 속성 6) SpecialCells - 조건에맞는작업영역선택 형식 : Range 개체.SpecialCells( 종류, [, 값종류 ] 특정셀영역에서빈셀, 또는수식을입력한셀만을대상으로작업해야하는경우 [ 홈 ] 탭 - [ 편집 ] 그룹 - [ 찾기및선택 ] - [ 이동옵션 ] 을선택, [ 이동옵션 ] 대화상자를열고해당기능을처 리하는데 VBA 에서는 SpecialCells 매서드를이용한다.

11 Macro & VBA Page 11 7) Value, Text, Formula 속성 Value Text Formula FormulaR1C1 셀내용을지정하거나표시 셀내용을셀서식이적용된형태의텍스트로표시 셀내용을셀서식이적용된형태의텍스트로표시 Formula 속성과기능이같고수식지정시 Row 번호와 Column 번호로셀주소지정 8) PasteSpecial 메서드 - 선택하여붙여넣기 Range 개체.PasteSpecial (Paste, Operation, SkipBlanks, Transpose)

12 Macro & VBA Page 12 9) 기타속성 ActiveCell Address / AddressLocal Areas Borders ColumnWidth Dependents DirectDependents DirectPrecedents EditDirectlyInCell FormulaArray FormulaHidden FormulaLabel HasFormula Height / Width Hidden HorizontalAlignment VerticalAlignment ActiveCell.Font.Bold = True 셀주소를반환비연속적으로선택된전체범위를반환네개의셀테두리를반환열의너비를반환및설정셀이참조되고있는셀범위를반환셀이직접참조되고있는셀범위를반환셀이직접참조하고있는모든셀범위를반환셀을더블클릭하여편집못하도록설정배열수식을반환및설정수식입력줄에수식이나타나지않도록설정수식레이블을반환및설정셀에수식이있는지조사셀의높이와너비를반환행과열의숨기기를설정문자열의맞춤을설정 Interior 셀의내부를참조 ( 셀음영색등 ) MergeArea MergeCells Name NumberFormat Precedents Previous / Next ReferenceStyle Row / Column RowHeight ShrinkToFit Text UsedRange Value / Value2 WrapText 병합된셀범위를참조 셀범위의병합및병합취소설정 셀의이름을반환및설정 셀의표시형식을반환및설정 셀이참조하고있는모든셀범위를반환 앞쪽과뒤쪽셀을참조 A1 참조형식과 R1C1 참조형식을반환및설정 셀범위의최초행번호, 열번호를반환 행의높이를반환및설정 문자크기를열너비로축소 셀의문자열을반환 사용하는셀범위를반환 셀값을반환및설정 Value2 에는통화및날짜형식을사용할수없다. 문자크기를열너비에맞춤

13 Macro & VBA Page 13 10) Range개체매서드 Activate AddCommnet AdvancedFilter AutoFill AutoFilter AutoFit Clear ClearComments ClearContents ClearFormats ClearNotes ClearOutline Delete Insert Paste PasteSpecial Select 셀을활성화범위에메모를추가고급필터하기자동채우기설정자동필터하기지정범위내의열너비, 행높이를가장알맞게조정셀범위의모든것지우기셀범위의메모지우기셀범위내의내용및수식지우기셀범위내의서식지우기셀범위에서메모와소리지우기셀범위에서테두리지우기셀삭제빈셀을삽입셀을붙여넣기선택하여붙여넣기셀을선택 11) 색지정하기 속성 ( 매서드 ) 형식기능 내장상수 Color 속성 RGB 함수 QBColor 함수 개체.Color = 색번호 내장상수, RGB 함수, QBColor 함수 ColorIndex 속성개체.ColorIndex = 색번호 156 까지의번호이용 ThemeColor 속성개체.ThemeColor = 색번호테마에따라테마색이용

14 Macro & VBA Page 14

15 Macro & VBA Page VBA 제어문 1) 단순 if 조건을만족할때만작업실행 If 조건식 Then 실행문 End If 조건을만족할때와그렇지않은때나눠서처리 If 조건식 Then 조건을만족할때실행문 Else 조건을만족하지않을때실행문 End If 2) 다중 If 조건에따라세가지이상의처리방법으로처리 If 조건식1 Then 조건1을만족할때실행문 ElseIf 조건2를만족하지않을때실행문 [ElseIf 문반복 ] Else 조건1,2를모두만족하지않을때실행문 End If

16 Macro & VBA Page 16 3) And/Or 사용하여여러조건체크하기 조건 1 And 조건 2... 조건 1 Or 조건 2... If 문에서조건을지정할때여러가지조건을함께확인해야할경우에사용 4) Select Case 문으로다중조건처리하기 Select Case 식 ( 변수 ) Case 값 1 실행문 1 [Case 값 2 실행문 2 CaseElse 실행문 n] End Select

17 Macro & VBA Page VBA 반복문 1) For문 For 카운트변수 = 초기치 To 최종치 [Step 증감치 ] 실행문 Next [ 카운트변수 ] 정확한반복횟수를알경우사용 2) For Each문셀영역이나여러워크시트, 여러통합문서처럼개체들의집합 ( 컬렉션 ) 을대상으로한개씩개별개체에대한방복작업을처리할때사용한다. For Each문은개체를대상으로반복하기때문에개체변수는반드시컬렉션개체와동일한데이터형식으로지정해야한다. For Each 개체변수 In 컬렉션개체실행문 Next

18 Macro & VBA Page 18 3) Do While문여러번반복처리할때반복할횟수를알고있다면 For문을사용하지만, 반복횟수를알수없고특정조건을만족허가나반대로만족하지않을때까지반복해야하는경우에는 Do문을사용 Do While문은조건식의결과가 True 값을가지는동안만반복한다. Do While 조건문 Loop 실행문 4) Do Until 문 Do While 문이조건식의결과가 True 값을가지는동안만반복하는경우에반해 Do Until 은조건 을만족할때까지반복한다. 즉, 조건식의결과가 False 값을가지는동안만반복한다. Do Until 조건문 Loop 실행문

19 Macro & VBA Page 시트데이터통합하기 1) 머리글항목을복사하는프로시저 Option Explicit Dim SumSheet As Worksheet ' 머리글항목복사 Private Sub 머리글복사 () Set SumSheet = Worksheets(1) Worksheets(2).Range("a2", Worksheets(2).Range("a2").End(xlToRight)).Copy SumSheet.Range("b2").PasteSpecial SumSheet.Range("a2") = " 월구분 " Application.CutCopyMode = False End Sub 2) 각시트별내용복사프로시저 ' 내용을복사하는프로시저 Private Sub 내용복사 () Set SumSheet = Worksheets(1) Dim irow As Integer Dim isheet As Integer For isheet = 2 To Worksheets.Count With Worksheets(iSheet) irow = SumSheet.Range("A2").CurrentRegion.Rows.Count + 1 SumSheet.Cells(iRow, 1) =.Name.Range(.Range("a3").End(xlDown),.Range("a3").End(xlToRight)).Copy SumSheet.Cells(iRow, 2).PasteSpecial End With Next Application.CutCopyMode = False End Sub

20 Macro & VBA Page 20 3) 월채우기프로시저 'A 열에월채우기 Private Sub 월채우기 () Set SumSheet = Worksheets(1) Dim strmon As String SumSheet.Range("a3").Select Do While ActiveCell.Offset(0, 1) <> "" If ActiveCell <> "" Then strmon = ActiveCell Else ActiveCell = strmon End If ActiveCell.Offset(1, 0).Select Loop End Sub 4) 세개의프로시저를순서대로실행하는메인프로시저 Sub 시트데이터통합 () Application.ScreenUpdating = False Call 머리글복사 Call 내용복사 Call 월채우기 Application.ScreenUpdating = True End Sub

21 Macro & VBA Page 여러파일데이터통합하기 1) 프로그램에서파일열기 Option Explicit Dim SumSheet As Worksheet Sub 파일데이터모으기 () Dim fileno As Variant Dim i As Integer Dim wb As Workbook Dim irow As Integer Set SumSheet = ThisWorkbook.Worksheets(1) On Error GoTo 오류처리 ' 오픈할파일이름얻어오기 fileno = Application.GetOpenFilename(filefilter:=" 엑셀파일 (*.xls*), *.xls*", _ MultiSelect:=True) Application.ScreenUpdating = False Application.DisplayAlerts = False ' 파일을열고내용복사 For i = 1 To UBound(fileNo) Set wb = Workbooks.Open(Filename:=fileNo(i), ReadOnly:=True) irow = sumsheet.range("a3").currentregion.rows.count + 3 SumSheet.Cells(iRow, 1) = wb.sheets(1).name With wb.sheets(1).range(.range("a3").end(xldown),.range("a3").end(xltoright)).copy End With SumSheet.Cells(iRow, 2).PasteSpecial wb.close Next Call 월채우기 Application.ScreenUpdating = True Application.DisplayAlerts = True Exit Sub 오류처리 : MsgBox " 파일을선택하지않았습니다." End Sub

22 Macro & VBA Page 22 Private Sub 월채우기 () Set SumSheet = Worksheets(1) Dim strmon As String SumSheet.Range("a3").Select Do While ActiveCell.Offset(0, 1) <> "" If ActiveCell <> "" Then strmon = ActiveCell Else ActiveCell = strmon End If ActiveCell.Offset(1, 0).Select Loop End Sub