標題: [分享] 金額數字轉換英文寫法(VBA自訂函數) [打印本頁]

---

作者: 准提部林    時間: 2020-3-21 12:13     標題: 金額數字轉換英文寫法(VBA自訂函數)

金額數字轉換英文寫法．VBA自訂函數  

Function USNumber(ByVal MyNumber, QType) As String
Dim i%, j%, TR, StrCT$, StrDR$, TT$, TU$, Num
If Val(MyNumber) <= 0 Then Exit Function
TR = Array("", "", " Thousand", " Million", " Billion", " Trillion")
'-小數部份---------------------------
Num = Split(Format(MyNumber, "0.00"), ".")
TT = Get999(Num(1))
If TT <> "" Then StrCT = "Cents " & TT
'-整數部份---------------------------
Num = Split(Format(Num(0), "#,##0"), ",")
For i = UBound(Num) To 0 Step -1
    TT = Get999(Num(i))
    j = j + 1:  TU = Trim(TT & TR(j) & " " & TU)
Next i
StrDR = Trim(TU & " " & IIf(QType = 1, "Dollars", ""))
'---------------------------------
USNumber = StrDR & IIf(StrDR = "" Or StrCT = "", "", " And ") & StrCT & " Only"
End Function

'=====================================
Function Get999(TTNum) As String
Dim TN, TY, QQ%, GStr1$, GStr2$
TN = Split("-One-Two-Three-Four-Five-Six-Seven-Eight-Nine-Ten-Eleven-Twelve-Thirteen-Fourteen-Fifteen-Sixteen-Seventeen-Eighteen-Nineteen", "-")
TY = Split("--Twenty-Thirty-Forty-Fifty-Sixty-Seventy-Eighty-Ninety", "-")
'-百位數---------------------------
GStr1 = TN(Int(TTNum / 100)) & " Hundred"
If GStr1 = " Hundred" Then GStr1 = ""
'-十/個位數---------------------------
QQ = TTNum Mod 100
GStr2 = Replace(Trim(TY(Int(Right(QQ, 2) / 10)) & " " & TN(QQ Mod 10)), " ", "-")
If QQ < 20 Then GStr2 = TN(QQ)
Get999 = Trim(GStr1 & " " & GStr2)
End Function

'=================================
參考檔:
[attach]31807[/attach]

程式碼不難, 若有不同格式需求, 請自行修改~~

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)