VBGood网站全文搜索 Google

搜索VBGood全站网页(全文搜索)
首页 - 经验之谈 - 把数字转成英文字
发表评论(0)作者:, 平台:, 阅读:8603, 日期:2000-05-11
把数字转成英文字


●建立D2T巨集,并把快速键设为Shift + Ctrl + T。




●写入程式码:


Sub D2T()

Dim MyStr As String

MyStr = ActiveCell.Text



If IsNumeric(MyStr) = True Then

ActiveCell.Value = ""


Select Case Len(MyStr)

Case 1

OneDG (MyStr)

Case 2

TwoDG (MyStr)

Case 3

ThreeDG (MyStr)

Case 4

OneDG (Left(MyStr, 1))

ActiveCell.Value = ActiveCell.Value + " Thousand "

ThreeDG (Right(MyStr, 3))

Case 5

TwoDG (Left(MyStr, 2))

ActiveCell.Value = ActiveCell.Value + " Thousand "

ThreeDG (Right(MyStr, 3))

Case 6

ThreeDG (Left(MyStr, 3))

ActiveCell.Value = ActiveCell.Value + " Thousand "

ThreeDG (Right(MyStr, 3))

Case 7

OneDG (Left(MyStr, 1))

ActiveCell.Value = ActiveCell.Value + " Millon "

ThreeDG (Mid(MyStr, 2, 3))

ActiveCell.Value = ActiveCell.Value + " Thousand "

ThreeDG (Right(MyStr, 3))

Case 8

TwoDG (Left(MyStr, 2))

ActiveCell.Value = ActiveCell.Value + " Millon "

ThreeDG (Mid(MyStr, 3, 3))

ActiveCell.Value = ActiveCell.Value + " Thousand "

ThreeDG (Right(MyStr, 3))

Case 9

ThreeDG (Left(MyStr, 3))

ActiveCell.Value = ActiveCell.Value + " Millon "

ThreeDG (Mid(MyStr, 4, 3))

ActiveCell.Value = ActiveCell.Value + " Thousand "

ThreeDG (Right(MyStr, 3))

Case 10

OneDG (Left(MyStr, 1))

ActiveCell.Value = ActiveCell.Value + " Billon "

ThreeDG (Mid(MyStr, 2, 3))

ActiveCell.Value = ActiveCell.Value + " Millon "

ThreeDG (Mid(MyStr, 5, 3))

ActiveCell.Value = ActiveCell.Value + " Thousand "

ThreeDG (Right(MyStr, 3))

Case 11

TwoDG (Left(MyStr, 2))

ActiveCell.Value = ActiveCell.Value + " Billon "

ThreeDG (Mid(MyStr, 3, 3))

ActiveCell.Value = ActiveCell.Value + " Millon "

ThreeDG (Mid(MyStr, 6, 3))

ActiveCell.Value = ActiveCell.Value + " Thousand "

ThreeDG (Right(MyStr, 3))

Case Else

End Select

End If

End Sub


Sub OneDG(MyStr As String)

Select Case MyStr

Case "0"

If ActiveCell.Value = "" Then ActiveCell.Value = ActiveCell.Value + "Zero"

Case "1"

ActiveCell.Value = ActiveCell.Value + "One"

Case "2"

ActiveCell.Value = ActiveCell.Value + "Two"

Case "3"

ActiveCell.Value = ActiveCell.Value + "Three"

Case "4"

ActiveCell.Value = ActiveCell.Value + "Four"

Case "5"

ActiveCell.Value = ActiveCell.Value + "Five"

Case "6"

ActiveCell.Value = ActiveCell.Value + "Six"

Case "7"

ActiveCell.Value = ActiveCell.Value + "Seven"

Case "8"

ActiveCell.Value = ActiveCell.Value + "Eight"

Case "9"

ActiveCell.Value = ActiveCell.Value + "Nine"

End Select

End Sub


Sub TwoDG(MyStr As String)

Select Case MyStr

Case "10"

ActiveCell.Value = ActiveCell.Value + "Ten"

Case "11"

ActiveCell.Value = ActiveCell.Value + "eleven"

Case "12"

ActiveCell.Value = ActiveCell.Value + "Twelve"

Case "13"

ActiveCell.Value = ActiveCell.Value + "Thirteen"

Case "14"

ActiveCell.Value = ActiveCell.Value + "Fourteen"

Case "15"

ActiveCell.Value = ActiveCell.Value + "Fifteen"

Case "16"

ActiveCell.Value = ActiveCell.Value + "Sixteen"

Case "17"

ActiveCell.Value = ActiveCell.Value + "Seventeen"

Case "18"

ActiveCell.Value = ActiveCell.Value + "Eighteen"

Case "19"

ActiveCell.Value = ActiveCell.Value + "Nineteen"

Case Else

Select Case Left(MyStr, 1)

Case "2"

ActiveCell.Value = ActiveCell.Value + "Twenty "

Case "3"

ActiveCell.Value = ActiveCell.Value + "Thirty "

Case "4"

ActiveCell.Value = ActiveCell.Value + "Forty "

Case "5"

ActiveCell.Value = ActiveCell.Value + "Fifty "

Case "6"

ActiveCell.Value = ActiveCell.Value + "Sixty "

Case "7"

ActiveCell.Value = ActiveCell.Value + "Seventy "

Case "8"

ActiveCell.Value = ActiveCell.Value + "Eighty "

Case "9"

ActiveCell.Value = ActiveCell.Value + "Ninety "

End Select

OneDG (Right(MyStr, 1))

End Select


End Sub


Sub ThreeDG(MyStr As String)

Select Case Left(MyStr, 1)

Case "1"

ActiveCell.Value = ActiveCell.Value + "One Handred "

Case "2"

ActiveCell.Value = ActiveCell.Value + "Two Handred "

Case "3"

ActiveCell.Value = ActiveCell.Value + "Three Handred "

Case "4"

ActiveCell.Value = ActiveCell.Value + "Four Handred "

Case "5"

ActiveCell.Value = ActiveCell.Value + "Five Handred "

Case "6"

ActiveCell.Value = ActiveCell.Value + "Six Handred "

Case "7"

ActiveCell.Value = ActiveCell.Value + "Seven Handred "

Case "8"

ActiveCell.Value = ActiveCell.Value + "Eight Handred "

Case "9"

ActiveCell.Value = ActiveCell.Value + "Night Handred "

End Select

TwoDG Right(MyStr, 2)

End Sub

●上面的程式码可以处理到11位数。 ●随意输入一个未超过11位数的数字,然后把输入游标移到该Cell里。



●一同按下 Shift + Ctrl + T。