Reputation: 1636

#NAME? error in Excel for VBA Function

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?

Upvotes: 35

Answers (13)

JonK

Reputation: 38

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.

Upvotes: 0

Hans-Gerhard Schehl

Reputation: 1

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...

Upvotes: 0

Mark

Reputation: 235

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.

Upvotes: 1

Pooyan Tavakoli

Reputation: 21

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

Upvotes: 2

Ross Bailey

Reputation: 1

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.

Upvotes: 0

Peter Rosenberg

Reputation: 1

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

Upvotes: 0

Theodor Paulus

Reputation: 151

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

Upvotes: 3

sf321

Reputation: 11

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.

Upvotes: 1

DrMarbuse

Reputation: 870

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

Upvotes: 7

Erik G

Reputation: 341

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.

Upvotes: 24

Karen

Reputation: 11

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.

Upvotes: 1