自定义SpellNumber函数货币辅币单位显示错误修复求助

云上老周

2026-7-20

自定义VBA函数SpellNumber辅币名称匹配问题解决

我开发了自定义VBA函数SpellNumber，通过VLOOKUP匹配BEST工作表H19单元格的货币类型，从DataList工作表B2:E23区域获取第三列主币名称与第四列辅币名称，实现金额转英文大写。例如选择AED时，金额2000.38应显示为"Two Thousand Dirham and Thirty Eight Fils only"，但目前函数仅能正确显示主币名称，辅币部分始终固定显示“Cents”，无法匹配对应辅币。

原代码

Option Explicit

'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

' String representation of amount.
MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")

' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
    Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
    MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber <> ""
    Temp = GetHundreds(Right(MyNumber, 3))
    If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
    If Len(MyNumber) > 3 Then
        MyNumber = Left(MyNumber, Len(MyNumber) - 3)
    Else
        MyNumber = ""
    End If
    Count = Count + 1
Loop

Select Case CheckSymbol()
    Case ""
        Dollars = "No Dollars"
    Case Else
        Dollars = Dollars & CheckSymbol()
End Select

Select Case Cents
    Case ""
        Cents = " and No Cents Only"
    Case "One"
        Cents = " and One Cent Only"
    Case Else
        Cents = " and " & Cents & " Cents Only"
End Select

SpellNumber = Dollars & Cents
End Function

' Check symbol and get corresponding value
Function CheckSymbol() As String
Dim symbol As String
symbol = Sheets("BEST").Range("H19").Value

Select Case symbol
    Case "US$", "AED", "GBP£", "EUR€", "SAR", "AUD$", "BRLR$", "CAD$", "CNY¥", "HKD$", "INR?", "JPY¥", "MXN$", "NZD$", "NOK-kr", "RUB?", "SGD$", "ZAR-R", "KRW-?", "SEK-kr", "CHF", "TRY?"
        CheckSymbol = Application.VLookup(symbol, Sheets("DataList").Range("B2:E23"), 3, False)
    Case Else
        CheckSymbol = " US Dollars"
End Select
End Function

' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)

' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
    Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If

' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
    Result = Result & GetTens(Mid(MyNumber, 2))
Else
    Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function


' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
    Select Case Val(TensText)
        Case 10: Result = "Ten"
        Case 11: Result = "Eleven"
        Case 12: Result = "Twelve"
        Case 13: Result = "Thirteen"
        Case 14: Result = "Fourteen"
        Case 15: Result = "Fifteen"
        Case 16: Result = "Sixteen"
        Case 17: Result = "Seventeen"
        Case 18: Result = "Eighteen"
        Case 19: Result = "Nineteen"
        Case Else
    End Select
Else ' If value between 20-99...
    Select Case Val(Left(TensText, 1))
        Case 2: Result = "Twenty "
        Case 3: Result = "Thirty "
        Case 4: Result = "Forty "
        Case 5: Result = "Fifty "
        Case 6: Result = "Sixty "
        Case 7: Result = "Seventy "
        Case 8: Result = "Eighty "
        Case 9: Result = "Ninety "
        Case Else
    End Select
    Result = Result & GetDigit(Right(TensText, 1)) ' Retrieve ones place.
End If

GetTens = Result
End Function


' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
    Case 1: GetDigit = "One"
    Case 2: GetDigit = "Two"
    Case 3: GetDigit = "Three"
    Case 4: GetDigit = "Four"
    Case 5: GetDigit = "Five"
    Case 6: GetDigit = "Six"
    Case 7: GetDigit = "Seven"
    Case 8: GetDigit = "Eight"
    Case 9: GetDigit = "Nine"
    Case Else: GetDigit = ""
End Select
End Function

问题分析

辅币部分的文本是硬编码的“Cents”/“Cent”，没有从DataList的第四列（E列）获取对应辅币名称
CheckSymbol函数仅返回主币名称（第三列，D列），未处理辅币信息的获取与传递

修改方案

新增CheckSubSymbol函数，通过VLOOKUP从DataList的第四列获取对应辅币名称
修改SpellNumber函数中处理辅币的逻辑，将硬编码的“Cents”替换为动态获取的辅币名称，同时处理单复数形式

修改后的完整代码

Option Explicit

'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
Dim mainCurrency As String
Dim subCurrency As String
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

' String representation of amount.
MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")

' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
    Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
    MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber <> ""
    Temp = GetHundreds(Right(MyNumber, 3))
    If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
    If Len(MyNumber) > 3 Then
        MyNumber = Left(MyNumber, Len(MyNumber) - 3)
    Else
        MyNumber = ""
    End If
    Count = Count + 1
