Excel 2016에서 VBA 워크 시트 함수를 사용하는 방법

VBA는 적절한 내장 함수를 제공하지만 사용자가 필요한 것을 항상 정확하게 찾을 수는 없습니다. 다행히 VBA 프로 시저에서 대부분의 Excel 워크 시트 함수를 사용할 수도 있습니다. 사용할 수없는 워크 시트 함수는 동등한 VBA 함수가있는 워크 시트 함수입니다. 예를 들어 VBA에는 Rnd와 같은 기능이 있으므로 Excel의 RAND 함수 (임의의 숫자 생성)는 사용할 수 없습니다.

VBA는 Excel의 워크 시트 기능을 Application 개체에 포함 된 WorksheetFunction 개체를 통해 사용할 수있게합니다. 다음은 VBA 문에서 Excel의 SUM 함수를 사용하는 방법의 예입니다.

Total = Application. WorksheetFunction. SUM (범위 ("A1: A12"))

표현식의 응용 프로그램 부분 또는 WorksheetFunction 부분을 생략 할 수 있습니다. 두 경우 모두 VBA는 자신이하는 일을 파악합니다. 즉,이 세 표현식은 모두 똑같이 작동합니다.

합계 = 신청. WorksheetFunction. SUM (범위 ("A1: A12")) 합계 = WorksheetFunction. SUM (범위 ("A1: A12")) 합계 = 응용 프로그램. SUM (Range ("A1: A12"))

개인적으로 WorksheetFunction 부분을 사용하여 코드가 Excel 함수를 사용하고 있음을 명확히 밝힙니다.

워크 시트 함수 예제

여기에 VBA 표현식에서 워크 시트 함수를 사용하는 방법이 나와 있습니다.

범위에서 최대 값 찾기

다음은 VBA 절차에서 Excel의 MAX 워크 시트 함수를 사용하는 방법을 보여주는 예제입니다. 이 프로시 저는 활성 워크 시트의 A 열에 최대 값을 표시합니다.

VBA 코드에서 워크 시트 함수 사용.

Sub ShowMax () Dim TheMax As Double TheMax = WorksheetFunction. MAX (범위 ("A: A")) MsgBox TheMax End Sub

MIN 함수를 사용하여 범위에서 가장 작은 값을 가져올 수 있습니다. 예상대로 워크 시트의 다른 기능을 비슷한 방식으로 사용할 수 있습니다. 예를 들어, LARGE 함수를 사용하여 범위의 999 번째로 큰 값을 결정할 수 있습니다. 다음 표현식은이를 보여줍니다. SecondHighest = WorksheetFunction. LARGE (범위 ("A: A"), 2) LARGE 함수는 두 개의 인수를 사용합니다. 두 번째 인수는

파트 2 -이 경우 (두 번째로 큰 값)를 나타냅니다. 모기지 지불 계산 다음 예는 PMT 워크 시트 함수를 사용하여 모기지 지불액을 계산합니다. 세 변수는 인수로 Pmt 함수에 전달되는 데이터를 저장하는 데 사용됩니다.메시지 상자에 계산 된 지불이 표시됩니다.

Sub PmtCalc () Dim IntRate Double Dim LoanAmt As Long IntRate = 0 0625 / 12 Periods = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction. PMT (IntRate, Periods, -LoanAmt) End Sub

다음 문이 표시하는 것처럼 값을 함수 인수로 직접 삽입 할 수도 있습니다.

MsgBox WorksheetFunction. PMT (0. 0625 / 12, 360, -150000)

그러나 변수를 사용하여 매개 변수를 저장하면 필요한 경우 코드를 읽고 수정할 수 있습니다.

조회 함수 사용

다음 예제에서는 VBA의 InputBox 및 MsgBox 함수와 Excel의 VLOOKUP 함수를 사용합니다. 부품 번호를 묻는 메시지를 표시 한 다음 조회 테이블에서 가격을받습니다. 아래의 범위 A1: B13은 PriceList입니다.

PriceList라는 범위에는 부품 가격이 포함됩니다.

Sub GetPrice () Dim PartNum As Variant Double 가격으로 Double PartNum = InputBox ("부품 번호 입력") 시트 ("가격"). Price = WorksheetFunction을 활성화하십시오. End Sub

GetPrice 절차가 작동하는 방법은 다음과 같습니다.

VBA의 InputBox 함수는 사용자에게 부품 번호를 묻는 메시지를 표시합니다.

사용자가 입력 한 부품 번호가 PartNum 변수에 지정됩니다.

다음 문장은 가격 시트가 활성 시트가 아닌 경우를 대비하여 가격 워크 시트를 활성화합니다.
이 코드는 VLOOKUP 함수를 사용하여 테이블에서 부품 번호를 찾습니다.
이 문에서 사용하는 인수는 워크 시트 수식에서 함수와 함께 사용하는 인수와 같습니다. 이 명령문은 함수의 결과를 Price 변수에 지정합니다.
코드는 MsgBox 기능을 통해 파트의 가격을 표시합니다.
이 절차는 오류 처리가 없으며 존재하지 않는 부품 번호를 입력하면 비참하게 실패합니다. (시도해보십시오.) 이것이 실제 비즈니스에서 사용되는 실제 응용 프로그램 인 경우보다 정상적으로 오류를 처리하는 명령문을 추가해야합니다.
워크 시트 함수 입력

Excel 붙여 넣기 대화 상자를 사용하여 워크 시트 함수를 VBA 모듈에 삽입 할 수 없습니다. 대신, 그런 기능을 구식 방식으로 입력하십시오: 손으로. 그러나

는

함수 붙여 넣기 대화 상자를 사용하여 사용할 함수를 식별하고 인수에 대해 확인할 수 있습니다. 모든 워크 시트 기능의 드롭 다운 목록을 표시하는 VBE의 자동 목록 구성원 옵션을 이용할 수도 있습니다. 응용 프로그램을 입력하기 만하면됩니다. WorksheetFunction

다음에 마침표가옵니다. 그런 다음 사용할 수있는 기능 목록을 볼 수 있습니다. 이 기능이 작동하지 않으면 VBE의 도구 → 옵션 명령을 선택하고 편집기 탭을 클릭 한 다음 멤버 자동 목록 옆에 체크 표시를하십시오. VBA 코드에서 사용할 수있는 워크 시트 함수 목록 가져 오기. 워크 시트 함수 사용에 대한 추가 정보