I was trying to get the person who wrote the Subroutine to change
their website, to make it a Function instead. I think the poster
asking the question came back again at much later time, and I
sent him an example spreadsheet.
here is the function:
Option Explicit
'-- obtained from
http://www.CharteredValuer.com
'--
http://pun1ww1-a.sancharnet.in:83/akl_valuer/index.htm
Function Rupees_as_text(kapil As Double)
'Attribute Macro4.VB_Description = "Macro recorded 05/01/01 by Shailesh 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 on (e-mail address removed)
' This is distributed as freeware. We do not take any responsiblity for any problem
' due to this macro
'
Dim words(100) As String
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"
Dim GHAU As String
Dim CRORE As String
Dim LAKH As String
Dim thousand As String, ERUPT As Variant
Dim HUNDRED As String
'==========================================
Dim AJEB1 As Variant
Dim BINDRZ As Variant
Dim BOROS As Variant, BORIS As Variant
Dim BORUS As Variant, CYRUS As Variant, CYROS1 As Variant
Dim DOZAL As Variant
Dim GRIPP1 As String
Dim GRIPP2 As Long
Dim GRIPP3 As Variant, PAISE As String
Dim GXZP1 As String, GXZP2 As String, GXZP3 As String
Dim GXZP4 As String, GXZP5 As String, GXZP6 As String, GXZP7 As String
Dim HARP1 As Variant, HARP2 As Variant, HARP3 As Variant
Dim HARP4 As Variant, HARP5 As Variant, HARP6 As Variant
Dim HARP7 As Variant
Dim JANTI As Variant, JANTI1 As Variant, JANTI2 As Variant, JANTI3 As Variant
Dim KARANT1 As Variant, KARANT2 As Variant, KARANT3 As Variant
Dim KARANT4 As Variant, KARANT5 As Variant, KARANT6 As Variant
Dim KARANT7 As Variant, KARANT8 As Variant
Dim LINZUR1 As Variant, LINZUR2 As Variant, LINZUR3 As Variant
Dim LINZUR4 As Variant, LINZUR5 As Variant, LINZUR As Variant
Dim PAST1 As Variant, PAST2 As Variant, PAST3 As Variant
Dim PAST4 As Variant, PAST5 As Variant, PAST12 As Variant
Dim RABJI1 As String
Dim XORE6 As String
Dim XORE7 As Variant, XORE8 As Variant
Dim XYME As Variant
Dim XYZ As String, FLITIES As Variant
Dim ZZART As Variant
GXZP6 = " "
GXZP7 = " "
'-- Limits in place 2004-09-23 ...
If kapil = 0 Then
Rupees_as_text = "**VOID** **VOID** **VOID** **VOID** **VOID**" _
& " **VOID** **VOID** **VOID** **VOID** **VOID**"
Exit Function
End If
If kapil > 999999999.99 Then
Rupees_as_text = "**VOID## **VOID** **VOID** **VOID** **VOID**" _
& " **VOID** **VOID** **VOID** **VOID** **VOID**"
Exit Function
End If
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"
Rupees_as_text = GHAU
End Function
Formula in B6: =A6
Format in B6: [>=10000000]##\,##\,##\,##0.00;[>=100000] ##\,##\,##0.00;##,##0.00
Formula in C6: =Rupees_as_text(A6)
12345678.99
20968481.19
989,999,999.99
990968481.19
2096848119
-------
0.00
1,23,45,678.99
2,09,68,481.19
98,99,99,999.99
99,09,68,481.19
209,68,48,119.00
-----
**VOID** **VOID** **VOID** **VOID** **VOID** **VOID** **VOID** **VOID** **VOID** **VOID**
Rupees One Crore Twenty Three Lakh Fourty Five Thousand Six Hundred & Seventy Eight - Paise Ninety Nine Only
Rupees Two Crore Nine Lakh Sixty Eight Thousand Four Hundred & Eighty One - Paise Nineteen Only
Rupees Ninety Eight Crore Ninety Nine Lakh Ninety Nine Thousand Nine Hundred & Ninety Nine - Paise Ninety Nine Only
Rupees Ninety Nine Crore Nine Lakh Sixty Eight Thousand Four Hundred & Eighty One - Paise Nineteen Only
**VOID## **VOID** **VOID** **VOID** **VOID** **VOID** **VOID** **VOID** **VOID** **VOID**
--
