Results 1 to 8 of 8

Thread: Amount to Words [EXCEL 2007]

  1. #1
    Member
    Join Date
    Jun 2012
    Posts
    80
    Rep Power
    13

    Question Amount to Words [EXCEL 2007]

    Hi All,

    I know there are countless threads about this topic, but I could never find a format suitable for the conversion.

    Basically, I want the amount, for example-
    1) 2159.83 to be converted as "AED Two Thousand Fifty-Nine & Eighty-Three Fils Only"
    2) 2159.00 to be converted as "AED Two Thousand Fifty-Nine Only"
    3) 212359.00 to be converted as "AED Two Hundred Twelve Thousand Three Hundred Fifty-Nine Only"

    AED = UAE Dirhams
    Fils = Cents of UAE :P

    Could somebody help me with a code or so for this conversion.


    Thanks a lot



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-06-2023 at 04:13 PM.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    This is a modification to the code I posted here "Yet Another Number-To-Words Function (Sorry, US Style Only)" where I changed the dollars and cents to output as AED and fils with the word Only at the end and, of course, I changed the function name itself to AEDtext. I removed all the other options so there is only one argument to the function... the number you want to convert to text. Copy/paste the following code into a standard module and then use AEDtext as if it were an Excel worksheet function, meaning if you have a number in A1, you could put this in B1...

    =AEDtext(A1)

    The AEDtext function can also be called from your own VB code routines if you need to use it that way instead. Here is the code...

    Code:
    Private sNumberText() As String
    
    Public Function AEDtext(NumberIn As Variant) As String
       Dim cnt As Long
       Dim DecimalPoint As Long
       Dim CardinalNumber As Long
       Dim CommaAdjuster As Long
       Dim TestValue As Long
       Dim CurrValue As Currency
       Dim CentsString As String
       Dim NumberSign As String
       Dim WholePart As String
       Dim BigWholePart As String
       Dim DecimalPart As String
       Dim tmp As String
       Dim sStyle As String
       Dim bUseAnd As Boolean
       Dim bUseCheck As Boolean
       Dim bUseDollars As Boolean
       Dim bUseCheckDollar As Boolean
       Dim AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR As String
      '----------------------------------------
      '  Begin setting conditions for formatting
      '----------------------------------------
       sStyle = "dollar"
       bUseAnd = sStyle = "and"
       bUseDollars = sStyle = "dollar"
       bUseCheck = (sStyle = "check") Or (sStyle = "dollar")
    '   bUseCheckDollar = sStyle = "checkdollar"
      '----------------------------------------
      '  Check/create array. If this is the first
      '  time using this routine, create the text
      '  strings that will be used.
      '----------------------------------------
       If Not IsBounded(sNumberText) Then
          Call BuildArray(sNumberText)
       End If
      '----------------------------------------
      '  Begin validating the number, and breaking
      '  into constituent parts
      '----------------------------------------
       NumberIn = Trim$(NumberIn)
       If Not IsNumeric(NumberIn) Then
          AEDtext = "Error - Number improperly formed"
          Exit Function
       Else
          DecimalPoint = InStr(NumberIn, ".")
          If DecimalPoint > 0 Then
             DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
             WholePart = Left$(NumberIn, DecimalPoint - 1)
          Else
             DecimalPoint = Len(NumberIn) + 1
             WholePart = NumberIn
          End If
          If InStr(NumberIn, ",,") Or _
             InStr(NumberIn, ",.") Or _
             InStr(NumberIn, ".,") Or _
             InStr(DecimalPart, ",") Then
             AEDtext = "Error - Improper use of commas"
             Exit Function
          ElseIf InStr(NumberIn, ",") Then
             CommaAdjuster = 0
             WholePart = ""
             For cnt = DecimalPoint - 1 To 1 Step -1
                If Not Mid$(NumberIn, cnt, 1) Like "[,]" Then
                   WholePart = Mid$(NumberIn, cnt, 1) & WholePart
                Else
                   CommaAdjuster = CommaAdjuster + 1
                   If (DecimalPoint - cnt - CommaAdjuster) Mod 3 Then
                      AEDtext = "Error - Improper use of commas"
                      Exit Function
                   End If
                End If
             Next
          End If
       End If
       If Left$(WholePart, 1) Like "[+-]" Then
          NumberSign = IIf(Left$(WholePart, 1) = "-", "Minus ", "Plus ")
          WholePart = Mid$(WholePart, 2)
       End If
      '----------------------------------------
      '  Begin code to assure decimal portion of
      '  check value is not inadvertently rounded
      '----------------------------------------
       If bUseCheck = True Then
          CurrValue = CCur(Val("." & DecimalPart))
          DecimalPart = Mid$(Format$(CurrValue, "0.00"), 3, 2)
          If CurrValue >= 0.995 Then
             If WholePart = String$(Len(WholePart), "9") Then
                WholePart = "1" & String$(Len(WholePart), "0")
             Else
                For cnt = Len(WholePart) To 1 Step -1
                  If Mid$(WholePart, cnt, 1) = "9" Then
                     Mid$(WholePart, cnt, 1) = "0"
                  Else
                     Mid$(WholePart, cnt, 1) = _
                                CStr(Val(Mid$(WholePart, cnt, 1)) + 1)
                     Exit For
                  End If
                Next
             End If
          End If
       End If
      '----------------------------------------
      '  Final prep step - this assures number
      '  within range of formatting code below
      '----------------------------------------
       If Len(WholePart) > 9 Then
          BigWholePart = Left$(WholePart, Len(WholePart) - 9)
          WholePart = Right$(WholePart, 9)
       End If
       If Len(BigWholePart) > 9 Then
          AEDtext = "Error - Number too large"
          Exit Function
       ElseIf Not WholePart Like String$(Len(WholePart), "#") Or _
             (Not BigWholePart Like String$(Len(BigWholePart), "#") _
              And Len(BigWholePart) > 0) Then
          AEDtext = "Error - Number improperly formed"
          Exit Function
       End If
      '----------------------------------------
      '  Begin creating the output string
      '----------------------------------------
      '  Very Large values
       TestValue = Val(BigWholePart)
       If TestValue > 999999 Then
          CardinalNumber = TestValue \ 1000000
          tmp = HundredsTensUnits(CardinalNumber) & "Quadrillion "
          TestValue = TestValue - (CardinalNumber * 1000000)
       End If
       If TestValue > 999 Then
         CardinalNumber = TestValue \ 1000
         tmp = tmp & HundredsTensUnits(CardinalNumber) & "Trillion "
         TestValue = TestValue - (CardinalNumber * 1000)
       End If
       If TestValue > 0 Then
          tmp = tmp & HundredsTensUnits(TestValue) & "Billion "
       End If
      '  Lesser values
       TestValue = Val(WholePart)
       If TestValue = 0 And BigWholePart = "" Then tmp = "Zero "
       If TestValue > 999999 Then
          CardinalNumber = TestValue \ 1000000
          tmp = tmp & HundredsTensUnits(CardinalNumber) & "Million "
          TestValue = TestValue - (CardinalNumber * 1000000)
       End If
       If TestValue > 999 Then
          CardinalNumber = TestValue \ 1000
          tmp = tmp & HundredsTensUnits(CardinalNumber) & "Thousand "
          TestValue = TestValue - (CardinalNumber * 1000)
       End If
       If TestValue > 0 Then
          If Val(WholePart) < 99 And BigWholePart = "" Then bUseAnd = False
          tmp = tmp & HundredsTensUnits(TestValue, bUseAnd)
       End If
      '  If in dollar mode, assure the text is the correct plurality
       If bUseDollars = True Then
          CentsString = HundredsTensUnits(DecimalPart)
          tmp = "AED " & tmp
          If Len(CentsString) > 0 Then
             tmp = tmp & "& " & CentsString
             tmp = tmp & "Fils Only"
          Else
            tmp = tmp & "Only"
          End If
       End If
      '  Done!
       AEDtext = NumberSign & tmp
    End Function
    
    Private Sub BuildArray(sNumberText() As String)
       ReDim sNumberText(0 To 27) As String
       sNumberText(0) = "Zero"
       sNumberText(1) = "One"
       sNumberText(2) = "Two"
       sNumberText(3) = "Three"
       sNumberText(4) = "Four"
       sNumberText(5) = "Five"
       sNumberText(6) = "Six"
       sNumberText(7) = "Seven"
       sNumberText(8) = "Eight"
       sNumberText(9) = "Nine"
       sNumberText(10) = "Ten"
       sNumberText(11) = "Eleven"
       sNumberText(12) = "Twelve"
       sNumberText(13) = "Thirteen"
       sNumberText(14) = "Fourteen"
       sNumberText(15) = "Fifteen"
       sNumberText(16) = "Sixteen"
       sNumberText(17) = "Seventeen"
       sNumberText(18) = "Eighteen"
       sNumberText(19) = "Nineteen"
       sNumberText(20) = "Twenty"
       sNumberText(21) = "Thirty"
       sNumberText(22) = "Forty"
       sNumberText(23) = "Fifty"
       sNumberText(24) = "Sixty"
       sNumberText(25) = "Seventy"
       sNumberText(26) = "Eighty"
       sNumberText(27) = "Ninety"
    End Sub
    
    Private Function IsBounded(vntArray As Variant) As Boolean
       On Error Resume Next
       IsBounded = IsNumeric(UBound(vntArray))
    End Function
    
    Private Function HundredsTensUnits(ByVal TestValue As Integer, _
                                  Optional bUseAnd As Boolean) As String
       Dim CardinalNumber As Integer
       If TestValue > 99 Then
          CardinalNumber = TestValue \ 100
          HundredsTensUnits = sNumberText(CardinalNumber) & " Hundred "
          TestValue = TestValue - (CardinalNumber * 100)
       End If
       If bUseAnd = True Then
          HundredsTensUnits = HundredsTensUnits & "and "
       End If
       If TestValue > 20 Then
          CardinalNumber = TestValue \ 10
          HundredsTensUnits = HundredsTensUnits & _
                              sNumberText(CardinalNumber + 18) & " "
          TestValue = TestValue - (CardinalNumber * 10)
       End If
       If TestValue > 0 Then
          HundredsTensUnits = HundredsTensUnits & _
                              sNumberText(TestValue) & " "
       End If
    End Function
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?f=27&t=35521&p=276185#p276185
    https://eileenslounge.com/viewtopic.php?p=276185#p276185
    https://eileenslounge.com/viewtopic.php?p=276185#p276185
    https://eileenslounge.com/viewtopic.php?p=276673#p276673
    https://eileenslounge.com/viewtopic.php?p=276751#p276751
    https://eileenslounge.com/viewtopic.php?p=276754#p276754
    https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=274367#p274367
    https://eileenslounge.com/viewtopic.php?p=274368#p274368
    https://eileenslounge.com/viewtopic.php?p=274370#p274370
    https://eileenslounge.com/viewtopic.php?p=274578#p274578
    https://eileenslounge.com/viewtopic.php?p=274577#p274577
    https://eileenslounge.com/viewtopic.php?p=274474#p274474
    https://eileenslounge.com/viewtopic.php?p=274579#p274579
    https://www.excelfox.com/forum/showthread.php/261-Scrolling-Marquee-text-on-Userform?p=864&viewfull=1#post864
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 04-07-2024 at 11:54 AM.

  3. #3
    Member
    Join Date
    Jun 2012
    Posts
    80
    Rep Power
    13
    Hi Rick,

    Thanks a lot for the code. Finally i got a conversion of my desired format. :D
    Is it possible to add a 'hyphen' ( - ) between Eighty-Three, Thirty-Six and so on?

    If its a lenghty process then ignore it.

    Also, i had a small question. Do i have to manually add the code to all the excel files i work on? Because i tried the "=AEDtext(A1)" in another excel sheet and the formula wasnt working.

    Awaiting your reply
    Last edited by msiyab; 06-07-2012 at 03:20 PM.

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by msiyab View Post
    Thanks a lot for the code. Finally i got a conversion of my desired format. :D
    You are quite welcome... I am glad I was able to help you out with that.

    Quote Originally Posted by msiyab View Post
    Is it possible to add a 'hyphen' ( - ) between Eighty-Three, Thirty-Six and so on?

    If its a lenghty process then ignore it.
    It turned out to be harder than I thought it would be (the original code is somewhat old and I haven't had to modify it in quite some time now), but I believe this will work correctly for you. Rather than trying to direct you to where I made changes, I am simply posting all the code, even those parts that did not change... just replace what I gave you earlier with the following...

    Code:
    Private sNumberText() As String
    Public Function AEDtext(NumberIn As Variant) As String
       Dim cnt As Long
       Dim DecimalPoint As Long
       Dim CardinalNumber As Long
       Dim CommaAdjuster As Long
       Dim TestValue As Long
       Dim CurrValue As Currency
       Dim CentsString As String
       Dim NumberSign As String
       Dim WholePart As String
       Dim BigWholePart As String
       Dim DecimalPart As String
       Dim tmp As String
       Dim sStyle As String
       Dim bUseAnd As Boolean
       Dim bUseCheck As Boolean
       Dim bUseDollars As Boolean
       Dim bUseCheckDollar As Boolean
       Dim AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR As String
      '----------------------------------------
      '  Begin setting conditions for formatting
      '----------------------------------------
       sStyle = "dollar"
       bUseAnd = sStyle = "and"
       bUseDollars = sStyle = "dollar"
       bUseCheck = (sStyle = "check") Or (sStyle = "dollar")
    '   bUseCheckDollar = sStyle = "checkdollar"
      '----------------------------------------
      '  Check/create array. If this is the first
      '  time using this routine, create the text
      '  strings that will be used.
      '----------------------------------------
       If Not IsBounded(sNumberText) Then
          Call BuildArray(sNumberText)
       End If
      '----------------------------------------
      '  Begin validating the number, and breaking
      '  into constituent parts
      '----------------------------------------
       NumberIn = Trim$(NumberIn)
       If Not IsNumeric(NumberIn) Then
          AEDtext = "Error - Number improperly formed"
          Exit Function
       Else
          DecimalPoint = InStr(NumberIn, ".")
          If DecimalPoint > 0 Then
             DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
             WholePart = Left$(NumberIn, DecimalPoint - 1)
          Else
             DecimalPoint = Len(NumberIn) + 1
             WholePart = NumberIn
          End If
          If InStr(NumberIn, ",,") Or _
             InStr(NumberIn, ",.") Or _
             InStr(NumberIn, ".,") Or _
             InStr(DecimalPart, ",") Then
             AEDtext = "Error - Improper use of commas"
             Exit Function
          ElseIf InStr(NumberIn, ",") Then
             CommaAdjuster = 0
             WholePart = ""
             For cnt = DecimalPoint - 1 To 1 Step -1
                If Not Mid$(NumberIn, cnt, 1) Like "[,]" Then
                   WholePart = Mid$(NumberIn, cnt, 1) & WholePart
                Else
                   CommaAdjuster = CommaAdjuster + 1
                   If (DecimalPoint - cnt - CommaAdjuster) Mod 3 Then
                      AEDtext = "Error - Improper use of commas"
                      Exit Function
                   End If
                End If
             Next
          End If
       End If
       If Left$(WholePart, 1) Like "[+-]" Then
          NumberSign = IIf(Left$(WholePart, 1) = "-", "Minus ", "Plus ")
          WholePart = Mid$(WholePart, 2)
       End If
      '----------------------------------------
      '  Begin code to assure decimal portion of
      '  check value is not inadvertently rounded
      '----------------------------------------
       If bUseCheck = True Then
          CurrValue = CCur(Val("." & DecimalPart))
          DecimalPart = Mid$(Format$(CurrValue, "0.00"), 3, 2)
          If CurrValue >= 0.995 Then
             If WholePart = String$(Len(WholePart), "9") Then
                WholePart = "1" & String$(Len(WholePart), "0")
             Else
                For cnt = Len(WholePart) To 1 Step -1
                  If Mid$(WholePart, cnt, 1) = "9" Then
                     Mid$(WholePart, cnt, 1) = "0"
                  Else
                     Mid$(WholePart, cnt, 1) = _
                                CStr(Val(Mid$(WholePart, cnt, 1)) + 1)
                     Exit For
                  End If
                Next
             End If
          End If
       End If
      '----------------------------------------
      '  Final prep step - this assures number
      '  within range of formatting code below
      '----------------------------------------
       If Len(WholePart) > 9 Then
          BigWholePart = Left$(WholePart, Len(WholePart) - 9)
          WholePart = Right$(WholePart, 9)
       End If
       If Len(BigWholePart) > 9 Then
          AEDtext = "Error - Number too large"
          Exit Function
       ElseIf Not WholePart Like String$(Len(WholePart), "#") Or _
             (Not BigWholePart Like String$(Len(BigWholePart), "#") _
              And Len(BigWholePart) > 0) Then
          AEDtext = "Error - Number improperly formed"
          Exit Function
       End If
      '----------------------------------------
      '  Begin creating the output string
      '----------------------------------------
      '  Very Large values
       TestValue = Val(BigWholePart)
       If TestValue > 999999 Then
          CardinalNumber = TestValue \ 1000000
          tmp = HundredsTensUnits(CardinalNumber) & "Quadrillion "
          TestValue = TestValue - (CardinalNumber * 1000000)
       End If
       If TestValue > 999 Then
         CardinalNumber = TestValue \ 1000
         tmp = tmp & HundredsTensUnits(CardinalNumber) & "Trillion "
         TestValue = TestValue - (CardinalNumber * 1000)
       End If
       If TestValue > 0 Then
          tmp = tmp & HundredsTensUnits(TestValue) & "Billion "
       End If
      '  Lesser values
       TestValue = Val(WholePart)
       If TestValue = 0 And BigWholePart = "" Then tmp = "Zero "
       If TestValue > 999999 Then
          CardinalNumber = TestValue \ 1000000
          tmp = tmp & HundredsTensUnits(CardinalNumber) & "Million "
          TestValue = TestValue - (CardinalNumber * 1000000)
       End If
       If TestValue > 999 Then
          CardinalNumber = TestValue \ 1000
          tmp = tmp & HundredsTensUnits(CardinalNumber) & "Thousand "
          TestValue = TestValue - (CardinalNumber * 1000)
       End If
       If TestValue > 0 Then
          If Val(WholePart) < 99 And BigWholePart = "" Then bUseAnd = False
          tmp = tmp & HundredsTensUnits(TestValue, bUseAnd)
       End If
      '  If in dollar mode, assure the text is the correct plurality
       If bUseDollars = True Then
          CentsString = HundredsTensUnits(DecimalPart)
          tmp = "AED " & tmp
          If Len(CentsString) > 0 Then
             tmp = tmp & "& " & CentsString
             tmp = tmp & "Fils Only"
          Else
            tmp = tmp & "Only"
          End If
       End If
      '  Done!
       AEDtext = NumberSign & tmp
    End Function
    
    Private Sub BuildArray(sNumberText() As String)
       ReDim sNumberText(0 To 27) As String
       sNumberText(0) = "Zero"
       sNumberText(1) = "One"
       sNumberText(2) = "Two"
       sNumberText(3) = "Three"
       sNumberText(4) = "Four"
       sNumberText(5) = "Five"
       sNumberText(6) = "Six"
       sNumberText(7) = "Seven"
       sNumberText(8) = "Eight"
       sNumberText(9) = "Nine"
       sNumberText(10) = "Ten"
       sNumberText(11) = "Eleven"
       sNumberText(12) = "Twelve"
       sNumberText(13) = "Thirteen"
       sNumberText(14) = "Fourteen"
       sNumberText(15) = "Fifteen"
       sNumberText(16) = "Sixteen"
       sNumberText(17) = "Seventeen"
       sNumberText(18) = "Eighteen"
       sNumberText(19) = "Nineteen"
       sNumberText(20) = "Twenty"
       sNumberText(21) = "Thirty"
       sNumberText(22) = "Forty"
       sNumberText(23) = "Fifty"
       sNumberText(24) = "Sixty"
       sNumberText(25) = "Seventy"
       sNumberText(26) = "Eighty"
       sNumberText(27) = "Ninety"
    End Sub
    
    Private Function IsBounded(vntArray As Variant) As Boolean
       On Error Resume Next
       IsBounded = IsNumeric(UBound(vntArray))
    End Function
    
    Private Function HundredsTensUnits(ByVal TestValue As Integer, _
                                  Optional bUseAnd As Boolean) As String
       Dim CardinalNumber As Integer
       If TestValue > 99 Then
          CardinalNumber = TestValue \ 100
          HundredsTensUnits = sNumberText(CardinalNumber) & " Hundred "
          TestValue = TestValue - (CardinalNumber * 100)
       End If
       If bUseAnd = True Then
          HundredsTensUnits = HundredsTensUnits & "and "
       End If
       If TestValue > 20 Then
          CardinalNumber = TestValue \ 10
          HundredsTensUnits = HundredsTensUnits & _
                              sNumberText(CardinalNumber + 18)
          TestValue = TestValue - (CardinalNumber * 10)
       End If
       If TestValue > 0 Then
          If Right(HundredsTensUnits, 1) <> " " Then HundredsTensUnits = HundredsTensUnits & "-"
          HundredsTensUnits = HundredsTensUnits & sNumberText(TestValue) & " "
       Else
          HundredsTensUnits = HundredsTensUnits & " "
       End If
       If Left(HundredsTensUnits, 1) = "-" Then HundredsTensUnits = Mid(HundredsTensUnits, 2)
    End Function
    Quote Originally Posted by msiyab View Post
    Also, i had a small question. Do i have to manually add the code to all the excel files i work on? Because i tried the "=AEDtext(A1)" in another excel sheet and the formula wasnt working.
    If you plan to give your workbook to others or if you plan to use it on more than one computer, then yes, each workbook will need to have the code added to it. On the other hand, if you only need the functionality from one computer, you could put the code in your Personal Workbook and run it from there. Here is a link to creating a Person Workbook if you do not now have one...

    How do I create a PERSONAL.XLS(B) or Add-in
    Last edited by Rick Rothstein; 06-07-2012 at 09:11 PM.

  5. #5

  6. #6
    Member
    Join Date
    Jun 2012
    Posts
    80
    Rep Power
    13
    Mr. Rick,

    I just got back to work and tried your codes on my work files. I have a small problem.

    When i type an amout, for example, 123, the result comes up like "AED One Hundred Twenty-Three & Fils Only"

    If there is no number after the decimal point, the Fils Only part should not come. Instead "AED One Hundred Twenty-Three Only" should appear.


    If you could only provide the code for the above mentioned problem.


    Thanks

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by msiyab View Post
    I just got back to work and tried your codes on my work files. I have a small problem.
    When i type an amout, for example, 123, the result comes up like "AED One Hundred Twenty-Three & Fils Only"
    If there is no number after the decimal point, the Fils Only part should not come. Instead "AED One Hundred Twenty-Three Only" should appear.
    Sorry (that's the problem when you go back and try to modify functioning code to work differently), see if this works the way you expect...

    Code:
    Private sNumberText() As String
    
    Public Function AEDtext(NumberIn As Variant) As String
       Dim cnt As Long
       Dim DecimalPoint As Long
       Dim CardinalNumber As Long
       Dim CommaAdjuster As Long
       Dim TestValue As Long
       Dim CurrValue As Currency
       Dim CentsString As String
       Dim NumberSign As String
       Dim WholePart As String
       Dim BigWholePart As String
       Dim DecimalPart As String
       Dim tmp As String
       Dim sStyle As String
       Dim bUseAnd As Boolean
       Dim bUseCheck As Boolean
       Dim bUseDollars As Boolean
       Dim bUseCheckDollar As Boolean
       Dim AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR As String
      '----------------------------------------
      '  Begin setting conditions for formatting
      '----------------------------------------
       sStyle = "dollar"
       bUseAnd = sStyle = "and"
       bUseDollars = sStyle = "dollar"
       bUseCheck = (sStyle = "check") Or (sStyle = "dollar")
    '   bUseCheckDollar = sStyle = "checkdollar"
      '----------------------------------------
      '  Check/create array. If this is the first
      '  time using this routine, create the text
      '  strings that will be used.
      '----------------------------------------
       If Not IsBounded(sNumberText) Then
          Call BuildArray(sNumberText)
       End If
      '----------------------------------------
      '  Begin validating the number, and breaking
      '  into constituent parts
      '----------------------------------------
       NumberIn = Trim$(NumberIn)
       If Not IsNumeric(NumberIn) Then
          AEDtext = "Error - Number improperly formed"
          Exit Function
       Else
          DecimalPoint = InStr(NumberIn, ".")
          If DecimalPoint > 0 Then
             DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
             WholePart = Left$(NumberIn, DecimalPoint - 1)
          Else
             DecimalPoint = Len(NumberIn) + 1
             WholePart = NumberIn
          End If
          If InStr(NumberIn, ",,") Or _
             InStr(NumberIn, ",.") Or _
             InStr(NumberIn, ".,") Or _
             InStr(DecimalPart, ",") Then
             AEDtext = "Error - Improper use of commas"
             Exit Function
          ElseIf InStr(NumberIn, ",") Then
             CommaAdjuster = 0
             WholePart = ""
             For cnt = DecimalPoint - 1 To 1 Step -1
                If Not Mid$(NumberIn, cnt, 1) Like "[,]" Then
                   WholePart = Mid$(NumberIn, cnt, 1) & WholePart
                Else
                   CommaAdjuster = CommaAdjuster + 1
                   If (DecimalPoint - cnt - CommaAdjuster) Mod 3 Then
                      AEDtext = "Error - Improper use of commas"
                      Exit Function
                   End If
                End If
             Next
          End If
       End If
       If Left$(WholePart, 1) Like "[+-]" Then
          NumberSign = IIf(Left$(WholePart, 1) = "-", "Minus ", "Plus ")
          WholePart = Mid$(WholePart, 2)
       End If
      '----------------------------------------
      '  Begin code to assure decimal portion of
      '  check value is not inadvertently rounded
      '----------------------------------------
       If bUseCheck = True Then
          CurrValue = CCur(Val("." & DecimalPart))
          DecimalPart = Mid$(Format$(CurrValue, "0.00"), 3, 2)
          If CurrValue >= 0.995 Then
             If WholePart = String$(Len(WholePart), "9") Then
                WholePart = "1" & String$(Len(WholePart), "0")
             Else
                For cnt = Len(WholePart) To 1 Step -1
                  If Mid$(WholePart, cnt, 1) = "9" Then
                     Mid$(WholePart, cnt, 1) = "0"
                  Else
                     Mid$(WholePart, cnt, 1) = _
                                CStr(Val(Mid$(WholePart, cnt, 1)) + 1)
                     Exit For
                  End If
                Next
             End If
          End If
       End If
      '----------------------------------------
      '  Final prep step - this assures number
      '  within range of formatting code below
      '----------------------------------------
       If Len(WholePart) > 9 Then
          BigWholePart = Left$(WholePart, Len(WholePart) - 9)
          WholePart = Right$(WholePart, 9)
       End If
       If Len(BigWholePart) > 9 Then
          AEDtext = "Error - Number too large"
          Exit Function
       ElseIf Not WholePart Like String$(Len(WholePart), "#") Or _
             (Not BigWholePart Like String$(Len(BigWholePart), "#") _
              And Len(BigWholePart) > 0) Then
          AEDtext = "Error - Number improperly formed"
          Exit Function
       End If
      '----------------------------------------
      '  Begin creating the output string
      '----------------------------------------
      '  Very Large values
       TestValue = Val(BigWholePart)
       If TestValue > 999999 Then
          CardinalNumber = TestValue \ 1000000
          tmp = HundredsTensUnits(CardinalNumber) & "Quadrillion "
          TestValue = TestValue - (CardinalNumber * 1000000)
       End If
       If TestValue > 999 Then
         CardinalNumber = TestValue \ 1000
         tmp = tmp & HundredsTensUnits(CardinalNumber) & "Trillion "
         TestValue = TestValue - (CardinalNumber * 1000)
       End If
       If TestValue > 0 Then
          tmp = tmp & HundredsTensUnits(TestValue) & "Billion "
       End If
      '  Lesser values
       TestValue = Val(WholePart)
       If TestValue = 0 And BigWholePart = "" Then tmp = "Zero "
       If TestValue > 999999 Then
          CardinalNumber = TestValue \ 1000000
          tmp = tmp & HundredsTensUnits(CardinalNumber) & "Million "
          TestValue = TestValue - (CardinalNumber * 1000000)
       End If
       If TestValue > 999 Then
          CardinalNumber = TestValue \ 1000
          tmp = tmp & HundredsTensUnits(CardinalNumber) & "Thousand "
          TestValue = TestValue - (CardinalNumber * 1000)
       End If
       If TestValue > 0 Then
          If Val(WholePart) < 99 And BigWholePart = "" Then bUseAnd = False
          tmp = tmp & HundredsTensUnits(TestValue, bUseAnd)
       End If
      '  If in dollar mode, assure the text is the correct plurality
       If bUseDollars = True Then
          CentsString = HundredsTensUnits(DecimalPart)
          tmp = "AED " & tmp
          If Len(Trim(CentsString)) > 0 Then
             tmp = tmp & "& " & CentsString
             tmp = tmp & "Fils Only"
          Else
            tmp = tmp & "Only"
          End If
       End If
      '  Done!
       AEDtext = NumberSign & tmp
    End Function
    
    Private Sub BuildArray(sNumberText() As String)
       ReDim sNumberText(0 To 27) As String
       sNumberText(0) = "Zero"
       sNumberText(1) = "One"
       sNumberText(2) = "Two"
       sNumberText(3) = "Three"
       sNumberText(4) = "Four"
       sNumberText(5) = "Five"
       sNumberText(6) = "Six"
       sNumberText(7) = "Seven"
       sNumberText(8) = "Eight"
       sNumberText(9) = "Nine"
       sNumberText(10) = "Ten"
       sNumberText(11) = "Eleven"
       sNumberText(12) = "Twelve"
       sNumberText(13) = "Thirteen"
       sNumberText(14) = "Fourteen"
       sNumberText(15) = "Fifteen"
       sNumberText(16) = "Sixteen"
       sNumberText(17) = "Seventeen"
       sNumberText(18) = "Eighteen"
       sNumberText(19) = "Nineteen"
       sNumberText(20) = "Twenty"
       sNumberText(21) = "Thirty"
       sNumberText(22) = "Forty"
       sNumberText(23) = "Fifty"
       sNumberText(24) = "Sixty"
       sNumberText(25) = "Seventy"
       sNumberText(26) = "Eighty"
       sNumberText(27) = "Ninety"
    End Sub
    
    Private Function IsBounded(vntArray As Variant) As Boolean
       On Error Resume Next
       IsBounded = IsNumeric(UBound(vntArray))
    End Function
    
    Private Function HundredsTensUnits(ByVal TestValue As Integer, _
                                  Optional bUseAnd As Boolean) As String
       Dim CardinalNumber As Integer
       If TestValue > 99 Then
          CardinalNumber = TestValue \ 100
          HundredsTensUnits = sNumberText(CardinalNumber) & " Hundred "
          TestValue = TestValue - (CardinalNumber * 100)
       End If
       If bUseAnd = True Then
          HundredsTensUnits = HundredsTensUnits & "and "
       End If
       If TestValue > 20 Then
          CardinalNumber = TestValue \ 10
          HundredsTensUnits = HundredsTensUnits & _
                              sNumberText(CardinalNumber + 18)
          TestValue = TestValue - (CardinalNumber * 10)
       End If
       If TestValue > 0 Then
          If Right(HundredsTensUnits, 1) <> " " Then HundredsTensUnits = HundredsTensUnits & "-"
          HundredsTensUnits = HundredsTensUnits & sNumberText(TestValue) & " "
       Else
          HundredsTensUnits = HundredsTensUnits & " "
       End If
       If Left(HundredsTensUnits, 1) = "-" Then HundredsTensUnits = Mid(HundredsTensUnits, 2)
    End Function

  8. #8
    Member
    Join Date
    Jun 2012
    Posts
    80
    Rep Power
    13
    Thanks a ton, Rick. Really appreciate it.

    Sorry for being such a pain in the arse and asking you soooooo many things.
    Last edited by DocAElstein; 10-22-2020 at 03:42 PM.

Similar Threads

  1. Test copy Activate Ribbon Tab In Excel 2007 2010 *
    By Excel Fox in forum Test Area
    Replies: 16
    Last Post: 01-22-2019, 05:05 PM
  2. Excel 2003 Classic Menu in Excel 2007-2010
    By Excel Fox in forum Classic Menu
    Replies: 7
    Last Post: 09-10-2014, 10:29 PM
  3. Excel 2007 - Freeze Pane without lines
    By irshath in forum Excel Help
    Replies: 1
    Last Post: 02-23-2013, 11:27 PM
  4. Replies: 1
    Last Post: 02-14-2013, 11:08 AM
  5. Excel 2003 Classic Menu for 2007-10
    By Admin in forum Greetings and Inception
    Replies: 0
    Last Post: 09-09-2011, 11:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •