1

Transcription

1 13 단원 프로그래밍 1. VBA 모듈작성 2. VBA 기본문법 3. 컨트롤프로시저 1/21

2 1. VBA 모듈작성 VBA 모듈은한단위로저장된프로시저, 구문, VBA 선언문의집합으로비주얼베이직프로그래밍언어를사용하여작성합니다. 먼저 VBA 편집기를살펴보도록하겠습니다. 프로젝트탐색기창 Sub 프로시저 코드창 속성창 Fuction 프로시저 (13_1.bmp) 프로젝트탐색기창 : 현재열려있는파일과각각의파일에속해있는워크시트와모듈이표시됩니다. 프로젝트탐색기창의각요소를선택하면속성창에해당요소에대한속성이표시되며더블클릭하면해당요소에대한코드창이표시됩니다. 속성창 : 속성창에는선택된개체에대한디자인모드속성과이들의현재설정사항들이나열되어있습니다. 코드창 (Module Sheet) : 선택한개체에대한코드나모듈내에속한매크로의코드가표시되는영역입니다. 2/21

3 1) 프로시저 특정한작업를수행하도록만들어진구문을프로시저 (Prpcedure) 라고합니다. 프로시저에는 Sub 프로시저와 Function 프로시저가있습니다. (13_1.bmp 참조 ) Sub 프로시저 : 지정된작업을수행하고결과값을반환하지않는프로시저 Function 프로시저 : 지정된작업을수행하고결과값을반환하는프로시저사용자정의함수를만들때사용합니다. 2) 프로시저호출 프로시저에서다른프로시저를호출하려고할때는원하는프로시저의이름만입력하여호출할수있습니다 색상지정 이라는프로시저호출 그럼 VBA 구문을만들때주의해야할사항들을살펴보도록하겠습니다. 콜론 (:) 을사용하여한줄에두개이상의명령문을연결하여입력할수있습니다. REM이나작은따옴표 ( ) 로시작하는문장은주석으로인식되어처리되지않습니다. VBA 구문은대소문자를구분하지않으며개체, 속성, 메서드, 함수와같은예약어는자동으로판별하여첫글자를대문자로고쳐줍니다. 구문을입력하다가문법적인오류가발행하면자동으로이를검사해줍니다. VBA 구문은개체의속성을변경하거나개체에특정동작을수행하는구조로이루어지며일반적인사용형태는다음과같습니다. 개체명. 메서드 : 개체가어떤동작을수행하도록합니다. 개체명. 속성 = 값 : 개체의속성을변경시킵니다. 3/21

4 2. VBA 기본문법 1) 변수 변수란, 수학의 x, y와같이변하는수를말하며, 프로그램관점에서는데이터가저장되는공간을의미합니다. 데이터를담는 빈상자 라고할수있겠습니다.. 변수를선언할때사용하는예약어는 Dim, Private, Public, Static 이렇게 4가지입니다. 변수선언형식 : Dim 변수명 As 데이터형식 변수를선언하는예약어에따라변수의사용범위가달라집니다. Dim : 모듈의처음에선언되었다면해당모듈내의모든프로시저에서사용프로시저내부에서선언되었다면해당프로시저에서만사용합니다. Private : 모듈의처음에선언되어서해당모듈내의모든프로스저에서사용 Public : 모듈의처음에선언되어서모든모듈내의모든프로시저에서사용 Static : 프로시저내부에선언되어서해당프로시저에만사용합니다. 변수에값을할당하는방법 구분형식예해석 변수변수이름 = 값 x = 35 x 에 35 를할당함 개체변수 Set 변수이름 = 개체 set Object1 = Object2 Object1 에 Object2 를할당함 변수의명시적 / 묵시적선언 : 사실변수는선언하지않고도사용할수있습니다. 변수를선언하고사용하는것은명시적선언, 선언하지않고사용하는것을묵시적선언이락합니다. 변수를작성할때에는다음규칙을따라야합니다. 최대 255자까지지정 변수의첫글자는문자로시작 변수에문자, 숫자, 밑줄문자 (_) 이외의부호나공백, 예약어는포함할수없음 비주얼베이직키워드를포함할수없음 4/21

5 2) 데이터형식 데이터형식은변수를선언할때지정하는것으로데이터형식이지정된해당변수에는지정된데이터형식의데이터만저장할수있습니다. 데이터형식 의미 크기 데이터형식 의미 크기 Integer 정수 2byte String 문자열 문자열길이 Long 정수 4 byte Variant 가변형 16 byte Single 실수 4 byte Boolean True, False 2 byte Double 실수 8 byte Date 날짜 8 byte Currency 통화 8 byte Byte 0~255 1 byte 3) 배열 데이터를담는상자가변수라고한다면배열은여러개의상자를연결해놓은것입니다. 배열선언 : 배열을선언할때는 Dim이나 Public 을사용합니다. ( 선언의예 ) Dim A(1 to 3) As Integer A 라는배열안에 A(1), A(2), A(3) 라는저장공간이생김. A(1) A(2) A(3) 이렇게만들어진 A(1), A(2), A(3) 은각각을변수처럼사용할수있습니다. 괄호안의번호가배열의각요소를구별하기위해주어진인덱스번호입니다. 배열을선언할때인덱스번호를선언하지않으면첫번째인덱스번호는자동으로 0 이됩니다. 4) 개체, 속성, 메서드, 이벤트 다음은개체와속성, 메서드, 이벤트등에대하여알아보겠습니다. 5/21

6 개체 (Object) : 무엇이든독립적인형질을갖고있으면개체라고할수있습니다. 엑셀에서보면통합문서, 시트, 셀, 차트, 도형등이각각독립시켜생각할수있으므로개체라고할수있습니다. 다만시트는통합문서에, 셀은시트에포함되므로개체는그속에하위개체를포함할수있습니다. 메서드 (Method) : 인쇄, 복사와같이개체가수행할수있는동작을의미합니다. 속성 (Property) : 글씨의색이나크기와같이개체의특성을의미합니다. 이벤트 (Event) : 사용자의조작이나혹은프로그램상에서발생하는사건을이벤트라고합니다. 우리가잘아는대표적인이벤트로클릭이있습니다. 1 Application 개체 속성메서드이벤트 ActiveCell ActiveSheet ActiveWindow ActiveWorkbook Selection ThisWorkbook Windows Workbooks Worksheets InputBox OnTime OnKey Quit NewWorkbook WorkbookBeforeClose SheetChange 현재셀현재시트현재창현재통합문서현재창에서선택한개체현재매크로코드가실행중인통합문서모든통합문서에있는모든창열려있는모든통합문서전체현재통합문서에있는모든워크시트사용자입력을위한대화상자표시프로시저가지정된시간에시행되도록지정특정키나키조합을누르면지정한프로시저실행 Microsoft Excel 끝내기새통합문서를만들때발생열려있는통합문서를닫기바로전에발생워크시트에있는셀이변경될때발생 6/21

7 2 Workbook(Workbooks 컬렉션 ) 개체 속 Count 통합문서의개수 3) 성 Name 통합문서의이름 Saved 통합문서의저장여부 Windows 지정한통합문서의모든창 Worksheets 지정한통합문서에있는워크시트 메 Add 새통합문서만들기 서 Close 통합문서닫기 드 NewWindow 통합문서의새창만들기 GetOpenFilename < 열기 > 대화상자에서선택한파일이름 GetSaveAsFilename < 다른이름으로저장 > 대화상자에서선택한파일이름 Open 통합문서열기 Save 통합문서저장하기 SaveAs 다른이름으로통합문서저장하기 이 Open 통합문서를열때 벤 Activate 통합문서가활성화될때 트 SheetActivate 시트를활성화할때 NewSheet 새로운시트를만들때 BeforeSave 통합문서가저장되기전에 Deactivate 통합문서가비활성화될때 BeforePrint 통합문서가인쇄되기전에 BeforeClose 통합문서를닫기전에 7/21

8 3 Worksheet(Worksheets 컬렉션 ) 개체 속성메소드이벤트 Cells Columns Name Range Rows Activate Add Copy Protect Select Unprotect Activate Calculate Change Deactivate 워크시트의모든셀워크시트의모든열워크시트의이름셀이나셀범위워크시트의모든행지정한워크시트를활성화시킴워크시트를새로생성워크시트의복사워크시트보호설정워크시트선택워크시트의보호해제워크시트가활성화될때워크시트가재계산된다음에워크시트의셀이변경될때워크시트가비활성화될때 8/21

9 4 Range 개체 속성메소드 ActiveCell Address Cells Count Currentregion End Formula FormulaR1C1 Item Next Offset Range Value AdvancedFilter AutoFill AutoFilter Clear ClearContents ClearFormats Copy Delete Find FindNext FindPrevious Select Sort Subtotal 현재셀셀또는셀범위의주소지정한범위의셀또는범위셀범위에있는셀의개수빈행과빈열로둘러싸인현재영역현재범위의상하좌우마지막에있는셀 A1 스타일의개체수식 R1C1 스타일의개체수식특정범위에서지정한행, 열만큼떨어진범위현재셀의다음셀특정범위에서지정한행, 열만큼떨어진범위셀이나셀범위지정한셀의값고급필터수행지정한범위에자동채우기자동필터수행지정한범위의전체내용지우기지정한범위의내용만지우기지정한범위의서식만지우기셀이나셀범위복사셀이나셀범위삭제지정한정보의찾기수행같은조건의다음셀찾기같은조건의이전셀찾기지정한셀이나셀범위선택지정한범위에대한정렬수행지정한범위에대한부분합수행 9/21

10 5 Chart(Charts 컬렉션 ) 개체 속성메서드이벤트 ActiveChart AxisTitle ChartType Add Axes ChartWizard Select SeriesChange 현재선택한차트지정한축의축제목지정한차트의종류새로운차트시트만들기차트의축을반환차트의속성수정차트를선택할때차트데이터요소의값을변경할때 6 Window(Windows 컬렉션 ) 개체 속성메서드 Caption SelectedSheets Activate Close 제목표시줄에표시되는이름지정한창에서선택한모든시트창을현재창으로만듬창을닫음 5) 제어문 조건에따라특정한명령를반복적으로실행할때사용합니다. 1 If Then Else문 : 조건을만족하면 A를수행하고그렇지않으면 B를수행합니다. 여기서 else 부분은생략될수도있습니다. 형태 If 조건 Then A Else B End If 10/21

11 2 If ~ Then ~ ElseIf ~ Else 문 : 조건 1 을만족하면 A 를수행하고조건 2 를만족하면 B 를수행, 둘다 만족하지않으면 C 를수행합니다. 형태 If 조건1 Then A ElseIf 조건2 Then B Else C End If 3 Select Case문 : 비교값이조건1에해당하면 A를수행, 조건2에해당하며 B를수행, 조건3에해당하면 C를수행합니다. 형태 Select Case 비교값 Case Is 조건1 A Case Is 조건2 B Case Is 조건3 C End Select 4 For ~ Next 문 : 시작값부터끝값까지증감값만큼계속 A 를반복수행합니다. 형태 For 변수 = 시작값 To 끝값 Step 증감값 A Next 변수 5 Do While ~ Loop 문 : 조건을만족할때까지만 A 를반복적으로수행합니다. 형태 Do While 조건 A Loop 11/21

12 6 Do ~ Loop While 문 : A 를실행한후에조건을만족할때까지 A 를반복수행합니다. 형태 Do A Loop While 조건 7 Do Until ~ Loop문 : 조건을비교한후조건이만족하지않을경우반복적으로 A를수행하고만족하면반복을중지합니다. 형태 Do Until 조건 A Loop 8 With 문 : 하나의개체에대해여러가지메서드나속성을변경할때사용하여프로그램의길이를줄일수있습니다. 프로시저의실행속도가향상되고반복하여입력하지않아도되기때문에편리합니다. With 문으로시작해서 End With문으로끝나며그사이에처리할명령문을입력합니다. 형태 With 개체명해당개체에공통적으로적용할메서드또는속성 End With 12/21

