免費問答

你好 ? 1.創建一個模塊： 在SHEET上，右鍵－》查看代碼。選中“模塊”－》插入模塊。2.寫代碼：Option Explicit Dim StrNO(19) As String Dim Unit(8) As String Dim StrTens(9) As StringPublic Function NumberToString(Number As Double) As String ? ?Dim Str As String, BeforePoint As String, AfterPoint As String, tmpStr As String ? ?Dim Point As Integer ? ?Dim nBit As Integer ? ?Dim CurString As String ? ?Dim nNumLen As Integer ? ?Dim T As String ? ?Call Init ? ?Str = CStr(Round(Number, 2)) ? ? Str = Number ? ?If InStr(1, Str, .) = 0 Then ? ? ? ?BeforePoint = Str ? ? ? ?AfterPoint = ? ?Else ? ? ? ?BeforePoint = Left(Str, InStr(1, Str, .) - 1) ? ? ? ?T = Right(Str, Len(Str) - InStr(1, Str, .)) ? ? ? ?If Len(T) < 2 Then AfterPoint = Val(T) * 10 ? ? ? ?If Len(T) = 2 Then AfterPoint = Val(T) ? ? ? ?If Len(T) > 2 Then AfterPoint = Val(Left(T, 2)) ? ?End If ? ?If Len(BeforePoint) > 12 Then ? ? ? ?NumberToString = Too Big. ? ? ? ?Exit Function ? ?End If ? ?Str = ? ?Do While Len(BeforePoint) > 0 ? ? ? ?nNumLen = Len(BeforePoint) ? ? ? ?If nNumLen Mod 3 = 0 Then ? ? ? ? ? ?CurString = Left(BeforePoint, 3) ? ? ? ? ? ?BeforePoint = Right(BeforePoint, nNumLen - 3) ? ? ? ?Else ? ? ? ? ? ?CurString = Left(BeforePoint, (nNumLen Mod 3)) ? ? ? ? ? ?BeforePoint = Right(BeforePoint, nNumLen - (nNumLen Mod 3)) ? ? ? ?End If ? ? ? ?nBit = Len(BeforePoint) / 3 ? ? ? ?tmpStr = DecodeHundred(CurString) ? ? ? ?If (BeforePoint = String(Len(BeforePoint), 0) Or nBit = 0) And Len(CurString) = 3 Then ? ? ? ? ? ?If CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) <> 0 Then ? ? ? ? ? ? ? ?tmpStr = Left(tmpStr, InStr(1, tmpStr, Unit(4)) + Len(Unit(4))) %26 Unit(8) %26 %26 Right(tmpStr, Len(tmpStr) - (InStr(1, tmpStr, Unit(4)) + Len(Unit(4)))) ? ? ? ? ? ?Else If CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) = 0 Then ? ? ? ? ? ? ? ?tmpStr = Unit(8) %26 %26 tmpStr ? ? ? ? ? ?End If ? ? ? ?End If ? ? ? ?If nBit = 0 Then ? ? ? ? ? ?Str = Trim(Str %26 %26 tmpStr) ? ? ? ?Else ? ? ? ? ? ?Str = Trim(Str %26 %26 tmpStr %26 %26 Unit(nBit)) ? ? ? ?End If ? ? ? ?If Left(Str, 3) = Unit(8) Then Str = Trim(Right(Str, Len(Str) - 3)) ? ? ? ?If BeforePoint = String(Len(BeforePoint), 0) Then Exit Do ? ? ? ?***.print Str ? ?Loop ? ?BeforePoint = Str ? ?If Len(AfterPoint) > 0 Then ? ? ? ?AfterPoint = Unit(8) %26 %26 Unit(7) %26 %26 DecodeHundred(AfterPoint) %26 %26 Unit(5) ? ?Else ? ? ? ?AfterPoint = Unit(5) ? ?End If ? ?NumberToString = BeforePoint %26 %26 AfterPoint End Function Private Function DecodeHundred(HundredString As String) As String ? ?Dim tmp As Integer ? ?If Len(HundredString) > 0 And Len(HundredString) <= 3 Then ? ? ? ?Select Case Len(HundredString) ? ? ? ? ? ?Case 1 ? ? ? ? ? ? ? ?tmp = CInt(HundredString) ? ? ? ? ? ? ? ?If tmp <> 0 Then DecodeHundred = StrNO(tmp) ? ? ? ? ? ?Case 2 ? ? ? ? ? ? ? ?tmp = CInt(HundredString) ? ? ? ? ? ? ? ?If tmp <> 0 Then ? ? ? ? ? ? ? ? ? ?If (tmp < 20) Then ? ? ? ? ? ? ? ? ? ? ? ?DecodeHundred = StrNO(tmp) ? ? ? ? ? ? ? ? ? ?Else ? ? ? ? ? ? ? ? ? ? ? ?If CInt(Right(HundredString, 1)) = 0 Then ? ? ? ? ? ? ? ? ? ? ? ? ? ?DecodeHundred = StrTens(Int(tmp / 10)) ? ? ? ? ? ? ? ? ? ? ? ?Else ? ? ? ? ? ? ? ? ? ? ? ? ? ?DecodeHundred = StrTens(Int(tmp / 10)) %26 - %26 StrNO(CInt(Right(HundredString, 1))) ? ? ? ? ? ? ? ? ? ? ? ?End If ? ? ? ? ? ? ? ? ? ?End If ? ? ? ? ? ? ? ?End If ? ? ? ? ? ?Case 3 ? ? ? ? ? ? ? ?If CInt(Left(HundredString, 1)) <> 0 Then ? ? ? ? ? ? ? ? ? ?DecodeHundred = StrNO(CInt(Left(HundredString, 1))) %26 %26 Unit(4) %26 %26 DecodeHundred(Right(HundredString, 2)) ? ? ? ? ? ? ? ?Else ? ? ? ? ? ? ? ? ? ?DecodeHundred = DecodeHundred(Right(HundredString, 2)) ? ? ? ? ? ? ? ?End If ? ? ? ? ? ?Case Else ? ? ? ?End Select ? ?End IfEnd Function Private Sub Init() ? ?If StrNO(1) <> One Then ? ? ? ?StrNO(1) = One ? ? ? ?StrNO(2) = Two ? ? ? ?StrNO(3) = Three ? ? ? ?StrNO(4) = Four ? ? ? ?StrNO(5) = Five ? ? ? ?StrNO(6) = Six ? ? ? ?StrNO(7) = Seven ? ? ? ?StrNO(8) = Eight ? ? ? ?StrNO(9) = Nine ? ? ? ?StrNO(10) = Ten ? ? ? ?StrNO(11) = Eleven ? ? ? ?StrNO(12) = Twelve ? ? ? ?StrNO(13) = Thirteen ? ? ? ?StrNO(14) = Fourteen ? ? ? ?StrNO(15) = Fifteen ? ? ? ?StrNO(16) = Sixteen ? ? ? ?StrNO(17) = Seventeen ? ? ? ?StrNO(18) = Eighteen ? ? ? ?StrNO(19) = Nineteen ? ? ? ?StrTens(1) = Ten ? ? ? ?StrTens(2) = Twenty ? ? ? ?StrTens(3) = Thirty ? ? ? ?StrTens(4) = Forty ? ? ? ?StrTens(5) = Fifty ? ? ? ?StrTens(6) = Sixty ? ? ? ?StrTens(7) = Seventy ? ? ? ?StrTens(8) = Eighty ? ? ? ?StrTens(9) = Ninety ? ? ? ?Unit(1) = Thousand 材?熌?? ? ? ? ?Unit(2) = Million 材?熌?? ? ? ? ?Unit(3) = Billion 材?熌?? ? ? ? ?Unit(4) = Hundred ? ? ? ?Unit(5) = Only ? ? ? ?Unit(6) = Point ? ? ? ?Unit(7) = Cents ? ? ? ?Unit(8) = And ? ?End If End Sub保存此代碼到本地3.模塊中已經定義了函數名稱：NumberToString直接當作EXCEL本地函數使用，例如在A1=7,在B1中輸入＝NumberToString（A1）就可以拉！