有时候,说出数字是有益的,甚至是强制性的。例如,您可能希望将“ 1234”拼写为“ 1,234”。下面的宏NumberToWords就是这样做的。它很长,但是必须进行大量检查才能组合正确的字符串。集合中实际上有五个宏。 NumberToWords调用NumberToWords之外的四个字符进行实际转换。

NumberToWords将转换0到999,999之间的任何数字。要使用它,只需选择要转换其内容的一个或多个单元格,然后运行它。您应注意,单元格必须包含整数值,而不是包含整数值的公式。兼容单元格的实际内容从原始数字更改为该数字的文本表示。换句话说,这不是格式更改,而是这些单元格的值更改。

Sub NumberToWords()

Dim rngSrc As Range     Dim lMax As Long     Dim lCtr As Long     Dim bNCFlag As Boolean     Dim sTitle As String, sMsg As String     Dim vCVal As Variant     Dim lNumber As Long, sWords As String

Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)

lMax = rngSrc.Cells.Count

bNCFlag = False     For lCtr = 1 To lMax         vCVal = rngSrc.Cells(lCtr).Value         sWords = ""

If IsNumeric(vCVal) Then             If vCVal <> CLng(vCVal) Then                 bNCFlag = True             Else                 lNumber = CLng(vCVal)

Select Case lNumber                 Case 0                     sWords = "Zero"

Case 1 To 999999                     sWords = SetThousands(lNumber)

Case Else                     bNCFlag = True                 End Select             End If         Else             bNCFlag = True         End If         If sWords > "" Then             rngSrc.Cells(lCtr) = sWords         End If     Next lCtr

If bNCFlag Then         sTitle = "lNumberToWords Macro"

sMsg = "Not all cells converted. May not be whole number or may be too large."

MsgBox sMsg, vbExclamation, sTitle     End If End Sub
Private Function SetOnes(ByVal lNumber As Integer) As String Dim OnesArray(9) As String     OnesArray(1) = "One"

OnesArray(2) = "Two"

OnesArray(3) = "Three"

OnesArray(4) = "Four"

OnesArray(5) = "Five"

OnesArray(6) = "Six"

OnesArray(7) = "Seven"

OnesArray(8) = "Eight"

OnesArray(9) = "Nine"

SetOnes = OnesArray(lNumber)

End Function
Private Function SetTens(ByVal lNumber As Integer) As String Dim TensArray(9) As String     TensArray(1) = "Ten"

TensArray(2) = "Twenty"

TensArray(3) = "Thirty"

TensArray(4) = "Fourty"

TensArray(5) = "Fifty"

TensArray(6) = "Sixty"

TensArray(7) = "Seventy"

TensArray(8) = "Eighty"

TensArray(9) = "Ninety"

Dim TeensArray(9) As String     TeensArray(1) = "Eleven"

TeensArray(2) = "Twelve"

TeensArray(3) = "Thirteen"

TeensArray(4) = "Fourteen"

TeensArray(5) = "Fifteen"

TeensArray(6) = "Sixteen"

TeensArray(7) = "Seventeen"

TeensArray(8) = "Eighteen"

TeensArray(9) = "Nineteen"

Dim iTemp1 As Integer Dim iTemp2 As Integer Dim sTemp As String     iTemp1 = Int(lNumber / 10)

iTemp2 = lNumber Mod 10     sTemp = TensArray(iTemp1)

If (iTemp1 = 1 And iTemp2 > 0) Then         sTemp = TeensArray(iTemp2)

Else         If (iTemp1 > 1 And iTemp2 > 0) Then             sTemp = sTemp + " " + SetOnes(iTemp2)

End If     End If     SetTens = sTemp End Function
Private Function SetHundreds(ByVal lNumber As Integer) As String Dim iTemp1 As Integer Dim iTemp2 As Integer Dim sTemp As String     iTemp1 = Int(lNumber / 100)

iTemp2 = lNumber Mod 100     If iTemp1 > 0 Then sTemp = SetOnes(iTemp1) + " Hundred"

If iTemp2 > 0 Then         If sTemp > "" Then sTemp = sTemp + " "

If iTemp2 < 10 Then sTemp = sTemp + SetOnes(iTemp2)

If iTemp2 > 9 Then sTemp = sTemp + SetTens(iTemp2)

End If     SetHundreds = sTemp End Function
Private Function SetThousands(ByVal lNumber As Long) As String Dim iTemp1 As Integer Dim iTemp2 As Integer Dim sTemp As String     iTemp1 = Int(lNumber / 1000)

iTemp2 = lNumber Mod 1000     If iTemp1 > 0 Then sTemp = SetHundreds(iTemp1) + " Thousand"

If iTemp2 > 0 Then         If sTemp > "" Then sTemp = sTemp + " "

sTemp = sTemp + SetHundreds(iTemp2)

End If     SetThousands = sTemp End Function

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(8351)适用于Microsoft Excel 2007、2010和2013。您可以在以下版本的Excel的较旧菜单界面中找到本技巧的版本: