#NAME? error in Excel for VBA Function

Question

I am making my first VBA program and trying to run the following function. The function checks a specific named range for the first row which does not have a value greater than it's leading value, but less than 1.

Public Function findPurchase()

Dim CRT As Range
Set CRT = Range("CostRateTable")

Dim existsBetter As Boolean
existsBetter = True

Dim r As Integer
r = 2
Dim c As Integer
c = 4

While existsBetter

    Dim Found As Boolean
    FoundBetter = False

    While Not FoundBetter And c <= CRT.Columns.Count
        If CRT(r, c) > CRT(r, 2) And CRT(r, c) < 1 Then
            FoundBetter = True
        Else
            c = c + 1
        End If
    Wend


    existsBetter = FoundBetter
    If existsBetter Then
        r = r + 1
    End If
Wend

findPurchase = CRT(r, 3)
'MsgBox(findPurchase)
End Function

I know the function does what it is supposed to because I have both manually checked the table of values, removed the comment ' from the MsgBox, and used the debug tools to step in and out of each of the functions steps as it went through the table. However, when I reference the function in Excel with =findPurchase() I'm given a #NAME? error. The function even shows up in the function auto-complete box when I begin to type its name. When I write other functions, both with and without parameters, I can reference them just fine, for example:

Function addtwo()
    addtwo = 1 + 2
End Function

What am I doing wrong with my function which causes it not to work?

Answer 1

You are getting that error because you have a module with the same name as the function.

Change that name to say find_Purchase and everything will be fine :) See the image below...

Answer 2

I had the same issue myself. It turned out that I "Saved As..." another file and macros were not enabled for that file. No banner on the top appeared, but a #NAME? error was generated. I reopened the file, enabled macros, and the problem was resolved.