Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Functions/VBA (XL2000/VBA)

    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
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Functions/VBA (XL2000/VBA)

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

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions/VBA (XL2000/VBA)

    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 ??
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Functions/VBA (XL2000/VBA)

    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

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions/VBA (XL2000/VBA)

    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
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Functions/VBA (XL2000/VBA)

    Tools - macro - macros..
    enter the name of the function
    press <options>
    enter the description
    <ok>

    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Functions/VBA (XL2000/VBA)

    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

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions/VBA (XL2000/VBA)

    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.
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions/VBA (XL2000/VBA)

    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 ??)
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Functions/VBA (XL2000/VBA)

    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

  11. #11
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions/VBA (XL2000/VBA)

    Apologies for being a pain here, but I simply can
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Functions/VBA (XL2000/VBA)

    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

  13. #13
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions/VBA (XL2000/VBA)

    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
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Functions/VBA (XL2000/VBA)

    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 <options>
    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 has some more details about doing this (this should work in xl97 or 2000)

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •