spellnumber

M

mikey

The help i requested for number to to be spelled in indian rupees , i
have the excel vba code but do not how to get it run in excel , code
as below hope you can help me .

Sub Macro4()

words(0) = "Zero"
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"
words(20) = "Twenty"
words(21) = "Twenty One"
words(22) = "Twenty Two"
words(23) = "Twenty Three"
words(24) = "Twenty Four"
words(25) = "Twenty Five"
words(26) = "Twenty Six"
words(27) = "Twenty Seven"
words(28) = "Twenty Eight"
words(29) = "Twenty Nine"
words(30) = "Thirty"
words(31) = "Thirty One"
words(32) = "Thirty Two"
words(33) = "Thirty Three"
words(34) = "Thirty Four"
words(35) = "Thirty Five"
words(36) = "Thirty Six"
words(37) = "Thirty Seven"
words(38) = "Thirty Eight"
words(39) = "Thirty Nine"
words(40) = "Fourty"
words(41) = "Fourty One"
words(42) = "Fourty Two"
words(43) = "Fourty Three"
words(44) = "Fourty Four"
words(45) = "Fourty Five"
words(46) = "Fourty Six"
words(47) = "Fourty Seven"
words(48) = "Fourty Eight"
words(49) = "Fourty Nine"
words(50) = "Fifty"
words(51) = "Fifty One"
words(52) = "Fifty Two"
words(53) = "Fifty Three"
words(54) = "Fifty Four"
words(55) = "Fifty Five"
words(56) = "Fifty Six"
words(57) = "Fifty Seven"
words(58) = "Fifty Eight"
words(59) = "Fifty Nine"
words(60) = "Sixty"
words(61) = "Sixty One"
words(62) = "Sixty Two"
words(63) = "Sixty Three"
words(64) = "Sixty Four"
words(65) = "Sixty Five"
words(66) = "Sixty Six"
words(67) = "Sixty Seven"
words(68) = "Sixty Eight"
words(69) = "Sixty Nine"
words(70) = "Seventy"
words(71) = "Seventy One"
words(72) = "Seventy Two"
words(73) = "Seventy Three"
words(74) = "Seventy Four"
words(75) = "Seventy Five"
words(76) = "Seventy Six"
words(77) = "Seventy Seven"
words(78) = "Seventy Eight"
words(79) = "Seventy Nine"
words(80) = "Eighty"
words(81) = "Eighty One"
words(82) = "Eighty Two"
words(83) = "Eighty Three"
words(84) = "Eighty Four"
words(85) = "Eighty Five"
words(86) = "Eighty Six"
words(87) = "Eighty Seven"
words(88) = "Eighty Eight"
words(89) = "Eighty Nine"
words(90) = "Ninety"
words(91) = "Ninety One"
words(92) = "Ninety Two"
words(93) = "Ninety Three"
words(94) = "Ninety Four"
words(95) = "Ninety Five"
words(96) = "Ninety Six"
words(97) = "Ninety Seven"
words(98) = "Ninety Eight"
words(99) = "Ninety Nine"
words(100) = "One Hundred"
BINDRZ = kapil
PAST1 = BINDRZ - Int(BINDRZ)
PAST12 = PAST1 * 100
PAST2 = Int(PAST12)
PAST3 = Str(PAST2)
PAST4 = Val(PAST3)
JANTI = Str(Int(BINDRZ))
JANTI1 = Val(JANTI)
JANTI2 = Int(JANTI1)
AJEB1 = Str(JANTI2)
CRORE = " Crore "
Lakh = " Lakh "
thousand = " Thousand "
HUNDRED = " Hundred "
PAISE = "- Paise"
GXZP1 = " "
GXZP2 = " "
GXZP3 = " "
GXZP4 = " "
GXZP5 = " "
GXZP6 = " "
GXZP7 = " "
XYZ = "&"
RABJI1 = Trim(AJEB1)
If Len(RABJI1) > 7 Then
KARANT1 = Right(RABJI1, 9)
KARANT2 = Right(RABJI1, 7)
KARANT3 = Val(KARANT1) - Val(KARANT2)
KARANT4 = KARANT3 / 10000000
KARANT5 = Str(KARANT4)
KARANT6 = Right(KARANT5, 2)
KARANT7 = Val(KARANT6)
If KARANT7 > 0 Then
KARANT8 = words(KARANT7)
Else
KARANT8 = ""
CRORE = ""
GXZP1 = ""
End If
Else
KARANT8 = ""
CRORE = ""
GXZP1 = ""
End If
If Len(RABJI1) > 5 Then
LINZUR = Right(RABJI1, 5)
LINZUR2 = Val(RABJI1) - Val(LINZUR)
LINZUR3 = LINZUR2 / 100000
XORE6 = Str(LINZUR3)
XORE7 = Right(XORE6, 2)
XORE8 = Val(XORE7)
If XORE8 > 0 Then
LINZUR4 = words(XORE8)
Else
LINZUR4 = ""
Lakh = ""
GXZP2 = ""
End If
Else
LINZUR4 = ""
Lakh = ""
GXZP2 = ""
End If
If Len(RABJI1) > 3 Then
HARP1 = Right(RABJI1, 3)
HARP2 = Val(RABJI1) - Val(HARP1)
HARP3 = HARP2 / 1000
HARP4 = Str(HARP3)
HARP5 = Right(HARP4, 2)
HARP6 = Val(HARP5)
If HARP6 > 0 Then
HARP7 = words(HARP6)
Else
HARP7 = ""
thousand = ""
GXZP3 = ""
End If
Else
HARP7 = ""
thousand = ""
GXZP3 = ""
End If
If Len(RABJI1) > 2 Then
BORIS = Right(RABJI1, 3)
CYRUS = Left(BORIS, 1)
CYROS1 = Val(CYRUS)
If CYROS1 > 0 Then
ERUPT = words(CYROS1)
Else
ERUPT = ""
HUNDRED = ""
GXZP4 = ""
End If
Else
ERUPT = ""
HUNDRED = ""
GXZP4 = ""
End If
If Len(RABJI1) > 1 Then
ZZART = Right(RABJI1, 2)
DOZAL = Val(ZZART)
If DOZAL > 0 Then
FLITIES = words(DOZAL)
Else
FLITIES = ""
XYZ = ""
End If
Else
FLITIES = ""
XYZ = ""
End If
If Len(RABJI1) < 2 And Len(RABJI1) > 0 Then
GRIPP1 = Left(RABJI1, 1)
GRIPP2 = Val(GRIPP1)
XYZ = ""
GXZP5 = ""
GXZP6 = ""
If GRIPP2 > 0 Then
GRIPP3 = words(GRIPP2)
Else
GRIPP3 = ""
End If
Else
GRIPP3 = ""
End If
If Len(RABJI1) < 3 And Len(RABJI1) > 0 Then
XYZ = ""
GXZP5 = ""
GXZP6 = ""
End If
If PAST4 > 0 Then
PAST5 = words(PAST4)
Else
PAST5 = ""
PAISE = ""
GXZP7 = ""
End If
XYME = Val(AJEB1)
ghau = "Rupees " + Trim(KARANT8) + GXZP1 + Trim(CRORE) + GXZP1 +
Trim(LINZUR4) + GXZP2 + Trim(Lakh) + GXZP2 + Trim(HARP7) + GXZP3 +
Trim(thousand) + GXZP3 + Trim(ERUPT) + GXZP4 + Trim(HUNDRED) + GXZP5 +
XYZ + GXZP4 + Trim(FLITIES) + GXZP6 + Trim(GRIPP3) + GXZP7 +
Trim(PAISE) + GXZP7 + Trim(PAST5) + " Only"
ActiveCell.Formula = ghau



End Sub
 
D

David McRitchie

Hi Mikey,
You must include all of the code, you left out the following
which were above the Sub Macro4
Public kapil As Double
Public words(100) As String
and it works after commenting out Option Explicit if you have
one of your own.

But would instead suggest making this a User Defined Function

You will have to comment out your own (if present)
' Option Explicit
and comment out
'Attribute Macro4.VB_Description = "Macro recorded 05/01/01 by Shailesh Agrawal"
'Attribute Macro4.VB_ProcData.VB_Invoke_Func = "Normal.NewMacros.Macro4"

Would suggest changing Sub Macro4( )
to a function of your own choosing i.e. Rupees_as_text



'Option Explicit
'Attribute VB_Name = "NewMacros"
'Public kapil As Double
'Public words(100) As String
Function Rupees_as_text(kapil As Double)
Dim words(100) As String
'Attribute Macro4.VB_Description = "Macro recorded 05/01/01 by Shailesh Agrawal"
'Attribute Macro4.VB_ProcData.VB_Invoke_Func = "Normal.NewMacros.Macro4"

...... and at the bottom assign ghau to the function name Rupees_as_text
'ActiveCell.Formula = ghau
Rupees_as_text = ghau
End Function

to use as a UDF
b30: 1234,567
b31: 589421

c30: =Agrawal(B30)
c31: =Agrawal(b31)

c30 and c31 display as
Rupees Twelve Lakh Thirty Four Thousand Five Hundred & Sixty Seven Only
Rupees Five Lakh Eighty Nine Thousand Four Hundred & Twenty One Only

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
M

mikey

Hi, David

