사용자 지정 함수를 Excel

매크로와 같은 사용자 지정 함수는 VBA(Visual Basic for Applications) 프로그래밍 언어를 사용하게 됩니다. 두 가지 중요한 방법으로 매크로와 다릅니다. 먼저 하위 프로시저 대신 Function프로시저를 사용합니다. 즉, 하위 문 대신 Function 문으로 시작하고 End Sub 대신 End Function으로끝을 습니다. 둘째, 작업을 수행하는 대신 계산을 수행합니다. 범위를 선택하고 서식을 지정하는 문과 같은 특정 종류의 문은 사용자 지정 함수에서 제외됩니다. 이 문서에서는 사용자 지정 함수를 만들고 사용하는 방법을 알아보고 있습니다. 함수 및 매크로를 만들 때 V Excel BE(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(수량, 가격)에서 DISCOUNT 함수에는 두 인수, 수량 및 가격이 필요합니다. 워크시트 셀에서 함수를 호출할 때 이러한 두 인수를 포함해야 합니다. 수식 =DISCOUNT(D7,E7)에서 D7은 수량 인수로, E7은 가격 인수입니다. 이제 할인 수식을 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 함수라는 워크시트 수식으로 반환됩니다.

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

VBA에는 ROUND 함수가 없지만, Excel 않습니다. 따라서 이 문에서 ROUND을 사용하기 위해 VBA에게 애플리케이션 개체(함수)에서 라운드 메서드(함수)를 Excel. 라운드라는 단어 앞에 Application이라는 단어를 추가하여 이 작업을 실행합니다. VBA 모듈에서 Excel 액세스해야 할 때마다 이 구문을 사용합니다.

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

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

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

함수 프로시저가 수행할 수 있는 한 가지 작업(계산 수행 제외)은 대화 상자를 표시합니다. 사용자 지정 함수에서 InputBox 문을 함수를 실행하는 사용자로부터 입력을 입력하는 수단으로 사용할 수 있습니다. 사용자에게 정보를 전달하는 수단으로 MsgBox 문을 사용할 수 있습니다. 사용자 지정 대화 상자 또는 UserForms를사용할 수도 있지만 이 소개의 범위를 벗어날 수 있습니다.

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

apostrophe는 Excel 줄의 오른쪽에 있는 모든 것을 무시할 것을 경고합니다. 따라서 직접 또는 VBA 코드를 포함하는 줄의 오른쪽에 주석을 만들 수 있습니다. 전체 목적을 설명하는 주석이 있는 비교적 긴 코드 블록을 시작한 다음 인라인 주석을 사용하여 개별 문을 문서화할 수 있습니다.

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

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

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

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

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

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

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

이 콘텐츠는 원래 Mark Dodge 및 Craig Stinson이 2007 Inside Out 2007의책의 일부로 Microsoft Office Excel 작성했습니다. 이후 최신 버전의 버전도 적용될 수 Excel 업데이트되었습니다.