NAME error in Excel for VBA Function

0 votes

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?

Apr 5 in Database by Edureka
• 13,640 points
51 views

1 answer to this question.

0 votes

Because you have a module with the same name as the function, you're seeing this issue.

enter image description here

Replace find Purchase with find Purchase and you'll be fine:) See the image below.enter image description here

answered Apr 5 by gaurav
• 13,460 points

Related Questions In Database

0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23 in Database by gaurav
• 13,460 points
44 views
0 votes
1 answer

how do I calculate discount with if function in excel?

Suppose a customer gets a 10 percent ...READ MORE

answered Feb 23 in Database by gaurav
• 13,460 points
614 views
0 votes
1 answer

Excel VBA - exit for loop

When we need to depart the For ...READ MORE

answered Mar 15 in Database by gaurav
• 13,460 points
38 views
0 votes
1 answer

Sigma or Summation function in Excel

The SUM function in Excel is another ...READ MORE

answered Mar 15 in Database by gaurav
• 13,460 points
43 views
0 votes
1 answer

Number of elements in a single dimension variant array in excel

You must do UBound - LBound + ...READ MORE

answered Mar 24 in Database by gaurav
• 13,460 points
64 views
0 votes
1 answer

Pasting an Excel range into an email as a picture

Hold down the ALT + F11 keys ...READ MORE

answered Mar 30 in Database by gaurav
• 13,460 points
87 views
0 votes
1 answer

Excel vba for loop

You must understand how to use loops ...READ MORE

answered Apr 5 in Database by gaurav
• 13,460 points
68 views
0 votes
0 answers

Unprotect workbook without password

I have a popular VBA code to ...READ MORE

Apr 7 in Database by Edureka
• 13,640 points
27 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

"Runtime Error 91: Object variable or with ...READ MORE

answered Mar 30 in Database by gaurav
• 13,460 points
210 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

When there was no unhidden workbook open ...READ MORE

answered Apr 6 in Database by gaurav
• 13,460 points
58 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP