Functions/VBA (XL2000/VBA)

Topic

New Reply

Hi,

Does anyone know if – or even better how, if it is possible, that you in the INSERT | FUNCTIONS | PASTE FUNCTIONS can have functions registered under your own “name” instead of only under the group “User defined”.

I have three-four various types of function groups, that I in function groups would like grouped under my initials + the function type name (eg. HR math, HR option, HR misc. etc. etc.)

Thanks

Reply | Quote

Replies

Take a look at How to Add a New Category to Function Category List , in Microsoft’s Knowledge Base

Andrew C

Reply | Quote

Thanks – looks promising, though my memory on XL macros has faded substantially… odd thing to have it only this way….??

Do the functions have to be done on the macro sheet or can I still do them in module under VBA ??

Reply | Quote

Odd indeed.

The function(s) should be placed as normal in a VBA module. You just use the XL4 Macro sheet to define the name of the category.

Andrew

Reply | Quote

Tried to follow the knowledge base article, but it’s not quite clear to me, how I get the connection between the XL 4.0 macro and the VBA module.
Eventhough scope of functions are public, they are not visible in the frame.

Could you please be very detailed how this works ??

Further is it possible somehow to add a decription to the function ?
Equivalent to what is shown just under the “Function Category” frame

Reply | Quote

Tools – macro – macros..
enter the name of the function
press
enter the description

You can not add text to describe the parameters so make their names as descriptive as possible if/when you define them in the function

Steve

Reply | Quote

Tools – macro – macros..
enter the name of the function
press
enter the description

You can not add text to describe the parameters so make their names as descriptive as possible if/when you define them in the function

Steve

Reply | Quote

Concerning the KB article:

In Microsoft Excel 2000 (it also works in XL97), right-click a sheet tab in the workbook and then click Insert.
Click MS Excel 4.0 Macro, and then click OK to add a macro sheet to the workbook.
On the Insert menu, point to Name, and then click Define. Under Macro, click Function.
In the Name box, type TEST1. [<< This is where you enter the NAME of the function you want to add to the category]
In the Category box, replace User Defined with the new category name, and then click OK.

Add the function (Test1 in the example), in a module and it will be in the defined category.

Steve

Reply | Quote

What about the “Refers to” line – what does this do in this respect ??
– has it any function at all or is its functionality “non-existing” in this connection.

Reply | Quote

What about the “Refers to” line – what does this do in this respect ??
– has it any function at all or is its functionality “non-existing” in this connection.

Reply | Quote

Does this also mean that you can only have 1 “Function name” per module ??
(so that a module in fact is equivalent to the “Function name” frame ??)

Reply | Quote

Just keep adding the function names and putting them into the category.

Once it is ALL done, you can delete the macro sheet and delete all the named ranges and the category and functions still persist.

Steve

Reply | Quote

Apologies for being a pain here, but I simply can

Reply | Quote

When I open your file and goto insert function,
I see the category “NDM_EqFunc” (it is right after “User defined”)
and it has the functions:
EPS
EV_EBITDA
PriceBook
PriceEarnings

I don’t understand, what is not working?

Steve

Reply | Quote

When I open your file and goto insert function,
I see the category “NDM_EqFunc” (it is right after “User defined”)
and it has the functions:
EPS
EV_EBITDA
PriceBook
PriceEarnings

I don’t understand, what is not working?

Steve

Reply | Quote

The functions seem just to refer to the defined names.

You can see this by the fact that they hold no parameters opposite to the

Reply | Quote

Have you added the functions to the category using the macro options dialog box?
I was under the impression, you KNEW how to change the categories, but you didn’t know how to CREATE a new one.

In the macro options dialog box
Tools – macro – macros
(enter function name)
Press
In excel2000 you (I think) you have the option to SELECT a category. select the new one you created.
[In excel97 we do NOT have this option and we can only change the category using the macrooptions method and coding it.]
http://www.ozgrid.com/VBA/DesciptionToUDF.htm%5B/url%5D has some more details about doing this (this should work in xl97 or 2000)

Steve

Reply | Quote

Have you added the functions to the category using the macro options dialog box?
I was under the impression, you KNEW how to change the categories, but you didn’t know how to CREATE a new one.

In the macro options dialog box
Tools – macro – macros
(enter function name)
Press
In excel2000 you (I think) you have the option to SELECT a category. select the new one you created.
[In excel97 we do NOT have this option and we can only change the category using the macrooptions method and coding it.]
http://www.ozgrid.com/VBA/DesciptionToUDF.htm%5B/url%5D has some more details about doing this (this should work in xl97 or 2000)

Steve

Reply | Quote

The functions seem just to refer to the defined names.

You can see this by the fact that they hold no parameters opposite to the

Reply | Quote

Apologies for being a pain here, but I simply can

Reply | Quote

Just keep adding the function names and putting them into the category.

Once it is ALL done, you can delete the macro sheet and delete all the named ranges and the category and functions still persist.

Steve

Reply | Quote

Does this also mean that you can only have 1 “Function name” per module ??
(so that a module in fact is equivalent to the “Function name” frame ??)

Reply | Quote

Concerning the KB article:

In Microsoft Excel 2000 (it also works in XL97), right-click a sheet tab in the workbook and then click Insert.
Click MS Excel 4.0 Macro, and then click OK to add a macro sheet to the workbook.
On the Insert menu, point to Name, and then click Define. Under Macro, click Function.
In the Name box, type TEST1. [<< This is where you enter the NAME of the function you want to add to the category]
In the Category box, replace User Defined with the new category name, and then click OK.

Add the function (Test1 in the example), in a module and it will be in the defined category.

Steve

Reply | Quote

Tried to follow the knowledge base article, but it’s not quite clear to me, how I get the connection between the XL 4.0 macro and the VBA module.
Eventhough scope of functions are public, they are not visible in the frame.

Could you please be very detailed how this works ??

Further is it possible somehow to add a decription to the function ?
Equivalent to what is shown just under the “Function Category” frame

Reply | Quote

Odd indeed.

The function(s) should be placed as normal in a VBA module. You just use the XL4 Macro sheet to define the name of the category.

Andrew

Reply | Quote

Thanks – looks promising, though my memory on XL macros has faded substantially… odd thing to have it only this way….??

Do the functions have to be done on the macro sheet or can I still do them in module under VBA ??

Reply | Quote

Take a look at How to Add a New Category to Function Category List , in Microsoft’s Knowledge Base

Andrew C

Reply | Quote