1. VBA 의개념 VBA (Visual Basic for Application) Visual Basic은 Ms에서 Basic을기반으로만든개체지향적인프로그램언어를말한다. Visual은 " 시각적 " 이라는의미이고, Basic은퍼스널컴퓨터에서사용된프로그램언어이다. 여기에

Transcription

1 경영 IT 실무 Excel VBA (WEEK 11~16) P1-24: VBA매뉴얼 P25: VBA Exercise P26-43: GROUP PROJECT 예제 소속 : 한동대학교 경영경제학부 성명 : 교수김대식 1

2 1. VBA 의개념 VBA (Visual Basic for Application) Visual Basic은 Ms에서 Basic을기반으로만든개체지향적인프로그램언어를말한다. Visual은 " 시각적 " 이라는의미이고, Basic은퍼스널컴퓨터에서사용된프로그램언어이다. 여기에 응용프로그램 을의미하는 Application이붙어서 "Application을위한 Visual Basic" 을의미한다. MS Office 계열의제품에는 Visual Basic이탑재되어있는데, VB언어의구조를가지고있지만, VB에비해제한적인개체 (object) 를사용한다. VBA에서다른응용프로그램의개체를사용하려면 " 참조 " 과정을거쳐 VB나 MS Office의개체를사용할수있다. BASIC 1960년대말미국 Dartmouth 대학의 John Kemeny 교수가개발하여주로퍼스널컴퓨터에채용되고있다. 간단한영어의어구를기반으로한명령이쓰이고있으므로쉽게배울수있다. 그러나퍼스널컴퓨터에적재되어있는것은그기종의하드웨어구성등에의한고유한명령을가지고있는경우가많다. BASIC이라고불리고있는것은 interpreter와 editor 등이포함된시스템프로그램이다. 인터프리터방식이기때문에컴파일러언어와비교하면처리속도가느리지만, 프로그램의수정이간단하고번역프로그램이사용하는기억소자가적어도되므로외부기억장치없이작은시스템으로도이용할수있다는특징이있다. 개체지향프로그래밍 (Object-oriented programming) 모든데이터를 object로취급하며, 이 object에는 class의개념이있어서상, 하위의관계가있다. class의구체적인예가 instance이다. object 사이는메시지의송신으로상호통신한다. 가장특징적인것은각 class에그메시지를처리하기위한방식이있다는것이다. 어떤 instance에메시지가도달하면그상위 class가그것을처리한다. 현재개체지향개념은프레임표현형식과융합하여인공지능을위한소프트웨어기법의하나로되어있다. 개체지향프로그램은 C, Pascal, BASIC 등과같은절차형언어 (procedure-oriented programming) 가크고복잡한프로그램을구축하기어렵다는문제점을해결하기위해탄생된것이다. 절차형언어에서는코드전체를여러개의기능부분즉, 인쇄하는기능부분과사용자로부터의입력을받는기능부분등으로분할하는데, 이와같이각기능부분을구성하는코드를 module이라고한다. 절차형언어에서는프로그램을여러기능으로나누고이들 module을편성하여프로그램을작성할경우, 각 module이처리하는데이터에대해서는전혀고려하지않는다. 다시말하면데이터취급이완전하지않고현실세계의문제를프로그램으로서표현하는것이곤란하다. 이러한절차형프로그래밍이가지는문제를해결하기위해탄생된개체지향프로그래밍은 object라는작은단위로서모든처리를기술하는프로그래밍방법으로서모든처리는 object에대한요구의형태로표현되며, 요구를받은 object는자기자신내에기술되어있는처리를실행한다. 이방법으로프로그램을작성할경우프로그램이단순화되고, 생산성과신뢰성이높은시스템을구축할수있다. 2

3 VB VB는사용자가프로그램을만드는데필요한도구를하나의완성된 object( 개체 ) 로제공한다. 이 object에는정의된어떤기능이내장되어있다. 사용자는각 object가어떤기능을하는지알아서, object를사용하는방법에따라사용하면된다. 이것을개체지향프로그램이라고한다. 그림은 VBA에서사용하는 Userform과 control을폼에삽입한것으로, 각각의항목들이 object 이다. 즉, Userform, 텍스트박스, 콤보박스등이모두 object이다. 만약프로그램언어 (C+) 로그림에있는하나의 object를만든다면, 화면에표시될 object를디자인하고, object의기능을만들어넣어야한다. 그러나 VBA에서는완성된 object를사용자에게제공하기때문에, form, 텍스트박스등을직접만들지않아도, 제공되는 object를복사해서사용만하면된다. 그러므로사용자는각 object의용도만익히면 object를만드는방법을모르더라도프로그래밍을할수있게된다. 예를들어텍스트박스는값을입력및출력할수있는 object이다. 콤보박스는리스트에값을등록시키고사용자가특정값을찾아선택하도록하는 object이다. 이처럼주어진 object를가지고원하는프로그램을만들기때문에 개체지향프로그램 이라고한다. 엑셀 VBA의주요용어와개념 A. 객체또는개체 (Object) i. 워크북, 워크시트, 셀, 차트, 유저폼, 클래스모듈, 그림, 선, 단추, 리스트박스, 텍 스트박스등눈에보이는모든것이객체 B. 속성 (Attribute): 객체가갖고있는특성 i. 텍스트박스의속성은위치, 크기, 글꼴, 색상값등이있다. C. 메서드 (Method) i. 복사하기, 잘라내기, 지우기, 붙여넣기, 이동하기, 보이기 (Show), 숨기기 (Hide)_, 저장하기, 열기, 닫기등실행명령을의미 D. 프로시저 (Procedure): 프로그램을구성하는작은프로그램 VB Editor에서 [ 삽입 ]-[ 모듈 ] i. Sub 프로시저 : Sub로시작하여 로끝난다. ii. Function 프로시저 : Function로시작하여 End Function로끝난다 매개변수를이용 3

