# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  NEED HELP FOR SPELL NUMBER FUNCTION (its not working for all excel sheet)

## saquibansari

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

----------


## LokeshKumar

Sorry.......Please upload the workbook for better clarification.....

----------


## ashishmehra2010

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 :Smilie:

----------


## saquibansari

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

----------


## saquibansari

lokes these r two file

----------


## saquibansari

Ashish,

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

----------


## LokeshKumar

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.......
read from this.....https://support.office.com/en-nz/art...0-6e4c3f5ee566

----------


## RIZVI

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

----------

