CODEWITHSUNDEEP
excelvba
DLeh
DLeh

Reputation: 24395

Excel not finding VBA Function

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.

Upvotes: 3

Views: 10758

Answers (3)

Graham Edwards
Graham Edwards

Reputation: 1

I had the same problem. I resolved the issue by removing "Public". Presto! Good luck:)

Upvotes: 0

omJohn8372
omJohn8372

Reputation: 174

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.

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

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.

Upvotes: 2

Related Questions