35

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?

Improve this question
7
  • Dim Found As Boolean Is it just because this line isn't dim'd correctly? Commented Sep 16, 2013 at 14:19
  • Ah, my bad. I fixed that, but am still getting the same error. That doesn't actually create a problem, but just creates an extra variable that I don't use. Commented Sep 16, 2013 at 14:20
  • Where have u declared FoundBetter? Also have you tried stepping through the code? Did you try running the function from VBA? Does it give any error? Commented Sep 16, 2013 at 14:42
  • Variables in VBA are declared to their best known type on first encounter, to the best of my knowledge. The statement assigning FoundBetter = False will cause VBA to initialize FoundBetter. You can see this similarly if you write the code: ` x = true if x then MsgBox("True") else MsgBox("False") end if` As I mentioned in my post, yes, I have stepped through every step of the code, and run the code from VBA, and it gives accurate results every time. In any case, I have already fixed the lack of declaration of FoundBetter and the problem persists. Commented Sep 16, 2013 at 14:52
  • May I see your excel file? Also please add "@" before my name so that I get the message alert... Commented Sep 16, 2013 at 16:03

13 Answers 13

Reset to default
59

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

enter image description here

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

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

How this is not mentioned on any "how to create a UDF" is beyond me. DO NOT NAME A MODULE AFTER THE FUNCTION INSIDE! Thank you so much for this answer @Siddharth, from now on my modules are called "mMyFuntion1, mMyFuntion2, etc..."
I feel you. Lot of things are not mentioned in MSDN. And even if they are, it is a pain to search for them. One gradually discovers them over a period of time. I usually name my modules and controls by prefixing 3 characters which tell me what they are just by looking at them. For example ModMain, ModCommonFunctions , ClsDataActions, BtnExit, BtnGenerate etc... I do not do that for variables and procedures though. There I use descriptive name. Dim TaxRate as Double or Sub GenerateTaxReport() or Function ReturnFinalAmount() etc...
24

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.

Improve this answer

1 Comment

Same issue to me. I copied a file, renamed it and opened it. Excel asks to enable macros and also performs the auto_open procedute. Nevertheless it behaves strangely because the function is still not evaluated automatically. I have to edit the cell containing the formula (F2 and Enter). Then the function is evaluated correctly. Then, after opening the workbook without saving and reopening it again everything is fine.
12

Make sure you have placed the function in a Standard Module. The error message means Excel can't find the function.

Improve this answer

5 Comments

It is in a module I created by right clicking and going to Insert>>Module. Other functions from the same Module (such as addtwo in my example) could be found.
Then be ABSOLUTELY certain the spelling of the name of the function matches its usage in the worksheet.
I'm very certain. The name even shows up in autocomplete, and I double click on it. I type in exactly =findPurchase() I still get a #NAME? error.
It is unfortunate that I cannot get to your workbook. I copied your code and it works fine on my computer (WIN 7 / Office 2007)
For whatever reason, just putting the function in the worksheet didn't work. Indeed, needed a module.
7

When Excel opens an unkown workbook containing VBA-Code, it usually asks for macros to be enabled by the user (depending on the application settings).

If the user then enables the macros, all event-driven procedures will be started, such as auto_open or others.

Custom VBA Functions however require for a full recalculation of the workbook. Otherwise the functions return-value still is #NAME, as the calculation is only done directly after opening the workbook.

In order to work directly at the first time opening, one has to add the following line to the workbook_open event

'
' Workbook open event
Private Sub Workbook_Open()
    Application.CalculateFullRebuild
End Sub
Improve this answer

2 Comments

Where would this be added? How do I get to the Workbook Open Event?
That seems to have been the reason in my case. After enabling VBA-Code in the Macro Settings, I had to close and re-open the file before the error went away. Thank you, +1!
3

Check "Trust access to the VBA project object model" in Macro settings from Macros security

Improve this answer

Comments

