[VBA] 'Sub'와 'Function' 완벽 가이드

이 포스트는 Excel Macro Mastery 사이트의 'The Complete Guide to the VBA Sub(by Paul Kelly)'의 내용을 다시 정리한 것입니다. 이번 포스트에서는 엑셀 서브루틴(Sub)과 함수(Function)에 대해 다룹니다.

Sub와 Function 요약

본 포스트는 VBA Sub와 Function 사용에 대한 완벽한 가이드를 제공합니다. 단지 Sub와 Function의 생성, 파라메터 전달, 리턴 값 등에 대한 간략하고 빠른 정보가 필요하다면 이번 요약 섹션만 보셔도 충분 합니다. 만일 Sub와 Function에 대해 보다 자세한 사항이 궁금하시다면 포스트를 처음 부터 끝까지 읽거나 오른쪽의 목차를 이용하실 수 있습니다.

	Sub	Function
특징	리턴 없음	값이나 객체를 리턴 가능 워크시트의 함수로써 사용 가능
생성	Sub SomeSub() End Sub	Function SomeFunc() End Function
파라메터와 함께 생성	Sub SomeSub(ByVal Price As Double) End Sub	Function SomeFunc(Price As Double) End Function
호출	Call SomeSub또는 SomeSub	Call SomeFunc 또는 SomeFunc
파라메터와 함께 호출	Call SomeSub(12.99) 또는 SomeSub(12.99)	Call SomeFunc(12.99) 또는 SomeFunc(12.99)
함수에서 값 리턴하기	리턴 없음	Function GetPrice() As Long     GetPrice = 12 End Function
함수로 부터 일반 변수 리턴 받기	리턴 없음	Price = GetPrice
함수에서 객체 리턴하기	리턴 없음	Function GetCollection() As Collection     Dim coll As New Collection     Set GetCollection = coll End Function
함수로 부터 객체 리턴 받기	리턴 없음	Set coll = GetCollection
파라메터가 있는 함수로 부터 값/객체 리턴 받기	리턴 없음	Price = SomeFunc(12) Set coll = GetCollection(12)
종료 하기	If IsError(Range("A1")) Then     Exit Sub End If	If IsError(Range("A1")) Then     Exit Function End If
Private 한정자 적용 (현재 모듈에서만 사용가능)	Private Sub SomeSub()	Private Function SomeFunc()
Public 한정자 적용 (전체 프로젝트에서 사용 가능)	Public Sub SomeSub()	Public Function SomeFunc()

들어가며

프로그래밍에서 반복되는 동일한 패턴의 코드를 모듈화하는 것은 생산성 향상과 오류를 줄이는데 아주 중요한 요소입니다. VBA에서는 Sub와 Function 을 이용해 코드를 모듈화하고 필요한 곳에서 모듈화된 코드를 호출할 수 있습니다. 이번 포스트에서는 VBA의 Sub와 Function을 작성하고 호출하는 방법에 대해 살펴 보도록 하겠습니다.

여러분은 본 포스트를 통해 다음과 같은 내용들을 배울수 있습니다.

• Sub와 Function의 차이점은 무엇인가요?
• Sub는 언제, Function은 언제 사용하나요?
• Sub와 Function은 어떻게 실행 하나요?
• 값을 리턴 받을 수 있나요?
• 파라메터는 어떻게 넘기나요?
• 선택 파라메터는 무엇인가요?

'Sub'란 무엇인가?

VBA 에서 Sub라는 키워드로 시작하여 End Sub로 끝나는 Sub 프로시져는 한번에 실행 되어야 할 여러줄의 코드를 모아 놓은 코드 블록이라 생각하시면 됩니다. 여러분이 Sub 프로시져를 실행하게 되면 프로시져 안에 적혀있는 모든 코드들이 실행 됩니다. 아래 코드 샘플은 아무것도 하지 않는 빈 Sub 프로시져 입니다.

Sub WriteValues()

End Sub

여러분은 'Sub' 키워드를 이용해 Sub 프로시져를 선언할 수 있습니다. Sub 프로시져의 이름은 지정된 규칙을 따르기만 한다면 여러분이 원하는 아무것이나 가능합니다. 여러분이 지켜야할 Sub 프로시져의 이름 규칙은 아래와 같습니다.

