VBA 함수 – 호출하기, 값 반환하기 & 매개변수

이 튜토리얼에서는 VBA에서 매개 변수가 있든 없든 함수를 만들고 사용하는 방법을 알려드립니다.

VBA에는 사용할 수 있는 많은 기본 제공 함수가 포함되어 있지만 직접 함수를 작성할 수도 있습니다. VBA에서 코드를 작성할 때 하위 프로시저나 함수 프로시저로 작성할 수 있습니다. 함수 프로시저는 코드에 값을 반환할 수 있습니다. 이는 VBA가 작업을 수행하여 결과를 반환하도록 하려는 경우에 매우 유용합니다. VBA 함수는 Excel의 기본 제공 Excel 함수처럼 Excel 내부에서 호출할 수도 있습니다.

인수 없이 함수 만들기

함수를 만드려면 함수에 이름을 지정하여 함수를 정의해야 합니다. 그런 다음 함수가 반환할 데이터 유형을 나타내는 데이터 유형으로 함수를 정의할 수 있습니다.

상수처럼 호출될 때마다 정적 값을 반환하는 함수를 만들고 싶을 수 있습니다.

Function GetValue() As Integer
   GetValue = 50
End Function

이 함수를 실행하면 함수는 항상 50이라는 값을 반환합니다.

VBA에서 객체를 참조하는 함수를 만들 수도 있습니다. 객체를 참조하는 함수의 값을 반환하려면 Set 키워드를 사용해야 합니다.

Function GetRange() as Range
  Set GetRange = Range("A1:G4")
End Function

VBA 코드에서 위의 함수를 사용하면 작업 중인 시트에서 항상 A1: G4의 셀 범위를 반환합니다.

서브 프로시저에서 함수 호출하기

함수를 만든 후에는 서브 프로시저에서 함수를 호출할 수 있습니다.

항상 50이라는 값이 반환됩니다.

또한 서브 프로시저에서 GetRange 함수를 호출할 수도 있습니다.

위 예제에서는 범위 객체의 셀 글꼴을 굵게 표시하기 위해 서브 프로시저에서 GetRange 함수를 호출합니다.

함수 만들기

단일 인수

함수에 매개 변수를 하나 이상 할당할 수도 있습니다. 이러한 매개변수를 인수라고 부릅니다.

Function ConvertKilosToPounds (dblKilo as Double) as Double
   ConvertKiloToPounds = dblKilo*2.2
End Function

서브 프로시저에서 위의 함수를 호출하여 킬로그램을 파운드 단위로 변환합니다.

필요한 경우 VBA 코드 내의 여러 프로시저에서 함수를 호출할 수 있습니다. 이 기능은 동일한 코드를 반복해서 작성하지 않아도 된다는 점에서 매우 유용합니다. 또한 긴 프로시저를 관리하기 쉽게 함수를 사용하여 간결하게 작성할 수 있습니다.

위 예제에는 두 개의 프로시저가 있으며, 각 프로시저는 함수의 dblKilo 인수에 전달된 킬로그램을 파운드 단위로 변환하기 위해 함수를 사용하고 있습니다.

다중 인수

여러 인수가 있는 함수를 만들고 서브 프로시저에서 함수에 값을 전달할 수 있습니다.

Function CalculateDayDiff(Date1 as Date, Date2 as Date) as Double
   CalculateDayDiff = Date2-Date1
End Function

함수를 호출하여 두 날짜 사이의 차이를 계산할 수 있습니다.

선택적 인수

함수에 선택적 인수를 전달할 수도 있습니다. 즉, 함수에 지정된 코드에 따라 인수의 필요 여부가 결정됩니다.

Function CalculateDayDiff(Date1 as Date, Optional Date2 as Date) as Double
'선택적 인수 Date2가 입력되어있는지 확인합니다.
'입력되지 않았을 경우 Date2를 오늘 날짜로 정의합니다.
   If Date2=0 then Date2 = Date
'차이를 계산합니다
   CalculateDayDiff = Date2-Date1 
End Function

인수의 기본값 설정하기

사용자가 인수를 생략할 경우 기본값으로 설정한 값이 대신 사용되도록 함수를 만들 때 선택적 인수의 기본값을 설정할 수도 있습니다.

Function CalculateDayDiff(Date1 as Date, Optional Date2 as Date="06/02/2020") as Double 
'차이를 계산합니다
   CalculateDayDiff = Date2-Date1 
End Function

ByVal 과 ByRef

함수에 값을 전달할 때 ByVal 또는 ByRef 키워드를 사용할 수 있습니다. 이 중 하나를 생략하면 ByRef가 기본값으로 사용됩니다.

