[Excel][VBA] Function Procesure(함수 프로시저)

함수 프로시저(사용자 정의 함수) : 계산을 수행하고 결과를 반환하는 VBA  프로시저

                                            VBA 코드나 워크시트의 수식에서 함수 프로시져 사용 가능

※ 사용자 정의 함수를 워크시트의 수식으로 사용하려면 Public으로 선언되고 표준 모듈에 저장되어야 한다.

 

 

■ 워크시트에서 사용자 정의 함수 사용하기

수식 > 함수 라이브러리 > 함수 삽입 > 범주 선택 : 사용자 정의 함수

---------------------------------------------------------------------------------------------------------------------------------

 

■ Function 프로시저 구문

[Public | Private | Friend] [ Static ] Function 함수명 [ ( 인수 ) ] [ As type ]
[ 실행문 ]
[ 함수명 = 반환값 ]
[ Exit Function ]
[ 실행문 ]
[ 함수명 = 반환값 ]
End Function

Public	프로젝트내 모든 모듈의 모든 프로시저에서 접근 가능(선택) 기본적으로 모든 프로시저는 Public 이다.        Function ProcessDate()  = Public Function ProcessDate() 모듈에 Option Private Module문이 사용되면 다른 프로젝트에서 접근 불가(모듈내 모든 프로시저를 private화)
Private	현재 모듈의 모든 프로시저에서 접근 가능(선택) 함수 마법사에 나타나지 않는다.
Friend	Optional. Used only in a class module. Indicates that the Function procedure is visible throughout the project, but not visible to a controller of an instance of an object.
Static	프로시저가 종료되더라도 프로시저 내에서 선언된 변수의 값이 유지된다. Static 속성은 procedure 에서 사용되더라도 Function 외부에서 선언된 변수에 영향을 주지 않습니다.(선택)
함수명	함수 프로시저 이름, 워크시트 함수 이름과 동일하면 워크시트 함수가 우선적으로 실행된다.
인수	함수 프로시저에 전달되는 인수, 쉼표( , )로 인수를 구분(선택)
type	함수 프로시저가 반환하는 값의 자료형 지정
실행문	VBA  명령문
반환값	함수 프로시저의 반환값, 적어도 한번은 어떤 값이 함수이름에 값이 할당 되어야 한다.

■ 인수 구문

[ Optional ] [ ByVal | ByRef ] [ ParamArray ] 인수명 [ ( ) ] [ As type ] [ = defaultvalue ]

Optional	Optional. Indicates that an argument is not required. If used, all subsequent arguments in arglist must also be optional and declared by using the Optional keyword. Optional can't be used for any argument if ParamArray is used.
ByVal	원래의 변수값을 복사해서 전달, 프로시져에서 인수의 값을 변경해도 원래의 변수 값이 변경되지 않음.
ByRef	Visual Basic 기본 전달 방식, 변수의 메모리 주소를 전달하므로 프로시져에서 인수의 값을 변경하면 원래의 변수 값이 변경된다.
ParamArray	Optional. Used only as the last argument in arglist to indicate that the final argument is an Optional array of Variant elements. The ParamArray keyword allows you to provide an arbitrary number of arguments. It may not be used with ByVal, ByRef, or Optional.
인수명	Required. Name of the variable representing the argument; follows standard variable naming conventions.
type	Optional. Data type of the argument passed to the procedure; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported) Date, String (variable length only), Object, Variant, or a specific object type. If the parameter is not Optional, a user-defined type may also be specified.
defaultvalue	Optional. Any constant or constant expression. Valid for Optional parameters only. If the type is an Object, an explicit default value can only be Nothing.

■ 사용자 정의 함수 실행하기

1. 다른 프로시저에서 호출하기

   - VBA 내장함수 호출과 동일

     Total = TestFunction(MyArray)

   - Application.Run 메소드 이용

     Total = Application.Run("TestFunction", "MyArray")

 

2. 워크시트의 수식에서 사용하기

      : 기본적으로 현재 파일에 있는 프로시저는 바로 사용 가능하나,

        다른 파일에 있는 프로시저는 파일명으로 위치 지정 필요

   ※ 다른 파일에 있는 프로 시저 사용

     1) = 파일명 ! 프로시저명 ( 인수 )    ' 빈칸없는 파일명

        = ' 파일명 ' ! 프로시저명 ( 인수 )    ' 빈칸있는 파일명

     2) 참조를 만들어 사용  => 바로 사용

        = 프로시저명 ( 인수 ) 

     3) Add-in(추가기능) 만들어 사용=> 바로 사용

 

3. VBE 직접 실행 창에서 호출하기

 

4. 간단한 Test용 Sub Procesure를 만들고 그 안에서 Function Procesure 호출하여 Test

 

