기본및활용 성균관대학교보험계리학과특강 중앙대학교통계학과 성병찬 E mail: bcseong@cau.ac.kr & Tel: 02 820 5216 목차 1. VBA의개념 2. 매크로또는모듈기록하기 3. 프로그래밍을위한주요구문및요소들 4. 활용예제 2/26
추천서적및웹사이트 - John Walkenbach, Excel Power Programming with VBA - Uno21.com - 또는수많은 VBA 관련웹사이트들 3/26 1. VBA 의개념 매크로 (macro) - 어떤단순한반복적인작업을일괄처리할수있도록하는기능 - MS Office: VBA; 아래아한글 VBA란? - Visual Basic for Application의약자 - Visual Basic (VB) VBA - MS Office 프로그램들에서매크로또는그이상을작성하기위한언어 - 장점 : 반복작업을짧은시간에처리할수있으며, MS Office 프로그램들의기능을미세하게조정하거나확장할수있다. 예 : 엑셀에서의데이터분석도구, KESS (http://stat.snu.ac.kr/time/) 등 - 단점 : 컴파일언어 (C, C++, Fortran) 에비해속도가느리고, 엑셀에서다양한관련함수들이많지않다는단점이있다. 참고 : DLL (dynamic linking library), 타프로그램 ( 예 : matlab) 과의연결 4/26
엑셀에서의 VBA - 편집창 (VBA Editor) 에들어가기및관련메뉴살펴보기 Alt + F11 [ 도구 ] [ 매크로 ], [ 추가기능 ], [ 데이터분석 ] - 구성요소 편집창 도구모음들 ( 표준, 편집, 디버거 (debugger) 등 ) 직접실행창, 지역창 메뉴살펴보기 : [ 삽입 ] [ 모듈 ] - 기타 개체 (object) = 속성 (property) + 방법 (method) 더많은개체와그것들의속성, 방법에익숙해져야 VBA 프로그래밍실력향상 예 : Range 개체의속성 (value) 와방법 (clear) Sub ChangeValue() Worksheets( sheet1 ).Range( A1 ).Value=123 5/26 Sub ClearRange() Worksheets( sheet1 ).Range( A1 ).Clear 디버깅 : 프로그램에존재하는버그들을잡아내는과정 Add in ( 추가기능 ): xla 주로모듈및유저폼으로만구성되어있다. xls= 스프레드시트 + xla ( 모듈및유저폼등 ) 엑셀도움말이용하기 : syntax ( 구문 ), 설명, 예제살펴보기 예1: rnd 함수 Function MyValue() a = Rnd b = Round(a, 4) MyValue = Int((6 * Rnd) + 1) ʹ 1과 6 사이의난수를발생합니다. End Function 예2: MsgBox 함수 Sub MsgboxDemo() 6/26
MsgBox ʺClick OK to continueʺ Sub GetAnswer() ans = MsgBox(ʺContinue?ʺ, vbyesno) Select Case ans Case vbyes MsgBox ʺ 예 ʺ Case vbno MsgBox ʺ 아니오 ʺ End Select 예3: Visual Basic에서사용할수있는워크시트함수목록 7/26 2. 매크로또는모듈작성하기 기록, 편집, 주석 (comment); 예, - A1에 123 입력하기 - 123이면 msgbox로 OK! 출력하기 - 주석달기와해제하기 - 기록하기를통하여개체들의이름, 속성및방법을살펴볼수있다. 예 : 매크로기록기를통하여위의예를실행해보자. Sub write_a1() ActiveCell.FormulaR1C1 = ʺ123ʺ Range(ʺA2ʺ).Select A2 값을바꿔보자. ActiveCell.FormulaR1C1 = ʺ123ʺ ActiveCell.Offset(1, 0).Range(ʺA1ʺ).Select (1,0) 대신 (2,2) 를대입해보자. 8/26
실행하기 - F5키 - 스프레드시트상에서 [ 도구 ] [ 매크로 ] - 단축키및할당하는방법 디버깅 : 직접실행창, 지역창 - 직접실행창 : 엑셀및 VBA 관련값부여및알아내기 예 : Range(ʺa1ʺ).Value=123 Print 25*3? 25*3? activeworkbook.name? Range( a1:a10 ).cells(2,1).value - 지역창 : 모듈의변수값알아내기 또는, 커서를해당변수위에둔다. - 예 : 중단점활용한디버깅 Sub mysum() 9/26 Dim sum As Double For i = 1 To 100 sum = sum + i Next i MsgBox sum, Title:=ʺ1부터100까지더하면?ʺ 연산자 (oprators) - 대입연산자 - 사칙연산 (+,, x, /) - 논리연산 (and, or, not) - 기타 : mod - 예 : Sub Operators() x = 1 x = x + 1 x = x ^ 2 10/26
y = (x * 2) / (x * 4) z = 10 Mod 3 FileOpen = True FileOpen = Not FileOpen Range(ʺA1ʺ).Value = 2009 사용자정의함수 (user defined function, module, subroutine) 의종류 - Sub vs Function - 반환값유무의차이 - 사용자정의함수이름규칙 예 : 숫자및특수기호로시작할수없다 ; 공백이있을수없다. - 사용자정의함수의구조 함수이름, 입력값, 본문, 반환값 ( 출력값 ) 예 : Sub test() 11/26 Sum=range( a1 ).value+1 Msgbox The answer is & Sum Function AddTwo(arg1, agr2) AddTwo=arg1+arg2 End Function Function func_mysum(endp As Double) As Double Dim sum As Double: Dim i As Double For i = 1 To endp sum = sum + i Next i func_mysum = sum End Function - 들여쓰기 (indentation) 12/26
모듈부르기 - Sub의실행 : [ 도구 ] [ 매크로 ] - Function의실행 : [ 삽입 ] [ 함수 ] - 모듈에서특정 sub 또는 function 부르기 - 예 : Sub test2() a = func_mysum(5) function call MsgBox ʺ1부터 5까지더하면ʺ & a sub_mysum sub call 13/26 3. 프로그래밍을위한주요구문및요소들 변수종류및선언하기 - 변수종류 (data type): Boolean, Integer, single, long, float, double, string, variant - Variant: 변수를특정타입으로정하기어려운경우 - Dim 변수이름 as 변수종류 - 주로함수의맨위에서선언 - 변수를선언하면프로그래밍이좀더편하거나쉬워질수있다. 변수를선언하지않고사용하면메모리낭비및디버깅어려울수있다. - Option explicit - 예제 : Sub test3() Dim v1 As Integer Dim v2 As Long Dim v3 As Double Dim v4 As String 14/26
v1 = 10 v1 = 10.2 v2 = 10.3 v3 = 10.3456 v4 = ʺI am a string type.ʺ conv = Int(v3) ʹconv is a variant type. Dim rn As Range Set rn = Range(ʺb2ʺ) Value = rn.value - 참고 : 개체변수 예 : Sub RangeV() Dim r As Range Dim s As Worksheet 15/26 Set s = ActiveSheet Set r = ActiveCell ʹ개체변수를할당할때는 Set 을사용! MsgBox s.name MsgBox r.address 배열 (array) - 통계학관련함수작성할때는주로두개이상의관측치를다루기때문에배열을사용하는것이편리 - 배열선언및사용방법 - 예제 : Sub myarray() Dim a(0 To 99) As Double Dim b(1 To 100) As Double Dim c(1 To 10, 1 To 10) As Integer 16/26
For i = 1 To 100 a(i 1) = i Next i MsgBox Application.sum(a) Debug.Print Application.Max(a) Debug.Print Application.Min(a) Debug.Print Application.Average(a) Sub myarray2() myend = 5 subarray myend Sub subarray(endp) Dim a() As Double ReDim a(0 To endp) ʹdynamic array ʹ끝이구체적으로정해졌을때비로소선언 For i = 1 To UBound(a) a(i 1) = i 17/26 Next i MsgBox Application.sum(a) 조건문 - 프로그램에서어떤판단을해야하는시점에서사용 - 종류 : if ~ then ~ end if, select case - 구문 If condition Then [statements] Select Case testexpression [ElseIf condition n Then [Case expressionlist n [elseifstatements]] [statements n]] [Else [Case Else [elsestatements]] [elsestatements]] End If End Select 18/26
- 예제 Function func_mysum(endp As Double) As Double Dim sum As Double: Dim i As Double For i = 1 To endp If i > 50 Then sum = sum + i ElseIf i > 20 Then sum = sum + 0.5 * i Else sum = sum + 0.1 * i End If Next i func_mysum = sum End Function Function Bonus(performance, salary) 19/26 Select Case performance Case 1 Bonus = salary * 0.1 Case 2, 3 Bonus = salary * 0.09 Case 4 To 6 Bonus = salary * 0.07 Case Is > 8 Bonus = 100 Case Else Bonus = 0 End Select End Function 반복문 (Loop) - 특정한작업을계속반복해야하는상황에서사용 - 종류 : For ~ next, do loop - 구문 For counter=start To end [Step stepval] 20/26
[statements] [Exit For] [statements] Next [counter] Do [While condition] [statements] [Exit Do] [statements] Loop - 예 : Sub SumOdd() sum = 0 For Count = 1 To 100 Step 2 sum = sum + Count Next Count MsgBox sum 21/26 Sub sumeven() sum = 0: Count = 1 Do While Count <= 100 Count = Count + 2 sum = sum + Count Loop MsgBox sum 행렬연산 - 통계학관련된함수를작성할때유용 - 단점 : 덧셈, 뺄셈이불편 ; 연산자가없다. - 종류 : Application.MMult(a,b),. MInverse(a),. Transpose(a) - 예제 : Sub Matrix_Al() 22/26
Dim A Set A = Range(Cells(1, 1), Cells(10, 10)) b = Application.MMult(A, A) ʹCalculate A^2 c = Application.MMult(Application.MMult(A, A), b) ʹCalculate A^3 D = Application.MInverse(Application.MMult(Application.Transpose(A), A)) ʹCalculate (Aʹ*A)^( 1) MsgBox D(2, 2) ʹFind (2,2) elts of (Aʹ*A)^( 1) 자주사용되는 VBA 함수들 - 수학통계함수 exp(), log(), max(), min(), sqr() Application..average(),.fact(),.max(),.min(),.sum() 23/26.normsdist() Rnd - 기타함수 selection inputbox, msgbox - 예제 : Sub Factorial() ʹcalculate the factorial of a number ʹOn Error Resume Next num = InputBox(ʺEnter integer numberʺ, ʺCalculate Factorialʺ) fac = Application.Fact(num) MsgBox ʺFactorial is ʺ & fac Sub myselection() 24/26
s = Application.sum(Selection) MsgBox ʺThe sum of selection is ʺ & s 셀을참조하는방법 - 상대참조, 절대참조 워크시트함수에서의개념이프로그램에서도사용 - 상대참조로매크로기록하기 - 예 : Sub 상대참조로기록 () ActiveCell.Offset(3, 4).Range(ʺA1ʺ).Select ActiveCell.FormulaR1C1 = ʺ123ʺ ActiveCell.Offset(1, 0).Range(ʺA1ʺ).Select Sub mymonth() ActiveCell.Offset(0,0)= Jan ActiveCell.Offset(0,1)= Feb 25/26 ActiveCell.Offset(0,2)= Mar - Range 방법, Cells 방법 - 예 : 디버그모드를이용하여워크시트의변화를살펴보자. Sub methods() Range(ʺc1:c10 a6:e6ʺ) = 3 Range(ʺA1,A3,A5,A7,A9ʺ) = 4 ActiveCell.Range(ʺb2ʺ) = 5 Worksheets(ʺsheet4ʺ).Cells(1, 1) = 1 ActiveSheet.Cells(3, 4) = 7 ActiveCell.Cells(2, 1) = 5 26/26