# NEED HELP FOR SPELL NUMBER FUNCTION (its not working for all excel sheet)

1. ## NEED HELP FOR SPELL NUMBER FUNCTION (its not working for all excel sheet)

hello
i have code to covert number to word but problem is its working only for current excel sheet till the time i am using when i closed excel and reopen g its doesn't work. how can i make it work for every excel...

STEPS i m using is for SPELLNUMBER...
1. COPY CODE THEN EXCEL (ALT+F11) THEN INSERT MODULE PAST FORMULA AFTER THAT SAVE AS (macro enable worksheet.xlsm) in C Drive.

i m using excel 2007.

Thanks & Regards
Saquib Ansari
ansarisaq@gmail.com
Mumbai India  Register To Reply

2. ## Re: NEED HELP FOR SPELL NUMBER FUNCTION (its not working for all excel sheet)

Sorry.......Please upload the workbook for better clarification.....  Register To Reply

3. ## Re: NEED HELP FOR SPELL NUMBER FUNCTION (its not working for all excel sheet)

Hi Saquib,

It seems there can be two scenarios:

1) You have a code which you need to save in Worksheet Module (if it starts with ``Please Login or Register  to view this content.``
2) You have code which you need to save in standard module. (if it starts with some thing like ``Please Login or Register  to view this content.``
Regards,
AM   Register To Reply

4. ## Re: NEED HELP FOR SPELL NUMBER FUNCTION (its not working for all excel sheet)

Lokesh,

below mentioned is code i m using, if u feel to make any change pls let me know or do it from ur end and send me edited code.

Function SpellNumber(amt As Variant) As Variant
Dim FIGURE As Variant
Dim LENFIG As Integer
Dim i As Integer
Dim WORDs(19) As String
Dim tens(9) As String
WORDs(1) = "One"
WORDs(2) = "Two"
WORDs(3) = "Three"
WORDs(4) = "Four"
WORDs(5) = "Five"
WORDs(6) = "Six"
WORDs(7) = "Seven"
WORDs(8) = "Eight"
WORDs(9) = "Nine"
WORDs(10) = "Ten"
WORDs(11) = "Eleven"
WORDs(12) = "Twelve"
WORDs(13) = "Thirteen"
WORDs(14) = "Fourteen"
WORDs(15) = "Fifteen"
WORDs(16) = "Sixteen"
WORDs(17) = "Seventeen"
WORDs(18) = "Eighteen"
WORDs(19) = "Nineteen"
tens(2) = "Twenty"
tens(3) = "Thirty"
tens(4) = "Fourty"
tens(5) = "Fifty"
tens(6) = "Sixty"
tens(7) = "Seventy"
tens(8) = "Eighty"
tens(9) = "Ninety"
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
FIGLEN = Len(FIGURE)
If FIGLEN < 12 Then
FIGURE = Space(12 - FIGLEN) & FIGURE
End If
If Val(Left(FIGURE, 9)) > 1 Then
SpellNumber = "Rupees "
ElseIf Val(Left(FIGURE, 9)) = 1 Then
SpellNumber = "Rupee "
End If
For i = 1 To 3
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
If i = 1 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " Crore "
ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " Lakh "
ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " Thousand "
End If
FIGURE = Mid(FIGURE, 3)
Next i
If Val(Left(FIGURE, 1)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 1))) + " Hundred "
End If
FIGURE = Mid(FIGURE, 2)
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
FIGURE = Mid(FIGURE, 4)
If Val(FIGURE) > 0 Then
SpellNumber = SpellNumber & " Paise "
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
End If
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
If Val(FIGURE) > 0 Then
SpellNumber = SpellNumber & " Only "
End If
End Function  Register To Reply

5. ## Re: NEED HELP FOR SPELL NUMBER FUNCTION (its not working for all excel sheet)

lokes these r two file  Register To Reply

6. ## Re: NEED HELP FOR SPELL NUMBER FUNCTION (its not working for all excel sheet)

Ashish,

please find attached codes for spell number and excel sheet where i save the codes.  Register To Reply

7. ## Re: NEED HELP FOR SPELL NUMBER FUNCTION (its not working for all excel sheet)

Hi,
Your code is working fine.....
I guess you want you can use this function to any file.....you have to declare it as public and you need to save a personal macro book.....which always have your code and always remain hide.......  Register To Reply

8. ## Re: NEED HELP FOR SPELL NUMBER FUNCTION (its not working for all excel sheet)

Hi Saquib,

appreciate if you can assist me with your own code on SPELLNUMBER.

1. for example, convert 345,987.78 to appear as "(USD: Three hundred & Forty Five thousand Nine hundred & Eighty Seven and Cents Seventy Eight only)". observed in your code that two words are pasted together in between.
2. as above but without the brackets and "USD"........ something like "Three ........ Eight only"

many thanks

Rizvi  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1