엑셀 VBA 사용자 정의 함수(UDF) 만들기 (Function 내이름(매개변수) As 데이터타입)

엑셀 파보자

엑셀 VBA 사용자 정의 함수(UDF) 만들기 (Function 내이름(매개변수) As 데이터타입)

파보자 2025. 4. 17. 09:06

엑셀, 마이크로소프트의 최고 걸작… 이라고 하기엔 뭔가 2% 부족하죠? 마치 김밥에 단무지가 빠진 느낌이랄까요? 하지만 걱정 마세요! 오늘 우리는 그 부족한 2%를 채워줄 마법의 도구, 바로 엑셀 VBA 사용자 정의 함수(UDF) 에 대해 알아볼 겁니다. VBA! 이름만 들어도 벌써 머리가 지끈거리신다고요? 으으, 겁먹지 마세요! 마치 냉장고 속 야채처럼 시들시들한 엑셀 실력에 비타민C 같은 활력을 불어넣어 줄 테니까요. Function 내이름(매개변수) As 데이터타입 이 이상한 주문 같은 코드가 곧 여러분의 손에서 춤을 추게 될 겁니다. 함수 인수와 반환 값 설정부터 실제 예제를 활용한 UDF 만들기 , 그리고 최종적으로 만든 UDF 활용법 까지! 오늘 이 포스팅 하나면 여러분도 엑셀 마법사로 변신! ✨ 자, 그럼 망설이지 말고 다 같이 VBA의 세계로 풍덩 빠져볼까요?

UDF 기본 구조 이해하기

자, 드디어 엑셀 VBA 세계의 마법 지팡이, 사용자 정의 함수(UDF)의 비밀을 파헤쳐 볼 시간입니다! 마치 연금술사가 비밀 레시피를 공개하듯, 저와 함께 UDF의 기본 구조를 하나하나 뜯어보며 그 매력에 푹 빠져봅시다!

UDF는 기본적으로 Function 키워드로 시작해서 End Function으로 끝납니다. 이 사이에 여러분의 놀라운 코딩 마법이 펼쳐지는 거죠! 마치 맛있는 샌드위치를 만드는 것과 같아요. 빵 사이에 원하는 재료를 넣듯이, Function과 End Function 사이에 여러분만의 로직을 넣으면 됩니다. 참 쉽죠?

Function 내함수이름(인수1 As 데이터타입1, 인수2 As 데이터타입2) As 데이터타입3

    ' 여기에 마법같은 코드를 작성하세요! ✨

    내함수이름 = 결과값

End Function

코드 분석

위의 코드를 보면 Function, 내함수이름, 인수, 데이터타입, 결과값, End Function과 같은 요소들이 보이실 겁니다. 하나씩 자세히 살펴볼까요?

각 구성 요소 설명

Function: 함수의 시작을 알리는 키워드입니다. "자, 이제 함수를 만들어볼까?" 라고 엑셀에게 신호를 보내는 역할을 합니다.
내함수이름: 여러분이 만들 함수의 이름입니다. Sum, Average처럼 기본 제공 함수와 겹치지 않도록 주의해야 합니다! 나만의 특별한 함수 이름을 지어주세요. (가독성을 위해 간결하고 명확한 이름을 사용하는 것이 좋습니다.)
인수: 함수에 입력할 값입니다. Sum 함수에서 숫자들을 입력하는 것처럼, 여러분의 함수에도 필요한 값들을 인수로 받을 수 있습니다. 인수는 여러 개를 사용할 수 있으며, 쉼표(,)로 구분합니다.
데이터타입: 인수와 함수의 반환 값의 데이터 유형을 지정합니다. Integer, String, Double, Variant 등 다양한 데이터 유형이 있습니다. 정확한 데이터 유형을 지정하면 함수의 정확성과 효율성을 높일 수 있습니다.
결과값: 함수가 계산한 결과를 저장하는 변수입니다. 함수 이름과 동일하게 설정해야 합니다. 이 값이 함수의 최종 결과물이 되는 것이죠!
End Function: 함수의 끝을 알리는 키워드입니다. "함수 만들기 끝!" 이라고 엑셀에게 알려주는 역할을 합니다.

자, 이제 UDF의 기본 구조를 이해하셨나요? 아직 조금 어렵게 느껴지신다면 걱정하지 마세요! 다음에는 실제 예제를 통해 UDF를 만들어보면서 더욱 자세히 알아보겠습니다.