Loop

' 获取主币和辅币名称
mainCurrency = CheckSymbol()
subCurrency = CheckSubSymbol()

Select Case mainCurrency
    Case ""
        Dollars = "No " & subCurrency
    Case Else
        Dollars = Dollars & mainCurrency
End Select

Select Case Cents
    Case ""
        Cents = " and No " & subCurrency & " Only"
    Case "One"
        ' 处理单数形式，多数辅币去掉末尾s即可，特殊情况可单独添加判断
        Cents = " and One " & Left(subCurrency, Len(subCurrency) - 1) & " Only"
    Case Else
        Cents = " and " & Cents & " " & subCurrency & " Only"
End Select

SpellNumber = Dollars & Cents
End Function

' Check symbol and get main currency name
Function CheckSymbol() As String
Dim symbol As String
symbol = Sheets("BEST").Range("H19").Value

Select Case symbol
    Case "US$", "AED", "GBP£", "EUR€", "SAR", "AUD$", "BRLR$", "CAD$", "CNY¥", "HKD$", "INR?", "JPY¥", "MXN$", "NZD$", "NOK-kr", "RUB?", "SGD$", "ZAR-R", "KRW-?", "SEK-kr", "CHF", "TRY?"
        On Error Resume Next ' 防止VLOOKUP找不到值报错
        CheckSymbol = Application.VLookup(symbol, Sheets("DataList").Range("B2:E23"), 3, False)
        On Error GoTo 0
    Case Else
        CheckSymbol = " US Dollars"
End Select
End Function

' Check symbol and get sub currency name
Function CheckSubSymbol() As String
Dim symbol As String
symbol = Sheets("BEST").Range("H19").Value

Select Case symbol
    Case "US$", "AED", "GBP£", "EUR€", "SAR", "AUD$", "BRLR$", "CAD$", "CNY¥", "HKD$", "INR?", "JPY¥", "MXN$", "NZD$", "NOK-kr", "RUB?", "SGD$", "ZAR-R", "KRW-?", "SEK-kr", "CHF", "TRY?"
        On Error Resume Next
        CheckSubSymbol = Application.VLookup(symbol, Sheets("DataList").Range("B2:E23"), 4, False)
        On Error GoTo 0
    Case Else
        CheckSubSymbol = " Cents"
End Select
End Function

' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)

' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
    Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If

' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
    Result = Result & GetTens(Mid(MyNumber, 2))
Else
    Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function


' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
    Select Case Val(TensText)
        Case 10: Result = "Ten"
        Case 11: Result = "Eleven"
        Case 12: Result = "Twelve"
        Case 13: Result = "Thirteen"
        Case 14: Result = "Fourteen"
        Case 15: Result = "Fifteen"
        Case 16: Result = "Sixteen"
        Case 17: Result = "Seventeen"
        Case 18: Result = "Eighteen"
        Case 19: Result = "Nineteen"
        Case Else
    End Select
Else ' If value between 20-99...
    Select Case Val(Left(TensText, 1))
        Case 2: Result = "Twenty "
        Case 3: Result = "Thirty "
        Case 4: Result = "Forty "
        Case 5: Result = "Fifty "
        Case 6: Result = "Sixty "
        Case 7: Result = "Seventy "
        Case 8: Result = "Eighty "
        Case 9: Result = "Ninety "
        Case Else
    End Select
    Result = Result & GetDigit(Right(TensText, 1)) ' Retrieve ones place.
End If

GetTens = Result
End Function


' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
    Case 1: GetDigit = "One"
    Case 2: GetDigit = "Two"
    Case 3: GetDigit = "Three"
    Case 4: GetDigit = "Four"
    Case 5: GetDigit = "Five"
    Case 6: GetDigit = "Six"
    Case 7: GetDigit = "Seven"
    Case 8: GetDigit = "Eight"
    Case 9: GetDigit = "Nine"
    Case Else: GetDigit = ""
End Select
End Function

说明

新增的CheckSubSymbol函数负责从DataList的第四列获取辅币名称
处理辅币单数时，通过Left(subCurrency, Len(subCurrency)-1)去掉末尾的s，适用于大多数英文辅币名称；若有辅币单复数同形或特殊规则，可单独添加判断逻辑
添加On Error Resume Next避免VLOOKUP找不到对应货币时触发运行错误

内容的提问来源于stack exchange，提问作者soldier2gud4me

自定义SpellNumber函数货币辅币单位显示错误修复求助

原代码 #

问题分析 #

修改方案 #

修改后的完整代码 #

说明 #

原代码

问题分析

修改方案

修改后的完整代码

说明