Re: spellnumber
Norman Harker
Here are some custom currency formats originally posted by Bill
Manville.
Rupees with Paise
[>9999999]"Rs."##\,##\,##\,##\,##0.00;[>99999]"Rs."##\,##\,##0.00;"Rs."##,##000
Rupees without Paise
[>9999999]"Rs."##\,##\,##\,##\,##0;[>99999]"Rs."##\,##\,##0;"Rs."##,##0
Rupees without Rs. notation
[>9999999]##\,##\,##\,##0.00;[>99999]"##\,##\,##0.00;"##,##0.00
And here's a worksheet_change event handler that covers negative
numbers as well:
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("A1:A20")) Is Nothing Then
Exit Sub
End If
With Target
Select Case Len(Abs(Int(.Value)))
Case Is <= 3
.NumberFormat = "###.00;(###.00)"
Case Is <= 5
.NumberFormat = "##,###.00;(##,###.00)"
Case Is <= 7
.NumberFormat = "#\,##\,###.00;(#\,##\,###.00)"
Case Is <= 9
.NumberFormat = "#\,##\,##\,###.00;(#\,##\,##\,###.00)"
Case Is <= 11
.NumberFormat = "#\,##\,##\,##\,###.00;(#\,##\,##\,##\,###.00)"
End Select
End With
End Sub
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njha...@optusnet.com.au
"mikey >" <<mikey....@excelforum-nospam.com> wrote in message
news:mikey....@excelforum-nospam.com...
>I need spellnumber macro to show indian rupees (like crores ,lakhs
> ,thousand and paise ) and also to view the number as 1,50,000.00
> urgent
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>
Norman Harker
I doubt that there's any code out there that has done this, so you are
going to have to roll your own.
You might start at:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;213360
David McRitchie
Did you write the code or did you get if from somewhere, if so where.
It doesn't look anything like what I'm used to seeing such as links in
http://www.mvps.org/dmcritchie/excel/strings.htm
which would have Function not Sub.
For instructions to install a macro or a function see
http://www.mvps.org/dmcritchie/excel/getstarted.htm
I gave your macro two shots and it is not in working order.
---
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
"mikey >" <<mikey....@excelforum-nospam.com> wrote in message news:mikey....@excelforum-nospam.com...
> 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
David McRitchie
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
"mikey >" <<mikey....@excelforum-nospam.com> wrote in message news:mikey....@excelforum-nospam.com...
> Hi,
>
> I did not write the code ,i have got the code from website
> www.charteredvaluer.com. I tried to run the macro but without
> result , i have no clue about prog. in vba
David McRitchie
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.
---
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
"mikey >" <<mikey....@excelforum-nospam.com> wrote
> After incorporating the changes ,i compiled it in the degub option and
> run but on typing the =agrawal() gives error #Names? . Codes as
> below, what > changes do I need to do Please make changes below
> so as to copy paste directly and run .
>