UDF는 엑셀의 기능을 무한대로 확장할 수 있는 강력한 도구입니다. 복잡한 계산, 데이터 처리, 자동화 등 다양한 작업을 UDF를 통해 간편하게 처리할 수 있습니다. 이제 여러분도 UDF의 세계로 뛰어들어 엑셀 마법사가 되어보세요!

함수 인수와 반환 값 설정

자, 이제 드디어!! UDF의 심장이라고 할 수 있는 인수와 반환 값 설정 에 대해 알아볼 시간입니다. 마치 레스토랑에서 메뉴를 고르는 것처럼, 함수에 어떤 재료(인수)를 넣고 어떤 요리(반환 값)를 받을지 결정하는 아주 중요한 단계 죠! 잘못 설정하면… 셰프님(엑셀)이 엄청 혼란스러워하실 수도 있다는 사실?! 잊지 마세요~?

함수 인수

먼저, 함수 인수! 이 녀석은 함수가 제대로 작동하기 위해 필요한 정보들을 전달하는 역할 을 합니다. 마치 요리 재료처럼 말이죠! 예를 들어, 두 숫자를 더하는 함수를 만든다고 생각해 보세요. 덧셈에 필요한 두 숫자가 바로 함수의 인수가 되는 겁니다. 참 쉽죠~?!

인수는 여러 개를 사용할 수도 있고, 아예 사용하지 않을 수도 있습니다. 필요에 따라 유연하게 조절 가능하다는 말씀! 인수의 데이터 타입(숫자, 문자, 날짜 등)도 명시 해야 합니다. 셰프님께 "적당히 넣어주세요~"라고 하면 안 되잖아요? "양파 2개, 당근 1개!" 이렇게 정확하게 알려드려야 맛있는 요리가 나오는 법이죠! ^^

자, 그럼 인수 설정의 예시를 한번 볼까요?

Function MySum(ByVal num1 As Double, ByVal num2 As Double) As Double

여기서 MySum은 함수의 이름, num1과 num2는 Double 타입의 인수입니다. ByVal은 값을 복사해서 전달하는 방식을 의미하는데, 이 부분은 나중에 좀 더 자세히 다뤄보도록 하죠! (기대하시라~ 두둥!)

함수 반환 값

다음은 반환 값! 함수가 열심히 작업한 결과물을 돌려주는 역할 을 합니다. 요리사가 정성껏 만든 요리라고 생각하면 딱! 이죠! 반환 값의 데이터 타입 역시 명시 해야 합니다. 셰프님께 "아무거나 주세요~"라고 하면… 곤란하시겠죠? "스테이크 주세요!" 처럼 원하는 요리를 정확하게 말씀드려야 합니다!

위 예시에서 As Double은 함수의 반환 값이 Double 타입임을 나타냅니다. 즉, MySum 함수는 두 개의 Double 타입 숫자를 받아서 더한 후, Double 타입의 결과를 반환하는 똑똑한 함수랍니다!

반환 값은 Function 프로시저 내에서 변수에 값을 할당하는 방식으로 설정할 수 있습니다. 마치 요리사가 완성된 요리를 접시에 담는 것과 같죠! 아래 예시를 보시면 더욱 이해가 쉬울 거예요!

Function MySum(ByVal num1 As Double, ByVal num2 As Double) As Double
    MySum = num1 + num2 ' 결과를 MySum 변수에 할당!
End Function

MySum = num1 + num2 이 부분이 바로 반환 값을 설정하는 핵심 코드 입니다! num1과 num2를 더한 결과를 MySum 변수에 담아서 반환하는 것이죠. 간단하지만 강력한 마법 같지 않나요?!

이처럼 인수와 반환 값을 제대로 설정하는 것은 UDF를 만드는 데 있어서 가장 기본적이면서도 중요한 부분 입니다. 처음에는 조금 어렵게 느껴질 수도 있지만, 몇 번 연습하다 보면 금방 익숙해질 거예요!

ParamArray를 이용한 가변 인수

자, 좀 더 깊이 들어가 볼까요? 만약 여러분이 100개의 숫자를 더해야 한다면 어떻게 하시겠어요? 각각의 숫자를 인수로 일일이 입력하는 건… 생각만 해도 아찔하죠?! 이럴 때는 ParamArray 라는 강력한 기능 을 사용할 수 있습니다! ParamArray는 가변 개수의 인수를 배열 형태로 받을 수 있도록 해줍니다. 마치 마법의 주머니처럼 말이죠!

