EXCEL表格中将数字金额转换为英文.docVIP

精品文档 精品文档 芅PAGE 聿蚇 精品文档 Excel表格中怎样将数字金额变换为英文 （如B1列写162890元，自动变换为英文 ONEHUNDREDSIXTYTWOTHOUSANDEIGHTHUNDREDNINETYDOLLARSANDNO）CENTS 1、新建Excel表格 2、按住“Alt+F11”打开VBA编写器 3、在VBA编写器中单击菜单栏“插入”——模块 4、在打开的模块中输入如下代码： OptionExplicit Function数字转英文(ByValMyNumber) DimDollars,Cents,Temp DimDecimalPlace,Count ReDimPlace(9)AsString Place(2)=Thousand Place(3)=Million Place(4)=Billion Place(5)=Trillion MyNumber=Trim(Str(MyNumber)) DecimalPlace=InStr(MyNumber,.) IfDecimalPlace0Then Cents=GetTens(Left(Mid(MyNumber,DecimalPlace+1)_00,2)) MyNumber=Trim(Left(MyNumber,DecimalPlace-1)) EndIf Count=1 DoWhileMyNumber Temp=GetHundreds(Right(MyNumber,3)) IfTempThenDollars=TempPlace(Count)Dollars IfLen(MyNumber)3Then MyNumber=Left(MyNumber,Len(MyNumber)-3) Else MyNumber= EndIf Count=Count+1 Loop SelectCaseDollars Case Dollars=NoDollars CaseOne Dollars=OneDollar CaseElse Dollars=DollarsDollars EndSelect SelectCaseCents Case Cents=andNoCents CaseOne Cents=andOneCent CaseElse Cents=andCentsCents EndSelect 数字转英文=DollarsCents EndFunction FunctionGetHundreds(ByValMyNumber) DimResultAsString IfVal(MyNumber)=0ThenExitFunction MyNumber=Right(000MyNumber,3) IfMid(MyNumber,1,1)0Then Result=GetDigit(Mid(MyNumber,1,1))Hundred EndIf IfMid(MyNumber,2,1)0Then Result=ResultGetTens(Mid(MyNumber,2)) Else Result=ResultGetDigit(Mid(MyNumber,3)) EndIf GetHundreds=Result EndFunction FunctionGetTens(TensText) DimResultAsString Result= IfVal(Left(TensText,1))=1Then SelectCaseVal(TensText) Case10:Result=Ten Case11:Result=Eleven Case12:Result=Twelve Case13:Result=Thirteen Case14:Result=Fourteen Case15:Result=Fifteen Case16:Result=Sixteen Case17:Result=Seventeen Case18:Result=Eighteen Case19:Result=Nineteen CaseElse EndSelect Else SelectCaseVal(Left(TensText,1)) Case2:Result=Twenty Case3:Result=Thirty Case4:Result=Forty Case5:Result=Fifty Case6:Result=Sixty Case7:Result=Seventy Case8:Result=Eighty Case9:Result=Ninety CaseElse EndSelect Result=ResultGetDigit_ (Right(TensText,1)) EndIf GetTens=Result EndFunction FunctionGetDigi