2

One reason for this problem is security restrictions.. I had this problem and I activate "Enable all macros" from security center, and the problem solved

Improve this answer

Comments

1

I had a similar persistent problem with one of my functions when everything else seemed fine. Open the worksheet & go to the Developer Tab. Open VBA, and back on the Developer ribbon select "View Code". See if it opens any similar Code (apart from your Module) specific to that worksheet (eg. Sheet2 (Code). I found that I had duplicated the code on the worksheet in addition to the Module. Delete the "worksheet" code. (You may need to save the workbook & re-open at this stage). When I deleted the worksheet code, the module function then worked.

Improve this answer

Comments

1

In addition to checking some of the above mentioned items, you might need to specify the filename where the custom function is actually defined, e.g. cell content =XLstart.xlsm!myCustomFunc(Arg1,Arg2) where myCustomFunc is defined in the startup file XLstart.xlsm.

Following the Excel help for "Correct a #NAME? error":

In the formula bar, select the [suspect] function name. In the Name Box (to the left of the formula bar), click the arrow and then select a [user-defined] function from the list that Excel suggests.

This will add the filename per the above format.

MS 2010, Windows 10.

Improve this answer

Comments

1

Here's why I got that error. This answer is not provided so far.

If you have two or more workbooks (spreadsheets) open, then you may have your module under the other workbook - not the only you want to do the calculation on. This may seem impossible but ... as soon as you open the Developer/VBA code editor Excel wants to show you the structure (objects, modules, etc) of every open workbook. It's not what I expect as a developer, but there it is. So like me, you may have pressed 'Add module' and dropped the code in another workbook and worksheet.

If this is your issue, nothing mention above will work. Move your VBA module and code to the correct spreadsheet visible through this VBA code editor.

Improve this answer

Comments

0

True, I had the same (in Excel 2010) and when I migrated to Excel 2016 , the function prototype was shown, but when I completed the function, the #NAME error was shown with a pop-up... so the code was never triggered.

It turned out I had a Macro of the same name as a Sub or UDF function ! I renamed the Macro, and then it worked

Cheers

Improve this answer

Comments

0

Another cause I found for the #NAME? error is that the macro workbook with the custom function has a range name the same as the function name. I changed the function name and solved the problem.

Improve this answer

Comments

0

This solution applies to users with an Excel installed in another language than "United States English": I had a similar problem when making a copy of the active workbook to duplicate it and immediately opened the copy afterwards:

Non-working code:

   ThisWorkbook.SaveCopyAs NewFileName
   Set wb = Workbooks.Open(FileName:=NewFileName)

This always showed me several cells with Error 2029 / "#NAME?". If I opened the Workbook "the official way" via the File-Menu it worked as expected.

I solved the issue by adding the parameter "local:=true" to the open statement:

Working code:

   ThisWorkbook.SaveCopyAs NewFileName
   Set wb = Workbooks.Open(FileName:=NewFileName, Local:=True)

as VBA expected english function names in my German workbook. With this parameter VBA is told directly to use the local names.

I hope that helps someone not to loose several hours, as I did...

Improve this answer

Comments

0

Short answer - if the function was working before, RESTART YOUR COMPUTER.

Long answer - I had this same thing happen to me. The problem is that the function I had created had been working for months. Then one day it just started showing a #NAME error instead of working like it was before. I had tried closing all other excel workbooks and even closing excel all-together and re-opening the sheet. Nothing seemed to work. Then for kicks, I edited the code to where I knew VBA would complain that there is a compile error. Surprisingly, it didn't complain. OK... I saved and closed excel anyways and then restarted my computer.

Once rebooted, I re-opened the excel workbook. Then VBA finally gave me a compile error. So I changed my function back to the original code I had before and now the sheet is running the function like it is supposed to. No more #NAME error.

Not sure all of those steps are necessary, but simply restarting the computer seems to have fixed my issue.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.