[Excel][VBA] Sub Procesure(서브 프로시저) 다루기

 

■ 사용자 정의 함수 사용 예

▶ 인수가 없는 경우   : 강제 재계산에 의해서만 수식 업데이트

Function User()     User = Application.UserName      End Function Sub ShowUser()     Debug.Print User         ' ( )없어도 가능 End Sub

[ 워크시트 수식으로 사용 ] =User()                           ' 반드시 ( ) 사용

 

▶ 인수가 있는 경우

1개 인수

2개 인수

Function Commission(Sales)     Const Tier1 = 0.08     Const Tier2 = 0.105     Const Tier3 = 0.12     Const Tier4 = 0.14 '   Calculates sales commissions     Select Case Sales        Case 0 To 9999.99: Commission = Sales * Tier1        Case 10000 To 19999.99: Commission = Sales * Tier2        Case 20000 To 39999.99: Commission = Sales * Tier3        Case Is >= 40000: Commission = Sales * Tier4     End Select End Function

Function Commission2(Sales, Years) As Single '    Calculates sales commissions based on '    years in service      Const Tier1 = 0.08      Const Tier2 = 0.105      Const Tier3 = 0.12      Const Tier4 = 0.14      Select Case Sales         Case 0 To 9999.99: Commission2 = Sales * Tier1         Case 10000 To 19999.99: Commission2 = Sales * Tier2         Case 20000 To 39999.99: Commission2 = Sales * Tier3         Case Is >= 40000: Commission2 = Sales * Tier4      End Select      Commission2 = Commission2 + _      (Commission2 * Years / 100) End Function

 

▶ 배열을 인수로 사용한 경우  : Variant 자료형이어야 한다.

Function SumArray(List) As Double     Dim Item As Variant     SumArray = 0     For Each Item In List         If WorksheetFunction.IsNumber(Item) Then _             SumArray = SumArray + Item     Next Item End Function

 

▶ Optional 인수가 있는 경우  : Variant형 Optional인수를 IsMissing함수로 인수 전달여부 확인

Function User(Optional UpperCase As Variant)     ' 인수가 True 이면 사용자이름을 대문자 전환 반환     ' 그외 컴퓨터에 저장된 값 반환     If IsMissing(UpperCase) Then UpperCase = False     User = Application.UserName     If UpperCase Then User = UCase(User)      End Function	=User() =User(True) =User(False) =User("King") =User(10)
Function Draw(Rng As Variant, Optional Recalc As Boolean = False) '    Chooses one cell at random from a range '    Make function volatile if Recalc is True      Application.Volatile Recalc '    Determine a random cell      Draw = Rng(Int((Rng.Count) * Rnd + 1)) End Function	Volatile 미실행 =draw(A1:A10) =draw(A1:A10, False) Volatile 실행 =draw(A1:A10,TRUE)

 

▶ 배열을 반환하는 사용자 정의 함수

    ※ 배열을 인수로 받는 사용자 정의 함수와 별개다.

- 워크시트 수식으로 이용시 [ 영역 선택 후 ] Ctrl + Shift + Enter 키를 사용

- 배열은 기본적으로 가로 방향(행단위) 배열이다.

- 워크시트 수식에서 반환배열의 방향 전환을 위하여 Transpose함수를 추가한다.

   ={Transpose(배열반환함수)}

 

Function MonthNames(Optional MIndex)     Dim AllNames As Variant     Dim MonthVal As Long          AllNames = Array("Jan", "Feb", "Mar", _         "Apr", "May", "Jun", "Jul", "Aug", _         "Sep", "Oct", "Nov", "Dec")     If IsMissing(MIndex) Then    ' 인수 미전달시 배열 전체 전달(가로방향 배열)         MonthNames = AllNames     Else         Select Case MIndex             Case Is >= 1      ' 인수가 1이상의 정수이면 인수값에 해당하는 값 반환 '            Determine month value (for example, 13=1)              MonthVal = ((MIndex - 1) Mod 12)              MonthNames = AllNames(MonthVal)           Case Is <= 0 ' 0 이하의인수전달시 transpose 적용 반환              MonthNames = Application.Transpose(AllNames)          End Select     End If End Function

 

▶ 오류값을 반환하는 사용자 정의 함수

함수명 = CVErr(에러상수)

 

▶ 인수의 개수가 정해지지 않은 사용자 정의 함수 ( ParamArray인수 )

   예 : 워크시트 함수 SUM

        = SUM(A!:A5, 12, 24*3) 

   - ParamArray 키워드는 프로시저의 마지막 인수에만 사용 가능

   - ParamArray 부분은 Variant형 배열로 선언된다.

   - Optional 키워드와 동시 사용 불가