vba - 使用 VBA 将数字转换为单词
问题描述
我有一列数字。在下一栏中,我想要数字的文本/单词转换。
示例: 123.561
将转换为One hundred twenty three point five six one
.
我不想转换为货币,只是将数字转换为文本,小数位数不限。
我怎样才能做到这一点?
解决方案
编辑:我已将以下程序调整为非货币、无限小数位。
编辑 2通过(1)和(2)中的两个更改考虑国际化,以使代码与其他小数分隔符(例如中欧的冒号)一起工作' -见评论 Function SpellNumber
Function fractionWords
例子:
MsgBox SpellNumber(2123.4575)
...返回:
Two Thousand One Hundred Twenty Three point Four Five Seven Five
将以下代码粘贴到新模块中:
Option Explicit
Function SpellNumber(ByVal numIn)
Dim LSide, RSide, Temp, DecPlace, Count, oNum
oNum = numIn
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
numIn = Trim(Str(numIn)) 'String representation of amount
' Edit 2.(0)/Internationalisation
' Don't change point sign here as the above assignment preserves the point!
DecPlace = InStr(numIn, ".") 'Pos of dec place 0 if none
If DecPlace > 0 Then 'Convert Right & set numIn
RSide = GetTens(Left(Mid(numIn, DecPlace + 1) & "00", 2))
numIn = Trim(Left(numIn, DecPlace - 1))
End If
RSide = numIn
Count = 1
Do While numIn <> ""
Temp = GetHundreds(Right(numIn, 3))
If Temp <> "" Then LSide = Temp & Place(Count) & LSide
If Len(numIn) > 3 Then
numIn = Left(numIn, Len(numIn) - 3)
Else
numIn = ""
End If
Count = Count + 1
Loop
SpellNumber = LSide
If InStr(oNum, Application.DecimalSeparator) > 0 Then ' << Edit 2.(1)
SpellNumber = SpellNumber & " point " & fractionWords(oNum)
End If
End Function
Function GetHundreds(ByVal numIn) 'Converts a number from 100-999 into text
Dim w As String
If Val(numIn) = 0 Then Exit Function
numIn = Right("000" & numIn, 3)
If Mid(numIn, 1, 1) <> "0" Then 'Convert hundreds place
w = GetDigit(Mid(numIn, 1, 1)) & " Hundred "
End If
If Mid(numIn, 2, 1) <> "0" Then 'Convert tens and ones place
w = w & GetTens(Mid(numIn, 2))
Else
w = w & GetDigit(Mid(numIn, 3))
End If
GetHundreds = w
End Function
Function GetTens(TensText) 'Converts a number from 10 to 99 into text
Dim w As String
w = "" 'Null out the temporary function value
If Val(Left(TensText, 1)) = 1 Then 'If value between 10-19
Select Case Val(TensText)
Case 10: w = "Ten"
Case 11: w = "Eleven"
Case 12: w = "Twelve"
Case 13: w = "Thirteen"
Case 14: w = "Fourteen"
Case 15: w = "Fifteen"
Case 16: w = "Sixteen"
Case 17: w = "Seventeen"
Case 18: w = "Eighteen"
Case 19: w = "Nineteen"
Case Else
End Select
Else 'If value between 20-99..
Select Case Val(Left(TensText, 1))
Case 2: w = "Twenty "
Case 3: w = "Thirty "
Case 4: w = "Forty "
Case 5: w = "Fifty "
Case 6: w = "Sixty "
Case 7: w = "Seventy "
Case 8: w = "Eighty "
Case 9: w = "Ninety "
Case Else
End Select
w = w & GetDigit _
(Right(TensText, 1)) 'Retrieve ones place
End If
GetTens = w
End Function
Function GetDigit(Digit) 'Converts a number from 1 to 9 into text
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
Function fractionWords(n) As String
Dim fraction As String, x As Long
fraction = Split(n, Application.DecimalSeparator)(1) ' << Edit 2.(2)
For x = 1 To Len(fraction)
If fractionWords <> "" Then fractionWords = fractionWords & " "
fractionWords = fractionWords & GetDigit(Mid(fraction, x, 1))
Next x
End Function
(改编自:微软)
网上还有其他几个例子。如果您正在搜索“将数字转换为文本” ,您可能已经找到了他们,因为这意味着更改数据类型。更好的搜索词是“ vba convert numbers to words ”。
推荐阅读
- linux - 在 GDB 中获取线程的堆栈区域
- graphdb - 我们如何在 GDB 工作台中对存储库进行排序?
- firebase - How can I get the dowload url from images that are resized with firestorage image-resize extension?
- c - 当 arg 不同时,为什么这段代码的结果是一样的?
- javascript - Uncaught SyntaxError: Invalid left-hand side in assignment - 解决方案?
- reactjs - Material-ui MuiThemeProvider 不适用
- python - ValueError:无法将操作数与形状一起广播到矢量化函数
- node.js - 无法安装 mongodb-client-encryption(libmongocrypt 的 Node.js 包装器)
- java - 如何从 Stage IntelliJ 自动调整 Swing 和 JavaFX 应用程序组件的大小
- vue.js - 在类星体输入中仅允许具有最多一位小数的数字