How to call user defined function (UDF) from custom menu in Excel2003?

[Dutch_Guy]

How can I call a user defined function from a custom menu?

I have created an add-in (xla) that contains several functions. These
functions work perfectly and show up under: Insert Function dialog box
under the category: User Defined

I am also able to create a custom menu by following the instructions
on the following awesome site:
http://www.ozgrid.com/VBA/custom-menus.htm

What I am unable to do is to call the functions from the custom menu.

Each UDF has its own module in the xla workbook and starts with Public
Function <function name>(serial As String) As Integer

The OnAction line reads:

..OnAction = "<function name>()"

Error message that I get: The macro '<function name>()' cannot be
found

Can you please help me out what I do wrong and tell me what the
correct syntax for OnAction is?

I appreciate your help.

 

[mcgurkle]

How can I call a user defined function from a custom menu?

I have created an add-in (xla) that contains several functions. These
functions work perfectly and show up under: Insert Function dialog box
under the category: User Defined

I am also able to create a custom menu by following the instructions
on the following awesome site:http://www.ozgrid.com/VBA/custom-menus.htm

What I am unable to do is to call the functions from the custom menu.

Each UDF has its own module in the xla workbook and starts with Public
Function <function name>(serial As String) As Integer

The OnAction line reads:

.OnAction = "<function name>()"

Error message that I get: The macro '<function name>()' cannot be
found

Can you please help me out what I do wrong and tell me what the
correct syntax for OnAction is?

I appreciate your help.

Hi Dutch_Guy

I'm not sure why your macros aren't working. Is the custom menu also
created in the add-in? You should probably delete the parentheses from
the string, because these can cause the function to be executed twice.

I'm a bit confused by the part

(serial As String) As Integer

In your custom menu, how can you pass an argument in or use the
result?

If you want to do this, i would recommend that you write an
intermediate function along the lines of:


public sub Intermediate()

dim intTemp as integer

intTemp = OriginalFunctionName("serialstring")

'do something with intTemp

end sub


Then in your menu, call

..OnAction = "Intermediate"

Hope this helps

Laura

 

[RB Smissaert]

You need:

..OnAction = "functionname"

So for example:

Function Function1() As String

Then do:

..OnAction = "Function1"


RBS

 

[Dutch_Guy]

Laura,

Thank you very much for your reply. You and RBS solved my problem. I
removed the parentheses.
You told me that I confused you and I confused myself as well, I am
still a beginner when it comes to VBA

(serial As String) As Integer

In your custom menu, how can you pass an argument in or use the
result?

Because my code line with .OnAction was not working, I did not expect
to get a no arguments error message.
So after you resolved my OnAction problem, I created a public sub
Intermediate() in which an inputbox is asking for a cell selection.
The selected cell contains the serial number (variant data type) which
is passed to the function.

My goal is to create a custom menu with formulas that are very
specifically designed for our company needs which can be used by
department employees. I want to avoid that each employee has to go to
the Insert Function dialog box, select the category: User Defined and
then browse through the large list of functions. With a custom menu, I
can add sub menus (categories) to make the formula selection easier.

I believe now I have to focus on error handling since canceling the
input dialog box or selecting multiple cells gives error messages

Dutch_Guy