엑셀 사용자 정의함수 개념과 활용

사용자 정의함수

이번에는 VBA(Visual Basic Application)기반의 사용자 정의 함수를 제작하는 방법에 대해 알아보자.

물론, 사용자 정의 함수를 학습하려는 사람들이 대부분 컴퓨터활용능력 1급 떄문에 하는 사람들이 많을 것이다.

여기에서는 컴퓨터활용능력 뿐만이 아니라 실무에서도 충분히 활용할 수 있는 내용으로 다루어 볼 것이다.

사전에 알아두어야 할 내용

- 변수와 데이터 타입의 사용에 대해서 알아야 한다

- 객체 지향 프로그래밍의 정의를 알고 있으면 이해하기 좋다.

- if문과 for문에 대해 알고 있으면 이해하기 좋다.

잘 아시다시피 VBA는 엑셀에서 사용하는 Visual Basic 문법을 기반으로 매크로 프로그래밍을 가능하게 하는 요소이다.

VBA는 객체지향프로그래밍 기법을 도입하여(객체지향 프로그래밍은 추후 언급하겠다.) 엑셀에서 매크로프로그래밍을 수행하도록 한다.

이를 위해서 Application 객체, WorkBook객체, WorkSheet객체, Range 객체 등을 가지고 있으며, 사용자는 이러한 VBA객체에 값을 할당하여 매크로기능을 수행하게된다.

Application객체는 속성명 209개, 메소드만 50개, 이벤트 47개를 가지고 있으며, WorkBook객체와 worksheet객체, range 객체도 다양한 속성과 메소드, 이벤트 등을 가지고 있다.

특히, Range객체는 엑셀의 셀 범위와 위치를 지정하는데 사용되는 객체이다.

객체의 개념과 VBA 객체에 대한 세부적인 것은 다음에 다루어 볼 것이다.

VBA에서는 모듈을 작성하는 방법이 두가지인데, 하나는 프로시저 타입, 다른하나는 함수 타입이다.

프로시저와 함수의 타입은 한가지 Return을 가지고 있느냐 가지지 않느냐하는 것이다.

먼저 프로시저의 형식을 확인하자

[형식][private | public | friend] [static] sub 이름(매개변수..)[Exit Sub]end sub

대괄호가 있는 것은 생략이 가능한 부문이다.

맨 앞에 있는 [private | public | friend] 부문은 액세스 지정자라고 한다. 액세스 지정자라는 것은 만들어진 프로시저를 누가 실행할 수 있느냐라는 것이다.

public 은 모든 모듈에서 프로시저를 액세스 할수 잇다는 것으로 기본값으로 설정되어 있다.

private는 단지 프로시저를 정의한 모듈에서만 실행할 수 있다는 것을 의미한다.

friend는 클래스 모듈에서만 사용할 수 있으며 프로젝트 전역에 걸쳐 확인할 수 있지만, 객체 인스턴스에는 보이지 않는 특성을 갖는다.

매개변수의 형식은 다음과 같다.

[optional] [byval | byref] [paramarray] 변수명 [as type] [=default]

optional은 생략이 가능한 매개변수를 지정할 때 사용하는 키워드로 함수를 사용할 때 해당 인수를 생략해도 된다는 것이다.

byval은 값에의한 전달을 의미한다.

byref는 참조에 의한 전달을 의미하며, VBA에서 매개변수전달의 기본값이다.

paramarray는 제일 마지막 매개변수로만 사용되어 지며, 데이터 타입은 variant 타입의 배열로서만 사용될 수 있다. 또한, byval, byref, optional과 함께 사용할 수 없다. sum함수, max, 등의 함수들은 paramarray를 사용하며, 여기에서도 sum함수를 직접 구현해볼 것이다.

as type은 데이터 타입을 지정한다.

비주얼 베이직에서 사용가능한 데이터 타입은 Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal, Date, String, Object, Variant, 특정 객체 타입 등이 가능하다.

각 데이터타입도 별도로 다룰것이다.

다음은 함수의 형식을 보자

[형식][private | public | friend] [static] Function 이름(매개변수..) [as return_type][Exit Function]함수명=표현식end function

함수는 프로시저와 동일하나 리턴값이 있다는 것이 다르다.

이 반환값이 중요한데. 반환값이 엑셀의 셀에 표시되는 값이된다.

즉, 함수명=표현식 부분이 리턴값인데 표현식 부분이 셀에 표시되는 것이다.

이 반환값을 사용하기 위한 예를 들어보자

먼저 VBA를 실행하려면 다음과 같이 설정하여 개발도구 메뉴를 표시하여야 한다.

리본메뉴에 개발도구 추가하기

이제 개발도구 탭에서 Visual Basic을 실행한다.

추가된 개발도구

다시 프로시저로 돌아가서 다음과 같이 VBA창에서 코드를 작성했다.

프로시저 작성

엑셀에서 함수를 실행했을 경우 리스트에 프로시저는 자동완성에서 뜨지 않으며 실행되지도 않는다.

프로시저 실행

call 명령을 사용하여 프로시저를 호출해보자.. 위의 소스와 같이 작성한 후 프로시저를 호출하게 된다면 다음과 같이 에러가 난다.

프로시저 호출결과

위 에러의 이유는 프로시저가 리턴값을 포함하지 못하는데 리턴이 포함되기 때문이다.

다음처럼 서브프로시저에서 리턴을 제거시켰다..

프로시저에서 리턴 제거결과

프로시저 및 함수 호출은 모두 call 키워드를 사용하며 형식은 다음과 같다.

call 이름(매개변수)

참고로 이야기 하자면 call은 함수나 프로시저 외에도 DLL도 로드 할 수 있다.

DLL 로드 형식은 Private declare sub messagebeep lib “user” (byval n as integer)처럼 기술하면된다. 아무튼 DLL을 사용하는 것은 여기에서의 범위를 넘어서므로 나중에 다루기로한다.

간단한 사용자 정의 함수를 한번 작성해보자.

사용자 정의 함수만 작성할 때는 위의 규칙을 지키면서 코드 작성만 하면 된다.

함수작성

실행 결과는 다음과 같다.

엑셀에서 함수실행

함수실행결과

위에서 언급한 함수형식에 맞추어 다시 작성하면 다음과 같다.

함수작성

사용자함수() 뒤의 As String 은 반환되는 데이터 타입을 지정한다. 문자열이 반환된다는 소리인데, 반환값과 타입이 다르면 에러가 난다.(생략 가능하다)

이제 사용자함수에 매개변수를 추가해보자. 간단한 방법을 한번 보자.

매개변수 추가

pdata라는 이름으로 매개변수를 추가했다.

이제 사용자함수를 실행할때에는 괄호안쪽에 값을 하나 추가하여야 한다.

매개변수가 추가된 함수사용

실행결과

그렇다면 위에서 작성한 내용의 의미는 무엇일까?

Function 사용자함수(pdata) - 이름이 사용자함수고 pdata가 받게되는 값

result = pdata + 100 - 받아온 pdata값에 100을 더하여 result에 저장

사용자함수 = result - result값을 반환한다.

End Function

pdata는 정확히 다음의 의미가 생략된 형태이다.

byref pdata as variant - 참조형 인수이며 데이터 타입은 variant이다.

byref란 참조를 기반으로 매개변수를 전달하는데, 위에서 설명했듯 인수의 기본값이다.

엑셀에서는 함수의 리턴값으로 항상 셀이 받아내는 구조이기 때문에 byval과 byref가 크게 차이가 있어보이지는 않는다.

다음의 예를 작성하고 실행해보면 그 차이를 알 수 있다.

값에의한 전달과 참조에 의한 전달

매개변수 뒤에 존재하는 as variant는 데이터 타입이다. 즉, 인수의 형태로 숫자,문자, 통화 등 여러 종류의 데이터를 받을 수 있다는 의미이다.

다음과 같은 함수를 보자

매개변수 타입지정

위의 예에서 보면 for each ~ next 구문을 사용하고 있다.

for each~next는 여러데이터를 하나씩 뽑아내어 뽑아낸 각 데이터를 사용할 수 있는 구문이다.

형식
for each element in group - element는 그룹 중 하나의 데이터를 담는 변수다.
내용 group는 여러 데이터를 담고 있어야 한다.
[exit for]
next [element]

위의 소스를 아래와 같이 실행 할 경우 어떻게 실행되는지 보자

함수실행

사용자함수에 매개변수로 B2:B6 범위를 지정하고 있다.

이것은 사용자함수의 pdata 매개변수에 저장되어 진다. 이렇게 되었을 때 pdata에는 B2, B3, B4, B5, B6 등 총 5개의 데이터를 담고 있게 된다. pdata를 for each 구문에 넣게 되면 첫 번째로 pdata의 첫 번째 값(b2셀의 값)을 for each 와 in 사이에 있는 temp 변수에 저장한다. temp 변수는 result 변수에 temp값이 누적된다.

다음 두 번째 값(b3셀의 값)을 temp 변수에 저장한후 result변수에 temp값을 누적시킨다. 다음 세 번째 값(b4셀의 값), 네 번째값 (b5셀의 값), 다섯 번째값(b6셀의 값)들을 temp변수를 사용하여 순서대로 result변수에 누적시키게 된다.

이제, sum함수와 같은 동작을 하는 함수를 만들어보자.

sum함수와 같은 동작을 하는 ssum함수

paramarray 의 의미는 파라미터를 가변배열을 의미하는 매개변수 키워드다. 이렇게 하면 더할 값들의 수를 적게는 한 개부터 많게는 여러개 까지 지정 할 수 있다. UBound(배열명)함수는 Visual Basic 함수로 배열의 가장 큰 인덱스를 알려주는 함수이다.

형식

ubound(배열명, [차원])

ssum함수 사용

위와 같이 엑셀에서 함수를 입력하고 범위지정을 하면 ubound()함수는 1이라는 값을 반환한다. 이때 주의해야 할 점은 ubound()함수가 반환하는 것은 배열의 인덱스번호를 0부터 시작한 인덱스번호를 알려준다는 것이다. 위의 수식에서는 첫 번째 인덱스가 0이고 두 번째 인덱스가 1이기 때문에 1이라는 값이 반환되는 것이다.

따라서, 위에서 작성한 다음 소스는 총 2회 반복문이 수행되어진다.

For i = 0 To UBound(parr) - i값이 0일 때 수행, 1일 때 수행

Next i

다음으로 set temp에 대해서 확인해보자

형식

set 객체변수=[new] 객체표현 또는

set 객체변수= Nothing

MSDN(MS도움말이다)에 따르면 set구문은 변수나 속성에 객체참조를 지정한다.

New는 객체지향프로그래밍에서 클래스 인스턴스를 생성할 때 사용하는 것인데, 이 역시 범위를 벗어나므로 다음에 다루도록 하겠다.

nothing 구문은 지정된 객체변수의 메모리를 해제하는 기능을 한다.

즉, 엑셀에서 셀범위를 지정하거나 셀참조를 하게되면 기본적으로 range객체를 생성하여 엑셀함수에 인수로써 집어넣게 된다.

위의 예를 들면, range객체에 b2:b6를 집어넣고, 또 다른 range객체에 c2:c6를 집어넣는다.

즉, 2개의 range객체 배열이 만들어진 것이다. 각 range객체배열은 5개씩의 range객체(셀)을 가지고 있게된다. 각 range객체배열로부터 5개의 range객체(셀)값을 뽑아내기 위해서, set 구문을 사용하게 된다.

따라서, 다음과 같은 코드가 만들어지게 된다.

Set temp = parr(i)

For Each cell In temp

result = result + cell

Next cell

단순화 시켰지만 sum함수와 비슷하게 기능을 수행 할 것이다.

우리가 함수를 실행할 경우 함수마법사를 사용하게되면 아래그림과 같이 나타나게 된다.

sum함수 마법사

위의 마법사를 보면 함수명, 기능, 매개변수 리스트 등이 포함되어서 설명되어 있음을 알수 있다.

위의 형식처럼 만들려면 엑셀의 workbook객체에 있는 macroption 함수를 사용하여야 한다.

아무래도 함수자체는 엑셀 통합문서 차원에서 지원되어야 하기 때문에 workbook 객체에 배치되어져 있는 것이다.

이를 사용하기 위해서는 아래 그림처럼 현재 통합문서에서 코드보기를 수행한다.

그런 후 코드창에서 Workbook 객체를 선택한다.

통합문서 함수 만들기

통합문서 함수만들기

Workbook객체를 선택한후 이벤트부문을 선택하고 open 이벤트를 선택한다.

open이벤트 선택

이제, macrooptions 함수를 사용하면되는데, 이 함수는 workbook 객체 안에 존재하는 application객체의 함수로 되어져 있으며 형식은 다음과같다.

형식

Application.MacroOptions(Macro, Description, HasManu, Menutext, HasShortcutKey, ShortcutKey, Category, StatusBar, HelpContextID, HelpFile, ArgumentDescriptions)

각 의미는 다음과 같다.

- Macro : 매크로 이름지정

- Description : 함수에 대한 설명 지정

- HasMenu, Menutext : 무시되는 인수(사용되지않는다.)

- HasShortcutKey : 단축키지정유무를 지정한다. Bool값이며 True, False로 지정하는데, True면 ShortcutKey 인수를 반드시 지정하여야 한다.

- ShortcutKey : HasShortcutKey값이 True일 경우 단축키를 지정한다. False일 경우 이값은 무시된다.

- Category : 함수마법사 창에서 지정될 범주를 지정한다.

**범주 : 재무함수(1), 날짜/시간함수(2), 사용자 정의 함수(14) 등

- StatusBar : 매크로에 대한 상태바 텍스트를 지정한다.

- HelpContextID : 매크로 도움말에 대한 ID값을 Integer 타입으로 지정한다.

- HelpFile : HelpContextID에 의해 정의된 도움말을 포함하는 도움말 파일의 이름을 지정한다.

- ArgumentDescriptions : 함수 매개변수에대한 설명을 1차원 배열형태로 지정한다.