VBA 마진율·할인가 UDF: 생략·0·빈 셀을 다른 입력으로 처리하기

가상 가격이 5000원일 때 할인율 칸을 비웠다는 이유만으로 10%를 적용하면 입력 누락이 정상 계산처럼 보일 수 있습니다. 이번 UDF는 두 번째 인수를 생략한 호출에만 기본 할인율을 적용합니다. 숫자 0은 무할인, 빈 셀은 입력 오류입니다. 이 세 경우를 구분하는 것이 단순 할인 산술보다 중요한 과제입니다.

회사 정산 기준을 주장하는 자료가 아니라 가상 입력으로 계약을 정하는 실습입니다. 기존 CompanyMargin·DiscountPrice의 입력 검사를 보존했습니다. 산술과 오류 우선순위는 Python으로 독립 검산했으며 실제 Excel VBA 실행은 하지 않았습니다.

마진율·할인가 UDF의 반환값을 먼저 정합니다

(15000-9000)/15000=0.4입니다. 이 예제의 수식 결과 0.4를 40%로 표시하려면 셀 표시 형식을 백분율로 바꾸며 결과에 다시 100을 곱하지 않습니다. 음수 매출·원가와 음수 price 입력값은 이 예제에서 허용하고 화폐 반올림은 하지 않습니다. 회계·반품·세금 처리 규칙까지 들어 있는 함수는 아닙니다.

UDF에서 빈 셀을 기본값으로 바꾸지 않는 이유

IsMissing은 Optional Variant 인수가 전달됐는지를 판별하는 함수입니다. 따라서 Optional ByVal rate As Variant를 쓰고 IsMissing(rate)가 참일 때만 0.1을 대입합니다. =DiscountPrice(5000,0)은 5000, 할인율 1은 0입니다. 입력 경계 시트의 빈 C11을 전달한 =DiscountPrice(B11,C11)은 #VALUE!입니다. 숫자처럼 보이는 문자열 "0.15"도 자동 변환하지 않습니다.

단일 셀 객체는 Value2를 읽고, 여러 셀 범위·배열·불리언·빈 값·문자열은 거부합니다. 이미 Excel 오류라면 그 오류를 전파합니다. CVErr는 지정한 오류 번호를 담은 Error 하위 형식의 Variant를 반환합니다. 그래서 반환형은 숫자로 고정하지 않고 Variant로 둡니다.

오류 검사 순서도 계약입니다. CompanyMargin은 매출의 자료형, 원가의 자료형, 매출 0 순서로 검사합니다. CompanyMargin(0,"bad")는 #DIV/0!가 아니라 #VALUE!여야 합니다. 왼쪽 인수에 #N/A가 있으면 오른쪽 인수보다 먼저 그 오류가 반환됩니다.

통합 문서에 넣을 전체 함수

practice-64.xlsx를 별도 XLSM으로 저장한 후 아래 코드를 표준 모듈에 넣습니다. ZIP의 ArticleExamples.bas를 가져와도 같습니다. 두 방법을 함께 사용해 함수 이름을 중복시키지 마세요. 워크시트 함수이므로 매크로 목록에서 버튼처럼 실행하는 Sub가 아닙니다.

첫 Attribute VB_Name 줄은 BAS 가져오기용 메타데이터입니다. BAS를 가져올 때는 그대로 두고, 코드 창에 직접 붙여넣을 때는 이 첫 줄만 제외합니다.

Attribute VB_Name = "ArticleExamples"
Option Explicit

Private Function ReadNumber(ByVal value As Variant) As Variant
    Dim raw As Variant
    Dim cell As Excel.Range
    On Error GoTo BadInput

    If IsObject(value) Then
        If TypeOf value Is Excel.Range Then
            Set cell = value
            If cell Is Nothing Then GoTo BadInput
            If cell.Areas.Count <> 1 Then GoTo BadInput
            If cell.Cells.CountLarge <> 1 Then GoTo BadInput
            raw = cell.Value2
        Else
            GoTo BadInput
        End If
    Else
        If IsArray(value) Then GoTo BadInput
        raw = value
    End If

    If IsError(raw) Then
        ReadNumber = raw
        Exit Function
    End If
    Select Case VarType(raw)
        Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal, 20
            ReadNumber = CDbl(raw)
            Exit Function
    End Select

BadInput:
    ReadNumber = CVErr(xlErrValue)
End Function

Public Function CompanyMargin(ByVal sales As Variant, ByVal cost As Variant) As Variant
    Dim s As Variant, c As Variant
    On Error GoTo ArithmeticError
    s = ReadNumber(sales)
    If IsError(s) Then
        CompanyMargin = s
        Exit Function
    End If
    c = ReadNumber(cost)
    If IsError(c) Then
        CompanyMargin = c
        Exit Function
    End If
    If CDbl(s) = 0 Then
        CompanyMargin = CVErr(xlErrDiv0)
        Exit Function
    End If
    CompanyMargin = (CDbl(s) - CDbl(c)) / CDbl(s)
    Exit Function

ArithmeticError:
    CompanyMargin = CVErr(xlErrNum)
End Function