4 E. 이벤트 (Event): 특정버튼을눌렀을때에실행할명령문을포함. Yes, No, Cancel 등의버튼과연결되어사용 Class와 Instance VBA에서제공하는 Userform을 class라고한다. VBA에서 object는 class와 instance의개념으로세분할수있는데, VBA가기본적으로제공하는모든 object는 class이고사용자는제공된 class를복사해서사용하게된다. 이렇게사용자가 class를복사하여사용한 object를 instance라고한다. Excel이제공하는도구상자의각종도형은 class가되고, 사용자가 sheet에도형을그리면복사된도형은 instance가된다. 복사된도형은 class의성격을그대로이어받는다. 하나의 class에서얼마든지 instance를만들어낼수있다. Userform에텍스트박스를삽입하면도구상자에표시된텍스트박스는 class이고사용자가삽입된텍스트박스는 instance가된다. Class 와 instance 는각각이 object 로서, class 는프로그래머가만들어제공하는 object 이고, 사용 자가이 class 를복사해사용하면 instance 가되는것이다. 즉, 사용자가사용하는 object 는 instance 가된다. 사용자도 class 를만들수있다. 사용자가필요한 object 의이름을정하고 object 의기능 ( 속성, 메서드 ) 를정의하면이것도 class 가된다. 이렇게만든 class 를사용하면 instance 가된다. Object 4

5 VBA 의개체 (object) 구조는다음과같다. 라이브러리 클래스 구성원 라이브러리가등록되어야만개체를찾을수있다. 라이브러리는확장자가 "olb" 인파일로시스 템폴더에등록된다. 위의 개체찾아보기 그림에는 Excel 라이브러리의클래스 (class) 와구성원 (property, method) 이표시된다. ( 보기 개체찾아보기 ) 라이브러리는 object 를아주크게분류한단위로, Excel, MsForm, Office 라이브러리가있다. 각 각의라이브러리에는 class 가있고, class 에는정의된 property 와 method 가있다. 이 class 를사용 자는 instance 로사용하게된다. 2. VBE VBE(Visual Basic Editor) 는 VBA 를작업하도록제공된공간이다. 통합문서창에서시트에작업 을하듯이, VBE 에서 VBA 작업을하게된다. VBE 는 [Alt+F11] 키의조합으로열수있다. Excel 화면에서단축키를누르면다음과같은 VBE 기본화면이나타난다. VB Editor 기본화면은다음과같이구성되어있다. 코드창 속성창 5

6 (1) 프로젝트탐색기창 열려있는파일과포함되어있는개체들의정보를보여준다. (2) 속성창 해당개체의속성값에대한정보를보여준다. 속성의수정도가능하다. 6

7 메뉴표시줄 표준도구모음 3. VBE 환경설정 VBE 환경설정은 [ 도구 옵션 ] 메뉴에서할수있다. 편집기탭 코드설정의옵션들은코드작성시에발생하는에러를줄이고작성을용이하게할수있도록 도와주는기능이므로모두설정하여사용하는것이편리하다. 자동구문검사 : 코드창에입력된코드에에러가있을경우사용자에게알려주는기능이다. 변수선언요구 : 코드창에자동으로 "Option Explicit" 문이삽입된다. 이구문은변수를명시적으로선언하여사용하도록하는기능으로, 선언하지않은변수는사용할수없게된다. 구성원자동목록 : 코드를작성할때 object의 property, method를자동으로보여주는상자를표시한다. 자동요약정보 : 입력시점에함수와함수의매개변수정보를보여준다. 자동데이터설명 : 코드창에서마우스를변수에갖다대면변수의값을보여주는기능이다. 7

8 편집기형식탭 코드창의폰트를설정하는탭으로글꼴과크기를설정할수있다. 일반탭 8

9 폼모눈설정 : Userform 의모눈을설정하는옵션이다. 모눈단위의너비와높이는 : 1 ~ 60 포 인트까지설정할수있다. 오류잡기 : 오류를처리하는방법을결정한다. 1) 오류가발생시무조건중단 : 오류가발생하면해당프로젝트가중단모드로들어간다. 이때오류처리기의활성여부나코드가클래스모듈에있는지의여부는상관없다. 2) 클래스모듈에서중단 : 클래스모듈에서발생한오류가처리되지않으면해당프로젝트는오류를일으킨클래스모듈의코드행에서중단모드로들어간다. 3) 처리되지않은오류발생시중단 : 오류처리기가활성화되어있으면중단모드로들어가지않고오류를잡는다. 활성중인오류처리기가없을경우, 해당프로젝트는중단모드로들어간다. 하지만클래스모듈에처리되지않은오류가있으면해당프로젝트는클래스의 offending 프로시저를일으키는코드행에서중단모드로들어간다. 오류잡기의옵션은 처리되지않은오류발생시중단 으로설정한다. 프로시저에오 류처리기 ( 오류를잡는문 ) 를작성하여예상되는오류를처리하고, 그래도처리되지않 는오류가발생되면프로시저를중단모드로들어간다. 컴파일 1) 요청할때컴파일 : 프로젝트를시작하기전에프로젝트를완전히컴파일할것인지, 또는필요에따라코드를컴파일하여응용프로그램을곧바로시작할수있게할것인지를결정한다. 2) 백그라운드컴파일 : 실행모드중에유휴시간을사용하여백그라운드에있는프로젝트의컴파일을완료할것인지를결정한다. 백그라운드컴파일을선택하면런타임실행속도가빨라진다. 요청할때컴파일을선택해야만이기능을사용할수있다. 코드창에코드를작성하고코드를실행시키면, 가장먼저컴파일과정을거치게된 다. 컴파일은프로시저의코드를기계어로변환하는것을말한다. 컴파일옵션을모두 체크하면실행속도가빨라진다. 도킹탭 9

