Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to call user defined function (UDF) from custom menu in Excel2003?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to call user defined function (UDF) from custom menu in Excel2003?
On 28 Feb, 13:33, Dutch_Guy wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to call user defined function (UDF) from custom menu in Excel 2003?
You need:
..OnAction = "functionname" So for example: Function Function1() As String Then do: ..OnAction = "Function1" RBS "Dutch_Guy" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to call user defined function (UDF) from custom menu in Excel2003?
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 On Feb 28, 8:42 am, mcgurkle wrote: On 28 Feb, 13:33, Dutch_Guy wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call user defined function in an add-in | Excel Programming | |||
Custom Format for User Defined Function | Excel Discussion (Misc queries) | |||
How to call a function of an user defined DLL from Excel? | Excel Programming | |||
Excel "Insert Formula" dialog always call my user defined function | Excel Programming | |||
Call GoalSeek from a user-defined-function | Excel Programming |