13 3. 컨트롤프로시저활용 1) 워크시트에 ActiveX 컨트롤추가 메뉴표시줄 < 보기 >-< 도구모음 >-< 컨트롤도구상자 > 항목을클릭합니다. 도구상자가나타나면명령단추컨트롤도구를선택하고마우스로드래그하여워크시트위에컨트롤을추가합니다. 속성창을표시하려면도구상자의속성아이콘을클릭합니다. 속성창을확인한후닫기단추를클릭하여속성창을닫습니다. 컨트롤의모양은디자인모드아이콘이눌려져있는상태에서속성창을이용하여변경합니다. 디자인모두아이콘이눌려져있지않으면실행모드로컨트롤에대한어떤동작을실행했을때이벤트프로시저가실행됩니다. 컨트롤의이벤트프로시저는컨트롤을선택하고코드보기아이콘을클릭하여현재시트의코드보기로전환합니다. 코드를작성한후창닫기아이콘을클릭하여비주얼베이직편집기를종료합니다.. 13/21

14 2) 사용자정의폼 사용자정의폼을사용하면도구상자에있는각종컨트롤을추가하여각컨트롤에대한이벤트프로시저를작성할수있습니다. 속 Name 성 BorderStyle StartUpPosition Caption Picture ShowModal 메 Hide 서 Show 드 Move 이 Initialize 벤 Click 트 Activate 폼의이름테두리형식폼의표시위치폼의제목표시줄에표시할텍스트폼의배경에사용할비트맵이미지폼을모달 ( 기본값 ) 또는모달리스트로표시폼을화면에서숨김사용자정의폼개체를표시폼을이동시킴사용자정의폼이화면에표시되기바로전에발생사용자정의폼이클릭될때발생사용자정의폼이활성화될때발생 관련내용을실습해보겠습니다. 메뉴표시줄 < 도구 >-< 매크로 >-<Visual Basic Editor> 항목을클릭합니다. VBA 편집기창메뉴표시줄의 < 삽입 >-< 사용자정의폼 > 메뉴를선택하면사용자정의폼이새로추가됩니다. 도구상자의레이블컨트롤을선택하고마우스로드래그하여워크시트위에컨트롤을추가합니다. 도구모음의속성창아이콘을클릭합니다. 14/21

15 여기서는첫번째방법을통해매크로를기록해보도록하겠습니다. 먼저 A2 셀을선택합니다. 메뉴의도구- 매크로 - 새매크로기록을실행합니다. 매크로기록대화상자가나타나는것을볼수있습니다. 매크로기록대화상자에대해간단히알아보도록하겠습니다. Caption 항목의값을지우고 기본급 이라고입력합니다. 닫기단추를클릭하여속성창을닫습니다. 도구상자의텍스트상자컨트롤을선택하고마우스로드래그하여워크시트위에컨트롤을추가합니다. 도구모음의속성창아이콘을클릭합니다. ( 이름 ) 항목의값을지우고 기본급 이라고입력합니다. 닫기단추를클릭하여속성창을닫습니다. 마우스를드래그하여두컨트롤이모두선택될수있도록합니다. [Ctrl] 키를누른채드래그하여두컨트롤을바로아래에복사합니다. 복사한레이블컨트롤을선택한후도구모음의속성창아이콘을클릭합니다. Caption 항목의값을지우고 수당 이라고입력합니다. 닫기단추를클릭하여속성창을닫습니다. 복사한텍스트상자컨트롤을선택한후도구모음의속성창아이콘을클릭합니다. ( 이름 ) 항목의값을지우고 수당 이라고입력합니다. 닫기단추를클릭하여속성창을닫습니다. 다음은수당레이블과텍스트상자를 [Ctrl] 키를누른채드래그하여폼의왼쪽아래에복사합니다. 복사한레이블컨트롤을선택한후도구모음의속성창아이콘을클릭합니다. 15/21

16 Caption 항목의값 수당 을지우고 세금 이라고입력합니다. 닫기단추를클릭하여속성창을닫습니다. 복사한텍스트상자컨트롤을선택한후도구모음의속성창아이콘을클릭합니다. ( 이름 ) 항목의값을지우고 세금 이라고입력합니다. 닫기단추를클릭하여속성창을닫습니다. 이번에는세금레이블과텍스트상자를 [Ctrl] 키를누른채드래그하여폼의오른쪽에복사합니다. 복사한레이블컨트롤을선택한후도구모음의속성창아이콘을클릭합니다. Caption 항목의값 세금 을지우고 실수령액 이라고입력합니다. 닫기단추를클릭하여속성창을닫습니다 복사한텍스트상자컨트롤을선택한후도구모음의속성창아이콘을클릭합니다. ( 이름 ) 항목의값을지우고 실수령액 이라고입력합니다. 닫기단추를클릭하여속성창을닫습니다. 이번에는명령단추컨트롤을선택합니다. 마우스로드래그하여수당과세금컨트롤사이에삽입합니다. 도구모음의속성창아이콘을클릭합니다. Caption 항목의값을지우고 계산 이라고입력합니다. 변경하는매크로를작성하도록하겠습니다. 닫기단추를클릭하여속성창을닫습니다. 이제명령단추에관련된코드를만들어보겠습니다. < 계산 > 단추를더블클릭하여코드창을화면에표시합니다. 16/21

17 < 계산 > 단추의 Click Procedure 를다음과같이작성합니다 세금 = 기본급 * 0.1 실수령액 = Format(Val( 기본급.Text) + Val( 수당.Text) - Val( 세금.Text)) 도구모음의 Sub/ 사용자정의폼실행단추를클릭합니다. 폼이나타나면기본급입력란에 을입력하고, 수당입력란에 을입력합니다. < 계산 > 단추를클릭하여결과를확인합니다. 3) 주요컨트롤주요컨트롤에대해살펴보겠습니다. 컨트롤 CheckBox ( 확인란 ) ComboBox ( 콤보상자 ) CommandButton( 명령단추 ) Frame ( 프레임 ) Image ( 이미지 ) Label ( 레이블 ) ListBox ( 목록상자 ) MultiPage ( 다중페이지 ) OptionButton ( 옵션단추 ) 설명선택사항을표시하는용도로사용, 중복체크가능긴목록을표현할때사용명령을수행하는용도로사용, 실행, 확인, 취소, 종료, 도움말등주로클릭이벤트를위한 프로시저에사용 컨트롤의그룹형성그림의삽입에사용다른컨트롤을설명하거나메시지표현을위해사용사용자가선택할항목표시페이지컬렉션을한개이상포함선택사항을표시하는용도, 하나만선택가능 17/21

18 ScrollBar ( 스크롤막대 ) SpinButton ( 스핀단추 ) TabStrip ( 연속탭 ) TextBox ( 텍스트상자 ) ToggleButton ( 토글단추 ) 목록이동, 아날로그값의증감에이용값의증감을위해사용탭컬렉션을한개이상포함문자열입력또는표시할때사용선택사항을표시하는용도로사용, 눌려진상태가유지 4) 디자인모드에서컨트롤의주요속성 디자인모드에서컨트롤의주요속성은다음과같습니다. 그림 Picture PictureAlignment PictureSizeMode PicturePosition 개체위에표시할비트맵지정배경그림의위치지정배경그림을표시하는방법지정캡션과비례하여그림의위치지정 글꼴 Font 텍스트의특성정의 기타 MouseIcon MousePointer Name 개체에사용자정의아이콘을지정 마우스를특정개체위로이동했을때나타나는포인터의형식지정 컨트롤이나개체의이름을지정 동작 AutoSize Cancel Default 전체내용이표시되도록자동으로개체크기를조정할것인지지정 CommandButton 이폼의취소단추인지지정 ( 동일폼에서한개만지정 ) CommandButton 이기본명령단추인지설정 ( 동일폼에서한개만지정 ) 18/21

19 Enabled Locked Maxlength MultiSelect TextAlign 컨트롤이사용자가발생한이벤트에대해반응할수있는지설정컨트롤의편집가능영부지정 TextBox 또는 ComboBox 에사용자가입력할수있는문자의최대수다중선택이가능한지여부지정컨트롤에서텍스트에서정렬방식지정 모양 Alignment BackColor ForeColor BorderColor BorderStyle BackStyle Caption DropButtonStyle PasswordChar ShowDropButtonWhen Value Visible 컨트롤에서캡션의정렬위치지정배경색지정전경색 ( 텍스트색 ) 지정테두리색지정컨트롤의테두리스타일지정배경스타일을투명또는불투명으로지정개체를구별하거나설명하기위해개체위에나타나는텍스트 ComboBox 에서드롭단추에표시되는기호지정 Textbox 에문자대신자리표시자문자를표시할것인지지정 ComboBox 또는 Textbox 에서드롭다운단추의표시시기지정컨트롤의상태나내용컨트롤을숨길것이니여부지정 위치 Height, Width Left, Top 포인트단위로개체의높이와너비지정 왼쪽과위쪽가장자리로부터컨트롤의위치지정 데이터 BoundColumn ColumnCount ColumnHeads ColumnWidths ControlSource 여러열의 ComboBox 나 ListBox 에서데이터의원본지정목록상자나콤보상자에서표시할열의숫자목록상자나콤보상자에서열머리글의표시여부지정여러열콤보상자나목록상자에서각열의너비지정워크시트범위나목록을컨트롤의 Value 속성에지정 19/21

20 ListRows ListStyle ListWidth RowSource Text TextColumn TopIndex ComboBox 에표시할행의최대개수 ListBox 나 ComboBox 에서목록의시각적인모양 ComboBox 에서목록의너비 ComboBox 나 ListBox 의원본목록 TextBox 의텍스트, ComboBox 나 ListBox 에서선택된행의변경 ComboBox 나 ListBox 에서특정열을지정목록의맨위에나타나는항목지정 스크롤 Max, Min MultiRow ScrollBars ScrollBar 나 SpinButton 의 Value 속성에입력가능한최대값과최소값 컨트롤이탭을하나이상가질수있는지지정 컨트롤, 폼, 페이지등이스크롤막대를포함하는지여부지정 5) 실행모드에서컨트롤의주요속성 실행모드에서컨트롤의주요속성은다음과같습니다. 속성 BoundValue Column List ListCount ListIndex Selected SelectedItem 컨트롤이포커스를받을때컨트롤의값콤보상자나목록상자에서한개이상의항목지정콤보상자나목록상자에서특정항목반환목록에있는항목의개수콤보상자나목록상자에서현재선택한항목의인덱스번호 (0 부터시작 ) 목록상자의항목선택상태선택한 Tab 또는 Page 개체반환 20/21

21 메서드 AddItem Clear Copy Cut Move Paste RemoveItem SetFocus 콤보상자나목록상자에항목추가콤보상자나목록상자의모든항목제거컨트롤의내용을클립보드로복사컨트롤의내용을클립보드로잘라내기컨트롤의이동클립보드의내용을컨트롤에붙여넣기콤보상자나목록상자의특정항목제거특정컨트롤로포커스이동 ( 활성상태로만듬 ) 이벤트 AfterUpdate BeforeUpdate Change Click DropButtonClick Enter Exit SpinDown SpinUp 컨트롤의데이터가변경된후에발생컨트롤의데이터가변경되기전에발생컨트롤의 Value 속성이변경될때발생컨트롤을클릭했을때발생드롭다운단추를클릭할때마다발생같은폼의컨트롤에서포커스를받기전에발생같은폼의컨트롤에서포커스를넘겨주기전에발생아래쪽이나왼쪽스핀단추를클릭할때발생위쪽이나오른쪽스핀단추를클릭할때발생 VBA 프로그래밍을학습한다는것은비주얼베이직프로그램을학습하는것과매우흡사합니다. 그만큼양도많고어려울수있습니다. 출제경향을살펴보면프로그램의결과를묻는문제나프로그램의빈부분괄호채우기, 명령문에대한분석, 프로그램설명, 프로그램의오류를찾는문제등수준높은문제들이많이출제되고있습니다. 많은문제를접하여출제경향을직접파악하여학습하실수있도록하시기바랍니다. 21/21