spell number

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Dec 12, 2005.

  1. Guest

    Guest Guest

    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
     
    Guest, Dec 12, 2005
    #1
    1. Advertisements

  2. Guest

    Gord Dibben Guest

    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.

    Tools>Macro>Record 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 File>Save from there or better yet hit ALT + Q to return to the
    Excel window.

    Then with Personal.xls active, hit Window>Hide.

    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
     
    Gord Dibben, Dec 12, 2005
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Liftoff

    AutoFiltler Displays number of number records in Status Bar

    Liftoff, Sep 8, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    140
    Debra Dalgleish
    Sep 8, 2003
  2. Marvin Hlavac

    Is there a formula to tell me if one number is a multiple of another number?

    Marvin Hlavac, Nov 28, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    200
    Vasant Nanavati
    Nov 28, 2003
  3. Guest

    Is there a formula to spell out a number in excel?

    Guest, Nov 11, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    928
    Guest
    Dec 18, 2004
  4. Guest

    how to spell out number using function in Excel

    Guest, Aug 27, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    6
    Views:
    9,302
  5. KVS

    How to Spell Number in English? I mean write 1 & see One?

    KVS, Mar 18, 2009, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    277
    Ashish Mathur
    Mar 18, 2009
Loading...

Share This Page