Problem with Function to Convert Number To Words

[prkhan56]

Hello All Experts,
I have obtained the following function from the Net (not sure from
which site)
It converts Numbers to Words in Indian Style Currency Format

It has following problem displaying for Unit and Tens Only for eg.
1 - is displayed as One Thousand One Only
2 - is displayed as Two Thousand Two Only
....
....
....
99 - is displayed as Ninety Nine Thousand Ninety Nine Only

1.10 - is displayed as One Thousand One And Ten Paisa Only
12.12 - is displayed as Rupees Twelve Thousand Twelve and Twelve
Paisa only
....
....

Rest all format from Hundreds onwards i.e. 123.45, 1234.56, 12345.67,
etc....are working perfect.
Can anyone help me in rectifying the problems of Unit and Tens?

I am using Excel 2003
Thanks in advance

Rashid Khan



Option Explicit

' Function for conversion of a Currency to words
' Parameter - accept a Currency
' Returns the number in words format
'*************************************************

Function CurrencyToWord(ByVal MyNumber)
Dim Temp
Dim Rupees, Paisa As String
Dim DecimalPlace, iCount
Dim Hundred, Words As String
ReDim Place(9) As String
Place(0) = " Thousand "
Place(2) = " Lakh "
Place(4) = " Crore "
Place(6) = " Arab "
Place(8) = " Kharab "
On Error Resume Next
' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumber))

' Find decimal place.
DecimalPlace = InStr(MyNumber, ".")

' If we find decimal place...
If DecimalPlace > 0 Then
' Convert Paisa
Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
Paisa = " and " & ConvertTens(Temp) & " Paisa"

' Strip off paisa from remainder to convert.
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

' Convert last 3 digits of MyNumber to rupees in word.
Hundred = ConvertHundred(Right(MyNumber, 3))
' Strip off last three digits
MyNumber = Left(MyNumber, Len(MyNumber) - 3)

iCount = 0
Do While MyNumber <> ""
'Strip last two digits
Temp = Right(MyNumber, 2)
If Len(MyNumber) = 1 Then
Words = ConvertDigit(Temp) & Place(iCount) & Words
MyNumber = Left(MyNumber, Len(MyNumber) - 1)

Else
Words = ConvertTens(Temp) & Place(iCount) & Words
MyNumber = Left(MyNumber, Len(MyNumber) - 2)
End If
iCount = iCount + 2
Loop

CurrencyToWord = "Rupees " & Words & Hundred & Paisa & " only"
End Function

' Conversion for Hundred
'*****************************************
Private Function ConvertHundred(ByVal MyNumber)
Dim Result As String

' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function

' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)

' Do we have a Hundred place digit to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
End If

' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & ConvertTens(Mid(MyNumber, 2))
Else
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mid(MyNumber, 3))
End If

ConvertHundred = Trim(Result)
End Function

' Conversion for tens
'*****************************************
Private Function ConvertTens(ByVal MyTens)
Dim Result As String

' Is value between 10 and 19?
If Val(Left(MyTens, 1)) = 1 Then
Select Case Val(MyTens)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
' .. otherwise it's between 20 and 99.
Select Case Val(Left(MyTens, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select

' Convert ones place digit.
Result = Result & ConvertDigit(Right(MyTens, 1))
End If

ConvertTens = Result
End Function

Private Function ConvertDigit(ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select
End Function

 

[David McRitchie]

Would this suit your purpose and test correctly ?
http://groups.google.com/groups?threadm=u0g#[email protected]

 

[prkhan56]

Hi Dave,
The link says "Sorry Document not found"

Any clue?

Rashid Khan

 

[David McRitchie]

The link
http://groups.google.com/groups?threadm=u0g#[email protected]

works fine for me, try one of these
http://groups.google.co.uk/groups?threadm=u0g#[email protected]

http://groups.google.com/group/micr...9c09e4357e4/fdfcbfb814476b90#fdfcbfb814476b90

Let me know if the original links works now or not, the second is the same
but a UK link instead of af .com link and the last one is what Google is trying
to foist on us and proprietize things by not including the message id.

 

[prkhan56]

Hello David,

Both the links says "Sorry docuement not found"

the following works
http://groups.google.com/group/micr...9c09e4357e4/fdfcbfb814476b90#fdfcbfb814476b90

I have the code referred to in the above thread - but due to my limited
knowledge, I dont know what to do with it....it is a Sub and not a
Function.. so I dont know how to make it work...
My previous code posted used to work like =CurrencyToWord(A1).. but I
dont know how to get the Sub to work

Thanks for your time

Rashid Khan

 

[David McRitchie]

Hi Rashid,

It came from http://pun1ww1-a.sancharnet.in:83/shaileshagrawal/down.htm
(actually the site is redirected from http://www.charteredvaluer.com )
by Shailesh Agrawa and he still has his site, but he never finished up the suggestion
to make it into a function. What I posted was a function -- I did not give it a name

Try this link (you want to look at original code, not HTML code) this is
a later posting where I placed a decent function name on the code.
http://groups.google.com/group/microsoft.public.excel/msg/244d8953bc2c0be5?dmode=source&hl=en

to see the entire thread as HTML
http://groups.google.com/group/micr...14d2a6b5fbe/244d8953bc2c0be5#244d8953bc2c0be5

I don't know why you could only read 1 of the 3 links provided before
they should all be the same. What browser and version are you using
and what operating system. I've not had trouble with such links not working
at Google. Though I have had problems with Google rendering code in HTML
and have to use options, original

I have no trouble browsing with either
Firefox 1.0.6   or with  Internet Explorer 6.0.2800.1106
running under  Windows 2000   Version 5.0 (Build 2195: Service Pack 4)

Actually it is more likely to be your newsreader that is messing up,  in
Outlook Express the links will wrap properly, at least for me.

 

[David McRitchie]

Hi Rashid,
I just noticed that your newsgroup browser might be Google since you posted from there.
Google is destroying the links because they have an at-sign (@), and they are
destroyed even if you look at the original code. I had thought they had
improved to at least stop messing up urls but that is certainly not the case.
If you were using a real newsgroup reader you would not have the problem.

Web based newsgroup reading allow those behind firewalls to access
newsgroups as web pages, but is not much of a substitute for accessing
newsgroups with a decent newsgroup reader, which will read in all new postings
so you are not delayed.

The intent is to reduce harvesting of email addresses by robots reading
Google and Google Groups. You have no protection by those other
reposters of newsgroup postings that mess up web searches by
rehashing newsgroup postings -- some of which have forum in their name

 

[prkhan56]

Hi David,
Thanks for your guidance.
But how can I rectify... I am quoting here my problem about configuring
Outlook Express for Newsgroup configuration but no one replied

quote
I am using Windows XP SP-2 and when I try to configure the newsgroup I
get the following error..

Server: msnews.microsoft.com
Protocol: NNTP
Port: 119
Secure(SSL): 0
Code: 800ccc0e

My hotmail account works perfect with Outlook Express 6.00 but not the
newsgroup..


Can anybody give me a clue?
unquote

What solution do you suggest...
Or is there any other Newsgroup Reader I can use?
I log in using my gmail account...
Thanks
Rashid Khan

 

[Dave Peterson]

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google to search for stuff you've posted (and find the replies,
too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

 

[prkhan56]

Thanks Dave,
Would go through all the links...

Rashid Khan