spell number
i got the following program in visual basic for change any number in spell
Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber) Dim Rupees, Paisa, Temp Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " ' String representation of amount. MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none. DecimalPlace = InStr(MyNumber, ".") ' Convert Paisa and set MyNumber to dollar amount. If DecimalPlace 0 Then Paisa = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If Count = 1 Do While MyNumber < "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp < "" Then Rupees = Temp & Place(Count) & Rupees If Len(MyNumber) 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Rupees Case "" Rupees = "No Rupees" Case "One" Rupees = "One Dollar" Case Else Rupees = Rupees & " Rupees" End Select Select Case Paisa Case "" Paisa = " and No Paisa Only" Case "One" Paisa = " and One Cent Only" Case Else Paisa = " and " & Paisa & " Paisa" End Select SpellNumber = Rupees & Paisa End Function ' Converts a number from 100-999 into text Function GetHundreds(ByVal MyNumber) Dim Result As String If Val(MyNumber) = 0 Then Exit Function MyNumber = Right("000" & MyNumber, 3) ' Convert the hundreds place. If Mid(MyNumber, 1, 1) < "0" Then Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred " End If ' Convert the tens and ones place. If Mid(MyNumber, 2, 1) < "0" Then Result = Result & GetTens(Mid(MyNumber, 2)) Else Result = Result & GetDigit(Mid(MyNumber, 3)) End If GetHundreds = Result End Function ' Converts a number from 10 to 99 into text. Function GetTens(TensText) Dim Result As String Result = "" ' Null out the temporary function value. If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19... Select Case Val(TensText) Case 10: Result = "Ten" Case 11: Result = "Eleven" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End Select Else ' If value between 20-99... Select Case Val(Left(TensText, 1)) Case 2: Result = "Twenty " Case 3: Result = "Thirty " Case 4: Result = "Forty " Case 5: Result = "Fifty " Case 6: Result = "Sixty " Case 7: Result = "Seventy " Case 8: Result = "Eighty " Case 9: Result = "Ninety " Case Else End Select Result = Result & GetDigit _ (Right(TensText, 1)) ' Retrieve ones place. End If GetTens = Result End Function ' Converts a number from 1 to 9 into text. Function GetDigit(Digit) Select Case Val(Digit) Case 1: GetDigit = "One" Case 2: GetDigit = "Two" Case 3: GetDigit = "Three" Case 4: GetDigit = "Four" Case 5: GetDigit = "Five" Case 6: GetDigit = "Six" Case 7: GetDigit = "Seven" Case 8: GetDigit = "Eight" Case 9: GetDigit = "Nine" Case Else: GetDigit = "" End Select End Function when file closed once then it not run again who it un permantly in all excell sheet |
spell number
Jawad
Place it in a module in your Personal.xls workbook to make it available for all open workbooks. Store your macros in Personal.xls, place it in your XLSTART folder and it will open when Excel is started. Personal.xls is created the first time you record a Macro using Macro Recorder. ToolsMacroRecord New Macro. A dialog box will come up asking you name the macro and where to place it. Pick Personal Macro Workbook from the dropdown. Copy and paste a couple of cells then Stop Recording. You now have a Personal.xls in your Office\XLSTART folder. You can go to Visual Basic Editor(Alt+F11) to view the macro you just recorded in a Module. You can add more macros by recording, typing or copying into the Module. You can do a FileSave from there or better yet hit ALT + Q to return to the Excel window. Then with Personal.xls active, hit WindowHide. When you close Excel you will be asked if you want to save Personal.xls. Yes! It will open hidden next time you start Excel. Gord Dibben Excel MVP On Mon, 12 Dec 2005 04:42:02 -0800, JAWAD CHOHAN wrote: i got the following program in visual basic for change any number in spell Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber) Dim Rupees, Paisa, Temp Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " ' String representation of amount. MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none. DecimalPlace = InStr(MyNumber, ".") ' Convert Paisa and set MyNumber to dollar amount. If DecimalPlace 0 Then Paisa = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If Count = 1 Do While MyNumber < "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp < "" Then Rupees = Temp & Place(Count) & Rupees If Len(MyNumber) 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Rupees Case "" Rupees = "No Rupees" Case "One" Rupees = "One Dollar" Case Else Rupees = Rupees & " Rupees" End Select Select Case Paisa Case "" Paisa = " and No Paisa Only" Case "One" Paisa = " and One Cent Only" Case Else Paisa = " and " & Paisa & " Paisa" End Select SpellNumber = Rupees & Paisa End Function ' Converts a number from 100-999 into text Function GetHundreds(ByVal MyNumber) Dim Result As String If Val(MyNumber) = 0 Then Exit Function MyNumber = Right("000" & MyNumber, 3) ' Convert the hundreds place. If Mid(MyNumber, 1, 1) < "0" Then Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred " End If ' Convert the tens and ones place. If Mid(MyNumber, 2, 1) < "0" Then Result = Result & GetTens(Mid(MyNumber, 2)) Else Result = Result & GetDigit(Mid(MyNumber, 3)) End If GetHundreds = Result End Function ' Converts a number from 10 to 99 into text. Function GetTens(TensText) Dim Result As String Result = "" ' Null out the temporary function value. If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19... Select Case Val(TensText) Case 10: Result = "Ten" Case 11: Result = "Eleven" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End Select Else ' If value between 20-99... Select Case Val(Left(TensText, 1)) Case 2: Result = "Twenty " Case 3: Result = "Thirty " Case 4: Result = "Forty " Case 5: Result = "Fifty " Case 6: Result = "Sixty " Case 7: Result = "Seventy " Case 8: Result = "Eighty " Case 9: Result = "Ninety " Case Else End Select Result = Result & GetDigit _ (Right(TensText, 1)) ' Retrieve ones place. End If GetTens = Result End Function ' Converts a number from 1 to 9 into text. Function GetDigit(Digit) Select Case Val(Digit) Case 1: GetDigit = "One" Case 2: GetDigit = "Two" Case 3: GetDigit = "Three" Case 4: GetDigit = "Four" Case 5: GetDigit = "Five" Case 6: GetDigit = "Six" Case 7: GetDigit = "Seven" Case 8: GetDigit = "Eight" Case 9: GetDigit = "Nine" Case Else: GetDigit = "" End Select End Function when file closed once then it not run again who it un permantly in all excell sheet |
All times are GMT +1. The time now is 07:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com