34

I defined a few functions in a workbook using VBA, and then expected to be able to use them in a cell formula - but Excel does not recognise the function. I just get #NAME?

Tried:

  • Realising I had created an XSLX file, I converted it to a XSLM file. Didn't work.
  • Removed all types from the function declaration. Didn't work.
  • Moved the function into the worksheet VBA module. Didn't work.
  • Added Public to the declaration. Didn't work.

What am I missing?

This isn't clever code, either:

Function Square2(AnyNumber)

'return the square of any integer
Square2 = AnyNumber * AnyNumber

End Function
Dave Jarvis
  • 30,436
  • 41
  • 178
  • 315
Mark Bertenshaw
  • 5,594
  • 2
  • 27
  • 40
  • 1
    When nothing works, I just restart excel. Seems to work for me. – nawfal Sep 22 '14 at 13:29
  • 1
    Microsoft Excel Objects such as 'Sheet1' or 'ThisWorkbook' are classes. I don't believe you can access Functions which you put in these classes through a cell. You could access them in VBA e.g. ThisWorkbook.Square2() but it's recommended to put all UDF's into as standard module and not a worksheet module. – Eddie Sep 28 '17 at 13:49

9 Answers9

55

Answer

Putting the function in the "ThisWorkbook" area can cause the #NAME? problem. Create a new Module (Right Click on the VBAProject Folder, Insert, New Module) and put the function there instead.

Steps

  1. Open the VBA Editor (Alt + F11 on Windows / Fn + Option + F11 on a Mac)
  2. Right-click VBAProject
  3. Select Insert >> Module

  4. Create a Public function inside Module1, for example:

    Public Function findArea(ByVal width as Double, _
                         ByVal height as Double) As Double
    ' Return the area
    findArea = width * height
End Function

  5. Call it from a cell, like any other function: =findArea(B12,C12)

Macro Screenshot

Dave Jarvis
  • 30,436
  • 41
  • 178
  • 315
Cortado-J
  • 2,035
  • 2
  • 20
  • 32
  • 4
    ps. another gotcha; if your module name is the same as your function name (e.g. module `Demo` contains `public function Demo()`, you'll get a `#NAME?`. Amending the module name to something different (e.g. `DemoModule` resolves this, as does using a fully qualified name in your formula (e.g. `=Demo.Demo()`) – JohnLBevan Mar 10 '16 at 13:33
  • When I provide a value for second parameter it reports: `Mit dieser Formel gibt es ein Problem. Sie möchten gar keine Formel eingeben? ...` – Paul Sep 19 '16 at 11:58
  • 1
    Worked for me, thanks. Only thing I'd add is that I didn't need to declare the function as public. – JL_SO Nov 22 '18 at 18:21
  • It didn't work for me only until I substituted `,` with `;` into the cell. So the call from the cell is for me `=findArea(B12;C12)` – Neuran Feb 09 '19 at 21:54
  • 1
    @JohnLBevan, thanks for this answer. This was *my* problem. What a silly bug to still have in VBA. (You should have added this as an *answer* instead of a comment, and I bet you would have gotten more credit. :-) – Randy Stegbauer Dec 09 '20 at 13:35
  • Thanks @RandyStegbauer; good to know it's helping. Feel free to post my comment as an answer and reap some kudos ;) – JohnLBevan Dec 09 '20 at 15:51
8

I faced the same issue, after struggling around following worked for me:

My function was inside a module in macro workbook called Personal.XLSB. I prefixed the function name with the personal macro workbook filename and !, so if function name is theFunction(x,y) I entered in cell "=PERSONAL.XLSB!theFunction(x,y). This worked.

Please note that PERSONAL.XLSB is always open in hidden mode for me.

Ben
  • 1,414
  • 2
  • 13
  • 18
Uttam
  • 596
  • 1
  • 6
  • 11
4

Make sure you are not in design mode.

There is a design mode button on the developer tab in excel and beside the run/stop buttons in the VBA editor. If it is selected, and won't let you unselect it, then try reopening the workbook with macros enabled.

If it still is enabled, or won't let macros run, make sure macros are enabled.

Activate macros.

-- https://stackoverflow.com/a/20659823/258482

Community
  • 1
  • 1
Arlen Beiler
  • 15,336
  • 34
  • 92
  • 135
4

I had an identical problem, including a working function that later stopped working giving a #NAME error. I have managed to fix both by making sure the name of the module is not the same as the name of the function. I had a working function F_1 in Module1, I changed the module name to F_1 and it stopped working, now back at Module1 and the function works again. My second function also began working when I changed the module name from F_2 to Module2.

Ross Hunter
  • 41
  • 1
1

XLSX file and XLSM files have nothing to do with it. Format plays the role when you save the file. (In XLSX, VBA code will be stripped while saving the file).

The below code from http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx works quite well inside a new module in my excel.

Function Discount(quantity, price)
    If quantity >= 100 Then
        Discount = quantity * price * 0.1
    Else
        Discount = 0
    End If
    Discount = Application.Round(Discount, 2)
End Function

Given that I cannot see your code, can you try whether below function works for you too? If so, start modifying the below function so that it becomes your function (for example, first change the name and see if it works, and then change number of parameters and check if it works, and then change name of parameters).

Niraj Nawanit
  • 2,431
  • 3
  • 16
  • 11
  • 1
    I used your function in a worksheet like this: =Discount(100,2) Same problem. – Mark Bertenshaw Sep 10 '12 at 12:25
  • And I did change the parameters like you suggested. – Mark Bertenshaw Sep 10 '12 at 12:27
  • Just to ask, now you are defining functions outside Worksheet module and Workbook module? You must not define inside Workheet or Workbook Modules. – Niraj Nawanit Sep 10 '12 at 12:35
  • I have tried defining functions inside Worksheet and Workbook modules. None work. Are you saying that this is impossible? In which case, where can I defined VBA functions that are used inside a worksheet cell? – Mark Bertenshaw Sep 10 '12 at 14:14
  • record a macro - see where the spreadsheet saves that code. put your code in the same place – SeanC Sep 10 '12 at 19:29
  • @SeanCheshire - I had no idea that only items in a VBA module would be visible to *any* worksheet. I assumed that the function would just be scoped to that Worksheet module. If you make an answer, then I'll mark it up. – Mark Bertenshaw Sep 11 '12 at 09:08
  • 2
    Mark - Don't put these functions in worksheet or workbook modules. Insert a new module, and put functions into this module. – Jon Peltier Oct 01 '12 at 15:02
1

I opened Excel, opened the code editor (Alt+F11), selected the new workbook, inserted a new Module, typed

Function Decrement(i As Integer) As Integer
  Decrement = i - 1
End Function

then went back to my workbook and in A1 typed =Decrement(2) and hit Enter, and it worked. Decrement showed up in the drop-down list of functions as I typed =Decr... It was recognized, and it worked. I didn't even have to save the workbook.

I know this isn't exactly an answer to your question, but it's the recipe I had luck with.

Jeff Maner
  • 1,179
  • 9
  • 23
1

I think there might be a problem if your Module has the same name as your function. Try renaming your module or your function.

Gustavo
  • 21
  • 1
0

Activate macros.

SunnyRed
  • 3,525
  • 4
  • 36
  • 57
0

If you are using the latest versions of Excel, to see VBA functions in another workbook you need to:

  1. Save your workbooks as .xlsm

  2. Enable macros as suggested above

  3. Set a reference. In VBA (Alt-F11) choose Tools/References, then browse to the workbook which contains the macro you want to use. Check that reference in the list.

    If you get an error message about the module names clashing just rename it in the project explorer first.

Community
  • 1
  • 1
PhilCoder
  • 1