VBA강좌

그리스의 철학자 에픽테투스는 이런 말을 했습니다.

"인간은 일어난 사건에 의해서가 아니라 그 사건에 대한 자신의 의견 때문에 고통을 느끼게 되는 것이다."

불가능이란 "사실"이 아니라 자신의 약한 의지와 다른 사람들의 잘못된 "의견"이 덧붙여진 것일 따름입니다.

무엇을 해도 좋은 계절, 봄이 오고 있습니다. 이번 봄에 꼭 이루고 싶은 것이 하나 있습니다. 그것이 무엇이냐 하면... 비밀이랍니다. ^^* 여러분은 이 봄에 이루고 싶은 어떤 꿈을 가지고 계신가요?

VBA 입문강좌 : Sub 프로시저와 Function 프로시저

Sub 프로시저와 Function 프로시저의 차이
Sub 프로시저
Function 프로시저
사용자 정의 함수 예제
사용자 정의 함수 범주 변경하기
사용자 정의 함수 설명 추가하기

이번 시간에는 Sub 프로시저와 Function 프로시저의 차이점, 그리고 Function 프로시저, 즉 사용자 정의 함수의 기본 개념과 응용 예제들에 대해 살펴봅니다.
　

Sub 프로시저와 Function 프로시저의 차이

우리가 VB Editor에서 작성하는 VBA 코드는 보통 '프로시저'라고 부르며, 가장 일반적인 프로시저는 Sub 프로시저와 Function 프로시저 입니다. 이 두 가지 프로시저의 가장 큰 차이점은,

Sub 프로시저 : 특정한 동작을 실행한다.
Function 프로시저 : 특정한 계산을 수행하고 그 결과값을 돌려준다.

김 판서가 두 명의 하인에게 똑같은 일거리를 주었습니다. 페인트를 한통씩 주고, "이제 봄이 되었으니 집 앞 담벼락에 페인트 칠을 해라!"하고... 한 나절이 지났습니다. 김 판서가 점검을 하러 나가 보았습니다. 갑돌이에게 맡긴 담벼락은 색칠을 되어 있는데 갑돌이는 어디론가 사라져 버리고 보이지 않는데, 삼돌이는 색칠을 마치고 주인에게 달려와서 결과를 보고합니다.

"저에게 주신 페인트 량은 100리터였는데, 담벼락의 넓이는 가로 10미터, 세로 2미터로 20 평방미터였습니다. 1 평방미터당 소요된 페인트 량이 0.3리터였으므로 60리터를 사용하여, 현재 40 리터의 페인트 재고가 남아 있습니다."

짐작하시겠지만, 여기서 갑돌이는 Sub 프로시저, 삼돌이는 Function 프로시저에 해당됩니다. 어떻습니까? Function 프로시저가 Sub 프로시저보다 더 똑똑해 보이지요? 앞으로는 똘똘한 Function 프로시저만 사용해야 되겠다는 생각이 드시나요?

하지만 세상은 공평한 것... 머리가 똑똑하면 얼굴이 받쳐주지 않고, 외모가 되면 머리가 따라주지 않는 것이 보통이지요. 머리도 똑똑하고 외모도 예술이면... 하다 못해 성격이 괴팍하다거나 팔자가 드세다거나 하는 등, 이 세상에 모든 것을 다 갖춘 사람은 없는 법이지요. Function 프로시저가 Sub 프로시저에 비해 지능은 뛰어난 반면 제약이 있습니다.

Sub 프로시저

보통 여러분이 VBA에서 작성하는 코드는 Sub 프로시저입니다. 매크로 기록기를 이용해서 작성한 코드 역시 Sub 프로시저입니다. 모든 Sub 프로시저는 Sub 라는 키워드로 시작해서 End Sub라는 statement로 끝을 맺습니다.

Sub 안녕하세요()

MsgBox "안녕하세요 " & Application.UserName & "님!"

End Sub

프로시저명 뒤에는 항상 한 쌍의 괄호가 붙습니다. 보통의 경우, 이 괄호 안에는 아무 것도 들어있지 않지만, 필요한 경우 argument, 즉 인수를 전달하여 실행을 할 수도 있습니다.

Function 프로시저

Function 프로시저는 다른 말로 사용자 정의 함수라고도 부릅니다. 모든 Function 프로시저는 Function 이라는 키워드로 시작해서 End Function 이라는 statement로 끝을 맺습니다. 사용자 정의 함수의 기본적인 작성 형식은 이러합니다.

Function 함수명(인수1, 인수2,...)

... 어쩌고 ...

... 저쩌고 ...

함수명 = 결과값

End Function

제곱근을 구해주는 사용자 정의 함수를 만들어 보면,

Function 제곱근(number, n)

제곱근 = number ^ (1/n)

End Function

여기서 number는 제곱근을 구하고자 하는 값(숫자), n은 몇 제곱근을 구할 것인지를 지정하는 인수입니다. 예를 들어, 위의 코드를 모듈시트에 작성한 다음, 워크시트 내 임의의 셀에 "=제곱근(2,2)" 라고 입력하면 2의 2제곱근 값인 "1.414213..." 라는 결과값이 구해집니다.

Function, 즉 함수는 몇 개의 인수를 가질 수도 있고 전혀 가지지 않을 수도 있습니다(Today, Now, Rand 등과 같이...).

Function 프로시저는 딱 두 가지 방법에 의해서만 실행할 수 있습니다.

(1) 다른 프로시저(Sub 혹은 Function 프로시저)에서 호출하는 방법
(2) 워크시트 내에서 수식의 형태로 사용하는 방법

반면, Sub 프로시저는 아주 다양한 방법으로 실행할 수 있는데, 이것이 사람들로 하여금 Sub 프로시저를 더 많이 사용하게 하는 한 가지 이유가 되기도 합니다.

(1) VBE에서 '표준' 도구모음에 있는 'Sub/사용자 정의 폼 실행' 아이콘 이용
(2) VBE에서 '실행-Sub/사용자 정의 폼 실행' 메뉴 이용
(3) VBE에서 '직접 실행 창'에서 바로 실행 ← Sub 프로시저명 입력/엔터
(4) 단축 키를 통해 실행(Ctrl + 단축 키) ← 단축 키가 미리 지정되어 있을 경우
(5) 다른 Sub 프로시저에서 호출하여 실행
(6) 워크시트에서 버튼이나 도형 등의 개체에 연결하여 실행
(7) '도구-매크로-매크로' 메뉴를 통해 실행
(8) 도구 모음의 사용자 지정 단추에 연결하여 실행
(9) 사용자 지정 메뉴에 연결하여 실행
(10) 특정한 이벤트가 발생하였을 때 실행 ← 이벤트 프로시저에 연결하여

참으로 많은 방법이 있기도 하지요? 이것 말고도 더 있을 지도 모릅니다. 그리고 Function 프로시저는 Sub 프로시저에서 쉽게 할 수 있는 글꼴을 변경한다거나 셀의 색상을 변경한다거나 하는 작업은 할 수 없습니다.
　

사용자 정의 함수 예제

(1) 인수를 하나도 가지지 않는 사용자 정의 함수

Function UserName()

UserName = Application.UserName

End Function

'=UserName()' 이라고 입력하면 현재 사용자의 이름이 표시됩니다.

(2) 상여금 계산 사용자 정의 함수

아래과 같은 사원 기본정보가 있다고 할 때, 직종에 따라 성과급을 차등 지급하는 사용자 정의 함수를 작성해 보겠습니다.

Function 성과급(직종코드, 연봉)

    Select Case 직종코드
        Case 1
            성과급 = 연봉 * 0.1
        Case 2, 3
            성과급 = 연봉 * 0.08
        Case 4 To 7
            성과급 = 1000000
        Case Is > 7
            성과급 = 500000
        End Select

End Function

이 함수를 실행하면 다음과 같은 결과를 얻을 수 있습니다.

(3) 선택적 인수^{Optional
argument}를 가지는 사용자 정의 함수

특정한 범위 내에서 상위 5개 값의 평균을 구해야 한다면 어떻게 해야 할까요? 엑셀에는 그런 기능을 수행하는 함수가 따로 없기 때문에 다음과 같은 형태의 수식을 사용해야 할 것입니다.

= (Large(영역, 1) + Large(영역, 2) + ... + Large(영역, 5)) / 5

물론 이 수식은 오류없이 제대로 작동합니다. 하지만 그다지 좋은 해결 방법이라고 하기는 어렵습니다. 왜냐고요? 모로가도 어디로만 가면 되지 않느냐구요?? 그렇다면... 만약 상위 10개 값의 평균을 구해야 한다면 수식을,

= (Large(영역, 1) + Large(영역, 2) + ... + Large(영역, 10)) / 10

이런 식으로 바꾸어 주실 것입니까? 그러면 100개 값의 평균은 어떻습니까? 곤란하겠지요? 이런 경우 사용자 정의 함수를 만들면 간단히 해결할 수 있습니다.

Function 상위평균(rngX, Optional n = 5)

    Dim dblSum As Double
    Dim i As Integer

    For i = 1 To n
        dblSum = dblSum + Application.WorksheetFunction.Large(rngX, i)
    Next i

    상위평균 = dblSum / n

