Results 1 to 9 of 9

Thread: Finding a UDF

  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Finding a UDF

    I apologise in advance for asking such a silly/trivial question, but I have drawn a blank on this.

    Some time ago I created a User-Defined Function called CellAbove, with help from this forum.

    Now I cannot find it anywhere in the Workbook Code. Obviously it is still active as it is used all over the Workbook. I need to edit it, but can't find it in the Project.

    Help please !

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Martin

    Have you tried using the Find What in the VBA Project???
    Go into your VBA code, click [Ctrl-F] to bring up the Find/search dialog.
    Click the radio button for Current Project etc.

    zeddy

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Yes, I have tried that: the Function name CellAbove does not come up even when searching the whole project.

    Instead I tried searching for the word Function and that found another UDF but not CellAbove ?!

    Where on earth can I have put it ?

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    It's not on one of the code sheets is it??

    Right-click on each sheet tab and select View Code

    Or is it in a Personal.xls file???

    zeddy

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    There IS a little code on the sheets, but not the CellAbove function.

    Neither is it in the ThisWorkbook module.

    I don't have a Personal.xls, which I double-checked by trying to use the function in a different Workbook - as expected Excel just reported #NAME?

    Ah- found it !!!

    It is simply defined as a Name. For anyone reading this sorry tale of amnesia, the Name definition is =INDIRECT("R[-1]C",0) . . . very handy.

    I had forgotten that you can use Names like this.

    Thanks Zeddy, for your patience :-)

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Well done for finding it Martin!
    Names that are defined directly as formulas can be tricky to locate as they do not appear in the Names drop-down box in the formula bar.
    In Excel2010 the Name Manager would have listed it though!

    For a moment I thought we might have to start searching Excel's hidden namesapce.
    This is a lovely session-dependant feature I used a lot that can retain parameter values set by VBA in a workbook which are retained while that Excel session is still open, even after the workbook that set the parameter value is closed!

    zeddy

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I'm very happy with Excel 2003 - but may have to think about installing a 3rd party name manager.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thank you Rory - looks very useful indeed.

Posting Permissions

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