【實用】支票銀碼寫法 : 在 Excel 將數字轉換中文大寫

【實用】支票銀碼寫法 : 在 Excel 將數字轉換中文大寫

http://www.borion.net/excel.htm

先執行Excel，之後選「工具」「巨集」→「Visual Basic編輯器」，

這時會進入Visual Basic的編輯環境，選功能表的「插入」→「模組」，

把「自動轉換中文大寫VBA程式碼」的內容一一輸入到

右方視窗的空白處，完成後再儲存。

然後, 關了 Visual Basic的編輯視窗, 就會回到Excel畫面,

在 A1 格輸入 ==> 12356.5

在 A2 格輸入公式 ==> =exchange(A1)

這樣 A2 格就會自動轉換出「壹萬貳仟參佰伍拾陸元伍角正」




但是分位, 我弄不到出現 " 正 "

=exchange(12356.56)：會轉換出「壹萬貳仟參佰伍拾陸元伍角陸分」



*************************************************************

「自動轉換中文大寫VBA程式碼」
*************************************************************

Function exchange(ByVal Myinput)
Dim Temp, TempA, MyinputA, MyinputB, MyinputC
Dim Place As String
Dim J As Integer
Place = "分角元拾佰仟萬拾佰仟億拾佰仟萬"
integer1 = "壹貳參肆伍陸柒捌玖"
integer2 = "正零元零零零萬零零零億零零零萬"

digitvalue = ""
If Myinput < 0 Then digitvalue = "負"
Myinput = Int(Abs(Myinput) * 100 + 0.5)
If Myinput > 999999999999999# Then
exchange = "數字太大了！"
Exit Function
End If
If Myinput = 0 Then
exchange = "零元零分"
Exit Function
End If

MyinputA = Trim(Str(Myinput))
digitlength = Len(MyinputA)
For J = 1 To digitlength
MyinputB = Mid(MyinputA, J, 1) & MyinputB
Next
For J = 1 To digitlength
Temp = Val(Mid(MyinputB, J, 1))
If Temp = 0 Then
MyinputC = Mid(integer2, J, 1) & MyinputC
Else
MyinputC = Mid(integer1, Temp, 1) & Mid(Place, J, 1) & MyinputC
End If
Next

digitlength = Len(MyinputC)
For J = 1 To digitlength - 1
If Mid(MyinputC, J, 1) = "零" Then
Select Case Mid(MyinputC, J + 1, 1)
Case "零", "元", "萬", "億", "正":
MyinputC = Left(MyinputC, J - 1) & Mid(MyinputC, J + 1, 30)
J = J - 1
End Select
End If
Next

digitlength = Len(MyinputC)
For J = 1 To digitlength - 1
If Mid(MyinputC, J, 1) = "億" And Mid(MyinputC, J + 1, 1) = "萬" Then
MyinputC = Left(MyinputC, J) & Mid(MyinputC, J + 2, 30)
Exit For
End If
Next

exchange = digitvalue & Trim(MyinputC)

End Function