10 도킹은 결합 을의미하는것으로서, VBA에서는각각의창에공간을배분하여위치시키는것을말한다. 직접실행창, 지역창, 조사식창, 프로젝트탐색기, 속성창, 개체찾아보기등 6개의창을 VBE의 창 이라고하는데, 이러한창들간에도킹을함으로써 VBA 사용시화면구성을보다효율적으로할수있다. 6개의창중에서도킹을체크하지않는창이있을때, VBE에서도킹을체크하지않은창을불러드리면선택한창만 VBE 전면에표시된다. 반면에도킹을체크한창들은각각자신의할당된위치에정렬된다. 10

11 4. VBE 메뉴 메뉴표시줄 파일 저장 (Ctrl+S) : 현재의프로젝트파일을저장한다. 파일가져오기 (Ctrl+M) : 외부에저장된 VB파일을현재의프로젝트로가져온다. frm : 사용자정의폼파일 bas : 모듈 cls : 클래스모듈프로젝트에서작성된모듈, 클래스모듈, 사용자정의폼등을 VB파일로저장하고, 파일가져오기 메뉴를사용해현재의프로젝트에추가시켜사용할수있다. VB파일을 Excel 파일로저장하기보다는 VB파일로저장하면파일의용량이작아질뿐아니라, 참고하고싶을때언제든지불러서참고할수있다. 텍스트로작성된파일도가져올수있다. 파일내보내기 (Ctrl+E) : 현재의프로젝트에서작성한모듈, 클래스모듈, 사용자정의폼을 VB파일로저장한다. 통합문서의모든모듈을파일내보내기로저장하고프로젝트의모듈시트를삭제하여보관하면매크로가없는문서가될수있다. VBA로자동화할경우에도사용되는기능이다. 11

12 편집 실행취소 (Ctrl+Z) 찾기 (Ctrl+F), 다음찾기 (F3), 바꾸기 (Ctrl+H)) : 모듈에서코드를찾을때사용한다. 속성 / 메서드목록명령 (Ctrl+J) : 개체이름뒤에마침표 (.) 를입력하면, 그개체에사용할수있는속성과메서드가들어있는목록상자를표시한다. 또는포인터가빈영역에있을때전체적으로사용할수있는메서드의목록을표시한다. ( 코드를입력할때목록상자가자동으로열리도록하려면옵션대화상자의편집기탭에서 구성원자동목록 을선택한다.) 사용자는메서드를직접입력하거나목록에서원하는항목을선택한후 Tab키를눌러입력시킬수있다. 상수목록명령 (Ctrl+Shift+J) : 등호 (=) 앞에입력한속성에대해유효한상수가들어있는목록상자를표시한다. ( 코드를입력할때목록상자가자동으로열리도록하려면옵션대화상자의편집기탭에서 구성원자동목록 을선택한다.) 사용자는상수를직접입력하거나목록에서원하는항목을선택한후 Tab키를눌러입 12

13 력시킬수있다. 요약정보명령 (Ctrl+I) : 코드창에서해당변수, 함수, 문, 메서드또는프로시저에대한구문을제공한다. 요약정보는항목에대한구문을표시하고현재매개변수를반전시킨다. 매개변수가있는함수나프로시저의경우입력하는매개변수와다음매개변수와구분하는데사용되는쉼표 (,) 를입력할때까지현재입력하고있는매개변수는굵은글씨로표시된다. ( 코드를입력할때요약정보가자동으로표시되도록하려면옵션대화상자의편집기탭에서 구성원자동목록 을선택한다.) 매개변수정보명령 (Ctrl+Shift+I) : 초기함수또는문의매개변수에대한정보를표시한다. 함수나문의매개변수로함수를포함하는함수나문이있을경우매개변수정보를선택하면처음함수에대한정보를제공한다. 요약정보는각포함함수에대한정보를제공한다. 입력하는매개변수와다음매개변수와구분하는데사용되는쉼표 (,) 를입력할때까지현재입력하고있는매개변수는굵은글씨로표시된다. 단어채우기명령 (Ctrl+Space) : 개체, 속성, 메서드, 함수, 예약어등의경우, Visual Basic 에서식별할수있는단어까지 입력하고명령을실행시키면나머지부분을자동으로완성한다. 삽입 삽입의하위메뉴에서모듈을클릭하면, 프로젝트에모듈시트가삽입된다. 사용자는모듈시트에 프로시저를삽입하고코드를작성하게된다. 모듈에는프로젝트의모듈 (Module1, Module2, ), 통합문서의모듈, 워크시트의모듈이있다. 삽입메뉴에서삽입하는모듈은프로젝트내에서공용으로사용할수있는프로젝트의모듈이고, 통합문서와워크시트의모듈은각각의개체안에서만작용하는이벤트모듈이다. 프로젝트탐색기창에서각각을더블클릭하여활성화시킬수있다. 13

14 모듈이활성화되면, 개체선택상자를클릭하여개체를선택할수있다. 개체를선택하면이벤트프로시저선택상자를클릭하여이벤트를선택하여, 개체에서발생하 는이벤트에관한코드를작성할수있다. 14

15 5. 프로시저 프로시저삽입 VBA를구성하는요소에는문 (Statement), 변수, 상수, 개체, 속성, 메서드, 프로시저, 모듈, 폼등이있다. 코드의작성은모듈에프로시저를삽입하고, 삽입된프로시저에하게된다. 프로시저는 Sub 프로시저, Function 프로시저, Property 프로시저로구분되며, Public, Private 키워드의사용으로프로시저의적용범위가달라진다. 모듈창을열고메뉴의 [ 삽입 프로시저 ] 를클릭하여아래와같은대화상자를불러내고프로시 저의이름을입력하여프로시저를추가할수있다. 프로시저는모듈에직접입력하여삽입할수도있다. 프로시저선언시구조는다음과같다. Public Private Sub Function Property 프로시저이름 ( 인수 ) Ex) example 이라는이름의 Sub 프로시저를 Private 범위로인수는없이삽입하려면선언문은다음과같다. Private Sub example() 단, 범위를생략할경우 Public 으로인식한다. 프로시저의적용범위 하나의프로젝트에는여러개의모듈이존재한다. 모듈은 5 가지로구분되는데, 워크시트의모듈, 15

16 통합문서의모듈, 프로젝트의모듈, 폼의모듈, 클래스모듈이있다. 프로젝트탐색기창에서확인할수있다. 시트 ( 다수 ) : 시트의모듈은각워크시트당 1개씩주어진다. 통합문서 (1개) : 프로젝트의통합문서의모듈이주어진다. 모듈 ( 다수 ) : [ 삽입 모듈 ] 로추가할수있다. 프로젝트의모듈이다. 폼 : [ 삽입 사용자정의폼 ] 으로추가할수있다. 폼은각각 1개씩의모듈을갖는다. 클래스모듈 : [ 삽입 클래스모듈 ] 로추가할수있다. 각각의모듈에서프로시저를 Private 또는 Public 으로하는가에따라서프로시저의적용되는범 위가달라진다. Public 프로시저는프로젝트의모든다른모듈에서호출하여사용할수있는프로 시저가되지만, 반대로 Private 프로시저는프로시저가삽입된모듈에서만호출될수있다. Ex) 아래의두개의모듈에서각각의프로시저에대한호출의가능여부를비교해보도 록한다. ( 프로시저는 Call 문으로호출한다.) Module1 Private Sub AAA() Call BBB 가능 Call DDD 가능 Call CCC 불가능 Module2 Private Sub CCC() Call AAA 불가능 Call BBB 가능 Call DDD 가능 Public Sub BBB() Call AAA 가능 Call DDD 가능 Call CCC 불가능 Public Sub DDD() Call AAA 불가능 Call BBB 가능 Call CCC 가능 Sub 프로시저와 Function 프로시저의차이 Sub 프로시저는프로시저의코드를실행하고 에서종료되는프로시저인반면, Function 프로시저는프로시저의코드를실행하고그결과값을반환하는프로시저이다. Function 프로시저를 사용자함수 라고한다. 즉, 사용자가필요에의해만든함수라는의미이다. Function 프로시저 Function 프로시저의작성도 Sub 프로시저와유사하다. Function 프로시저는코드를실행하고 결과값을그프로시저의이름에담아서반환한다. Ex) Function 프로시저의작성과호출 16

17 Sub AAA() Dim A as Integer A = HowVal(10) Module1 Function HowVal(intNum As Integer) HowVal = intnum * 50 End Function Function 프로시저를호출할때는프로시저의이름을바로입력한다. 또한, Function 프로시저에인수 ( 위의예에서는 intnum) 를사용하도록작성하였다면, 인수도함께입력한다. 작성한 AAA() 프로시저를실행하면 Function 프로시저에서인수를 10으로하여 HowVal Function 프로시저를호출하였으므로 HowVal = 10 * 50 이되어, HowVal(10) 은 500이되고이값이변수 A에기록된다. 17

18 6. 변수 변수는프로그램내에서수시로변화하는값 ( 문자, 숫자 ) 을관리하기위해서사용한다. 예를들어 5, 10, 20이라는숫자가있을때, 이세개의숫자를특정숫자 15와비교하여 크다, 또는 작다 라는메시지를표시하는프로그램을만들고자한다. 이때만약각각의숫자를직접비교하도록프로그램을작성한다면코드가복잡하고길어지게된다. 이러한경우각각의값을변수에임시로저장하여비교하도록프로그램을작성하면코드작성이간편해질뿐아니라, 가독성이높아져코드의검토및수정작업이용이해진다. 변수의선언 Dim : 가장일반적인변수선언방법이다. 변수의선언에는프로시저내에서선언하여프로시저수준의변수로사용하는방법과프로시저외부, 즉모듈에선언하여모듈수준의변수로사용하는방법이있다. Dim 변수이름 As 속성 Public : Public문으로변수를모듈에선언하면, 프로젝트의모든프로시저에서인식되는변수로사용할수있다. VBE 옵션에서 [ 변수선언요구 ] 를체크하면, 코드창에 Option Explicit 라는문이자동으로입력된다. 이것은변수를반드시선언하도록하고, 선언하지않은변수는사용할수없도록하는기능을한다. 변수를선언하여사용하면코드의가독성이좋아지고오류발생시디버그가용이해진다. Ex) 아래의두개의모듈에서선언된변수들에값을지정하고자할때, 각각의변수를인 식및사용할수있는지여부를비교해보도록한다. ( 변수에값을지정할때는 변수이름 = 지정할값 의형식으로작성한다.) Module1 Module2 Dim A 모듈수준의변수 Public B 프로젝트수준의변수 Sub AAA() A = 10 가능 B = 100 가능 Sub BBB() A = 10 불가능 B = 100 가능 이름지정규칙 VB에서프로시저, 변수, 상수, 인수등의이름을정할때는다음과같은규칙에유의하여사용하도록한다. 1) 이름은문자로시작해야한다. 즉, 숫자, 형식선언문자등으로시작하는이름은사용할수없다. 18

19 2) 이름에는공백이나마침표 (.), 느낌표 &, $, # 문자를사용할수없다. 3) 이름은최대 255자를넘을수없다. 4) VB의예약어는사용할수없다. 즉, VB에서내부적으로이미정해진함수, 속성, 메서드명은이름으로사용할수없다. 5) 모듈에서같은이름의변수를선언할수없다. A. 변수 ( 변경가능 ): a=15 B. 상수 ( 변경불가 ): Cost pi= C. 연산자 : i. 사칙연산자 + - * / ii. 지수연산자 ^ iii. 비교연산자 < <= > >= <> D. InputBox() 함수는변수로입력된내용을받는다. 변수 = InputBox( 메시지, [ 제목 ]) E. MsgBox() 함수는메시지를윈도우형태로출력한다. F. MsgBox 메시지, [ 단추형식 ], [ 제목 ] Msgbox 안녕하세요?, vbok, 제목1 MsgBox 계속진행하겠습니까?, vbyesnocancel, 제목2 G. 데이터선언문 Dim a As Integer Dim b As Long Dim c As String*4 Dim d As String Dim e As Double Dim f As Single Dim g As Variant -32,768 ~ 32,767 사이의정수더큰정수길이가 4인문자일반적인문자실수 (8 byte) 실수 (4 byte) 모든데이터형태가가능 19

20 H. For~Next구문 For 카운터변수 = 시작값 To 종료값 [ 건너뛸값 ] 처리할내용 Exit For Next [ 카운터변수 ] I. Do~Loop 구문 Do [While 조건 ] [Until 조건 ]. [Exit Do]. Loop J. If ~ Then 문 If 조건1 Then ElseIf 조건2 Then Else. End If K. Select ~ Case 문여러가지경우에대하여구분하여사용하는문장 Select Case 변수 Case 95 To 100 Hakjum = A+ Case 90 To 94.9 Hakjum = A. [Case Else].. End Select 20

21 7. Excel VBA UserForm 사용설명 7-1. User form 작성 프로그램의작업수행에필요한데이터나명령등을입력받기위하여사용자가원하는 User form을디자인하여사용할수있다. [ 삽입 사용자정의폼 ] 을선택하여 User form을생성하고, [ 도구상자 ] 의개체들을원하는곳에위치시켜서작성한다. ([ 도구상자 ] 가보이지않을경우 [ 보기 도구상자 ] 선택 ) 예제 ) User form 작성 7-2. Macro Function Macro Function 은반복되는복잡한작업의수행을용이하게하기위한기능이다. 작업과 정을기록해두었다가필요한경우에실행시켜자동으로작업을수행하도록한다. 예제 ) Macro 작성및코드확인 2주차에실습했던데이터테이블을 Macro를사용하여다시작성하도록한다. 작성된 Macro의코드를확인하고, 직접코드를입력하여 Macro를만들어본다. ([ 매크로 매크로보기 ] 에서작성된 Macro1을선택하고 [ 편집 ], 또는 Alt+F11 VBA Editor의프로젝트탐색기에서 [ 모듈 Module1] 더블클릭 ) 7-3. 실행파일 (Procedure) Module - 프로그램의코드를입력하는창 (1) 표준 Module - Sheet Module, ThisWorkbook Module, 모듈 Module (2) 폼 (Form Module) (3) 클래스모듈 (Class Module) 21

22 Procedure - 코드에서각각의기능을수행하는프로그램의한단위 (1) Public / Private - 적용범위 ( 호출가능범위 ) (2) Sub / Function - 결과값반환여부변수 - 작업을수행하는데있어서필요한값들을임시로저장 String, Single, Integer, Boolean 예제 ) Procedure 작성 Message Box / 변수사용 Message Box 로부터값을입력받아변수에저장하기위한 Procedure Message Box / If 문 Message Box 로부터입력되는값에따라각각다른 Message Box 를호출 [ 예 ] 선택 [ 아니오 ] 선택 암호확인프로그램 (Input Box / Message Box / If 문 / Do Loop 문 ) 22

23 Input Box 로암호를입력받는프로그램, 3 회까지시도 암호일치 암호불일치 7-4. User form 의명령단추 User form - 데이터나명령을입력또는출력하기위해사용자가원하는 Form을디자인하여사용하는것 Form Module - User form에서의 event(ex: 명령단추클릭 ) 에따라실행되는 procedure를작성하는곳 예제 ) 암호설정 / 확인프로그램 Userform 을사용한초기화면 23