End Function

여기서... Optional 이라는 새로운 단어가 하나 나왔군요. 우리가 잘 아는 Left 함수를 생각해 보면,

Left(텍스트, 추출할 문자 수)

이런 형식으로 사용됩니다. 만약 '추출할 문자 수' 인수를 생략하면 엑셀이 알아서 1로 간주합니다. 즉 다음 두 수식은 같은 결과값을 돌려줍니다.

= Left(A1, 1)
= Left(A1)

이처럼, 사용자 정의 함수에서 특정한 인수를 생략하면 기본적으로 어떤 값을 갖도록 설정할 때 사용하는 것이 Optional 키워드입니다.

(4) 인수의 개수가 정해져 있지 않은 사용자 정의 함수

어떤 워크시트 함수는 인수의 개수가 미리 정해져 있지 않은 것이 있습니다. 어떤 것이 있을까요? 대표적인 것으로 Sum 함수가 있습니다.

= Sum(number1, number2,...)

이런 식으로 30개 까지의 인수를 가질 수 있습니다. 여기서 첫번째 인수는 반드시 있어야 하지만 나머지 인수는 상황에 따라 변합니다. 이런 함수는 어떻게 하면 만들 수 있을까요? ParamArray 키워드를 사용하면 가능합니다.

Function MySum(ParamArray XXX() As Variant) As Double
    Dim varX As Variant

    For Each varX In XXX
        MySum = MySum + varX
    Next varX
End Function
　

ParamArray 키워드는 항상 Variant 데이터 타입이며, 항상 선택적 인수^{Optional argument}입니다. 설령 Optonal 키워드를 사용하지 않았더라도 말입니다.

사용자 정의 함수 범주 변경하기

기본적으로 사용자 정의 함수는 '사용자 정의' 범주에 포함되어 있습니다. VBA를 이용하면 이 범주를 변경할 수 있습니다(왜 그렇게 만들었는지 이유를 알 수는 없지만, 워크시트 상태에서 수작업으로는 바꾸어 줄 방법이 없습니다).

MacroOptions 메서드를 사용하여 다음과 같이 해 주면 '상위평균'이라는 사용자 정의 함수의 범주가 수학/삼각함수 범주로 변경됩니다.

Sub ChangeCategory()
Application.MacroOptions macro:="상위평균", Category:=3
End Sub

<MacroOption 메서드의 기본 제공 범주>

값	함수 범주
0	모두
1	재무
2	날짜/시간
3	수학/삼각
4	통계
5	찾기/참조 영역
6	데이터베이스
7	텍스트
8	논리
9	정보
10	Commands(이 범주는 화면상에 표시되지 않습니다)
11	Customizing(이 범주도 숨겨져 있습니다)
12	Macro Control(이 범주도 숨겨져 있습니다)
13	DDE/External(이 범주도 숨겨져 있습니다)
14	사용자 정의(Default)
15	공학(분석 도구를 추가 설치한 경우 나타납니다)

사용자 정의 함수 설명 추가하기

'함수 마법사' 대화상자에서 엑셀의 워크시트 함수를 선택해 보면 해당 함수에 대한 간단한 설명이 나타나는 것을 볼 수 있습니다. 하지만 사용자 정의 함수에 대해서도 마찬가지로 이러한 설명이 나타나도록 할 수 있습니다.

(1) '도구-매크로-매크로' 메뉴를 선택하니다.
(2) '매크로 이름' 항목에 해당 함수의 이름을 입력합니다.
('매크로' 대화상자에는 사용자 정의 함수의 이름이 표시되지 않습니다)
(3) '옵션' 버튼을 클릭합니다.
(4) '설명' 항목에 함수에 대한 자세한 설명을 기입합니다.
(5) '확인' 버튼을 클릭합니다.
(6) '취소' 버튼을 클릭합니다.

새로운 함수 범주를 만들 수 있을까?

함수 범주를 새로이 추가할 수는 없습니다. 또한 사용자 정의 함수의 인수^argument에 세부적인 설명을 추가할 수도 없습니다. 대신 사용자 정의 함수의 인수 이름을 의미있는 것으로 지정해 주면 어느 정도는 직관적으로 이해할 수 있게 되므로 편리합니다.

원래 이번 강좌는 계획에 없던 것입니다만, 오피스 튜터에서 가졌던 [Excel VBA 활용 과정]을 수강하신 분들에게 설명드린 내용을 정리하면서 포함하는 것이 좋을 것 같아 작성하였습니다.

이번 강좌는 여기까지...
　

	Previous	Next