After incorporating the changes ,i compiled it inthe degub option an
run
but on tpying the =agrawal() gives error #Names? . Codes as belo
,what
changes do i need to do . PLeas make changes below so as to copy past
directly and run .

Attribute VB_Name = "NewMacros"
Public kapil As Double
Public words(100) As String
Function Rupees_as_text(kapil As Double)
Dim words(100) As String
Attribute Macro4.VB_Description = "Macro recorded 05/01/01 by Shailes
Agrawal"
Attribute Macro4.VB_ProcData.VB_Invoke_Func
"Normal.NewMacros.Macro4"
' Your comments and Feed back will be most appreciated.
' Macro4 Macro
' Macro recorded 05/01/2001 by Shailesh Agrawal Associates
' This macro is available free of cost at www.CharteredValuer.com
' For Further inquiry feel free to contact o
(e-mail address removed)
' This is distributed as freeware. We do not take any responsiblity fo
any problem
' due to this macro
'
kapil = ActiveCell.Value

Function Rupees_as_text()

words(0) = "Zero"
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"
words(20) = "Twenty"
words(21) = "Twenty One"
words(22) = "Twenty Two"
words(23) = "Twenty Three"
words(24) = "Twenty Four"
words(25) = "Twenty Five"
words(26) = "Twenty Six"
words(27) = "Twenty Seven"
words(28) = "Twenty Eight"
words(29) = "Twenty Nine"
words(30) = "Thirty"
words(31) = "Thirty One"
words(32) = "Thirty Two"
words(33) = "Thirty Three"
words(34) = "Thirty Four"
words(35) = "Thirty Five"
words(36) = "Thirty Six"
words(37) = "Thirty Seven"
words(38) = "Thirty Eight"
words(39) = "Thirty Nine"
words(40) = "Fourty"
words(41) = "Fourty One"
words(42) = "Fourty Two"
words(43) = "Fourty Three"
words(44) = "Fourty Four"
words(45) = "Fourty Five"
words(46) = "Fourty Six"
words(47) = "Fourty Seven"
words(48) = "Fourty Eight"
words(49) = "Fourty Nine"
words(50) = "Fifty"
words(51) = "Fifty One"
words(52) = "Fifty Two"
words(53) = "Fifty Three"
words(54) = "Fifty Four"
words(55) = "Fifty Five"
words(56) = "Fifty Six"
words(57) = "Fifty Seven"
words(58) = "Fifty Eight"
words(59) = "Fifty Nine"
words(60) = "Sixty"
words(61) = "Sixty One"
words(62) = "Sixty Two"
words(63) = "Sixty Three"
words(64) = "Sixty Four"
words(65) = "Sixty Five"
words(66) = "Sixty Six"
words(67) = "Sixty Seven"
words(68) = "Sixty Eight"
words(69) = "Sixty Nine"
words(70) = "Seventy"
words(71) = "Seventy One"
words(72) = "Seventy Two"
words(73) = "Seventy Three"
words(74) = "Seventy Four"
words(75) = "Seventy Five"
words(76) = "Seventy Six"
words(77) = "Seventy Seven"
words(78) = "Seventy Eight"
words(79) = "Seventy Nine"
words(80) = "Eighty"
words(81) = "Eighty One"
words(82) = "Eighty Two"
words(83) = "Eighty Three"
words(84) = "Eighty Four"
words(85) = "Eighty Five"
words(86) = "Eighty Six"
words(87) = "Eighty Seven"
words(88) = "Eighty Eight"
words(89) = "Eighty Nine"
words(90) = "Ninety"
words(91) = "Ninety One"
words(92) = "Ninety Two"
words(93) = "Ninety Three"
words(94) = "Ninety Four"
words(95) = "Ninety Five"
words(96) = "Ninety Six"
words(97) = "Ninety Seven"
words(98) = "Ninety Eight"
words(99) = "Ninety Nine"
words(100) = "One Hundred"
BINDRZ = kapil
PAST1 = BINDRZ - Int(BINDRZ)
PAST12 = PAST1 * 100
PAST2 = Int(PAST12)
PAST3 = Str(PAST2)
PAST4 = Val(PAST3)
JANTI = Str(Int(BINDRZ))
JANTI1 = Val(JANTI)
JANTI2 = Int(JANTI1)
AJEB1 = Str(JANTI2)
CRORE = " Crore "
Lakh = " Lakh "
thousand = " Thousand "
HUNDRED = " Hundred "
PAISE = "- Paise"
GXZP1 = " "
GXZP2 = " "
GXZP3 = " "
GXZP4 = " "
GXZP5 = " "
GXZP6 = " "
GXZP7 = " "
XYZ = "&"
RABJI1 = Trim(AJEB1)
If Len(RABJI1) > 7 Then
KARANT1 = Right(RABJI1, 9)
KARANT2 = Right(RABJI1, 7)
KARANT3 = Val(KARANT1) - Val(KARANT2)
KARANT4 = KARANT3 / 10000000
KARANT5 = Str(KARANT4)
KARANT6 = Right(KARANT5, 2)
KARANT7 = Val(KARANT6)
If KARANT7 > 0 Then
KARANT8 = words(KARANT7)
Else
KARANT8 = ""
CRORE = ""
GXZP1 = ""
End If
Else
KARANT8 = ""
CRORE = ""
GXZP1 = ""
End If
If Len(RABJI1) > 5 Then
LINZUR = Right(RABJI1, 5)
LINZUR2 = Val(RABJI1) - Val(LINZUR)
LINZUR3 = LINZUR2 / 100000
XORE6 = Str(LINZUR3)
XORE7 = Right(XORE6, 2)
XORE8 = Val(XORE7)
If XORE8 > 0 Then
LINZUR4 = words(XORE8)
Else
LINZUR4 = ""
Lakh = ""
GXZP2 = ""
End If
Else
LINZUR4 = ""
Lakh = ""
GXZP2 = ""
End If
If Len(RABJI1) > 3 Then
HARP1 = Right(RABJI1, 3)
HARP2 = Val(RABJI1) - Val(HARP1)
HARP3 = HARP2 / 1000
HARP4 = Str(HARP3)
HARP5 = Right(HARP4, 2)
HARP6 = Val(HARP5)
If HARP6 > 0 Then
HARP7 = words(HARP6)
Else
HARP7 = ""
thousand = ""
GXZP3 = ""
End If
Else
HARP7 = ""
thousand = ""
GXZP3 = ""
End If
If Len(RABJI1) > 2 Then
BORIS = Right(RABJI1, 3)
CYRUS = Left(BORIS, 1)
CYROS1 = Val(CYRUS)
If CYROS1 > 0 Then
ERUPT = words(CYROS1)
Else
ERUPT = ""
HUNDRED = ""
GXZP4 = ""
End If
Else
ERUPT = ""
HUNDRED = ""
GXZP4 = ""
End If
If Len(RABJI1) > 1 Then
ZZART = Right(RABJI1, 2)
DOZAL = Val(ZZART)
If DOZAL > 0 Then
FLITIES = words(DOZAL)
Else
FLITIES = ""
XYZ = ""
End If
Else
FLITIES = ""
XYZ = ""
End If
If Len(RABJI1) < 2 And Len(RABJI1) > 0 Then
GRIPP1 = Left(RABJI1, 1)
GRIPP2 = Val(GRIPP1)
XYZ = ""
GXZP5 = ""
GXZP6 = ""
If GRIPP2 > 0 Then
GRIPP3 = words(GRIPP2)
Else
GRIPP3 = ""
End If
Else
GRIPP3 = ""
End If
If Len(RABJI1) < 3 And Len(RABJI1) > 0 Then
XYZ = ""
GXZP5 = ""
GXZP6 = ""
End If
If PAST4 > 0 Then
PAST5 = words(PAST4)
Else
PAST5 = ""
PAISE = ""
GXZP7 = ""
End If
XYME = Val(AJEB1)
ghau = "Rupees " + Trim(KARANT8) + GXZP1 + Trim(CRORE) + GXZP1 +
Trim(LINZUR4) + GXZP2 + Trim(Lakh) + GXZP2 + Trim(HARP7) + GXZP3 +
Trim(thousand) + GXZP3 + Trim(ERUPT) + GXZP4 + Trim(HUNDRED) + GXZP5 +
XYZ + GXZP4 + Trim(FLITIES) + GXZP6 + Trim(GRIPP3) + GXZP7 +
Trim(PAISE) + GXZP7 + Trim(PAST5) + " Only"
ActiveCell.Formula = ghau
Rupees_as_text = ghua

End Function
 
D

David McRitchie

Hi Mikey,
remove or comment out the first three lines. Note the
Dim Words(100) As String
is inside the Function and that kapil is now a parameter
of the function.

I changed the name of the function to make more sense, so
perhaps parts of my same reply had two different names. Use...
=Rupees_as_text(9999999.99) --- if in same workbook
=Rupees_as_text(C9)
=personal.xls!Rupees_as_text(C9) --- from a different workbook
and you MUST have an argument for this function.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with Function to Convert Number To Words 9
How realizes the following request 5
Display Numbers as Words 3
Convert Number to words 1
Help with Number To Words Code for Indian Currency 9
convert number to intext 0
convert figures into words on Microsft Excel 3
Conversion from Number to Word!! Aki 1

Top