CH10)VBA 스프레드시트일반 1-VBA(Visual Basic for Application) 1)VBA(Visual Basic for Application) MS-오피스사에서사용되는매크로작업용언어로 Visual Basic언어와유사한문법구조 VBE(Visual Bas

Transcription

1 1-VBA(Visual Basic for Application) 1)VBA(Visual Basic for Application) MS-오피스사에서사용되는매크로작업용언어로 Visual Basic언어와유사한문법구조 VBE(Visual Basic Editor) 는 VBA언어로 - 작성된매크로를수정 - 다양한개체의속성을편집 - 직접매크로를작성할때사용하는편집기 VBE(Visual Basic Editor) 실행방법 - 메뉴-[ 도구 ]-[ 매크로 ]-[Visual Basic Editor]( 바로가기키 : + ) - 메뉴-[ 도구 ]-[ 매크로 ]-[ 매크로 ]( + ) 에서 편집 클릭 - 시트탭의바로가기메뉴에서코드보기선택 - 매크로실행개체의바로가기메뉴에서 [ 매크로지정 ] 의 편집 선택 2) 프로그래밍의기본 1 모듈 (Module) : 프로시저의집합 ( 표준모듈, 폼모듈, 클래스모듈 ) 2 프로시저 (Procedure) : 특정기능을실행할수있도록나열된명령문의집합 Sub ~ End Sub Function ~ End Function ( 사용자정의함수 ) Property ~ End Property 프로시저내에작성된코드를실행하는가장일반적인형태로결과값을반환하지않음 작성된 Sub프로시저는워크시트에서매크로처럼연결하여사용가능 프로서지내에작성된코드를실행하고실행된결과값을반환하는함수 작성된Function프로시저는워크시트에서일반내장함수와같은방법으로사용 개체의속성을정의할때사용하는것으로반환값이있음 [ 사용자정의함수 ] 는아래와같이 VBE 에입력시함수마법사에나타남 프로시저적용범위 엠엔디시스템 ( 1/11

2 public 모듈전체의모든프로시저에서해당프로시저를호출 private 해당프로시저가속한모듈내에서만해당프로시저호출 friend static 클래스모듈에사용 모듈전체의모든프로시저에서해당프로시저호출 static으로선언된프로시저내의변수들은프로그램이종료될때까지값이유지 해당프로시저가속한모듈내에서만해당프로시저호출 3 개체 (Object) - 프로그래밍과정에서작업한내용이실제적용되는독립된대상 - 개체와매서드또는개체와속성은마침표 (.) 로구분 - 통합문서, 셀, 차트, 폼같은엑셀의구성요소를말함 4 속성 (Property) - 크기, 색, 화면위치와같은개체의특성이나가능, 불가능과같은개체의상태 - 개체명, 마침표 (.), 속성이름, 등호 (=), 새속성값차례로입력 5 메서드 (Method) - 개체가실행할수있는동작, 행동으로서특정개체에서만실행할수있는프로시저 6 이벤트 (Event) - 프로그램사용중에일어나는사건 ( 마우스클릭또는셀이동등 ) 을의미 - 사용자가마우스또는키보드를움직이는동작이나프로그램의실행결과로인해서발생 - 이벤트프로시저 : 이벤트가일어났을때실행되도록작성된프로시저 - 프로시저를구분할때는개체이름과이벤트이름사이에밑줄 (_) 넣어구분 7 컬렉션 (Collection) - 개체모음, 개체이름에복수형 -s를붙임 1 매크로실행 : 매크로를실행할수있게매크로대화상자가열림 2 매크로기록 : 새로운매크로를기록할수있게매크로기록대화상자가열림 3 보안 : 보안수준을설정할수있게보안대화상자열림 4 VBE(Visual Basic Editor) 가나타남 컨트롤도구상자를표시 : 컨트롤도구상자실행 6 디자인모드 : 워크시트에삽입된컨트롤도구를편집하거나실행하기위해작업 모드로전환 7 Microsoft Script 편집기가실행 2-VBA 문법 1) 상수 변수 배열 1 상수 (Constant): 프로그램처리과정에서변함없는항상같은값 2 변수 (Variable): 컴퓨터가명령을처리하는도중발생하는값을저장하기위한공간으로변할수있는값 - 변수이름의길이는 255자내이고, 예약어는사용불가능 - 변수이름의첫글자는반드시영문이나한글로시작해야하고, 영문, 숫자, 한글, 밑줄 (_) 혼합사용가능 - 문장부호나공백을포함할수없음 - 사용자가사용하는모든변수를선언하도록하려면 Optical Explicit를첫줄에삽입 Optical Explicit은모듈의선언부에삽입하여프로시저내에선언되지않은변수를사용할경우에러표시 3 변수선언 엠엔디시스템 ( 2/11

3 1 키워드 ( 예약어 ): VBA에서사용하기위해미리정의해둔명령어 2 변수이름 - 사용자가임의로지정한것으로한프로시저내에서동일한변수이름이사용되지못함 Dim A as Integer 키워드 + 변수이름 + As 데이터유형 변수선언키워드종류 - DIM: 프로시저의처음에선언하면해당프로시저내에서만사용가능모듈의처음에선언하면해당모듈내에서만사용가능 - State: 모듈의처음에선언, 해당모듈내의모든프로시저에서사용이가능프로시저종료후에도값유지 - Private: 모듈의처음에선언, 해당모듈내의모든프로시저에서사용이가능 - Public: 모듈의처음에선언, 모든모듈에서사용이가능 3 데이터유형 - 변수에담길자료의크기나유형 ( 숫자인지, 문자인지등 ) 을지정. 생략시 Variant형으로자동지정 변수선언예 숫자데이터형식 Dim 영어 As Integer -영어를정수로저장할수있는변수로선언 Dim 총점 As Long -총점을긴정수형으로저장할수있는변수로선언 Dim 평균 As Single -평균을 Single형으로저장할수있는변수로선언 String 데이터형식 Dim 과목명 As String - 과목명을문자로저장할수있는변수로선언 4 배열 (Array) - 동일한데이터유형을여러개사용한경우, 모든데이터를하나의변수이름으로정의해서사용 - 배열을선언할때이름다음에 () 를만들어배역의크기지정 - 배열의위치 ( 첨자 ) 는 0부터시작, Option Base 1' 을선언하면배열의위치 ( 첨자 ) 는 1부터시작 5 데이터유형 엠엔디시스템 ( 3/11

4 데이터유형크기설명 바이트 (Byte) 1 바이트 까지의정수를저장할수있는데이터형 음수의값은저장할수없음 Boolean 2 바이트 true, false 정수형 (Integer) 2 바이트 -32,768 32,767 긴정수형 (Long) 4 바이트 -2,147,483,648 2,147,483,647 싱글 (Single) 더블 (Double) 4 바이트 8 바이트 E E-45 의음수 E E38 범위 ( 양수 ) 의단정도부정소수점의값을저장할수있는데이터형 E E-324 의음수 E E308 의양수 통화 (Currency) 8바이트 통화형을저장하기위한데이터형소수네자리까지의숫자를받음 -922,337,203,685, ,337,203,685, Date 8바이트 100년 1월 1일 ~ 9999년 10월 31일 Object 4바이트 set명령문을사용하여지정된개체참조 String( 가변길이 ) 10바이트 + 문자열길이 0 ~ 약 20억 String( 고정길이 ) 문자열길이 1 ~ 65,400 Variant( 숫자 ) 16바이트 DOUBLE형범위내의모든숫자 Variant( 문자 ) 22바이트 + 문자열길이 가변길이 String과같은범위 사용자정의형식 type 명령문으로선언된요소들이가진크기 type 명령문을사용해서만든사용자정의형식 3-VBA 문법 ; 제어문 1)IF...Then ~ End If 문 - 조건을만족할경우에만실행문을수행 엠엔디시스템 ( 4/11

5 조건을한번에만족하는경우 If 조건식 Then 실행문 < 조건을만족할때의실행문 > End if 조건을만족하는경우와그렇지않은경우 If 조건식 Then 실행문 1 < 조건을만족할때의실행문 > else 실행문 2 < 조건을만족하지않을때의실행문 > End if 여러조건을판별하여그에따라조건이나뉘는경우 If 조건식 1 Then 실행문 1 < 조건 1 을만족할때의실행문 > elseif 조건식 2 Then 실행문 2 < 조건 2 를만족할때의실행문 > elseif 조건식 3 Then 실행문 3 < 조건 3 를만족할때의실행문 >.. Else 실행문 < 위에서열거한조건이외의조건일때의실행문 > End if 2)Select Case ~ End Select 문 - 비교값을여러종류의값과비교하여해당조건에맞는문장을실행 조건이여러개일경우각조건별로실행문을실행 Select Case 수식 ( 또는변수 ) Case 값 1 실행문 1 < 수식의값이값 1 과같을때의실행문 > Case 값 2 실행문 2 < 수식의값이값 2 과같을때의실행문 >.. Case Else 실행문 3 < 수식의값이 Case 문에서열거한값에없을때실행문 > End Select 3)With ~ End With - 대상개체의이름을다시참조하지않고도지정된개체에대한여러개의명령문을수행 엠엔디시스템 ( 5/11

6 With 개체. 속성 End With With 명령문사용시 Selection.Font.Name = " 바탕 ".Size = 15.Bold = FALSE.Italic = TRUE End With 명령문사용하지않을시 Selection.Font Selection.Name = " 바탕 " Selection.Size = 15 Selection.Bold = FALSE Selection.Italic = TRUE End Selection( 대상개체이름 ) 을계속반복 4)Do While ~ Loop 문 - 조건을만족할때까지실행문을반복적으로수행 ( 조건을먼저검사 ) 5)Do ~ Loop While 문 - 먼저실행문를실행한후조건을만족할때까지계속적으로실행문을반복수행 ( 조건을나중에검사 ) 조건식을만족하지않을시한번도실행되지않음 Do While 조건식실행문 < 조건식의결과가참인동안실행문을반복실행 > Loop 조건식을만족하지않아도한번은무조건실행 Do < 실행문을실행한후조건식검사 > 실행문 < 조건식이참인동안실행문을반복실행 > Loop While 조건식 6)Do Until ~ Loop - 조건을만족할때까지반복실행 조건을먼저검사 ( 조건식을만족하면한번도실행되지않음 ) 조건을나중에검사 ( 조건식을만족하더라도한번은실행 ) Do Until 조건식 실행문 < 조건식의결과가참일때까지실행문반복실행 > Do 실행문 < 실행문을실행한후조건식검사. 조건식의결과가참일때까지실행문반복실행 > Loop Loop Until 조건식 7)While ~ Wend 문 - 조건식이참 (true) 인동안반복실행함 - 사용법이 Do While ~ Loop 문과동일 엠엔디시스템 ( 6/11

7 While 조건식실행문 < 조건식의결과가참인동안실행문을반복실행 > Wend 8) For ~ Next 문 - 지정한횟수만큼실행문을반복실행 - 증가값을지정하지않을경우 1 이증가값이된다 For 반복변수 = 시작값 To 최종값 [Step 증가값 ] 실행문 < 시작값에서최종값이될때까지증가값만큼씩증가하면서실행문을반복실행 > Next 반복변수 9)Exit 문 - For... Next 문, Do... Loop 문, Sub 프로시저, Function 프로시저등의작업을강제로중지 10)For Each ~ Next 문 - 컬렉션에포함된개체의수만큼명령코드를반복실행 For Each 개체변수 In 컬렉션개체실행문 < 컬렉션개체의수만큼명령코드를반복실행 > Next 개체변수 주석 : 코드내용을사용자가쉽게파악하도록프로시저나명령의내용을설명 - 주석사용방법 : 작은따옴표 ( ) 로시작하거나 Rem 뒤에공백을둠 ( 연두색글씨로표시 ) 4-VBA 문법 ; 입 출력문 1)MsgBox - 대화상자안에단순하게메시지를보여주거나, 여러단추를표시해클릭된단추에대한번호를정수형데이터유형으 로반환할수있음 - 형식 : MsgBox( 메시지, [ 버튼종류 ], [ 대화상자타이틀 ], [ 도움말파일, 도움말번호 ]) 상수 값 설명 vbokonly 0 < 확인 > 단추만표시 vbokcancel 1 < 확인 >,< 취소 > 단추표시 vbyesnocancel 3 < 예 >,< 아니오 >,< 취소 > 단추표시 vbyesno 4 < 예 >,< 아니오 > 단추표시 사용할아이콘지정하는상수 엠엔디시스템 ( 7/11

8 상수 값 설명 vbquestion 32 ' 질의경고 아이콘표시 vbexclamation 48 ' 메시지경고 아이콘표시 vbinformation 64 ' 메시지정보 아이콘표시 2)InputBox - 대화상자안에내용을입력할수있는입력란을보여주며, 사용자가내용을입력하고단추를누르면입력란의내용을포함하는문자열을반환하는대화상자를만듦 - 형식 : InputBox( 메시지, [ 대화상자타이틀 ], [ 기본값 ], [ 가로위치 ], [ 세로위치 ], [ 도움말파일, 도움말번호 ]) 5-Excel 개체 1)Application: 엑셀프로그램자체 2)Workbook 개체 : 엑셀통합문서또는엑셀파일을지칭, Workbooks 컬렉션의구성원 3)Worksheet 개체 : 엑셀시트, Worksheets 컬렉션의구성원 1 Worksheet 주요속성 속성 기능 속성 기능 Cells 워크시트의모든셀 Range 워크시트의셀이나셀범위 Columns 워크시트의모든열 Rows 워크시트의모든행 Entirecolumn 지정된범위내의모든열 Entirerow 지정된범위내의모든행 Name 워크시트의이름 Visible 워크시트의표시여부지정 2 Worksheet 주요매서드매서드 기능 매서드 기능 Activate 해당워크시트를활성화 Protect 워크시트보호 Add 새로운워크시트삽입 Select 워크시트선택 Copy 워크시트복사 Unprotect 보호된워크시트해제 3 Worksheet 주요이벤트이벤트 기능 이벤트 기능 Activate 워크시트가활성화될때발생 Deactivate 워크시트의활성화가취소될때발생 Calculate 워크시트가다시계산될때발생 SelectionChange 선택영역을변경할때발생 Change 워크시트가변경될때발생 4)Range 개체 - 엑셀의셀또는셀집합 ( 범위 ) - 4가지개체중가장많이쓰이고중요함 1 Range의주요속성 엠엔디시스템 ( 8/11

9 속성 기능 속성 기능 ActiveCells 현재셀 formular1c1range R1C1 스타일의수식 Address 참조하는셀주소 Item 지정된범위에대한오프셋범위지정 Cells 지정된범위의모든셀 Next 다음시트나셀 Count 지정된범위의셀수 Offset 지정된범위에서떨어진셀 Currentregion 데이터가있는인접영역의범위 Range 셀이나영역범위 End 지정된범위의마지막셀 Value 지정된셀 Formula A1 스타일의수식 2 Range의주요메서드속성 기능 속성 기능 AdvancedFilter 고급필터 Delete 지우기 AutoFill 자동채우기 Find 찾기 AutoFilter 자동필터 FindNext 다음찾기 Clear 삭제 select 선택 Clearcontents 내용만삭제 Sort 정렬 ClearFormats 서식만삭제 Subtotal 지정된셀 Copy 복사 5)UserForm 개체 - 사용자가인터페이스의일부분을구성하는창이나대화상자 1 UserForm의주요속성속성기능속성기능 Cation 폼의제목줄에표시될내용지정 Name 폼의이름지정 StartUppositin UserForm 의시작위치지정 2 UserForm의주요메서드메서드 기능 메서드 기능 Show 폼표시 Move 폼이동 Hide 폼숨기기 Unlode 폼이메모리에서제거됨 3 UserForm의주요이벤트이벤트 기능 이벤트 기능 Initialize 폼이초기화될때 Activate 폼이활성화될때 Click 폼을클릭할때 Dbclick 폼을더블클릭할때 사용자정의폼도구상자 개체선택 : 컨트롤의이동이나크기조정을위해폼에작성된컨트롤선택 엠엔디시스템 ( 9/11

10 2 레이블 : 입력란컨트롤의제목이나폼에고정적으로표시할내용입력 3 입력란 (TextBox): 사용자가입력하고수정할수있는텍스트를갖는컨트롤을만듦 4 콤보상자 (ComboBox): 이미지정된목록상자에내용을표시하여선택하거나새로입력 5 목록상자 (ListBox): 콤보상자와같이목록을표시하나새로운내용을입력할수없고한꺼번에여러항목을표시할수있도록스크롤이됨 6 확인란 (CheckBox): 여러가지중한가지이상의값을선택해야하는경우사용 7 옵션단추 : 여러가지중한가지만을선택해야하는경우사용 8 토글단추 : 설정과해제를번갈아지정하는단추 (True와 False값을가짐 ) 9 프레임 : 시각적으로나기능적으로컨트롤을그룹으로묶을때사용 10 명령어단추 (CommandButton): 사용자가명령을수행할때선택할단추를만들때사용 11 연속탭 : 응요프로그램의창이나대화상자의하나에여러개의페이지를만들수있도록함 12 다중페이지 : 정보를표시하는페이지여러개를단일집합으로표시 13 스크롤막대 : 항목등이많아그중하나를선택시목록을쉽게검색할수있도록이동할수있게함 14 스핀단추 : 지정된숫자범위의이동에사용 15 이미지 : 비트맵, 아이콘등의그래픽이미지를폼에표시 6) 컨트롤개체 1 컨트롤의주요속성 ( 디자인모드 ) 속성 기능 Picture PictureAlignment Font MouseIcon/ MousePointer Name 표시할비트맵그림지정배경그림의위치지정글꼴의모양, 크기, 속성등을지정마우스포인터가해당컨트롤에위치할때아이콘모양과마우스포인터모양을지정컨트롤이나개체의이름지정 2 컨트롤의주요속성 ( 실행모드 ) 속성 BoundValue Column List ListCount ListIndex Selected SelectedItem 기능컨트롤이포커스를받을때의컨트롤값지정한범위에서첫번째영역의첫번째열번호로반환항목을반환컨트롤에서목록항목의수를반환현재선택한항목을식별항목을선택현재선택된 Tab이나 Page 개체를반환 3 컨트롤의주요메서드 속성 기능 Cory Cut Additem Clear 개체복사개체잘라내기항목추가개체의모든속성삭제 4 컨트롤의주요이벤트 엠엔디시스템 ( 10/11

11 속성 기능 Click Change Enter / Exit SpinDown / SpinUp 사용자가개체를클릭할때발생워크시트의셀이사용자나외부연결에의해서변경될때발생포커스를받거나 / 잃을때발생위쪽 / 아래쪽스핀버튼을클릭할때발생 엠엔디시스템 ( 11/11