Excel에서 사용자 지정 함수 만들기

매크로와 같은 사용자 지정 함수는 VBA(Visual Basic for Applications) 프로그래밍 언어를 사용합니다. 두 가지 중요한 방법으로 매크로와 다릅니다. 먼저 하위 프로시저 대신 함수 프로시저를 사용합니다. 즉, Sub 문 대신 Function 문으로 시작하고 End Sub 대신 End Function으로 끝납니다. 둘째, 작업을 수행하는 대신 계산을 수행합니다. 범위를 선택하고 서식을 지정하는 문과 같은 특정 종류의 문은 사용자 지정 함수에서 제외됩니다. 이 문서에서는 사용자 지정 함수를 만들고 사용하는 방법을 알아봅니다. 함수 및 매크로를 만들려면 Excel과 별도로 새 창에서 열리는 VBE(Visual Basic 편집기)를 사용합니다.

주문이 100대 이상인 경우 회사에서 제품 판매에 대해 10%의 수량 할인을 제공한다고 가정해 보겠습니다. 다음 단락에서는 이 할인을 계산하는 함수를 보여 줍니다.

아래 예제에서는 각 항목, 수량, 가격, 할인(있는 경우) 및 결과 확장 가격을 나열하는 주문 양식을 보여줍니다.

이 통합 문서에서 사용자 지정 DISCOUNT 함수를 만들려면 다음 단계를 수행합니다.

1. Alt+F11을 눌러 Visual Basic 편집기 열고(Mac에서 FN+ALT+F11을 누름) > 모듈삽입을 클릭합니다. Visual Basic 편집기 오른쪽에 새 모듈 창이 나타납니다.

2. 다음 코드를 복사하여 새 모듈에 붙여넣습니다.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

이제 새 DISCOUNT 함수를 사용할 준비가 되었습니다. Visual Basic 편집기 닫고 G7 셀을 선택한 다음 다음을 입력합니다.

=DISCOUNT(D7,E7)

Excel은 단위당 $47.50에서 200단원에 대해 10% 할인을 계산하고 $950.00를 반환합니다.

VBA 코드의 첫 번째 줄인 Function DISCOUNT(quantity, price)에서 DISCOUNT 함수에는 수량 과 가격이라는 두 개의 인수가 필요하다는 것을 알 수 있습니다. 워크시트 셀에서 함수를 호출하는 경우 이러한 두 인수를 포함해야 합니다. =DISCOUNT(D7,E7) 수식에서 D7은 수량 인수이고 E7은 price 인수입니다. 이제 DISCOUNT 수식을 G8:G13에 복사하여 아래와 같은 결과를 얻을 수 있습니다.

Excel에서 이 함수 프로시저를 해석하는 방법을 살펴보겠습니다. Enter 키를 누르면 Excel에서 현재 통합 문서에서 DISCOUNT라는 이름을 찾고 VBA 모듈의 사용자 지정 함수임을 찾습니다. 괄호, 수량 및 가격으로 묶인 인수 이름은 할인 계산의 기반이 되는 값의 자리 표시자입니다.

다음 코드 블록의 If 문은 수량 인수를 검사하고 판매된 항목 수가 100보다 크거나 같은지 여부를 결정합니다.

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

판매된 항목 수가 100보다 크거나 같은 경우 VBA는 다음 문을 실행하여 수량 값을 가격 값으로 곱한 다음 결과를 0.1로 곱합니다.

Discount = quantity * price * 0.1

결과는 할인 변수로 저장됩니다. 값을 변수에 저장하는 VBA 문을 대입 문이라고 합니다. 이 문은 등호의 오른쪽에 있는 식을 평가하고 결과를 왼쪽의 변수 이름에 할당하기 때문입니다. Discount 변수는 함수 프로시저와 이름이 같기 때문에 변수에 저장된 값은 DISCOUNT 함수를 호출한 워크시트 수식으로 반환됩니다.

수량이 100보다 작은 경우 VBA는 다음 문을 실행합니다.

Discount = 0

마지막으로 다음 문은 Discount 변수에 할당된 값을 10진수 두 자리로 반올림합니다.

Discount = Application.Round(Discount, 2)

VBA에는 ROUND 함수가 없지만 Excel은 이 함수를 사용합니다. 따라서 이 문에서 ROUND를 사용하려면 Application 개체(Excel)에서 Round 메서드(함수)를 찾도록 VBA에 지시합니다. 이렇게 하려면 Round이라는 단어 앞에 Application 이라는 단어를 추가합니다. VBA 모듈에서 Excel 함수에 액세스해야 할 때마다 이 구문을 사용합니다.

사용자 지정 함수는 Function 문으로 시작하고 End Function 문으로 끝나야 합니다. 함수 이름 외에도 Function 문은 일반적으로 하나 이상의 인수를 지정합니다. 그러나 인수 없이 함수를 만들 수 있습니다. Excel에는 인수를 사용하지 않는 몇 가지 기본 제공 함수(예: RAND 및 NOW)가 포함되어 있습니다.

Function 문 다음에 함수 프로시저에는 함수에 전달된 인수를 사용하여 결정을 내리고 계산을 수행하는 하나 이상의 VBA 문이 포함됩니다. 마지막으로 함수 프로시저의 어딘가에 함수와 이름이 같은 변수에 값을 할당하는 문을 포함해야 합니다. 이 값은 함수를 호출하는 수식으로 반환됩니다.

사용자 지정 함수에서 사용할 수 있는 VBA 키워드 수는 매크로에서 사용할 수 있는 수보다 작습니다. 사용자 지정 함수는 워크시트의 수식 또는 다른 VBA 매크로 또는 함수에 사용되는 식에 값을 반환하는 것 외에는 아무 작업도 수행할 수 없습니다. 예를 들어 사용자 지정 함수는 창 크기를 조정하거나 셀에서 수식을 편집하거나 셀의 텍스트에 대한 글꼴, 색 또는 패턴 옵션을 변경할 수 없습니다. 함수 프로시저에 이러한 종류의 "action" 코드를 포함하면 함수는 #VALUE 반환합니다. 오류를 반환합니다.

함수 프로시저가 수행할 수 있는 작업 중 하나는 계산 수행과는 별개로 대화 상자를 표시하는 것입니다. 사용자 지정 함수에서 InputBox 문을 함수를 실행하는 사용자의 입력을 가져오는 수단으로 사용할 수 있습니다. MsgBox 문을 사용자에게 정보를 전달하는 수단으로 사용할 수 있습니다. 사용자 지정 대화 상자 또는 UserForms를 사용할 수도 있지만 이 소개의 scope 이외의 주제입니다.

간단한 매크로와 사용자 지정 함수도 읽기 어려울 수 있습니다. 설명 텍스트를 주석 형식으로 입력하여 쉽게 이해할 수 있습니다. 설명 텍스트 앞에 아포스트로피가 있는 주석을 추가합니다. 예를 들어 다음 예제에서는 주석이 있는 DISCOUNT 함수를 보여 줍니다. 이와 같은 주석을 추가하면 시간이 지남에 따라 사용자 또는 다른 사용자가 VBA 코드를 더 쉽게 유지할 수 있습니다. 나중에 코드를 변경해야 하는 경우 원래의 작업을 더 쉽게 이해할 수 있습니다.

아포스트로피는 Excel에 동일한 줄의 오른쪽에 있는 모든 항목을 무시하도록 지시하므로 줄 자체 또는 VBA 코드가 포함된 줄의 오른쪽에 메모를 만들 수 있습니다. 전반적인 목적을 설명하는 주석으로 비교적 긴 코드 블록을 시작한 다음 인라인 주석을 사용하여 개별 문을 문서화할 수 있습니다.

매크로 및 사용자 지정 함수를 문서화하는 또 다른 방법은 설명이 포함된 이름을 지정하는 것입니다. 예를 들어 매크로 레이블의 이름을 지정하는 대신 MonthLabels 로 이름을 지정하여 매크로가 제공하는 용도를 보다 구체적으로 설명할 수 있습니다. 매크로 및 사용자 지정 함수에 설명이 포함된 이름을 사용하는 것은 많은 프로시저를 만든 경우 특히 유사하지만 동일하지 않은 프로시저를 만드는 경우에 특히 유용합니다.

매크로 및 사용자 지정 함수를 문서화하는 방법은 개인 기본 설정의 문제입니다. 중요한 것은 설명서의 몇 가지 방법을 채택하고 일관되게 사용하는 것입니다.

사용자 지정 함수를 사용하려면 함수를 만든 모듈이 포함된 통합 문서를 열어야 합니다. 해당 통합 문서가 열려 있지 않으면 #NAME? 함수를 사용하려고 할 때 오류가 발생했습니다. 다른 통합 문서에서 함수를 참조하는 경우 함수 이름이 함수가 있는 통합 문서의 이름으로 앞에 와야 합니다. 예를 들어 Personal.xlsb라는 통합 문서에서 DISCOUNT라는 함수를 만들고 다른 통합 문서에서 해당 함수를 호출하는 경우 단순히 =discount()가 아니라 =personal.xlsb!discount()를 입력해야 합니다.

함수 삽입 대화 상자에서 사용자 지정 함수를 선택하여 키 입력(및 가능한 입력 오류)을 직접 저장할 수 있습니다. 사용자 지정 함수는 사용자 정의 범주에 표시됩니다.

사용자 지정 함수를 항상 사용할 수 있도록 하는 더 쉬운 방법은 별도의 통합 문서에 저장한 다음 해당 통합 문서를 추가 기능으로 저장하는 것입니다. 그런 다음 Excel을 실행할 때마다 추가 기능을 사용할 수 있도록 할 수 있습니다. 이 작업을 수행하는 방법은 다음과 같습니다.

이러한 단계를 수행한 후에는 Excel을 실행할 때마다 사용자 지정 함수를 사용할 수 있습니다. 함수 라이브러리에 추가하려면 Visual Basic 편집기 반환합니다. VBAProject 제목 아래의 Visual Basic 편집기 Project Explorer 보면 추가 기능 파일의 이름을 따서 명명된 모듈이 표시됩니다. 추가 기능에는 .xlam 확장이 있습니다.

Project Explorer 해당 모듈을 두 번 클릭하면 Visual Basic 편집기 함수 코드가 표시됩니다. 새 함수를 추가하려면 코드 창에서 마지막 함수를 종료하는 End Function 문 뒤에 삽입 지점을 배치하고 입력을 시작합니다. 이러한 방식으로 필요한 만큼 함수를 만들 수 있으며 함수 삽입 대화 상자의 사용자 정의 범주에서 항상 사용할 수 있습니다.

이 콘텐츠는 원래 자신의 책 Microsoft Office Excel 2007 인사이드 아웃의 일환으로 마크 닷지와 크레이그 스틴슨에 의해 작성되었습니다. 이후 최신 버전의 Excel에도 적용되도록 업데이트되었습니다.