[엑셀/VBA] 사용자 정의 함수(Custom Function) 만들기

 

  이번 엑셀 VBA 포스팅에서는 사용자 정의 함수(Custom Function)을 만드는 방법에 대해 정리해보려고 한다. 먼저 아래 점수표를 예시로 평균이 80점 이상인 학생들에 대해 합격/불합격(PASS/FAIL)을 판정하는 함수를 구현해보려고 한다. 나중에 파이썬 xlwings와 엑셀을 연동할 때 내가 필요한 엑셀 함수를 VBA를 통해 구현하면 좀더 프로그램 속도 측면에서 효율적이지 않을까 싶다.

 

점수표

 

 

  일반적으로 합격/불합격을 판정하기 위해선 이미 엑셀에서 기본적으로 제공하고있는 AVERAGE 함수와 IF 함수를 사용하면 쉽게 구현할 수 있다. 아래와 같이 말이다.

 

 

평균, 합격여부 엑셀 기본 함수로 구현해보기

 

  이번 포스팅은 사용자 정의 함수를 연습하는 내용으로 위 합격여부 판단을 하는 함수를 사용자 정의 함수로 만들어보려고 한다. 

 

  먼저 기존 VBA 개발 창을 띄우기 위해 엑셀 파일에서 Alt+F11을 눌러보자. 이전에도 언급했던 내용지만 엑셀 VBA 코딩을 하기 위해선 파일 확장자를 .xlsm 파일로 만들어야 한다. 

 

  그리고 아래와 같이 모듈을 하나 생성하고 오른쪽 코드처럼 작성해보자.

 

모듈 생성 후, 함수 정의하기

 

 

  사용자 정의함수 형태는 위에 작성한 바와 같이 Fuction으로 정의해야 한다. 기존에 VBA 코드들은 Sub라는 명칭으로 시작했었던 것과는 차이가 있다. 

 

 

<사용자 정의 함수 구현 형태>

Function 함수명(함수입력변수1, 함수입력변수2 ....)

#함수구현

End Function

 

  -. 함수명 : 엑셀 셀에서 불러올 때 사용할 함수명이다.

  -. 함수입력변수 : 함수를 사용할 때 필요한 변수들이다.(셀영역 등)

  -. 함수구현 : 함수입력변수를 받아 실제 함수 내용을 구현하는 부분이다.

 

 

  그래서 이번에 내가 작성해 볼 사용자 정의 "함수는 합격 기준이 될 점수(정수)"와 "계산할 영역(Range)"을 입력받아 함수를 사용한 셀 영역에 PASS/FAIL을 바로 출력해주는 것이다. 즉, 위에서 AVERAGE와 IF의 기능을 묶어 사용자 정의 함수를 만드는 것이다. 구현한 코드는 아래와 같다.

 

 

<코드>

Function passfail(standard, rng)
    
    #설정 영역 rng의 평균값을 Value라는 변수에 저장
    Value = WorksheetFunction.Average(rng)
    
    #평균값 Value가 설정한 standard(기준)과 비교하여 PASS, FAIL 출력
    If Value >= standard Then
        passfail = "PASS"
    Else
        passfail = "FAIL"
    End If
       

End Function

 

 

<사용자 정의 함수 구현>

 

 

  passfail이라는 명칭으로 사용자 정의 함수를 구현하였더니 아래와 같이 셀 영역에서 passfail을 호출할 수 있다

 

 

 

  다음은 실제 실행 결과이다. 함수의 첫번째 요소는 standard=80 (80점 기준), 두번째 요소 rng는 계산할 영역을 설정하면 면 사용자 정의 함수 결과를 확인할 수 있다.

결과