24 [ 암호설정 ] 을선택한경우암호설정 form 호출 [ 암호확인 ] 을선택한경우앞에서작성한 암호확인프로그램 호출 암호확인프로그램 대신 UserForm3 을호출하여암호를확인하도록수정 24

25 엑셀 VBA TUTORIAL 문제 1. 엑셀 VBA의기본연습 A. 1부터 100까지수열의합을구하고그값을 MsgBox를이용하여보여라. For~Next 문과 Do~While 문을사용하여라. B. 시작하는정수와마지막정수를 InputBox를이용하여읽고그합을구하라. C. InputBox를이용하여읽은시작하는정수와마지막정수를워크시트내의 B2, B3에보이고수열의합은 B4에보여라. 2. Select~Case 를이용하여학점을구하는 Function 을만들어보라 3. UserForm 을만들어이곳에수열의합을구하기위한시작값과마 지막값을입력하는 Box 를만들어읽은후그결과를원하는워크 시트의원하는셀에표시하여보라 주에사용하였던엑셀파일을열고 Data Table 을작성하는매크 로를녹음한후 VB Editor 를사용하여코드를이해하고그것을변 경하여자동으로 Data Table 을만드는매크로를만들어라. 25

26 엑셀과 VBA 을연결시키는프로젝트예제 Manual 1. 엑셀파일을다음과같이수정한다. (1) 가정치들을별도의워크시트에만들고그이름을 Assumption 이라고한다. (2) Income Statement 는별도의워크시트에만들고 ( 이름을 Income-Statement 라고함 ) 필요한함수들을수정한다. (3) 별도의워크시트에로그인할때나타날화면을만든다. 워크시트이름을 Login 화면 26

27 이라고한다. (4) 메인메뉴가나타날화면을별도의워크시트에만들고이름을 배경화면 이라한다. (5) 로그아웃할때나타날화면을별도의워크시트에만들고이름을 Logout 화면 이라 한다. 27

28 28

29 2. User-Form을만든다 (1) Alt+F11 를누르고매크로이름을입력한후 VBA Editor로들어간다. (2) 삽입-사용자정의폼 (User-Form) 을선택한다. 사용자정의폼의이름을 UserForm1 이라고한다. (3) 명령단추를눌러선택한후마우스를이동하여원하는위치에서왼쪽버튼을누른후드래그하여원하는위치에서버튼을떼면명령단추가만들어지고그이름이 CommandButtion1이된다. 같은방법으로원하는만큼의버튼을만든다. 이명령단추들은아직매크로가연결되어있지않다. 29

30 속성창에서각버튼의 Caption 을변경하고 Font 를원하는크기로조정한다. 도구상자의레이블을선택하여 UserForm1 의제목을 Main Menu 라고입력한다. 30

31 (4) UserForm2 를다음과같이만든다. 여기서는도구상자의텍스트상자를이용한다. 네개의박스의이름은 TextBox1, TextBox2, TextBox3, TextBox4 31

32 3. Macro 기록을이용하여필요한매크로를만든다 (1) 매크로기록을이용하여워크시트이동하는매크로를만든다. 매크로기록을시작하고그이름을 메인메뉴 로정한다. 배경화면을선택하고 A1을선택한다. 기록중지를누른후편집을보면 Public Sub 메인메뉴 () Worksheets(" 배경화면 ").Select Range("A1").Select 여기에메인메뉴가나타나게하려면 UserForm1.Show를삽입한다. Public Sub 메인메뉴 () Worksheets(" 배경화면 ").Select Range("A1").Select UserForm1.Show 이매크로를실행하여제대로작동하는지확인한다. Income-Statement 워크시트와 Assumption 워크시트옆에삽입-도형에서원하는도형을삽입하고이도형위에마우스를가져간후오른쪽마우스를눌러방금작성한 메인메뉴 매크로를지정하여작동하는지확인한다. (2) 매크로기록을선택한후이름을 MyScreen 이라한다. 32

33 엑셀매뉴에서보기 표시 / 숨기기에서수식입력줄, 눈금선, 머릿글의선택을취소한 후다시선택한다. 기록을중지하고매크로편집기에서함수를살펴보면서다음과같이 수정한다. Sub MyScreen() ' 매크로기록을이용하여작성한다. ' Formular 입력창을닫는다 Application.DisplayFormulaBar = False ' Row & Column의번호를나타내는 Headings 를닫는다 ActiveWindow.DisplayHeadings = False ' Row & Column의 Gridline을없앤다 ActiveWindow.DisplayGridlines = False ' Full screen mode로전환한다 Application.DisplayFullScreen = True Sub OriginalScreen() ' Formular 입력창을연다 Application.DisplayFormulaBar = True ' Row & Column의번호를나타내는 Headings 를연다 ActiveWindow.DisplayHeadings = True ' Row & Column의 Gridline을보이게한다 ActiveWindow.DisplayGridlines = True ' Full screen mode로전환한다 Application.DisplayFullScreen = True ' 원래 screen으로전환한다 Application.WindowState = xlnormal 이두매크로를실행하여화면이어떻게변화하는지살펴본다. (3) 매크로기록을선택하고이름을 DataTable1 작성 이라고한후 Data Table 을 33

34 Assumption 워크시트의우측에만든다. 기록을중지한다. 4. Macro 만들기와편집을이용하여필요한프로그램을만든다. (1) UserForm2를화면에올리면서 TextBox1,2,3,4에워하는값이나타나도록한다. 이를위해매트로새로만들기를선택하여이름을 Assumption보기 로하고다음과같이입력한다. Public Sub Assumption보기 () ' 메인메뉴가사라진다. UserForm1.Hide ' Assumption 워크시트의 D3:D7의값이 TextBox 1~4에나타나게한다. UserForm2.TextBox1.Value = Worksheets("Assumption").Range("D3").Value UserForm2.TextBox2.Value = Worksheets("Assumption").Range("D4").Value UserForm2.TextBox3.Value = Worksheets("Assumption").Range("D5").Value UserForm2.TextBox4.Value = Worksheets("Assumption").Range("D7").Value ' 가정치입력을위한 UserForm2가나타난다. UserForm2.Show (2) TextBox 1,2,3,4에서값이수정될경우수정된값들을 Assumption 워크시트의원하는 Range로보내주는매크로를기록한다. 이름은 Assumption수정 이라한다. Public Sub Assumption수정 () 34

35 ' TextBox 1~4의수정된값이 Assumption 워크시트의 D3:D7에입력된다. Worksheets("Assumption").Range("D3").Value = UserForm2.TextBox1.Value Worksheets("Assumption").Range("D4").Value = UserForm2.TextBox2.Value Worksheets("Assumption").Range("D5").Value = UserForm2.TextBox3.Value Worksheets("Assumption").Range("D7").Value = UserForm2.TextBox4.Value ' 가정치입력을위한 UserForm2가사라지고메인메뉴로돌아간다 UserForm2.Hide 메인메뉴 (3) DataTable1작성매크로중일부를다음과같이수정한다. InputBox를이용하여 Data Table에서매출증가율의 Minimum과 Maximum을입력하게하고이두값을이용하여 5개의값으로자동계산하게한다. Sub DataTable1작성 () ' Dim Max, Min As Single Dim i As Integer ' DataTable1작성 Macro ' 데이터테이블을작성할워크시트를오픈한다. Sheets("Assumption").Select ' 데이터테이블의 Column Input Cell 의최대, 최소값을물어서자동으로 5 단계로나누 어계산한다. theprompt = "Enter the minimum value for the annual sales increase rate." thetitle = "Min/Max 입력창 " thedefault = 0.06 Min = InputBox(thePrompt, thetitle, thedefault) theprompt = "Enter the maximum value for the annual sales increase rate." thetitle = "Min/Max 입력창 " thedefault = 0.1 Max = InputBox(thePrompt, thetitle, thedefault) ' 매출증가율을 Min, Max 사이에 5단계로나누어계산하고데이터테이블의 Column Input에해당되는곳이입력한다. Range("G7").Select ActiveCell.FormulaR1C1 = Min 35

36 Range("G8").Select ActiveCell.FormulaR1C1 = Min + (Max - Min) / 4 Range("G9").Select ActiveCell.FormulaR1C1 = Min + (Max - Min) * 2 / 4 Range("G10").Select ActiveCell.FormulaR1C1 = Min + (Max - Min) * 3 / 4 Range("G11").Select ActiveCell.FormulaR1C1 = Max ' 이하의모든매크로코드는매크로기록을이용하여구한것으로수정이필요없다. 이렇게수정된매크로를실행하여정상적으로실행되는지확인한다. 5. 매크로명령들을 User-Form 의명령단추에연결한다. (1) UserForm1 을열고각명령단추를더블클릭하고, 각명령단추를클릭할경우실행할 매크로이름또는매크로코드를입력한다. Private Sub CommandButton1_Click() Assumption 보기 Private Sub CommandButton2_Click() UserForm1.Hide Worksheets("Income-statement").Select Range("A1").Select Private Sub CommandButton3_Click() UserForm1.Hide DataTable1작성 Private Sub CommandButton4_Click() UserForm1.Hide Auto_Close (2) UserForm2 에있는명령단추에대한매크로 36

37 CHANGE, CANCEL 두버튼을더블클릭한후각단추에대한매크로를입력한다. Private Sub CommandButton1_Click() Assumption 수정 Private Sub CommandButton2_Click() UserForm2.Hide 메인메뉴 이제는메인메뉴매크로를실행하여전체프로그램이어떻게조정되는지확인하라. 6. 자동로그인, 자동로그아웃을위한프로그램을만든다. (1) 엑셀파일을열때최초로실행되는파일은 Auto-Open 이다. 여기에서파일에접속할 수있는권한을물어볼수있다. Public Sub Auto_Open() ' 엑셀이열리면서자동으로실행된다. ' 초기화면으로이동한다 Sheets("Login화면 ").Select Range("A1").Select ' InputBox를이용하여패스워드나이름을입력하기위한준비 theprompt = "Please enter the password." thetitle = " 경영IT실무프로젝트 Login" thedefault = " 홍길동 " ' 입력이올바르면메인메뉴로이동하고 3번틀리면나가게한다 ' i는암호입력회수를나타낸다. i = 0 Do While i < 3 i = i + 1 thereply = InputBox(thePrompt, thetitle, thedefault) If thereply = "*****" Then MsgBox "Welcome to 경영 IT 실무프로젝트 " ' 엑셀메뉴를사라지게한다. 37