• 이름은 abc와 같은 글자로 시작해야 하며 *, &와 같은 특수 문자나 숫자로 시작해서는 안됩니다.
• 이름 사이에 공백이 있으면 안됩니다.
• 이름은 워크북 단위 내에서 유일해야 합니다(똑같은 이름의 Sub를 계속 선언할 수 업습니다)
• 이름에 예약어를 사용할 수는 없습니다.(Sub Sub()..와 같이 사용할 수 없습니다.
• (사실은 몇몇 제약 사항이 있지만 지금은 아무것이나 가능하다고 알고 넘어 갑시다)

Sub 프로시져의 끝을 나타내기 위해서는 End Sub 키워드를 사용합니다.

'Function'이란 무엇인가?

그렇다면 Function은 무엇일까요? 본질적으로 Sub 프로시져와 Function 프로시져는 동일합니다. 단, Function은 값을 리턴할 수 있지만 Sub는 값을 리턴하지 못합니다. 이것이 둘의 가장 큰 차이점 입니다(둘의 다른 차이점도 곧 살펴 보겠지만 이것이 가장 큰 차이점입니다. 지금은 이것만 기억해도 충분합니다).

일반적으로 여러분이 값을 리턴해야 할 필요가 있을 때는 Function 프로시져를 만들면 되고, 그렇지 않을 때는 Sub 프로시져를 만들면 됩니다. 아래 예제에서는 Function을 선언하고 있습니다. Sub를 선언할 때와 매우 비슷한 것을 볼 수 있습니다.

Function PerformCalc()

End Function

선택적으로 Function이 어떤 타입을 리턴 할 것인지를 명시 할 수 있습니다. '선택적'이란 단어를 쓴것은 명시하지 않아도 Function 프로시져를 선언하는데 아무런 에러가 발생하지 않는다는 뜻입니다. 만일 여러분이 명시적인 리턴 타입을 지정하지 않는다면 기본적으로 Variant 타입이 지정됩니다. 이것은 VBA에서 런타임에 타입을 결정한다는 뜻입니다.

아래는 리턴 타입을 명시하는 예를 보여주고 있습니다. Function 프로시져의 이름 뒤에 'As ...'를 이용해 Function 프로시져가 리턴하는 타입에 대해 기술하고 있음을 주목해 주십시오.

Function PerformCalc() As Long

End Function

Function은 일반 타입, 개체 및 컬렉션을 포함하여 변수로 선언할 수 있는 모든 타입을 반환할 수 있습니다.

프로시져?

앞에서 'Sub 프로시져', 'Function 프로시져'와 같이 '프로시져'라는 용어가 자주 등장합니다. VBA에서 프로시져는 한줄 또는 그 이상의 코드로 작성된 코드 실행 단위 입니다. VBA에는 역할과 특징에 따라 다양한 종류의 프로시져가 존재합니다.

예를 들어 이번 포스트에서 다루는 'Sub 프로시져', 'Function 프로시져가 있습니다. 이 외에도 속성 프로시져, 연산자 프로시져가 있습니다. 이번 포스트에서는 Sub, Function 프로시져에 대해 집중적으로 다루어 볼 예정입니다.

본문에서 '프로시져'라는 단어는 Sub 와 Function 프로시져(Procedure) 모두를 의미합니다.

Sub vs Function

Sub 프로시져

• Sub ~ End Sub문으로 묶인 코드 블럭입니다.
• Sub 프로시져는 작업을 수행한 다음 호출자에게 컨트로를 반환하지만 값을 반환하지는 않스빈다.
• VBA, Buttion, Event 등에서 호출될 수 있습니다.

Function 프로시져

• 기본적으로는 Sub 프로시져와 같습니다. 다만, 호출자에게 컨트롤을 반환하면서 값이나 개체를 리턴할 수 있습니다(하지만 꼭 리턴을 해야만 하는것은 아닙니다.)
• VBA, Button, Event 등에서 호출 가능하지만 매크로 리스트에서 보이지 않습니다. 실행하기 위해서는 직접 입력해야 합니다.
• 만일 Function이 Public 한정자로 선언된 경우, 이것은 현재 워크북의 워크시트 Function 리스트에 나타납니다.

NOTE 1. 여러분은 "Option Private Module"을 사용하여 현재 모듈에서 Sub를 숨길 수 있습니다. 즉, 다른 프로젝트 및 응용 프로그램에서 Sub를 볼 수 없습니다. 또한 개발자 탭에서 매크로 창을 불러올 때 하위 목록에 나타나지 않습니다.

Sub/Function 프로시져 호출하기

처음으로 프로그래밍을 접하는 사람들은 하나의 프로시져에 모든 코드를 작성하려는 경향이 있습니다. 사실 이것은 그렇게 좋은 방법은 아닙니다. 코드를 의미 있는 최소한의 단위의 프로시져를 만들고 그것을 조합해서 사용하는 것이 확장성과 코드 가독성에 훨씬 도움을 줍니다.

프로시져를 조합한다는 것은 하나의 프로시져에서 필요한 다른 프로시져를 '호출'한다는 것입니다. 프로시져 호출을 어떻게 하는지 아래 코드 예제를 통해 살펴 보도록 하겠습니다.

Sub Main()
    
    ' 각 프로시져들을 호출 합니다.
    CopyData
    AddFormulas
    FormatData

End Sub

Sub CopyData()
    ' Add code here
End Sub

Sub AddFormulas()
    ' Add code here
End Sub

Sub FormatData()
    ' Add code here
End Sub

여러분은 위 예에서 Main 이라는 프로시저 블록 안에 세 개의 Sub 프로시져들의 이름이 나열되어 있는 것을 보실수 있습니다.

VBA는 프로그램을 실행하다 프로시저 이름이 적혀있는 라인에 도달하면 해당 프로시져의 코드를 실행합니다. 이를 우리는 프로시저를 호출한다고 부릅니다. 위 예에서는 Main 프로시져가 호출 되면서

예를 들어 Main 서브 프로시저에서 CopyData 서브 프로시저를 호출합니다. 사실 프로시저를 호출하기 위해서 'Call'이라는 명시적인 키워드가 있습니다. 위의 호출 코드를 아래 처럼 쓸 수도 있습니다.

Sub Main()
    
    Call CopyData
    Call AddFormulas
    Call FormatData

End Sub

Call 키워드는 생략 가능한 선택 사항입니다. 실제 VBA 프로그래밍에서는 잘 사용하지 않긴합니다. 

Function에서 값을 리턴하는 방법

Function에서 값을 반환하기 위해서는 함수 이름에 값을 할당하면 됩니다. 아래 예를 보시면 금방 이해 되실 겁니다.

Function GetAmount() As Long
    ' Returns 55
    GetAmount = 55
End Function

Function GetName() As String
    ' Returns John
    GetName = "John"
End Function

Function에서 값을 반환하면 그 값을 받아 저장할 필요가 있습니다. 아래 예제에서는 이를 보여 줍니다.

Sub WriteValues()
    Dim Amount As Long
    ' GetAmount 함수로 부터 값을 리턴 받음
    Amount = GetAmount
End Sub

Function GetAmount() As Long
    GetAmount = 55
End Function

Debug.Print 함수를 이용해 리턴 받은 값을 손쉽게 확인할 수 있습니다. 이 함수는 직접 실행 창(메뉴 > 보기 > 직접 실행 창, Ctrl+G) 에서 에 값을 출력합니다.

Sub WriteValues()
    ' Print return value to Immediate Window
    Debug.Print GetAmount
End Sub

Function GetAmount() As Long
    GetAmount = 55
End Function

 

매개변수(Parmeter) 사용

매개변수(parameter)는 '인자(argument)'라고도 불리며 Sub 또는 Function에 건네지는 값을 말합니다.

교과서적인 의미로 봤을 때 '매개변수'와 '인자'는 서로 다릅니다. 매개변수는 Sub/Function에 선언된 변수를 의미하고 인자는 그 Sub/Function에 넘겨지는 값을 의미합니다. 하지만 실제 현업에서는 대부분 이 둘에 대한 구분 없이 같은 의미로 사용되고 있으므로 굳이 신경써서 구분하지 않아도 됩니다.

Sub UsingArgs()
    ' 아래에서 56을 인자(argument)라고 합니다
    CalcValue 56
    
    ' 34 역시 인자 입니다.
    CalcValue 34
End Sub

' 아래에서 amount는 매개변수(parameter) 입니다.
Sub CalcValue(ByVal amount As Long)

End Sub

위는 매개변수와 인자의 차이를 보여 드리기 위한 예제 입니다. 위 내용은 중요하지는 않으니 그냥 이런것이 있구나하고 넘어가시면 됩니다. 대신 아래의 내용은 중요합니다.

• Sub/Function은 여러개의 매개변수를 가질 수 있습니다.
• 매개변수는  ByRef 또는 ByVal을 사용하여 전달 됩니다(기본값은 ByRef 입니다).
• 매개변수는 기본값을 지정하여 선택적 매개변수로 만들수 있습니다. 선택적 매개변수의 경우 프로시저를 호출할 때 인수를 지정하지 않아도 되며, 인수를 지정하지 않는 경우 기본값이 사용 됩니다.
• 매개변수 선언에서  New 키워드를 사용할 수 있습니다.
• 변수 타입이 선언되지 않으면 매개변수는 기본적으로 Variant 형으로 지정됩니다.

' 타입이 Long인 매개변수 선언
Sub UseParams1(count As Long)
End Sub

' 타입이 String도 가능 합니다
Sub UseParams2(name As String)
End Sub

' 엑셀에서 사용하는 Currency 타입도 가능합니다.
Sub UseParams3(amount As Currency)
End Sub

' Collection 타입 역시 가능합니다
Sub UseParamsColl(coll As Collection)
End Sub

' 클래스 모듈을 만들었다면 Class module도 매개변수로 사용 가능 합니다.
Sub UseParamsClass(o As Class1)
End Sub

' 마일 아무런 타입을 지정하지 않았다면 기본으로 Variant 타입이 됩니다.
Sub UseParamsVariant(value)
End Sub

' 배열도 매개변수로 사용가능 합니다.
Sub UseParamsArray(arr() As String)
End Sub

' 동시에 여러개의 매개 변수를 선언하는 것도 가능하고
' Currency = 56.77 처럼 기본 값을 지정하여 사용자가 선택 인자로 만들 수 있습니다.
Sub LongLine(ByVal count As Long, Optional amount As Currency = 56.77, Optional name As String = "John")

' 함수가 너무 길면 _ 문자를 이용해 여러줄로 나눌 수 있습니다.
Sub LongLine(ByVal count As Long _
            , Optional amount As Currency = 56.77 _
            , Optional name As String = "John")

반환 값이 있는 Function에 매개 변수 사용

앞에서 우리는 Function 프로시져에서 값을 반환하는 것과 프로시져를 호출할 때 매개변수를 넘겨주는 방법에 대해 알아 보았습니다. 이번 섹션에서는 값을 리턴하는 프로시저를 매개변수와 함께 호출할 때 주의해야 할 점에 대해 살펴 보도록 하겠습니다.

아래 예제는 에러가 발생합니다.

Sub UseFunction()
    Dim result As Long
    result = GetValue 24.99
End Sub

Function GetValue(amount As Currency) As Long
    GetValue = amount * 100
End Function

VAB에서는 값을 반환하는 Function 프로시져를 호출할 때 매개 변수를 넘겨줘야 한다면 괄호를 사용해야 합니다.

result = GetValue (24.99)

ByRef 와 ByVal

VBA에서 모든 매개변수는 ByRef 또는 ByVal 속성을 가지고 있습니다. 만일 둘 중 아무것도 지정되어 있지 않은 경우라면 ByRef가 기본값으로 적용 됩니다.

' 값으로 매개변수 넘기기
Sub WriteValue1(ByVal x As Long)

End Sub

' 참조로 매개변수 넘기기
Sub WriteValue2(ByRef x As Long)

End Sub

' 명시적으로 지정되지 않았으므로 ByRef로 지정
Sub WriteValue3(x As Long)

End Sub

• ByVal - 전달한 매개변수의 복사본을 만듭니다. 이는 매개변수의 값을 프로시져 내부에서 변경한다고 하더라도 복사본을 수정하는 것이기 때문에 원본의 값은 그대로 유지 된다는 것을 의미합니다.
• ByRef - 전달한 매개변수의 참조를 만듭니다. 이는 매개변수의 값을 프로시져 내부에서 변경한다면 참조가 가리키는 원본을 변경합니다.

아래 코드 예제는 ByRef와 ByVal 속성을 가진 매개변수들이 프로시져 내부에서 값이 변경될 때 원본에 어떤 영향을 미치는지 보여 줍니다.

Sub Test()

    Dim x As Long
    
    ' Pass by value - x 의 값은 변경되지 않습니다.
    x = 1
    Debug.Print "ByVal 프로시져 호출 전 x "; x
    SubByVal (x)
    Debug.Print "ByVal 프로시져 호출 후 x "; x
    
    
    ' Pass by reference - x 값은 변경 됩니다.
    x = 1
    Debug.Print "ByRef 프로시져 호출 전 x "; x
    SubByRef x
    Debug.Print "ByRef 프로시져 호출 후 x "; x

End Sub

Sub SubByVal(ByVal x As Long)
    ' x 는 복사본이므로 값을 변경해도 원본에는 영향 없다.
    x = 99
End Sub

Sub SubByRef(ByRef x As Long)
    ' x 는 참조 변수이므로 원본을 변경한다
    x = 99
End Sub

아래와 같은 결과를 출력합니다.

ByVal 프로시져 호출 전 x 1
ByVal 프로시져 호출 후 x 1
ByRef 프로시져 호출 전 x 1
ByRef 프로시져 호출 후 x 99

기본 변수 타입을 사용하는 경우 ByRef를 사용하는 것을 피해야 합니다. 여기에는 두가지 주요 이유가 있습니다.

• 값을 전달하는 사람이 값이 변경될 것이라고 예상하지 못할 수 있으며, 이로 인해 예상하지 못한 버그가 발생할 수 있습니다.
• 호출시 괄호를 사용하면 ByRef가 작동하는 것을 막습니다(다음 섹션에서 설명)

ByRef 사용시 잘 알려지지 않은 함정

매개변수를 ByRef 속성으로 사용할 때 주의해야 할 한 가지가 있습니다. 괄호를 사용하는 경우 Sub/Function 을 호출할 때 ByRef로 전달된 경우에도 전달한 변수를 변경할 수 없습니다.

아래 예에서는 괄호 없이 Sub를 호출한 다음 괄호를 사용하여 호출하고 있습니다. 이로 인해 다른 결과를 출력합니다.

Sub Test()

    Dim x As Long

    ' 괄호 없이 호출합니다. x는 변경 됩니다.
    x = 1
    Debug.Print "괄호 없이 호출 전. x "; x
    SubByRef x
    Debug.Print "괄호 없이 호출 후. x "; x

    ' 괄호와 함께 호출. x는 변경 되지 않습니다.
    x = 1
    Debug.Print "괄호와 함께 호출 전. x "; x
    SubByRef (x)
    Debug.Print "괄호와 함께 호출 후. x "; x

End Sub

Sub SubByRef(ByRef x As Long)
    x = 99
End Sub

 결과는 아래와 같습니다.

괄호 없이 호출 전. x 1
괄호 없이 호출 후. x 99
괄호와 함께 호출 전. x 1
괄호와 함께 호출 후. x 1

위의 예에서 Sub를 Function으로 변경하더라도 동일하게 동작하는 것을 확인할 수 있습니다. 하지만 Function에서 값을 반환하면 ByRef는 아래 코드와 같이 우리가 예상하는대로 정상적으로 동작합니다.

Sub TestFunc()

    Dim x As Long, ret As Long

    ' Call using with Parentheses - x will not change
    x = 1
    Debug.Print "괄호와 함께 호출 전. x "; x
    FuncByRef (x)
    Debug.Print "괄호와 함께 호출 후. x "; x
    
    ' Call using with Parentheses and return - x will change
    x = 1
    Debug.Print "괄호와 함께 호출 전. x "; x
    ret = FuncByRef(x)
    Debug.Print "괄호와 함께 호출 후. x "; x

End Sub

Function FuncByRef(ByRef x As Long)
    x = 99
End Function

괄호와 함께 호출 전. x 1
괄호와 함께 호출 후. x 1
괄호와 함께 호출 전. x 1
괄호와 함께 호출 후. x 99

앞의 섹션에서 언급했듯이 기본 타입에 대해 ByRef를 사용하여 변수를 전달하는 것을 피하고 대신 ByVal을 사용해야 합니다. 이것을 통해 전달한 변수가 실수로 변경되는 것을 방지하고, 괄호 사용 유무와 상관 없이 동작에 영향을 미치지 않습니다.

그리고 기본적으로 프로시져를 호출할 때 괄호를 사용하여 호출하는 습관을 들이는 것을 권합니다. 이는 예상치 못한 컴파일 에러를 발생 시킨다던지, 다른 결과를 출력하는 것을 막아 줍니다.

객체 타입의 매개변수에 ByRef 또는 ByVal 적용하기

프로시져에 일반 변수를 매개변수로 전달하는 것과 객체를 전달하는 것은 다른 동작을 보입니다. 이유는 객체 변수는 값 자체를 가진 것이 아닌 객체를 가리키는 참조 변수이기 때문입니다. 아래는 객체를 가리키는 변수를 프로시져의 매개변수로 넘기고 프로시져 내부에서 새로운 객체를 할당 했을 때 ByVal과 ByRef가 각각 어떻게 다르게 동작하는지 보여 줍니다.

ByVal로 넘겨진 매개변수에 새로운 객체를 할당하더라도 원본에는 영향을 미치지 않지만 ByRef로 넘겨진 변수는 프로시져 호출이 종료 되고 난 뒤에 새로운 객체를 가리키고 있는 것을 확인할 수 있습니다.

Sub UseObject()
    
    Dim coll As New Collection
    coll.Add "Apple"
    
    ' 이 호출 이후에 coll은 여전히 원본 coll을 참조합니다.
    CollByVal coll
    
    ' 이 호출 이후에 coll은 새로운 Collection객체를 참조합니다.
    CollByRef coll
    
End Sub

Sub CollByVal(ByVal coll As Collection)
    
    ' 참조를 복사한 변수에 새로운 Collection을 할당하므로 원본에 영향을 주지 못합니다.
    Set coll = New Collection
    coll.Add "ByVal"

End Sub

Sub CollByRef(ByRef coll As Collection)
    
    ' coll이 가리키고 있는 참조에 New를 하므로 원본을 변경합니다.
    Set coll = New Collection
    coll.Add "ByRef"

End Sub

ByVal을 사용하여 coll 변수를 전달하면 변수의 복사본이 생성 됩니다. 이 변수에 무엇이든 할당할 수 있으며 원래 변수에는 영향을 미치지 않습니다. 

ByRef를 사용하여 coll 변수를 전달할 때 원래 변수를 사용하고 있습니다. 이 변수에 다른 것을 할당하면 원래 변수도 다른것 객체를 가리키게 됩니다.

이에 대한 보다 자세한 사항은 객체 매개변수에 ByRef와 ByVal 사용하기를 참고 부탁 드립니다.

선택적 매개변수

때때로 코드가 실행될 때마다 같은 값이 되는 매개변수가 있습니다. 이런 매개변수에는 기본값을 제공하여 명시적으로 값을 지정하지 않으면 기본값을 사용하게 하는 선택적 매개변수로 만들 수 있습니다.

아래 예에서는 보고서의 이름을 선택적 매개변수로 지정하여 명시적으로 보고서의 이름을 지정하지 않는 경우 기본값을 사용하도록 하여 코드를 간단하게 만들 수 있습니다.

Sub CreateReport(Optional reportName As String = "Daily Report")

End Sub

위 프로시져에 보고서의 이름을 지정하지 않으면 "Daily Report"로 지정됩니다.

Sub Main()
    
    ' 보고서의 이름이 "Daily Report"가 됩니다.
    CreateReport
    
    ' 명시적으로 지정했으므로 보고서의 이름이 "Weekly Report"가 됩니다.
    CreateReport "Weekly Report"

End Sub

Optional 매개변수는 매개변수 리스트의 뒤쪽에 위치해야 합니다. 만일 일반 매개변수 앞에 선택적 매개변수를 선언하면 에러가 발생합니다.

Sub/Function 프로시져는 여러개의 선택적 매개변수를 정의할 수 있으며, 여러분은 일부 매개변수에만 값을 지정할 수도 있습니다.

만일 값을 지정하고 싶지 않다면 매우 간단합니다. 그냥 빈 상태로 두면 됩니다. 하지만 보다 더 나은 방법은 ":=" 오퍼레이터를 이용해 값을 지정할 매개변수를 명시하는 것입니다.

Sub Multi(marks As Long _
            , Optional count As Long = 1 _
            , Optional amount As Currency = 99.99 _
            , Optional version As String = "A")
            
    Debug.Print marks, count, amount, version
    
End Sub

Sub UseBlanks()

    ' marks, count
    Multi 6, 5
    
    ' marks, amount 지정하지 않는 매개변수를 그냥 비워둔것에 주목
    Multi 6, , 89.99
    
    ' marks, version
    Multi 6, , , "B"
    
    ' marks,count and version
    Multi 6, , , "F"

End Sub

Sub UseName()

    ' marks and count
    Multi 12, count:=5
    
    ' marks and amount
    Multi 12, amount:=89.99
    
    ' marks and version
    Multi 12, version:="B"
    
    ' marks,count and version
    Multi 12, count:=6, version:="F"

End Sub

위 예제를 통해 알수 있듯이 사용하지 않는다고 매개 변수를 비워두는 것 보다 :=를 이용해 사용할 매개변수를 명시적으로 지정하는 것이 코드 가독성에 훨씬 도움이 됩니다.

wk.SaveAs "C:\Docs\data.xlsx", , , , , , xlShared
    
wk.SaveAs "C:\Docs\data.xlsx", AccessMode:=xlShared

IsMissing Function

IsMissing 함수는 매개변수가 전달 되었는지 여부를 검사하여 true/false를 리턴하는 함수입니다. 이 함수를 이용하여 우리는 선택적 매개변수가 제공되었는지 확인 할 수 있습니다. IsMissing 함수는 Long 및 Double과 같은 기본 타입에서는 작동하지 않기 때문에 Variant 매개변수를 이용합니다.

Sub test()
    ' "Parameter not missing" 출력
    CalcValues 6
    
    ' "Parameter missing" 출력
    CalcValues
    
End Sub

Sub CalcValues(Optional x)

    ' 매개변수 체크
    If IsMissing(x) Then
        Debug.Print "Parameter missing"
    Else
        Debug.Print "Parameter Not missing"
    End If

End Sub

Custom Function

여러분이 엑셀에서 제공하는 기본 함수 외에도 여러분의 커스텀 Function 프로시져를 만들어 엑셀 시트에서 사용할 수 있습니다. 시트에서 사용할 수 있는 커스텀 함수는 모듈을 새로 만들어 public 함수를 추가해주면 됩니다.

먼저 프로젝트 뷰에서 우클릭 > 삽입 > 모듈을 선택해 모듈을 추가하도록 합니다.

일단 아래 Function 프로시져를 모듈에 작성 합니다.

Public Function MyNewFunction()
    MyNewFunction = 99
End Function

위와 같이 작성후 엑셀의 함수 박스에 "=My"라고 검색하면 아래 스크린샷 처럼 함수 목록이 뜨는것을 확인할 수 있습니다.

만일 여러분이 셀에 위 함수를 적용하게 된다면 셀에 99가 적용되는 것을 확인할 수 있습니다.

결론

이상 VBA Sub/Function 프로시져에 대해 살펴 보았습니다. 마치기 전에 이번 포스트에서 살펴 보았던 내용을 다시 한번 요약해 보도록 하죠.

• Function은 값을 반환하지만 Sub는 값을 반환하지 않습니다.
• Function은 workbook의 함수 목록에 노출되고, 시트에서 사용가능 합니다.
• ByRef 매개변수를 사용하면 원본 값을 변경할 수 있습니다.
• Sub 를 매개변수와 함께 호출 시 괄호를 사용하면 ByRef가 작동하지 않습니다.
• 값을 반환하지 않을 때 Sub/Function의 매개변수에 괄호를 사용하지 마세요.
• 값을 반환 할 때 Function의 매개변수에 괄호를 사용합니다.

부록 1. 같이 읽으면 좋은 글

• [VBA] Collection 완벽 가이드
• [VBA] 엑셀 매크로 시작 완벽 가이드
• [VBA] 객체(Object) 완벽 가이드
• [VBA] 배열 완벽 가이드