Function SumAll(ParamArray nums() As Variant) As Double
    Dim i As Long, total As Double
    For i = LBound(nums) To UBound(nums)
        total = total + nums(i)
    Next i
    SumAll = total
End Function

이 SumAll 함수는 몇 개의 숫자를 입력하든 모두 더해서 결과를 반환합니다. ParamArray 덕분에 100개든 1000개든 문제없죠! 정말 편리하지 않나요?!

다양한 반환 값과 함수 이름 명명 규칙

또한, 반환 값은 숫자, 문자, 날짜뿐만 아니라 배열이나 객체도 될 수 있습니다. 예를 들어, 특정 범위의 셀 값을 배열로 반환하는 함수를 만들 수도 있죠! 이처럼 UDF는 무궁무진한 가능성을 가지고 있습니다. 여러분의 상상력을 마음껏 발휘해 보세요!

그리고 잊지 마세요! 함수의 이름은 명확하고 직관적으로 짓는 것이 좋습니다. 함수의 기능을 잘 나타내는 이름을 사용하면 코드를 이해하고 관리하기가 훨씬 수월해집니다. 예를 들어, 숫자를 더하는 함수의 이름을 CalculateSum이나 AddNumbers처럼 짓는 것이 좋겠죠?

주석의 중요성

마지막으로, 함수 내부에 주석을 추가하는 것도 잊지 마세요! 주석은 코드의 기능을 설명하는 메모입니다. 나중에 코드를 다시 볼 때, 혹은 다른 사람이 코드를 볼 때 주석이 있다면 이해하기가 훨씬 쉬워집니다. 주석은 '(작은따옴표)로 시작합니다. 예를 들어, ' 이 함수는 두 숫자를 더합니다. 와 같이 작성할 수 있습니다.

자, 이제 여러분은 UDF의 달인이 될 준비를 마쳤습니다! 다음 장에서는 실제 예제를 통해 UDF를 만들어보면서 실력을 발휘해 보세요! 화이팅!

실제 예제로 UDF 만들어보기

자, 이제 드디어!! 쇼 타임입니다! 앞서 배운 UDF 기본 구조를 바탕으로 실제 예제를 만들어 볼 건데요, 벌써부터 두근거리지 않으세요?! (저만 그런가요? ^^;) 걱정 마세요! 제가 쉽고 재밌게 설명해 드릴게요!

텍스트에서 특정 문자 제거

첫 번째 예제는 텍스트 문자열에서 특정 문자를 제거하는 함수입니다. "B-A-N-A-N-A"에서 "-"를 제거하고 깔끔하게 "BANANA"로 만들어주는 마법 같은 함수죠! 함수 이름은 RemoveChars로 정했습니다. 멋지죠? 😎

Function RemoveChars(InputText As String, RemoveChar As String) As String

Dim i As Long

Dim Result As String

Result = ""

For i = 1 To Len(InputText)

If Mid(InputText, i, 1) <> RemoveChar Then

Result = Result & Mid(InputText, i, 1)

End If

Next i

RemoveChars = Result

End Function

어떻습니까? 참 쉽죠?! Mid 함수를 사용해서 문자열을 한 글자씩 쪼개고, RemoveChar와 비교해서 같지 않으면 Result에 차곡차곡 쌓아줍니다. 마치 레고 블록을 조립하는 것 같지 않나요? 😄 Len 함수는 문자열의 길이를 알려주는 똑똑한 친구입니다. For 루프는 1부터 문자열의 길이까지 꼼꼼하게 반복하며 모든 문자를 검사합니다. 결과적으로 RemoveChars 함수는 깔끔하게 처리된 문자열을 반환합니다!

두 수의 차이 계산

두 번째 예제는 좀 더 복잡한(?) 함수를 만들어 볼게요. 두 개의 숫자를 입력받아, 큰 수와 작은 수의 차이를 계산하는 함수입니다. 이름하여 Difference 함수! (이름 짓는 센스 어떤가요? 😜)

Function Difference(Num1 As Double, Num2 As Double) As Double

If Num1 > Num2 Then

Difference = Num1 - Num2

Else

Difference = Num2 - Num1

End If

End Function

If 문을 사용해서 Num1이 Num2보다 큰지 작은지 판단하고, 그에 따라 차이를 계산합니다. 참 쉽죠?! Double 데이터 타입을 사용해서 소수점까지 정확하게 계산할 수 있도록 했습니다. 이 함수를 사용하면 123.45와 67.89의 차이도, 3.14와 2.71의 차이도 문제없습니다!

0을 제외한 평균 계산

세 번째 예제는 조금 더 실용적인 함수를 만들어보겠습니다. 엑셀에서 특정 범위의 셀 값들의 평균을 구하는 함수는 자주 사용되죠? 하지만 가끔 0 값을 제외하고 평균을 구해야 하는 경우가 있습니다. 그럴 때 유용한 AverageExcludingZero 함수를 만들어 볼게요.

Function AverageExcludingZero(rng As Range) As Double

Dim cell As Range

Dim sum As Double

Dim count As Long

sum = 0

count = 0

For Each cell In rng

If cell.Value <> 0 Then

sum = sum + cell.Value

count = count + 1

End If

Next cell

If count > 0 Then

AverageExcludingZero = sum / count

Else

AverageExcludingZero = 0 ' 0으로 나누는 오류 방지!

End If

End Function

이 함수는 rng라는 범위를 입력받아, 0이 아닌 값들의 합을 구하고, 0이 아닌 셀의 개수를 세어 평균을 계산합니다. For Each 루프를 사용해서 범위 내의 모든 셀을 순회하며 값을 확인하고, 0이 아닌 경우에만 합계와 개수를 누적합니다. 만약 범위 내에 0이 아닌 값이 하나도 없다면, 0으로 나누는 오류를 방지하기 위해 평균을 0으로 설정합니다. 정말 똑똑한 함수죠?! 😉

주차 계산

네 번째 예제는 날짜 함수입니다. 주어진 날짜가 몇 번째 주차인지 계산하는 WeekNumber 함수를 만들어 보겠습니다.

Function WeekNumber(InputDate As Date) As Integer

WeekNumber = Format(InputDate, "ww")

End Function

Format 함수를 사용하면 날짜를 원하는 형식으로 변환할 수 있습니다. "ww"는 주차를 나타내는 형식 코드입니다. 이 함수를 사용하면 특정 날짜가 몇 번째 주차인지 간단하게 알 수 있습니다!

자, 이렇게 네 가지 예제를 통해 UDF를 만드는 방법을 알아보았습니다. 어떠셨나요? 생각보다 어렵지 않죠? 😉 이제 여러분도 자신만의 UDF를 만들어 엑셀 작업을 더욱 효율적으로 할 수 있게 되었습니다! 다음에는 만든 UDF를 활용하는 방법에 대해 알아보겠습니다. 기대해주세요! ✨

만든 UDF 활용하는 방법

자, 드디어! 힘들게 만든 VBA UDF(User Defined Function), 이제 써먹어야죠?! 그냥 방치할 순 없잖아요? ^^ 마치 최첨단 무기를 만들어 놓고 창고에 넣어두는 것과 같다고요! 이제 여러분의 엑셀 시트를 UDF 파워로 꽉꽉 채워봅시다!

UDF를 활용하는 방법은 생각보다 간단합니다. 마치 기본으로 내장된 엑셀 함수(SUM, AVERAGE, VLOOKUP… 아, 너무 많아서 다 쓰기도 힘드네요!?)처럼 사용하면 됩니다. 하지만 몇 가지 깨알 팁들을 알아두면 훨씬 더 효율적으로, 그리고 스마트하게(?) UDF를 쓸 수 있다는 사실!

함수 마법사 활용하기

UDF를 처음 사용한다면 함수 마법사를 이용하는 게 가장 편리합니다. fx 버튼을 클릭하고, '사용자 정의' 카테고리에서 여러분이 만든 UDF를 찾아보세요. 마치 숨바꼭질처럼 찾는 재미가 쏠쏠할 겁니다 (특히 UDF를 많이 만들었을 때!). 함수 인수를 입력하는 창도 친절하게 제공되니, 혹시라도 인수 순서가 헷갈릴 걱정은 붙들어 매세요!

직접 입력하기

자, 이제 UDF 사용에 좀 익숙해지셨나요? 그럼 직접 입력하는 방법도 있습니다. 수식 입력줄에 =내UDF(인수1, 인수2, ...) 와 같이 직접 입력하면 됩니다. 타자 연습도 하고, 시간도 절약하고, 일석이조! 게다가 직접 입력하면 왠지 모르게 엑셀 고수가 된 기분까지 느낄 수 있다는 건 안 비밀입니다~?!