ByVal은 변수의 복사본을 함수에 전달한다는 의미이고, ByRef는 변수의 원래 값을 참조한다는 의미입니다. 변수의 복사본(ByVal)을 전달하면 변수의 원래 값은 변경되지 않지만, 변수를 참조하면 변수의 원래 값이 함수에 의해 변경됩니다.

Function GetValue(ByRef intA As Integer) As Integer
   intA = intA * 4
   GetValue = intA
End Function

위의 함수에서 ByRef를 생략해도 함수는 동일한 방식으로 작동합니다.

Function GetValue(intA As Integer) As Integer
   intA = intA * 4
   GetValue = intA
End Function

이 함수를 호출하기 위해 서브 프로시저를 실행할 수 있습니다.

Sub TestValues()
   Dim intVal As Integer
'값이 10인 변수를 생성합니다
   intVal = 10
'GetValue 함수를 실행하고 직접실행창에 값을 표시합니다
   Debug.Print GetValue(intVal)
'intVal 변수의 값을 직접실행창에 표시합니다 
   Debug.Print  intVal
End Sub

직접실행창에는 두 번 모두 40이라는 값이 표시됩니다. 변수를 함수에 전달하면 10에 4를 곱한 값이 함수에 전달됩니다. ByRef 키워드를 사용하면(또는 완전히 생략하면) IntVal 변수의 값이 수정됩니다. 수정된 값은 직접실행창에 함수 결과를 먼저 표시(40)한 다음 직접실행창에 IntVal 변수의 값(40)을 표시할 때 확인할 수 있습니다.

원래 변수의 값을 변경하지 않으려면 함수에서 ByVal을 사용해야 합니다.

Function GetValue(ByVal intA As Integer) As Integer
intA = intA * 4
GetValue = intA
End Function

이제 서브 프로시저에서 함수를 호출하면 변수 IntVal의 값은 10으로 유지됩니다.

Exit Function

특정 조건을 확인해서 충족할 경우 값을 반환하기 위해 함수를 종료해야 한다면 Exit Function구문이 필요할 수 있습니다. 함수가 종료되지 않고 계속 실행된다면 원하는 값을 얻지 못할수도 있습니다.

Function FindNumber(strSearch As String) As Integer
   Dim i As Integer
'입력으로 받은 strSearch 문자열의 각 문자를 반복합니다
   For i = 1 To Len(strSearch)
   '문자가 숫자일 경우, 해당 값을 반환합니다
      If IsNumeric(Mid(strSearch, i, 1)) Then
         FindNumber= Mid(strSearch, i, 1)
   '그런다음 함수를 종료합니다
         Exit Function
      End If
   Next
   FindNumber= 0
End Function

위의 함수는 숫자를 찾을 때까지 제공된 문자열을 반복한 다음 문자열에서 해당 숫자를 반환합니다. 이 함수는 문자열에서 첫 번째 숫자만 찾은 다음 함수를 종료합니다.

위의 함수는 아래와 같은 서프 프로시저에서 호출할 수 있습니다.

Sub CheckForNumber()
   Dim NumIs as Integer
'숫자를 찾기 위해 find number 함수에 텍스트를 입력합니다
   NumIs = FindNumber("Upper Floor, 8 Oak Lane, Texas")
'직접실행창에 함수실행의 결과를 표시합니다
   Debug.Print NumIs
End Sub

Excel 시트 내에서 함수 사용하기

VBA 코드에서 서브 프로시저를 사용하여 함수를 호출하는 방법 외에도 Excel 시트 내에서 함수를 호출할 수도 있습니다. 생성한 함수는 기본적으로 함수 목록의 사용자 정의 섹션에 표시됩니다.

fx 를 클릭하면 함수 마법사 대화상자가 표시됩니다

범주 선택에서사용자 정의를 선택합니다.

사용자 정의 함수(UDF)에서 필요한 함수를 선택합니다.

또는 Excel에서 함수 작성을 시작할 때 함수 드롭다운 목록에 해당 함수가 표시됩니다.

Excel 시트 내에서 함수를 사용할 수 없도록 하려면 VBA 코드에서 함수를 만들 때 “Private Function”을 붙여서 만들어야 합니다.

Private Function CalculateDayDiff(Date1 as Date, Date2 as Date) as Double 
   CalculateDayDiff = Date2-Date1 
End Function

이제 사용 가능한 Excel 함수를 보여주는 드롭다운 목록에 표시되지 않습니다.

흥미로운 사실은, 함수를 찾을 때 목록에 나타나지는 않지만  여전히 사용할 수 있습니다!

두 번째 인수를 선택 사항으로 선언한 경우 Excel 시트와 VBA 코드 내에서 이 인수를 생략할 수 있습니다.

Excel 시트에서 인수 없이 생성한 함수를 사용할 수도 있습니다.