UDF to Convert Numeric Dollar Values into Text in Excel

[jwdecker]

I have searched all over the internet for the past two days and each instance I have used the code provided to turn numeric dollar values into English Text. None seem to work. Here is the code I have used and when I try to run the macro I get the #Name error. I have tried installing this as a module and into a workbook. I am new to this so some explination on what I am looking at would be appreciated. Thank you.

Code: Select all

Option Explicit
 'Main Function
 Function SPELLDOLLAMNT(ByVal MyNumber)
 Dim Dollars, Cents, 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 cents and set MyNumber to dollar amount.
 If DecimalPlace > 0 Then
 Cents = 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 Dollars = Temp & Place(Count) & Dollars
 If Len(MyNumber) > 3 Then
 MyNumber = Left(MyNumber, Len(MyNumber) - 3)
 Else
 MyNumber = ""
 End If
 Count = Count + 1
 Loop
 Select Case Dollars
 Case ""
 Dollars = "No Dollars"
 Case "One"
 Dollars = "One Dollar"
 Case Else
 Dollars = Dollars & " Dollars"
 End Select
 Select Case Cents
 Case ""
 Cents = " and No Cents"
 Case "One"
 Cents = " and One Cent"
 Case Else
 Cents = " and " & Cents & " Cents"
 End Select
 SPELLDOLLAMNT = Dollars & Cents
 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

[Sisyphus]

Hi,
I can find nothing wrong with the function you have posted. So, there must be something wrong with the way you deploy it. I tried this:

Code: Select all

=SPELLDOLLAMNT(B7)


In my test I installed the function in a code module and I changed the amounts in B7 several times. The text appeared correctly in the cell with the formula which I didn't format at all, meaning it was formatted as "General".

I was able to replicate the error you produced by copying the UDF to the ThisWorkbook module instead of a code module. To insert a code module right-click on the Project name in the Project Explorer window. Then select "Insert..." and "Module". The default name of the module that is inserted will be "Module1".

I hope that solves your problem.
Merry Christmas!

[Don]

If you're still having issues, include the workbook here that contains this UDF and the error that you got. That would give us a quick way to tell if you inserted/used the UDF incorrectly or not.