38 MyScreen ' UserForm1이나타나게한다. 메인메뉴 Exit Do Else Beep theprompt = "Invalid! Please check and enter the password again." End If Loop If i = 3 Then Sheets("Logout화면 ").Select Range("A1").Select MsgBox "Sorry, you are not allowed to access." Workbooks("Excel-VBAproject.xlsm").Saved = True Workbooks("Excel-VBAproject.xlsm").Close End If 3 번의로그인기회를주려면다음과같이 Do While. Loop 를이용하여수정한다. 이름 은반드시 Auto_Open 이어야한다. 수정불가. Public Sub Auto_Open() ' 엑셀이열리면서자동으로실행된다. ' 초기화면으로이동한다 Sheets("Login화면 ").Select Range("A1").Select ' InputBox를이용하여패스워드나이름을입력하기위한준비 theprompt = "Please enter the password." thetitle = " 경영IT실무프로젝트 Login" thedefault = " 홍길동 " thereply = InputBox(thePrompt, thetitle, thedefault) If thereply = "*****" Then MsgBox "Welcome to 경영IT실무프로젝트 " ' 엑셀메뉴를사라지게한다. MyScreen 38

39 ' UserForm1이나타나게한다. 메인메뉴 Else Beep Sheets("Logout화면 ").Select Range("A1").Select MsgBox "Sorry, you are not allowed to access." Workbooks("Excel-VBAproject.xlsm").Saved = True Workbooks("Excel-VBAproject.xlsm").Close End If (2) 프로그램을끝낼때자동으로실행되는파일은 Auto_Close이다. Public Sub Auto_Close() ' 프로그램을종료할때자동으로실행된다. Sheets("Logout화면 ").Select Range("A1").Select OriginalScreen Workbooks("Excel-VBAproject.xlsm").Saved = False MsgBox "See you again!" Workbooks("Excel-VBAproject.xlsm").Close 7. User-Form 의 TextBox, ListBox, ComboBox 등의포맷을변경한다. 이제는프로그램이좀더효과적으로보이도록업그레이드해보자. (1) UserForm2의 TextBox1,2,3,4에포맷을추가하려면 Assumption보기 매크로를다음과같이수정한다. Public Sub Assumption보기2() ' Assumption보기에서숫자의 Format을지정해준다. ' 메인메뉴가사라진다. UserForm1.Hide ' Assumption 워크시트의 D3:D7의값이 TextBox 1~4에나타나게한다. ' 각 TextBox 에나타날숫자의형식을지정한다. UserForm2.TextBox1.Value = Format(Worksheets("Assumption").Range("D3").Value, "$##,###.00") 39

40 UserForm2.TextBox2.Value = Format(Worksheets("Assumption").Range("D4").Value, "##.00%") UserForm2.TextBox3.Value = Format(Worksheets("Assumption").Range("D5").Value, "##.00%") UserForm2.TextBox4.Value = Format(Worksheets("Assumption").Range("D7").Value, "##.00%") ' 가정치입력을위한 UserForm2가나타난다. UserForm2.Show (2) UserForm2 에 TextBox 외에 ListBox, ComboBox 등을이용하려면새로운입력폼 (UserForm3) 을만들고 Assuption 보기 매크로와 Assumption 수정 매크로를다음과 같이수정한다. ComboBox1 ListBox1 (3) UserForm3 를이용하여가정치를보기위하여 Assumption 보기 매크로를수정한다. Public Sub Assumption 보기 3() 40

41 ' Assumption보기에서숫자의 Format을지정해준다. ' 메인메뉴가사라진다. UserForm1.Hide ' Assumption 워크시트의 D3:D7의값이 TextBox 1~4에나타나게한다. ' 각 TextBox 에나타날숫자의형식을지정한다. UserForm3.TextBox1.Value = Format(Worksheets("Assumption").Range("D3").Value, "$##,###.00") UserForm3.TextBox2.Value = Format(Worksheets("Assumption").Range("D4").Value, "##.00%") ' TextBox3 대신에 ComboBox 사용해본다 UserForm3.ComboBox1.Value = Format(Worksheets("Assumption").Range("D5").Value, "##.00%") UserForm3.ComboBox1.AddItem "5%" UserForm3.ComboBox1.AddItem "10%" UserForm3.ComboBox1.AddItem "15%" UserForm3.ComboBox1.AddItem "20%" ' TextBox4 대신에 ListBox1을이용해본다. UserForm3.ListBox1.AddItem "50%" UserForm3.ListBox1.AddItem "60%" UserForm3.ListBox1.AddItem "70%" UserForm3.ListBox1.AddItem "80%" ' 가정치입력을위한 UserForm2 가나타난다. UserForm3.Show (4) UserForm3에서수정된값을엑셀로돌려주기위하여 Assumption수정 을다음과같이고친다. Public Sub Assumption수정3() ' TextBox 1~4의수정된값이 Assumption 워크시트의 D3:D7에입력된다. Worksheets("Assumption").Range("D3").Value = UserForm3.TextBox1.Value Worksheets("Assumption").Range("D4").Value = UserForm3.TextBox2.Value ' ComboBox1 에서의선택항목을확인 For i = 0 To 3 If UserForm3.ComboBox1.ListIndex = i Then 41

42 Next End If Worksheets("Assumption").Range("D5").Value = UserForm3.ComboBox1.List(i) ' ListBox1 에서의선택항목을확인 For j = 0 To 3 If UserForm3.ListBox1.Selected(j) Then Worksheets("Assumption").Range("D7").Value = UserForm3.ListBox1.List(j) End If Next ' 가정치입력을위한 UserForm2가사라지고메인메뉴로돌아간다 UserForm3.Hide 메인메뉴 42