Macro is returning Name Error What's Wrong?

PWil84

New Member
Joined
May 20, 2021
Messages
33
Office Version
  1. 2019
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.

VBA Code:
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


1627514060321.png
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,515
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
Sub Test()
    count
End Sub

If it automatically captializes the first letter, like this:
VBA Code:
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

Well-known Member
Joined
Nov 7, 2011
Messages
3,823
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2011
Messages
3,823
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ok, I formatted the code from the OP if anyone wants to take a stab at it:

VBA Code:
'   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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,126
Office Version
  1. 365
Platform
  1. Windows
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.

1627528428137.png



Cell Formulas
RangeFormula
B3:B5B3=SpellNumber(A3)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,590
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,515
Office Version
  1. 365
Platform
  1. Windows
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").
 

Forum statistics

Threads
1,176,300
Messages
5,902,371
Members
434,971
Latest member
JKW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top