Public Function DiscountPrice(ByVal price As Variant, Optional ByVal rate As Variant) As Variant
    Dim p As Variant, r As Variant
    On Error GoTo ArithmeticError
    p = ReadNumber(price)
    If IsError(p) Then
        DiscountPrice = p
        Exit Function
    End If
    If IsMissing(rate) Then
        r = 0.1
    Else
        r = ReadNumber(rate)
        If IsError(r) Then
            DiscountPrice = r
            Exit Function
        End If
    End If
    If CDbl(r) < 0 Or CDbl(r) > 1 Then
        DiscountPrice = CVErr(xlErrNum)
        Exit Function
    End If
    DiscountPrice = CDbl(p) * (1 - CDbl(r))
    Exit Function

ArithmeticError:
    DiscountPrice = CVErr(xlErrNum)
End Function

D열의 호출식은 문자열입니다

기본 예제와 입력 경계 시트는 B:C 입력, D 호출식 설명, E 예상값, F 실제 실행값으로 구분했습니다. XLSX에는 VBA가 없으므로 D열은 실행 수식이 아니라 =DiscountPrice(B6,C6) 같은 텍스트입니다. 같은 문자열을 F열 수식 입력줄에 직접 입력한 뒤 결과를 E와 대조합니다. =D6으로 참조하면 문자열만 돌아옵니다.

기본 예제 E6:E11의 예상값은 0.4, -0.2, #DIV/0!, 4500, 4250, #NUM!입니다. 숫자 예상값은 일반 산술 수식이고 오류는 예상 오류를 적은 문자열입니다. F열은 실제 실행 전이라 비웠습니다. 기본값을 수정할 때는 코드의 0.1과 기본 예제 B14의 기준값을 함께 바꾸세요.

UDF를 넣고 F열에서 확인하는 순서

1. practice-64.xlsx를 별도 XLSM 사본으로 저장하고 ArticleExamples.bas를 가져옵니다.

2. 기본 예제·입력 경계 D열의 텍스트 호출식을 같은 행 F열에 직접 수식으로 입력합니다. =D6처럼 참조하지 않습니다.

3. E열 기대값과 F열 실제값을 비교합니다. B13의 =NA() 오류는 의도된 입력입니다.

4. 생략·숫자 0·빈 셀·숫자 문자열을 구분합니다. 기본값 변경 시 코드와 기본 예제 B14를 함께 수정합니다.

5. 실제 Excel에서의 컴파일·실행·재계산은 아직 검증하지 않았습니다.

검증 결과: 값뿐 아니라 자료형을 봅니다

입력 경계의 10행은 할인율 0·1·음수, 빈 금액, 숫자 문자열 금액, 빈 할인율, 숫자 문자열 할인율, 기존 #N/A, 다중 셀 범위, TRUE를 각각 다룹니다. 첫 두 값은 5000과 0, 나머지는 정의한 오류입니다. B13의 =NA()만 의도적으로 실제 Excel 오류 입력을 생성하는 수식입니다. 다른 예상 오류 표시는 실행 실패가 아니라 기대값 문자열입니다.

독립 Python 검사는 이 경계를 읽어 참조 함수를 실행하고, XLSX D열에 UDF 수식이 잘못 들어 있지 않은지와 F열이 비어 있는지 확인합니다. 문자열 "5000"을 숫자 5000으로 변환한 뒤 검산하면 계약 검사가 아니므로 자료형도 검사합니다. 실제 Excel에서 결과가 다르면 F값과 해당 셀 자료형을 함께 기록하세요.

VBA UDF 오류를 숨기지 않고 복구하기

#NAME?가 나오면 UDF가 해당 XLSM의 VBA 표준 모듈에 있는지, 이름이 중복되지 않았는지, 매크로 실행 정책과 컴파일 오류를 확인합니다. 같은 함수를 두 번 붙였다면 사본에서 중복 모듈을 제거하고 한 정의만 남깁니다. #VALUE!가 숫자 문자열 때문이라면 원본 입력이 숫자가 맞는지 확인한 뒤 해당 셀만 실제 숫자로 다시 입력하고 같은 UDF를 다시 계산합니다. 빈 값은 임의로 0으로 대체하지 않습니다. #NUM!이면 할인율 범위나 산술 오류를, #DIV/0!이면 정상 숫자인 매출 0을 확인합니다.

함수의 한계

Python 검산은 Excel의 Variant·Range 객체나 VBA 컴파일러를 실행하지 않습니다. 대규모 시트의 재계산 시간, 업무용 숫자 변환 정책, 반올림 기준은 검증하지 않았습니다. UDF가 필요 없는 한두 셀 계산은 일반 수식으로도 충분하며, 여기서 얻는 이점은 입력과 오류 규칙을 함수 안에 모았다는 점입니다.

공식 자료

IsMissing function (Visual Basic for Applications)

CVErr function (Visual Basic for Applications)

출처 원문 수집일: S1 2026-09-24; S2 2026-09-24.

이어서 연습할 글

입력 검사와 변수·조건문을 한 작업에서 다시 읽으려면 VBA 기초 다섯 가지로 완료 행 추출하기를 이어서 연습할 수 있습니다.

작성자 소개

최지원

엑셀·VBA·파이썬·AI 업무 자동화의 사용 절차와 실습 자료를 정리합니다.

WP 게시물 작성자의 글 보기