Macro is returning Name Error What's Wrong?

[PWil84]

I'm working on a project and wanted to know why the name error is appearing when I copied the macro below to the project file? The file is attached as well.

Option Explicit

'Main Function

Function SpellNumber(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

SpellNumber = 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

 

[Joe4]

One thing is that you should NEVER use reserved words like "Count" for the names of variables, functions, or procedures (reserved words are words that are already used by Excel for things like functions, properties, methods, etc). Doing so can cause errors and unexpected results.

If you are not sure if the word is a "reserved" or not, someone showed me a little trick that works in most cases. Start a brand new procedure, something like "Sub Test()". Do NOT declare any variables in it. Then type the word you want to test in lower case, like this:

Sub Test()
    count
End Sub

If it automatically captializes the first letter, like this:

Sub Test()
    Count
End Sub

then it is a reserved word. So don't use it.

One thing I often do to avoid that issue is add the word "my" in front of variable names, i.e. "mycount".

Also, when posting your VBA code, please use code tags! It maintains the formatting and makes your code much easier for us to read.
See: How to Post Your VBA Code

 

[johnnyL]

Another thing to point out, you put a bunch of 'Dim' statements at the top, but you didn't declare them as anything, so basically you wasted your breath, so to speak, by putting that code in.

You should declare what you want them to be, otherwise the default value of 'variant' is assumed, which equates to you never including them in the first place.

Numerous errors on the code.

 

[johnnyL]

Ok, I formatted the code from the OP if anyone wants to take a stab at it:

'   Main Function
Function SpellNumber(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 "
'
    MyNumber = Trim(Str(MyNumber))              ' String representation of amount.
    DecimalPlace = InStr(MyNumber, ".")         ' Position of decimal place 0 if none.
'
    If DecimalPlace > 0 Then                    ' Convert cents and set MyNumber to dollar amount.
        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
'
    SpellNumber = 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)
'
    If Mid(MyNumber, 1, 1) <> "0" Then                          ' Convert the hundreds place.
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
'
    If Mid(MyNumber, 2, 1) <> "0" Then                          ' Convert the tens and ones place.
        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

At this point I don't know if I should scratch my watch or wind my butt. I need a break!

 

[Alex Blakenburg]

Have you definitely copied the code into a standard module ?
Also do you have macros enabled ?
It works fine for me.

Thanks @johnnyL that was great.

20210729 VBA Spell Numbers Convert no to words.xlsm
	A	B	C
1
2	Value	Text Conversion
3	12.23	Twelve Dollars and Twenty Three Cents
4	64,537.23	Sixty Four Thousand Five Hundred Thirty Seven Dollars and Twenty Three Cents
5	857,564.00	Eight Hundred Fifty Seven Thousand Five Hundred Sixty Four Dollars and No Cents
6
Sheet1

Cell Formulas
Range	Formula
B3:B5	B3	=SpellNumber(A3)

 

[Peter_SSs]

you put a bunch of 'Dim' statements at the top, but you didn't declare them as anything, so basically you wasted your breath,
... which equates to you never including them in the first place.

Whilst I agree that the OP would be much better off declaring the type of each variable, I disagree that they have wasted their breath/time.
The OP has "Option Explicit" set at the top so they will be required to be declared in some form so definitely does not equate to never including them.

@PWil84
When providing sample code, please ensure that you have indented it and preserve that indentation by using the forum's available 'code tags'. My signature block below has more information about that.
Your code in the format that johnnyL has re-posted (thanks @johnnyL) is much easier for helpers to read & de-bug.

 

[Joe4]

Whilst I agree that the OP would be much better off declaring the type of each variable, I disagree that they have wasted their breath/time.
The OP has "Option Explicit" set at the top so they will be required to be declared in some form so definitely does not equate to never including them.

I agree with Peter here. While it undoubtedly would be better to include the variable type (and I recommend that they do so), it is NOT a wasted effort.
By combining it with Option Explicit, it would help in error debugging, i.e. if they made a typo and tried to use a variable name that does not match one of the declared ones, the code would return an error message when trying to compile, alerting them of the error.

There are really two different reasons to declare your variables:
1. To designate the variable type to limit the memory used and to ensure only valid values are being entered into those variables.
2. To prevent typos/errors and using undeclared variables (when combined with "Option Explicit").