3

I'm trying to create a VBA function to call in a cell in Excel, but the cell comes up as #NAME? when I enter =MyFunction("a")

In my VBA Module Module1:

Public Function MyFunction(anything As String)
    MyFunction = anything
End Function

in the cell:

=MyFunction("a")

Result:

#NAME?

What am I doing wrong here? When I start typing =MyFunction in the cell, Excel lists it as an option, but doesn't show the parameters and then produces this result.

This is in a 2007 .xlsm file.

Community
  • 1
  • 1
DLeh
  • 23,806
  • 16
  • 84
  • 128
  • FWIW, It works fine for me in a 2013 .xlsx file. Perhaps try putting the function into a clean spreadsheet to see if it can work for you at all. – DeanOC Jan 21 '15 at 21:47
  • Okay I made a new file and it worked, so it must be something weird with my document. I'll copy my data into there and try again. – DLeh Jan 21 '15 at 21:48
  • You probably had made an error but left the function in the cell, since it's not volatile, it would not "update" by fixing the function, maybe? For your paramters (tooltip), you could look at the suggestions here: http://stackoverflow.com/questions/4262421/how-to-put-a-tooltip-on-a-user-defined-function – David Zemens Jan 21 '15 at 21:49
  • I think when i saved the file as a `.xlsm` it didn't automatically enable macros on the file. I reopened the file and it prompted me to enable macros – DLeh Jan 21 '15 at 21:49

2 Answers2

2

Your UDF is in the correct place and is coded correctly and will be "recognized" if macros are enabled for the workbook in question!

Check your security settings.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • I had originally created the file as a `.xlsx`, and saved as `.xlsm` to enable macros on it. Now that I've re-opened the file it asked me to enable macros. Thanks! – DLeh Jan 21 '15 at 21:51
  • I have made the same mistake about a million times myself ! – Gary's Student Jan 21 '15 at 21:52
1

I have a macro-enabled file, I have Macro Security set to Enable All, I checked Trust access to the VBA project object model, I have my workbook in a Trusted Location, and I still cannot type in my function name or find it in the Insert Function dialog box. What other security settings are there?

This is on a recent unmodified install of 2016 x64 on Windows 10 Pro.

I have, in ThisWorkbook (Code):

Public Function TestJohn(derp As String) As String
    TestJohn = "test john " & derp
End Function

And I have in Sheet2 (Code):

Public Function TestJohn2(derp As String) As String
    TestJohn2 = "test john2 " & derp
End Function

Neither of which appear as usable functions for Sheet2.

omJohn8372
  • 154
  • 1
  • 6
  • Nothing so far, @MichaelZiluck. – omJohn8372 Jul 16 '21 at 18:28
  • 2
    I think I found the problem. We both were under the impression that you're supposed to put it in either ThisWorkbook or SheetX. What you need to do instead is right-click on VBAProject in the Project panel, and then select Insert > New Module. Put your function in there and it should work fine! – Michael Ziluck Jul 16 '21 at 18:36
  • @MichaelZiluck you saved my day. This did work for me like a charm – Dar Feb 06 '22 at 12:52