2018 연구결과보고서 엑셀 VBA 로구현하는실생활프로그램 ILS 정보교과연구회
- 목차 - Chapter A. 1. 비주얼베이직 1 2. 엑셀에서 Visual Basic 시작하기 1 3. 비주얼베이직의기초개념 2 4. 비주얼베이직기초 5 5. 변수와상수 6 6. MsgBox 11 7. 조건문 14 8. 반복문 18 9. 엑셀시트접근하기 20 Chapter B. 엑셀 VBA 로구현하는실생활프로그램 1. 도서관도서검색프로그램 23 2. POS(Point of Sale) 27
Chapter A.
1. 비주얼베이직 (Visual Basic) 비주얼베이직 (Visual Basic) 은프로그래밍에입문하려는사람들에게프로그래밍을쉽게이해하고작성할수있도록하는프로그래밍언어이다. 비주얼베이직은과거베이직 (Basic) 부터시작되어 GW-Basic, Quick Basic 등을이용하여콘솔에서텍스트형식으로작성되던것을마이크로소프트 (Microsoft) 에서윈도우 GUI(Graphic User Interface) 형식으로프로그램을쉽게개발할수있도록만든언어가 Visual Basic이다. 비주얼베이직은프로그래밍의입문자들이쉽게배울수있지만, 하드웨어를제어하지못하는단점이있다. 하지만, 비주얼베이직은 MS-Office 계열의프로그램에심을수있는 VBA(Visual Basic for Application) 가있어, MS-Office 프로그램들을더욱강력하게만들수있는언어이다. 그래서단순한프로그래밍의감을익히기위한초급언어라고생각하면안된다. 보통의가정집에서비주얼베이직을따로설치하여사용하는경우는드물다. 왜냐하면가정에서프로그램을개발하는경우가드물기때문이다. 하지만엑셀은일반가정에서도자료정리및계산을위해많이사용된다. 그래서일반가정에도엑셀프로그램이설치된것을쉽게볼수있다. 그래서앞으로비주얼베이직을비주얼베이직스튜디오가아닌엑셀의비주얼베이직에디터 (Visual Basic Editor) 를이용하여비주얼베이직의문법을익히도록하겠다. 그리고비주얼베이직이익숙해지면엑셀 VBA도하도록하겠다. 2. 엑셀에서 Visual Basic 시작하기 가. Visual Basic 실행 1 엑셀을실행한다. 2 Alt + F11 키를누른다. - 1 -
나. Hello, Visual Basic 출력하기. 1) 엑셀개발도구꺼내기 : [ 파일 ]-[ 옵션 ]-[ 리본사용자지정 ]- 개발도구 체크 2) 버튼만들기 : [ 개발도구 ]-[ 삽입 ]- (Active X 컨트롤 ) 버튼 선택 - 드래그 3) 디자인모드 에서버튼더블클릭 4) 코드 (Code) 3. 비주얼베이직의기초개념 비주얼베이직을시작하기위해서는개체와속성, 메서드, 그리고이벤트에대한이해가필요하다. 비주얼베이직도객체지향프로그래밍 (OOP : Object Oriented Programming) 이기때문에 Object에대한개념부터알아보자. 가. 개체 (Object) 흔희들객체, 또는개체라고불리는 Object는우리가구분할수있는것들을말한다. 프로그램제작시윈도우폼에들어가는커맨드버튼이라든가텍스트박스등프로그램작성중에사용되는컨트롤들을우리는개체 (Object) 라고부른다. 예를들면교실에서구분할수있는것은모두개체이다. 책상, 의자, 칠판, 시계, 사물함등구분이가능한모든것이개체이다. 나. 개체의속성 (Property) 개체의속성은개체가갖는성질을나타낸다. 예를들면개체에서보이는이름이라든가색깔, 위치값등개체가갖는성질이다. 이러한개체의성질은사용자가접근하여수정이가능하다. 예를들면책상의속성은 1학년 1반교실의 1분단앞쪽에위치하고, 상판이나무로되어있으며, 높이는 70Cm 정도이다. 라고이야기할수있다. 다. 개체의메서드 (Method) 개체의메서드는개체가할수있는일이다. 예를들어텍스트박스의내용을지운다던가, 포커스를이동하는것등이개체가할수있는일이다. 위에서책상은무슨일을할수있을까? 책을올려놓는것, 학생이공부를할수있는것, 높은곳을올라가기위한발판으로사용할수있는것등이책상이할수있는일이다. - 2 -
실습하기 우리주변의개체를찾아보자 개체속성 (3 개이상 ) 메서드 (3 개이상 ) 라. 이벤트 (Event) 이벤트는개체와는별개로생각해야한다. 개체와관련된개념이아니라는뜻이다. 이벤트는말그대로 사건 이다. 어떤사건인가하면, 윈도우상에서발생하는사건들을말한다. 예를들면윈도우창에서마우스를 클릭 한다던가, 이동 하거나 드래그, 키보드버튼을누르는것, 창이닫히는것 등윈도우에서발생할수있는여러가지사건들을이벤트라고한다. 이벤트명 Click() DblClick() Drag() KeyPress() Terminate() 설명마우스왼쪽버튼을누른사건마우스왼쪽버튼을더블클릭한사건마우스를드래그하는사건키보드를누른사건윈도우가종료한사건 - 3 -
마. 코드의이해 개체의이름 이벤트이름 Private Sub CommandButton1_Click() MsgBox "Hello, Visual Basic" 화면에나타낼내용 메시지박스를나타내라는명령어 바. 실행모습 실습하기다음문제를수행하시오. 1. 버튼을삽입하여다음과같이속성을설정하시오. 속성명 속성 ( 이름 ) cmd_ 이름 Caption 이름 2. 이름 버튼을클릭하면자신의이름이나타나도록코딩하시오. - 4 -
4. 비주얼베이직의기초 가. 프로시저 (Procedure) VBA에서원하는작업단위로수행할수있는명령로구성할수있는작업의단위이다. 이러한프로시저의작성단위에대한제한은없지만, 수행해야하는작업단위별로쪼개서프로시저를작성하는것이작성하기도좋고보기도좋다. 이렇게작업단위별로프로시저를작성하여필요시한프로시저에서다른프로시저를호출하도록하면된다. 프로시저는두부분으로나눌수있는데, 정의부와호출부이다. 정의부는프로시저가해야할일을작성한부분으로보통모듈에작성된다. 그리고호출부는매크로나개체, 다른프로시저에서프로시저를호출할수있도록작성된다. 나. 서브프로시저 Sub 키워드를사용하여선언하는프로시저의구조는다음과같다. [Private Public] [Static] Sub 프로시저명 [ 매개변수 ] 프로시저의이름은변수명작성규칙과같다. 또한프로시저의이름이셀주소와같아서도안된다. 대부분의프로그래머들은프로시저의이름을보고어떠한일을하는지알수있도록의미있는이름을부여한다. 예를들어서 Arraysort, Find_name 등과같이언더바 (_) 를이용하여조금길더라도프로시저이름만보고도프로시저가하는일을알수있도록한다. 1) Private 프로시저 Private 프로시저는동일한모듈에있는다른프로시저를호출할수있다. 그러나서로다른모듈에있는경우에는호출할수없다. Private Sub... Find_name() 2) Public 프로시저 Public 프로시저는같은워크북상의어떤프로시저에서도호출할수있는프로시저이다. 그리고 Public 프로시저는기본프로시저로서 Public 키워드는생략이가능하다. 다음두개의프로시저는동일한프로시저이다. Public Sub... Create_Record() - 5 -
Sub Create_Record()... 5. 변수와상수 가. 변수 (Variable) 변수는어떤값을보관하기위한기억장소이다. 사전적인의미로 ' 가변적인 ' 이라는뜻을가진단어로서, 변수는프로그램이작동되는동안기억하는값이변경될수있다. 그리고변수는한번에하나의값만기억한다. 그래서변수는가장최근의값만기억한다. 1) 변수형 1 정수형 : 소수점이없는정수의값만기억하는변수형. ( Integer, Long ) 2 실수형 : 소수점이있는실수의값만기억하는변수형. ( Single, Double ) 3 문자형 : 문자를기억하는변수형. ( String ) 4 논리형 : 논리값인참또는거짓을기억하는변수형. ( Boolean ) 5 가변형 : 어떤값이든기억할수있는변수 ( Variant ) * = 의이해 컴퓨터에 = 은두가지용도로사용된다. 1. 대입 : 우변의값을좌변에대입. A = B + C -> 우변의 B + C 연산한결과를좌변의변수 A 에대입 ( 저장 ) 한다. 2. 같다. : 좌변과우변의값이같음. IF A = B THEN -> 조건을묻는식에서는변수 A 와변수 B 의값이같은지묻는관계연산자로서사용됨. 2) 변수의영역 1 지역변수 (Local Variable) 지역변수는프로시저내에서선언하여사용하는변수로, 해당프로시저가실행될때메 모리에변수공간을할당받고, 해당프로시저가종료되면변수공간이메모리에서해제된 다. * 선언방법 Dim 변수명 as 변수 - 6 -
* 사용방법 Public Sub CommandButton1_Click() Dim Num as Integer Num 변수는정수를기억하는변수 2 모듈변수 (ModualWide Variable) 모듈변수는같은모듈내의모든프로시저가사용할수있는변수로서모듈내의프로시저 보다먼저선언되어야한다. Dim Cnt as Integer Cnt 변수는 Sub 프로시저나 Function 보다먼저선언된모듈변수이다. Public Sub CommandButton1_Click() cnt=cnt+1 프로시저 Function Test2() cnt=cnt+1 End Function 3 공용변수 (Public Variable) 프로젝트내의모든모듈에있는프로시저에서동일한변수를사용할수있는변수이다. - 7 -
Public tot as Integer Sub Test1() tot=tot+sum tot 변수는 Public 예약어를 사용했으므로공용변수이다. Sub Test2() tot=tot-min - 8 -
4 정적변수 (Static Variable) 모듈내에서선언하며프로시저가종료된후에도값이유지되어해당모듈내의모든프 로시저에서사용이가능하다. Static Num as Integer -> Num 변수는정수를기억하는정적변수 3) 변수의크기 변수타입데이터형필요한크기저장가능한범위 Byte 정수형 1 Byte 0~255 Boolean 논리형 2 Byte True or False ( 참또는거짓 ) Integer 정수형 2 Byte -32,768 ~ 32767 Long 정수형 4 Byte -2,147,483,648 ~ 2,147,483,647 Single 실수형 4 Byte Double 실수형 8 Byte Currency 실수형 8 Byte -3.402823E38 ~ -1.401298E-45( 음수값 ) 1.401298E-45 ~ 3.402823E38( 양수값 ) Date 날짜형 8 Byte 100 년 1 월 1 일 ~ 9999 년 12 월 31 일 Object 개체형 8 Byte 모든오브젝트변수 Sting 문자형문자열길이최대 65,535 Variant( 숫자 ) 가변형 16 Byte Double 형범위 Variant( 문자 ) 가변형 22 Byte+ 문자열길이 String 형범위 나. 상수 (Constant) 상수는어느곳에사용해도의미 ( 값 ) 이변하지않는값을말한다. 사전적인의미로 ' 변함없는 ' 이라는뜻을가진단어로서, 프로그램이작동되는동안의미가변하지않는다. 1 숫자상수 : 자연수나시수와같이우리가일반적으로알고있는수치값이숫자상수이다. 예 ) 13, 15.4, 1000 2 문자상수 : 한개의문자를상수로간주한것으로작은따옴표를이용하여상수를나 타낸다. 예 ) 'A', 'B', '@' - 9 -
3 문자열상수 : 한개의이상의문자를상수로간주한것으로큰따옴표를이용하여나 타낸다. 예 ) "KOREA", "SCHOOL", "WHO?" 실습하기다음코드를입력하고, 결과를출력해보자. Dim A as Integer Dim B as Integer A=36767 B=36768 MsgBox A MsgBox B 1. A 를출력한값은얼마인가? 2. B 를출력한값은얼마인가? 3. B 값이위처럼출력된이유는무엇인가? - 10 -
6. MsgBox 메시지박스는간단한내용을화면에보여주는기능이다. 메시지박스의기본구조는다 음과같다. MsgBox Prompt,[Buttons as VBMsgBoxStyle = vbokonly],[title],[helpfile],[context] 1 Prompt : 메시지박스에표시할텍스트 2 VbMsgBoxStyle : 메시지박스에표시할버튼스타일선택 3 Title : 메시지박스제목표시줄에표시할제목 4 HelpFile : F1을눌렀을때도움말파일지정 5 Context : HelpFile을지정했을때반드시사용해야하는항목 메시지박스스타일 (VbMsgboxStryle) 옵션의종류 상수 값 설명 vbokonly 0 확인 (OK) (Default) vbokcancel 1 확인 (OK), 취소 (Cancel) vbabortretryignore 2 중지 (Abort), 다시시도 (Retry), 무시 (Ignore) vbyesnocancel 3 예 (Yes), 아니오 (No), 취소 (Cancel) vbyesno 4 예 (Yes), 아니오 (No) vbretrycancel 5 다시시도 (Retry), 무시 (Ignore) vbcritical 16 주의아이콘 vbquestion 32 물음표아이콘 vbexclamation 48 느낌표아이콘 vbinformation 64 정보아이콘 vbdefaultbutton1 0 첫째단추가기본값 (Default) vbdefaultbutton2 256 둘째단추가기본값 vbdefaultbutton3 512 셋째단추가기본값 vbdefaultbutton4 768 넷째단추가기본값 vbapplicationmodal 0 모달메시지상자 (Default) vbsystemmodal 4096 시스템모달메시지상자 vbmsgboxhelpbutton 16384 메시지상자에도움말단추를추가 vbmsgboxsetforeground 65536 메시지상자를전경창으로지정 vbmsgboxright 524288 텍스트가오른쪽맞춤되었습니다 vbmsgboxrtlreading 1048576 히브리어와아랍어시스템의경우텍스트의읽 - 11 -
상수값설명 기방향이오른쪽에서왼쪽으로나타나도록함 메시지박스의버튼에따른상수값 상수 값 설명 vbok 1 확인 vbcancel 2 취소 vbabort 3 중단 vbretry 4 다시시도 vbignore 5 무시 vbyes 6 예 vbno 7 아니오 1) MsgBox를명령어로사용하기메시지박스를명령어로사용한다는것은 MsgBox를사용하고결과값을반환받지않겠다는것이다. 명령어로사용하는경우 확인, 취소 버튼으로명령을구분해서처리할수없다. 다음을보자. MsgBox 아침을드셨나요?,vbOkCancel - 12 -
2) MsgBox를메서드로사용하기메시지박스를메서드로사용하면 MsgBox의버튼에따라결과값을반환받아버튼에따라명령을다르게처리할수있다. 다음을보자. Dim answer as Integer answer = MsgBox( 아침을드셨나요?,vbOkCancel) If answer = vbok Then MsgBox 네 Else MsgBox 아니오 End If vbok 메시지박스에서 확인 버튼을누르면 answer는 vbok를기억합니다. vbok는상수값 1이다. vbcnacel 메시지박스에서 취소 버튼을누르면 answer는 vbcancel를기억합니다. vbcancel은상수값 2이다. 메시지박스버튼중 확인 버튼을누르면 answer가 1을저장하게되고, 취소 버튼을누르면 answer가 2를저장하게된다. 그러면다음에배울제어연산자 (If) 를활용하여위의보기처럼 확인 버튼을눌렀을경우 네, 취소 버튼을눌렀을경우 아니오 가메시지박스로출력되게된다. - 13 -
7. 조건문 조건문은선택을해야할때사용할수있다. 합격여부를판단하기위해점수가 80점이상이면합격이고, 80점미만일경우불합격을알려줘야하는경우다음과같이표현할수있다. 또는 3 개이상을구분해야할때, 다음과같이구분할수있다. 그럼비주얼베이직에서조건문을사용해보자. 비주얼베이직에서조건문을사용하기위해 서는 If - Then 명령을사용해야한다. If Then 명령의기본형식은다음과같다. 기본형식 If 조건 Then End If 참일때수행할명령 조건은참 (True) 또는거짓 (False) 의 결과값을갖는다. 그럼위의문제를코드로표현해보자. If 점수 >= 80 Then Msgbox 합격입니다 End If - 14 -
위의코드는조건식이참일때수행하는명령만갖추고있다. 즉, 점수가 80 점이상일때 에는 합격입니다 를출력하지만거짓일경우만에출력할수있는것은없다. 만약 If 점수 >= 80 Then Msgbox 합격입니다 End If Msgbox 불합격입니다 (A) (B) 라고코드를쓴다면 (A) 명령은 80점이상일경우에만출력되지만, (B) 명령은 80점이상일경우뿐만아니라 80점미만일경우에도출력된다. 왜냐하면 (B) 명령은 IF 블록밖에작성되었기때문이다. IF 명령은 End IF 까지만 If 블록으로처리되기때문에 (B) 블록은 IF 명령과는상관없는명령이다. If If 점수 >= >= 80 80 Then Msgbox 합격입니다 합격입니다 If 명령의범위 End If MsgBox 불합격입니다 그렇다면거짓일경우에만수행하는명령을작성해야한다면어떻게해야할까? 거짓일경 우에수행할코드는 Else 명령으로처리한다. 다음기본형식을보자. 기본형식 If 조건 Then 참일때수행할명령 Else 거짓일때수행할명령 End If If 명령블록안에 Else 명령을추가하면거짓일경우에만수행하는명령을삽입할수있 다. 그럼위의순서도를다음과같이완성할수있다. If 점수 >= 80 Then Msgbox 합격입니다 Else MsgBox 불합격입니다 End If (A) (B) - 15 -
위의코드는점수가 80 이상일경우에는 (A) 명령을수행하고, 그렇지않을경우즉, 80 점미만일경우에는 (B) 명령을수행한다. 그럼여기서조건식에사용하는부등호를살펴보자. 조건식 내용 설명 > 크다, 초과 부등호를읽을때는왼쪽을기준으로읽는다. 왜냐하면 < 작다, 미만 >= 크거나같다, 이상 <= 작거나같다. 이하 = 같다. <> 같지않다. 10 > 5 를읽을때왼쪽부터 10 은 5 보다크다 라 고읽게된다. 따라서부등호만읽을때는 크다 라고 읽는다. 5 는 10 보다작다 가틀린것은아니지만, 우리 가글을읽을때왼쪽에서오른쪽으로읽는다는 을기억하자. 사실 다음코드를보고출력하는값을적어보자. [ 문제 1] [ 문제 2] If 10 >= 5 Then Msgbox 원숭이 End If MsgBox 고릴라 위코드가출력하는값은? Dim A as Integer Dim B as Integer A = 55 B = 37 A = A + 60 B = B + A If A > B Then Msgbox A Else MsgBox B End If 위코드가출력하는값은? - 16 -
다음문제를보고코드를작성하시오. 1. 다음의합을구하는코드를작성하시오. 2. 다음의합을구하는코드를작성하시오. - 17 -
8. 반복문 반복문은일정횟수동안동일한코드를반복수행해야하거나, 조건을만족할때까지반 복해야할명령이있을때사용한다. 가. For - Next For Next 명령은반복하는횟수가정해져있을때사용하는반복명령이다. 기본형식 For 변수 = 초기값 To 종료값 Step 변화값 Next 변화값이 1 인경우에는 Step 생략가능 다음예를보자 For A=1 To 3 MsgBox A * 5 Next (A) 변수 A 출력값 1 5 2 10 3 15 4 반복종료 위코드는변수 A가 1부터 3까지 1씩증가하는반복문이다. A가 1씩증가하다가 3까지반복하기로했는데 4가되는순간반복을종료하고 (A) 의위치로이동하게된다. 반복을종하는것은종료값을지났을때종료하게되는것이다. 위코두에서 Step 1은생략되어있다. 생략하지않았다면다음과같다. For A=1 To 3 Step 1 반복을하면가장많이반복하는것이 1 씩증가하는반복이기때문에 Step 1 이기본으 로설정되어있는것이다. 그럼증가만하는반복만가능할까? 아니다. 감소하는반복도 가능하다. For A=5 To 3 Step -1 MsgBox A * 5 Next (A) 변수 A 출력값 5 25 4 20 3 15 2 반복종료 위코드에서 Step -1 이있어서반복할때마다 -1 씩변하게된다. 그러다종료값이 3 을 지나 2 가되는순간반복이종료되고 (A) 위치로이동하게된다. - 18 -
평가문제다음문제를풀어보세요. 다음코드를보고물음에답해보자. [ 문제 1] [ 문제 2] S=0 For A=1 To 10 Step 3 For A=1 To 5 MsgBox A S=S+A Next Next MsgBox A MsgBox S (A) MsgBox A (B) (1) 위코드에서 (A) 가출력하는값은? (1) 위코드에서출력하는값을모두적으 시오. (2) 위코드에서 (B) 가출력하는값은? [ 문제 3] 다음의합을구하는코드를작성하시오. [ 문제 4] 다음의합을구하는코드를작성하시오. [ 문제 5] 다음의합을구하는코드를작성하시오. [ 문제 6] 다음의합을구하는코드를작성하시오. [ 문제 7] 다음의합을구하는코드를작성하시오. [ 문제 8] 1~100 까지의수중에서 3 과 8 의공배수개수를구하는코드를작성하시오. - 19 -
9. 엑셀시트접근하기 가. 기본개념 1) 개체 (Object) 독립적으로구분할수있는것은모두개체라고한다. 우리교실에서구분할수있는것은살펴보자. 우리는교실에서책상, 의자, 컴퓨터, 칠판등을구분할수있다. 이것을개체라고한다. 개체는속성 (Property) 과메서드 (Method) 를갖는다. 1 속성 (Property) 속성은개체가갖는성질이다. 컴퓨터의속성은제조회사, CPU의성능, RAM의크기, 형태등이고, 모니터의속성은제조회사, 모니터사이즈등이다. 사람은키, 몸무게, 성별, 나이등을속성이라고할수있다. 2 메서드 (Method) 메서드는개체가할수있는행동이다. 컴퓨터는계산을할수있고, 게임을실행할수있다. 모니터는사람에게화면을보여줄수있다. 사람은말할수있고, 먹을수있고, 걸을수있는등개체가할수있는것을우리는메서드라고한다. 2) 이벤트 (Event) 윈도우에서일상적인일이아닌뜻밖의일이발생한것을우리는이벤트라고한다. 키보드를누르는것, 마우스를이동하는것, 마우스를클릭하는것등을이벤트라고한다. PC 본체에 USB 메모리를꽂으면새로운장치를발견하였다고하면서드라이버를설치하고 USB 메모리를사용할수있게된다. USB를꽂아새로운장치를발견하게된것이이벤트이고, 드라이버를설치하여사용할수있게하는것은이벤트를처리한결과인것이다. 나. Range 개체 1) 기본형식 엑셀시트의셀에접근하는개체로다음과같이사용한다. 기본형식 Range( 범위 ) 범위는엑셀에서사용하는표현대로사용 사용예는다음과같다. Range( A2 ).Value = 10 Range( A1:C2 ).Value = 30-20 -
2) Range 개체의속성 속성명 Value Formula Column Columns Row Rows CurrentRegion End 역할 ( 기본속성 ) 값셀서식열번호지정한범위의열전체행번호지정한범위의행전체기준위치부터데이터가연속적으로입력된범위기준위치부터데이터가입력된마지막위치 위의속성을이용하여데이터가입력될빈행을찾는방법을알아보자. 다음과같이입력된시트가있다. 이시트에서다음에입력될행은 7 행이다. A=Range( A2 ).CurrnetRegion.Rows.Count 위와같이입력되었을때 A가갖는값은얼마일까? [A2] 셀부터연속적으로입력된셀중 Rows( 전체행 ) 의 Count( 개수 ) 를구하는것이므로 5 를갖는다. 그러면처음에비어있던행의개수 1과다음입력행 1을더한 2 를추가하면새롭게입력될행을찾을수있다. 그러면입력될행은다음과같이구할수있다. A=Range( A2 ).CurrentRegion.Rows.Count+2 위와같은식이비어있는행을찾는데유용한방법이므로꼭기억하길바란다. 또다른방법은다음과같다. A=Range( A2 ).End(xlDown).Row - 21 -
다. Cells 개체 1) 기본형식 엑셀시트의셀에접근하는개체로다음과같이사용한다. 기본형식 Cells( 행번호, 열번호 ) Range 개체는지정된영역을한번에접근할수있고, Cells 개체는셀을한개씩접근할때유 용하다. 다음예를보자. 코드 실행결과 For i=1 To 10 Next Cells(i,1)=i 라. Sheets 개체 1) 기본형식 엑셀시트를선택하는개체로다음과같이사용한다. 기본형식 Sheets( 시트이름 ).Select Sheets 개체는사용자가지정한시트이름을이용하여시트를선택할때사용하는개체이다. 사용자가지정하는시트이름이아닌엑셀에서사용하는고유시트이름을사용하여 Sheet1 시 트를접근할때는다음과같이사용한다. Sheet1.Select - 22 -
엑셀 VBA 로구현하는실생활프로그램 Chapter B. 엑셀 VBA 로구현하는 실생활프로그램
엑셀 VBA 로구현하는실생활프로그램 1. 도서관도서검색프로그램 가. 기능 - 도서관의도서목록에서특정단어가포함된도서를찾는다. - 검색된도서를클릭하면도서의위치가영수증프린터에서출력된다. - 다음시트로구성되어있다. 도서목록, 도서검색, 도서검색인쇄 1) 도서목록 : 현재보유하고있는도서전체목록으로 등록번호, 자료명, VOL, 저자, 발행처, 발행년도, 청구기호, 등록일, 자료상태 정보를저장 2) 도서검색 : 사용자가검색한도서를나열한시트 3) 도서검색인쇄 : 검색된도서중도서를찾기위해특정도서의위치를인쇄할경우에인쇄모습을만들고인쇄하는시트 나. 화면구성및코드 1) 도서검색폼 Private Sub cmd검색 _Click() Dim i As Integer Dim endrow As Integer Dim k As Integer k = 4 endrow = Range("C3").CurrentRegion.Rows.Count + 2 ' 도서목록의마지막행을찾는다. - 23 -
엑셀 VBA 로구현하는실생활프로그램 If Len(txt 검색 ) >= 2 Then ' 도서검색은 2 자이상일경우에만수행 For i = 4 To endrow If InStr(1, Cells(i, 3), txt검색 ) Then 'InStr() 함수를이용하여시트에있는도서목록과 ' 폼의도서검색에입력한문자중일치하는행이있는지찾음 태 ' ' 일치할경우에 ' 도서목록 ' 시트의해당행의 ' 제목 ', ' 청구기호 ', ' 자료상 ' 를 ' 도서검색 ' 시트에복사한다. Sheets(" 도서검색 ").Cells(k, 1) = Cells(i, 3) Sheets(" 도서검색 ").Cells(k, 2) = Cells(i, 9) Sheets(" 도서검색 ").Cells(k, 3) = Cells(i, 11) Next End If k = k + 1 Me.Hide ' 검색이종료되면 ' 도서검색 ' 폼을숨긴다. Sheets(" 도서검색 ").Select Else MsgBox " 검색명 2자이상을입력하세요." + Chr(13) + " 그래야... 찾기쉬움." End If 2) 도서검색시트 - 24 -
엑셀 VBA 로구현하는실생활프로그램 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim ns As Integer ' 더블클릭한행기억 ns = ActiveCell.Row ' 더블클릭한도서를인쇄하기위해 ' 도서검색시트 ' 로복사 Sheets(" 도서검색인쇄 ").Cells(3, 2) = Cells(ns, 1) Sheets(" 도서검색인쇄 ").Cells(4, 2) = Cells(ns, 2) Sheets(" 도서검색인쇄 ").Cells(5, 2) = Cells(ns, 3) ' 인쇄 Sheets(" 도서검색인쇄 ").Select ActiveWindow.SelectedSheets.PrintOut - 도서검색인쇄 시트모습 - 다시검색 버트을눌렀을경우 Private Sub cmd 재검색 _Click() Dim endrow As Integer ' 도서를재검색하기위해검색된목록삭제 endrow = Range("A3").CurrentRegion.Rows.Count + 2 Range("A4:C" & endrow).delete Sheets(" 도서목록 ").Select 도서검색폼.Show - 25 -
엑셀 VBA 로구현하는실생활프로그램 다. 도서검색설치및운용시연모습 - 26 -
엑셀 VBA 로구현하는실생활프로그램 2. POS(Point to Sale) 가. 기능 - 다음시트를가지고있음물품목록, 재고목록, 매출현황, 영수증 - 다음폼을가지고있음메인화면, 영업화면 - 파일을실행하면 재고목록 시트를활성화하고, 메인화면 폼을실행함 - 메인화면 폼에서 영업시작 버튼을클릭하면 메인화면 폼이 Unload 되고, 영수증 시트를활성화한후, 영업화면 폼을실행함 - 영업화면 폼의 txt입력 텍스트상자에제품고유번호를입력하면 재고목록 시트의제품고유번호를찾아텍스트상자에입력한고유번호와같은제품을찾음 - 만약고유번호를찾으면 영수증 시트에품명과금액을가져오고, 수량은 1씩증가시킨후, txt입력 텍스트상자의내용을지움 - 만약찾은고유번호가이미 영수증 시트에입력된제품이면새로운행을추가하지않고기존제품의수량을 1 증가시킴 - 만약고유번호를찾지못하면 txt입력 텍스트상자의내용을지움 - 영수증 시트의제품을더블클릭하면수량이 1씩감소됨 - 만약더블클릭한제품의수량이 1이면행삭제됨 - 계산하기 버튼을누르면 영수증 시트에등록된제품의총합을구함. 이때제품의공급가액과부가세도함께구함 - 카드 와 현금 버튼중 카드 에는아무기능이없고, 현금 버튼누르면받은돈에서거스름돈을구함. 이때받은돈이총액보다적으면부족하다는메시지를띄우고, 총액보다많으면거스름돈을구함 - 27 -
엑셀 VBA 로구현하는실생활프로그램 나. 화면구성및코드 1) 메인화면폼 - 파일이처음시작될때자동으로실행되는폼 - 파일이처음시작될때발생하는이벤트 : Open() - 파일이처음시작될때자동으로실행하는프로시저 : Auto Public CntRow As Integer 'Public : 공공의. 전역변수선언 Sub Auto_Open() 'Auto : 자동실행 '_Open() : 파일이열릴때호출되는이벤트 Sheets(" 재고목록 ").Select 메인화면.Show Private Sub UserForm_Initialize() 'UserForm : 지금현재폼 '_Intialize() : 초기화. 폼이처음시작될때자동으로실행 ' 폼이실행되면 Intialize() 이벤트가호출 CntRow = Range("A4").End(xlDown).Row 'CntRow : 재고목록시트의제품의마지막행값을기억 ' 전역변수. 현재엑셀의어디서든지사용할수있는변수 ' 어디에선언되어있나? 모듈 'Range("A4") : A4셀의 '.End(xlDown) : 아래방향으로의값이들어있는셀의끝 '.Row : 행번호 Private Sub cmd영업시작 _Click() Unload Me 'Unload : 실행을종료하기위해 RAM에서내리는것. 'Load : CPU에서처리하기위해 RAM에프로그램을올리는것. 'Me :cmd영업시작버튼이속해있는폼 Sheets(" 영수증 ").Select 'Sheets(" 시트명 ") : 시트명으로지정된시트를접근 '.Select : 시트를선택 ( 이동 ) '=Sheet4.Select 영업화면.Show modeless 'Modeless : 다른시트나폼을사용할수있음 - 28 -
엑셀 VBA 로구현하는실생활프로그램 2) 영업화면폼 - 메인화면 폼에서 영업시작 버튼을누르면시작되는폼 - 텍스트박스에제품고유번호를입력하면 재고목록 시트에서동일한고유번호검색. - 동일한고유번호를찾으면 품명 과 가격 데이터를가져와 영수증 시트에입력한다. 이때, 영수증 시트에동일한품명이있으면 수량 을 1 증가시키고, 없으면새로운행에입력한다. - 동일한고유번호가없으면텍스트박스내용을지우고, 아무일도하지않는다. Private Sub UserForm_Initialize() ' 영업화면폼이처음실행될때실행 Dim tmp As Integer ' 영업을시작하면영수증시트의제품들을모두삭제 (6 행부터 ) tmp = Range("A5").CurrentRegion.Rows.Count + 4 ' 값이들어있는마지막행을찾음 ' 영수증시트에서기존의목록을삭제 If tmp > 5 Then Range("A6:C" & tmp).delete End If Private Sub txt입력 _Change() 'txt입력개체가 '_Change() : 변할때실행되는이벤트 Dim i As Integer ' 반복문 Dim k As Integer ' 반복문 Dim flag As Boolean ' 값을찾았는가? If Len(txt입력 ) = 3 Then 'Len() : 문자의길이를구한다. 'txt입력개체에입력된문자의길이가 3자가된경우실행 flag = False ' 값을찾기전. 값을못찾은상태 putrow = Range("A5").CurrentRegion.Rows.Count + 5 ' 빈행 - 29 -
엑셀 VBA 로구현하는실생활프로그램 For i = 4 To CntRow 'CntRow : 재고목록의마지막행 '4행부터실행하는이유 : 재고목록시트의고유번호가 4행부터시작 If Val(txt입력 ) = Sheets(" 재고목록 ").Cells(i, 1) Then 'Val() : 수치값으로변환 'Sheets(" 재고목록 ").Cells(i,1) : 재고목록시트의제품고유번호 flag = True ' 찾았음표시 Exit For 'For문을빠져나감 End If Next If flag = True Then ' 영수증에내용기재하기 ' 값을찾았을경우 For k = 6 To putrow - 1 ' 영수증내에서같은제품을찾으려고하는반복문 ' 영수증은 6행부터입력되어있음 If Cells(k, 1) = Sheets(" 재고목록 ").Cells(i, 4) Then ' 찾았으면 'Cells(k,1) : 영수증시트의제품명 'Sheets(" 재고목록 ").Cells(i,4): 제품목록시트의제품명 ' 근데왜 i행을쓸까? ' 위반복문에서재고목록시트에서찾은행을 i가가지고있음 ' 이 If문은 flag가 True일때만실행되므로가능 putrow = k 'putrow값을영수증시트에서찾은행값으로지정 ' 수량을증가시키기위해 Exit For End If Next Cells(putRow, 1) = Sheets(" 재고목록 ").Cells(i, 4) Cells(putRow, 2) = Cells(putRow, 2) + 1 Cells(putRow, 3) = Format(Sheets(" 재고목록 ").Cells(i, 5) * Cells(putRow, 2), "#,###") ' 재고목록시트의단가영수증시트의수량 End If txt입력 = "" ' 새로운고유번호입력을위해 txt입력을지운다. End If - 30 -
엑셀 VBA 로구현하는실생활프로그램 - 계산하기 버튼을누르면 총금액 과 공급가액, 부가세 를구하여다음과같이입력 된다. Private Sub cmd계산 _Click() putrow = Range("A5").CurrentRegion.Rows.Count + 5 ' 영수증시트에서빈행을찾음 Cells(putRow, 1) = "'=======================" ' 총금액 Cells(putRow + 1, 1) = " 총금액 " Cells(putRow + 1, 3) = "=SUM(C6:C" & putrow - 1 & ")" ' 공급가액 Cells(putRow + 2, 1) = " 공급가액 " Cells(putRow + 2, 3) = Int(Cells(putRow + 1, 3) * 10 / 11) ' 부가세 Cells(putRow + 3, 1) = " 부가세 " Cells(putRow + 3, 3) = Cells(putRow + 1, 3) - Cells(putRow + 2, 3) - 카드 버튼을누르면카드결제가이루어지므로아무변화없다. - 현금 버튼을누르면고객에게현금을받은후잔돈을계산해야하므로다음과같이나타난 다. - 31 -
엑셀 VBA 로구현하는실생활프로그램 Private Sub cmd카드 _Click() ' 카드계산이므로현금계산에필요한개체를숨긴다. lab받은돈.visible = False lab거스름돈.visible = False txt받은돈.visible = False txt거스름돈.visible = False cmd입력.visible = False Private Sub cmd현금 _Click() ' 현금버튼을누르면계산에필요한개체를보이도록한다. lab받은돈.visible = True lab거스름돈.visible = True txt받은돈.visible = True txt거스름돈.visible = True cmd입력.visible = True Private Sub cmd입력 _Click() ' 현금계산시거스름돈을구함 txt거스름돈 = txt받은돈 - Cells(putRow + 1, 3) ' 영수증의총금액 If txt거스름돈 < 0 Then ' 받은돈이부족한경우 txt거스름돈 = "" MsgBox " 금액이부족합니다.", vbcritical End If - 고객에게받은돈을입력하면거스름돈을계산하여표시한다. 만약총금액보다받은돈이 적으면아래와같이경고창을표시한다. - 32 -
엑셀 VBA 로구현하는실생활프로그램 3) 영수증시트 - 영수증시트에서제품의행을더블클릭하면수량이 1 개씩감소한다. 만약수량이 1 개 밖에없을경우에는행을삭제한다. 곤드레나물 삭제전 곤드레나물 삭제후 곤드레나물 또삭제후 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'BeforeDoubleClick() : 시트의특정셀을더블클릭했을때실행되는이벤트 ' 더블클릭후커서가나타나기전 Dim r As Integer ' 선택된셀의행번호 r = ActiveCell.Row 'AcriveCell : 현재활성화 ( 선택 ) 된셀 '.Row : 행번호 If r > 5 Then ' 행머리글삭제예방 If Cells(r, 2) > 1 Then ' 수량이 2개이상이면수량을감소시킴 ' 금액 = 단가 * 수량 Cells(r, 3) = (Cells(r, 3) / Cells(r, 2)) * (Cells(r, 2) - 1) ' 금액. 재고목록시트를접근하지않고단가를구함수량을줄여서계산 Cells(r, 2) = Cells(r, 2) - 1 ' 수량 1개줄이기 Else Rows(r).Select Selection.Delete ' 활성화된셀이속한행을삭제 End If End If Cells(r + 1, 1).Select ' 커서가생기므로아래행으로이동하여커서가생기지않도록함 - 33 -
엑셀 VBA 로구현하는실생활프로그램 다. POS 설치및운용시연모습 - 34 -