다른 함수와 조합하기

UDF의 진정한 파워는 다른 함수와 조합했을 때 발휘됩니다. 예를 들어, =SUM(내UDF(A1:A10))처럼 UDF의 결과를 SUM 함수의 인수로 사용할 수도 있습니다. 이렇게 하면 UDF를 더욱 다양하게 활용할 수 있겠죠? 상상력을 발휘해서 나만의 엑셀 함수 조합을 만들어 보세요! 마치 연금술사처럼 새로운 기능을 만들어내는 재미를 느낄 수 있을 겁니다!

이름 정의 활용하기

UDF 이름이 너무 길거나 복잡하다면, 이름 정의 기능을 활용해서 간단하게 바꿀 수 있습니다. 예를 들어, CalculateSuperComplexFormula라는 UDF를 SCF로 바꿔서 =SCF(인수1, 인수2)와 같이 사용할 수 있습니다. 이렇게 하면 수식이 훨씬 간결해지고, 보기에도 좋겠죠? 가독성 UP! 효율성 UP!

VBA 코드에서 호출하기

VBA 코드 내에서도 UDF를 호출할 수 있습니다. Application.WorksheetFunction.내UDF(인수1, 인수2)와 같이 사용하면 됩니다. VBA와 UDF의 환상적인 콜라보! 엑셀 자동화의 끝판왕을 경험할 수 있습니다. 자동화의 세계로 풍덩~!

다른 통합 문서에서 UDF 사용하기

다른 통합 문서에서 만든 UDF를 사용하려면, 먼저 해당 통합 문서를 열어야 합니다. 그리고 수식 입력줄에 =[통합문서이름.xlsm]시트이름!내UDF(인수1, 인수2)와 같이 입력하면 됩니다. 다른 통합 문서에 있는 UDF도 마음껏 활용하세요! 다른 사람이 만든 UDF도 가져다 쓸 수 있다니… 이것이 바로 공유 경제의 힘?!

매크로 보안 설정 확인하기

UDF를 사용하려면 매크로 보안 설정이 "모든 매크로 포함" 또는 "디지털 서명된 매크로 포함"으로 설정되어 있어야 합니다. 보안 설정이 제대로 되어 있지 않으면 UDF가 작동하지 않을 수 있으니 주의하세요! 보안도 중요하니까요!

오류 처리 추가하기

UDF에서 발생할 수 있는 오류를 처리하기 위해 On Error GoTo 문을 사용할 수 있습니다. 오류가 발생하면 특정 코드를 실행하거나, 오류 메시지를 표시하도록 설정할 수 있습니다. 안정적인 UDF를 위해 오류 처리도 잊지 마세요! 꼼꼼함이 빛나는 순간입니다!

도움말 추가하기

UDF에 대한 도움말을 추가하면 다른 사용자가 UDF를 더 쉽게 이해하고 사용할 수 있습니다. VBA 코드에 주석을 추가하거나, 별도의 도움말 파일을 만들 수 있습니다. 친절한 UDF 개발자, 멋지지 않나요?!

자, 이제 여러분은 UDF 활용의 달인이 되었습니다! 축하드립니다! 이제 엑셀 세상을 정복할 일만 남았네요! UDF 파워를 마음껏 활용해서 업무 효율을 높이고, 엑셀 마스터로 거듭나세요! Go, Go, Power Exceler~!

자, 이제 VBA UDF의 세계 정복을 위한 여정이 거의 끝났습니다! UDF 기본 구조부터 실제 활용까지, 마치 롤러코스터를 탄 것처럼 신나셨죠? (멀미 나시는 건 아니죠…?) 이제 여러분의 엑셀은 단순한 스프레드시트가 아닙니다. 여러분만의 강력한 무기, 아니 마법 지팡이로 변신했죠! 함수 마법사 창에 여러분이 만든 함수가 딱! 하고 나타나는 순간 , 그 희열은… 직접 경험해보셔야 합니다. 앞으로 엑셀과 씨름하다 막히는 부분이 있다면, 주저 말고 UDF를 떠올리세요! 여러분의 번뜩이는 아이디어와 UDF의 만남이 엑셀의 신세계를 열어줄 겁니다 . 자, 이제 엑셀 마법사가 되어 세상을 놀라게 할 준비되셨나요?

저작자표시 비영리 변경금지