数字转换文字

此文章已于 23:50:13 2016/3/12 发布到 醉资源博客数字转换为文字帐户醉资源博客If youve ever needed to display a number written out as text, you probably discovered that Excel does not offer such a function. When Excel fails to deliver, its often possible to correct the deficiency by using VBA. Heres a VBA function, named SPELLDOLLARS, that you can use in worksheet formulas.Excel examplesHere are some examples of SPELLDOLLARS.The VBA codeTo use this code, press Alt+F11 to activate Visual Basic Editor. Then choose Insert Module to insert a new VBA module. Copy the code and paste it into the new module.Function SPELLDOLLARS(cell) As Variant Returns a value, spelled out in words Dim Dollars As String, Cents As String Dim TextLen As Long, Pos As Long Dim Temp As String Dim iHundreds As Long, iTens As Long, iOnes As Long Dim Ones As Variant, Teens As Variant, Tens As Variant Dim Units(2 To 5) As String Dim bHit As Boolean, NegFlag As Boolean Is it a non-number or empty cell? If Not IsNumeric(cell) Or cell = “ Then SPELLDOLLARS = CVErr(xlErrValue) Exit Function End If Is it negative? If cell 15 Then SPELLDOLLARS = CVErr(xlErrNum) Exit Function End If Do the cents part Cents = Right(Dollars, 2) & “/100 Dollars” If cell = Pos - 2 Then bHit = False If TextLen = Pos Then iHundreds = Asc(Mid$(Dollars, TextLen - Pos + 1, 1) - 48 If iHundreds 0 Then Temp = Temp & “ “ & Ones(iHundreds) & “ Hundred” bHit = True End If End If iTens = 0 iOnes = 0 If TextLen = Pos - 1 Then iTens = Asc(Mid$(Dollars, TextLen - Pos + 2, 1) - 48 End If If TextLen = Pos - 2 Then iOnes = Asc(Mid$(Dollars, TextLen - Pos + 3, 1) - 48 End If If iTens = 1 Then Temp = Temp & “ “ & Teens(iOnes) bHit = True Else If iTens = 2 Then Temp = Temp & “ “ & Tens(iTens) bHit = True End If If iOnes 0 Then If iTens = 2 Then Temp = Temp & “-” Else Temp = Temp & “ “ End If Temp = Temp & Ones(iOnes) bHit = True End If End If If bHit And Pos 3 Then Temp = Temp & “ “ & Units(Pos 3) End If End If Next Pos SPELLDOLL