How to convert numbers to words in Excel VBA

ipi99 · Aug 5th 2020

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

Hello Everyone

Good day,

I'm using below VBA code for last 1 years and today I discovered the small error in VBA code .

When I write minus -10,000 it shows on "Only Hundred Ten Thousand"

When I write positive 10,000 it shows on "Only Ten Thousand" works fine

Can someone please correct my VBA code. You great help will be highly appreciated

Below example:

(10,000.99) Only Hundred Ten Thousand & 99/100 Riyals

10,000.99 Only Ten Thousand & 99/100 Riyals

(10,000,000.99) Only Hundred Ten Million & 99/100 Riyals

10,000,000.99 Only Ten Million & 99/100 Riyals

(10,000,000,000.99) Only Hundred Ten Billion & 99/100 Riyals

10,000,000,000.99 Only Ten Billion & 99/100 Riyals

(10,000,000,000,000.90) Only Hundred Ten Trillion & 90/100 Riyals

10,000,000,000,000.90 Only Ten Trillion & 90/100 Riyals

Formula =spellbilling

MY VBA CODE:

Code

'Main Function
Function SpellBilling(ByVal MyNumber)
Dim Riyals, Halalas, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert Halalas and set MyNumber to Riyal amount.
If DecimalPlace > 0 Then
Halalas = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Riyals = Temp & Place(Count) & Riyals
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Riyals
Case ""
Riyals = "No Riyal"
Case "One"
Riyals = "Only One Riyal"
Case Else
Riyals = "Only " & Riyals
Riyals = Riyals & ""
End Select
Select Case Halalas
Case ""
Halalas = " & 00/00 Riyals"
Case "One"
Halalas = " & 01/100 Riyals"
Case "Two"
Halalas = " & 02/100 Riyals"
Case "Three"
Halalas = " & 03/100 Riyals"
Case "Four"
Halalas = " & 04/100 Riyals"
Case "Five"
Halalas = " & 05/100 Riyals"
Case "Six"
Halalas = " & 06/100 Riyals"
Case "Seven"
Halalas = " & 07/100 Riyals"
Case "Eight"
Halalas = " & 08/100 Riyals"
Case "Nine"
Halalas = " & 09/100 Riyals"
Case "Ten"
Halalas = " & 10/100 Riyals"
Case "Eleven"
Halalas = " & 11/100 Riyals"
Case "Twelve"
Halalas = " & 12/100 Riyals"
Case "Thirteen"
Halalas = " & 13/100 Riyals"
Case "Fourteen"
Halalas = " & 14/100 Riyals"
Case "Fifteen"
Halalas = " & 15/100 Riyals"
Case "Sixteen"
Halalas = " & 16/100 Riyals"
Case "Seventeen"
Halalas = " & 17/100 Riyals"
Case "Eighteen"
Halalas = " & 18/100 Riyals"
Case "Nineteen"
Halalas = " & 19/100 Riyals"
Case "Twenty "
Halalas = " & 20/100 Riyals"
Case "Twenty One"
Halalas = " & 21/100 Riyals"
Case "Twenty Two"
Halalas = " & 22/100 Riyals"
Case "Twenty Three"
Halalas = " & 23/100 Riyals"
Case "Twenty Four"
Halalas = " & 24/100 Riyals"
Case "Twenty Five"
Halalas = " & 25/100 Riyals"
Case "Twenty Six"
Halalas = " & 26/100 Riyals"
Case "Twenty Seven"
Halalas = " & 27/100 Riyals"
Case "Twenty Eight"
Halalas = " & 28/100 Riyals"
Case "Twenty Nine"
Halalas = " & 29/100 Riyals"
Case "Thirty "
Halalas = " & 30/100 Riyals"
Case "Thirty One"
Halalas = " & 31/100 Riyals"
Case "Thirty Two"
Halalas = " & 32/100 Riyals"
Case "Thirty Three"
Halalas = " & 33/100 Riyals"
Case "Thirty Four"
Halalas = " & 34/100 Riyals"
Case "Thirty Five"
Halalas = " & 35/100 Riyals"
Case "Thirty Six"
Halalas = " & 36/100 Riyals"
Case "Thirty Seven"
Halalas = " & 37/100 Riyals"
Case "Thirty Eight"
Halalas = " & 38/100 Riyals"
Case "Thirty Nine"
Halalas = " & 39/100 Riyals"
Case "Forty "
Halalas = " & 40/100 Riyals"
Case "Forty One"
Halalas = " & 41/100 Riyals"
Case "Forty Two"
Halalas = " & 42/100 Riyals"
Case "Forty Three"
Halalas = " & 43/100 Riyals"
Case "Forty Four"
Halalas = " & 44/100 Riyals"
Case "Forty Five"
Halalas = " & 45/100 Riyals"
Case "Forty Six"
Halalas = " & 46/100 Riyals"
Case "Forty Seven"
Halalas = " & 47/100 Riyals"
Case "Forty Eight"
Halalas = " & 48/100 Riyals"
Case "Forty Nine"
Halalas = " & 49/100 Riyals"
Case "Fifty "
Halalas = " & 50/100 Riyals"
Case "Fifty One"
Halalas = " & 51/100 Riyals"
Case "Fifty Two"
Halalas = " & 52/100 Riyals"
Case "Fifty Three"
Halalas = " & 53/100 Riyals"
Case "Fifty Four"
Halalas = " & 54/100 Riyals"
Case "Fifty Five"
Halalas = " & 55/100 Riyals"
Case "Fifty Six"
Halalas = " & 56/100 Riyals"
Case "Fifty Seven"
Halalas = " & 57/100 Riyals"
Case "Fifty Eight"
Halalas = " & 58/100 Riyals"
Case "Fifty Nine"
Halalas = " & 59/100 Riyals"
Case "Sixty "
Halalas = " & 60/100 Riyals"
Case "Sixty One"
Halalas = " & 61/100 Riyals"
Case "Sixty Two"
Halalas = " & 62/100 Riyals"
Case "Sixty Three"
Halalas = " & 63/100 Riyals"
Case "Sixty Four"
Halalas = " & 64/100 Riyals"
Case "Sixty Five"
Halalas = " & 65/100 Riyals"
Case "Sixty Six"
Halalas = " & 66/100 Riyals"
Case "Sixty Seven"
Halalas = " & 67/100 Riyals"
Case "Sixty Eight"
Halalas = " & 68/100 Riyals"
Case "Sixty Nine"
Halalas = " & 69/100 Riyals"
Case "Seventy "
Halalas = " & 70/100 Riyals"
Case "Seventy One"
Halalas = " & 71/100 Riyals"
Case "Seventy Two"
Halalas = " & 72/100 Riyals"
Case "Seventy Three"
Halalas = " & 73/100 Riyals"
Case "Seventy Four"
Halalas = " & 74/100 Riyals"
Case "Seventy Five"
Halalas = " & 75/100 Riyals"
Case "Seventy Six"
Halalas = " & 76/100 Riyals"
Case "Seventy Seven"
Halalas = " & 77/100 Riyals"
Case "Seventy Eight"
Halalas = " & 78/100 Riyals"
Case "Seventy Nine"
Halalas = " & 79/100 Riyals"
Case "Eighty "
Halalas = " & 80/100 Riyals"
Case "Eighty One"
Halalas = " & 81/100 Riyals"
Case "Eighty Two"
Halalas = " & 82/100 Riyals"
Case "Eighty Three"
Halalas = " & 83/100 Riyals"
Case "Eighty Four"
Halalas = " & 84/100 Riyals"
Case "Eighty Five"
Halalas = " & 85/100 Riyals"
Case "Eighty Six"
Halalas = " & 86/100 Riyals"
Case "Eighty Seven"
Halalas = " & 87/100 Riyals"
Case "Eighty Eight"
Halalas = " & 88/100 Riyals"
Case "Eighty Nine"
Halalas = " & 89/100 Riyals"
Case "Ninety "
Halalas = " & 90/100 Riyals"
Case "Ninety One"
Halalas = " & 91/100 Riyals"
Case "Ninety Two"
Halalas = " & 92/100 Riyals"
Case "Ninety Three"
Halalas = " & 93/100 Riyals"
Case "Ninety Four"
Halalas = " & 94/100 Riyals"
Case "Ninety Five"
Halalas = " & 95/100 Riyals"
Case "Ninety Six"
Halalas = " & 96/100 Riyals"
Case "Ninety Seven"
Halalas = " & 97/100 Riyals"
Case "Ninety Eight"
Halalas = " & 98/100 Riyals"
Case "Ninety Nine"
Halalas = " & 99/100 Riyals"
Case Else
Halalas = " & " & Halalas & " Halalas"
End Select
SpellBilling = Riyals & Halalas
End Function
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
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 ' If value between 20-99...
Select Case Val(Left(TensText, 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
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function
' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

Display More

ipi99 · Aug 10th 2020

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

Please remove this tread

How to convert numbers to words in Excel VBA

rory Aug 5th 2020

Share

Tags