0

I have created a UDF Excel via ALT+F11 in own Module. Have read many tutorials where everything seems so easy (that you can use your UDF in each Cell of opened Workbook). But I can't use my function.

Tried two test functions in Module (Test and Test2).

Checked everything (disabled all Macro security, allow access to VBA project model) but I don't see my custom function when I type "=" in a cell.

I'm using Excel in active Office 365 license so it is the newest version.

What could cause that my UDF is not applicable in the Excel cell? I just get "#NAME?" / "#VALUE?" as result and no auto suggestion of function name...

maybe anyone has a hint for me what could cause that issue. Thx!

Public Function test()
    test = 1
End Function

Public Static Function test2()
    test2 = 123
End Function
baer999
  • 810
  • 2
  • 14
  • 26
  • Are you calling it through `=test()` on your worksheet? I assume you missing the `()` at the end. – JvdV May 10 '19 at 08:35
  • 2
    Which module did you put the code into? – Rory May 10 '19 at 08:52
  • `Public Function Test()` ***`As What`***? – Chronocidal May 10 '19 at 08:54
  • @Chronocidal If not declared, it is implicitly `as Variant`. – Ron Rosenfeld May 10 '19 at 09:42
  • @RonRosenfeld, I doubt it. Putting it in a wrong module would cause Excel to return a syntax error. – JvdV May 10 '19 at 09:51
  • As suggested by @Rory, if you are not seeing your custom function, then you have not installed it into a regular module in your active workbook. The `#NAME?` will follow. If you were seeing the custom function, then you are probably not adding the `()` at the end in the worksheet – Ron Rosenfeld May 10 '19 at 09:51
  • @JvdV Did you try it? I just put it into a worksheet and reproduced what the OP reported. In a regular module, but missing the `()`, still shows it in the dropdown prompt. – Ron Rosenfeld May 10 '19 at 09:52
  • @RonRosenfeld, Yes I tried. Putting function on sheet2, calling it on sheet1, resulting in syntax error. Therefore I suggested that `()` must be missing. I'm using Excel 2016, might this behaviour differ? Also, not showing in dropdown prompt. – JvdV May 10 '19 at 09:54
  • @JvdV I cannot reproduce what you write. If the function is in the worksheet module for sheet1, it shows a `#NAME?` no matter in what worksheet I enter the function, and whether I enter it as `=test` or `=test()` I do not see a `Syntax Error`. I doubt behavior has changed in different versions. I have O365 with all regular (not Insiders) updates. – Ron Rosenfeld May 10 '19 at 10:06
  • @RonRosenfeld, I'll write an answer to demonstrate. Hopefully that's allowed for this purpose. – JvdV May 10 '19 at 10:09
  • As can be seen in @JvdV answer: **alt-F11** opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select **Insert/Module** and paste the code below the window that opens. – Ron Rosenfeld May 10 '19 at 12:52
  • yes did all within the following file but don't get auto suggestion / UDF working in a cell... can you see any problem in this file ?! ufile.io/sswxp9em – baer999 May 11 '19 at 19:05
  • my exact Office / Excel version is: ufile.io/sdxreosd – baer999 May 11 '19 at 19:05

2 Answers2

2

@RonRosenfeld,

This is to demonstrate what behaviour I'm seeing:

  • Scenario 1: Function pasted in ThisWorkbook calling it in Sheet1:

enter image description here

  • Scenario 2: Function pasted in Sheet2 calling it in Sheet1:

enter image description here

  • Scenario 3: Function pasted in Module1 calling it in Sheet1:

enter image description here

This is why I came to the conclusing the () must be missing to show the #NAME error since otherwise there would be a syntax error.

@OP, You seem to have put your function in a class module, instead of a regular module.

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Maybe that behavior is due to your non-English version of Excel? Can you upload the workbook and post a link? I'd like to see if it occurs here. – Ron Rosenfeld May 10 '19 at 10:20
  • @RonRosenfeld Blame it on the foreigners! :)...... Just kidding. I'll upload it [here](https://ufile.io/ko1sdyyr) – JvdV May 10 '19 at 10:21
  • Nope, do not see the syntax error. If I move the (cut/paste) the functions from the regular module to the Workbook or Worksheet modules, I just get the `#NAME?` errors (with or without the `()`). And, unless it is in a Regular Module, I don't see it in the drop-down. Hence my thought that it is a placement problem (wrong type of module). Interesting difference between the `American` and your version, though. – Ron Rosenfeld May 10 '19 at 10:38
  • Thanks for your hints right now, but don't get it work so far... my exact version can you see in following screenshot: https://ufile.io/sdxreosd – baer999 May 10 '19 at 12:10
  • @baer999, You have put it in a class module, instead of a regular module. – JvdV May 10 '19 at 12:20
  • "You seem to have put your function in a class module, instead of a regular module.": along with the picture was enough to solve my case – user1767316 Nov 24 '21 at 23:40
0

Functions located in a module created using: add a Module(second menu from left)/Module succesfully shows up in the function menu.

user1767316
  • 3,276
  • 3
  • 37
  • 46