Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding macros in modules (Excel 2000)

    I often have to go find a macro because of some glitch. So I open the VBA Explorer and am confronted with the tree structure we all know. My question: I have a devil of at time trying to find the macro because it is in one of x-number of folders all called Module xx. I have a hard time locating a named sub, even if I know the name. So I wind up clicking through the modules and clicking the lists and wasting time back-tracking to unhide Personal.xls and step through the Macro/edit dialog box. Is there a better way to track these down, or get them listed "outside" the tree structure, or some display option to show the dumb name I gave the module, or anything to make finding these things easier? If I, as a rank-amateur, have trouble finding my little tricks, I wonder what the big-guns must go through to find their huge macros. Maybe there's an add-on or something. I tried unsuccessfully to rename the module, or tried to incorrectly. That would be some help, anyway. Thanks.

  2. #2
    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: Finding macros in modules (Excel 2000)

    If you are in the code window, you cna go to edit -find and search. YOu can search on different options including the Project (ie All the mods in a workbook), the module, or just the procedure.

    Also if you did know the module it was in, you can goto any of th routines in it, by clicking on the right pulldown at the top of the codewindow
    Steve

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Finding macros in modules (Excel 2000)

    In addition to Steve's advice, I recommend that you Name your both your Projects and modules something meaningful (rename Projects in the Project Properties pop-up window, and rename Modules in the Module Properties window), don't put too many procedures in each, and group your procedures by module in some logical order.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding macros in modules (Excel 2000)

    I little known trick, that works for VISIBLE workbooks:

    In Excel press F5. Type the name of the macro and hit OK.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    88
    Thanks
    58
    Thanked 0 Times in 0 Posts

    Re: Finding macros in modules (Excel 2000)

    To add to the replies you have already received:
    I have over 40 modules of code in my Personal.xls. I 'manage' my procedures by moving them (cut-and-paste, using Ctrl-X, Ctrl-V) to the appropriate module. Example: I have a module named SelectM (I end all my module names with the "M", for 'module', in order to avoid confusion with any named ranges). All the subs that help me to select cells are stored there.
    P.S.: Some of the best 'select' subs come from John Walkenbach; see his web site at www.j-walk.com, or buy his latest great book, Microsoft Excel 2003 Formulas.

    How to change the name "Module 31" to "SelectM":
    From Excel, press Alt-F11, as one of the ways to open the Visual Basic Environment (VBE).
    Press Ctrl-R to view the Project Explorer. In the Project Explorer pane, click on the name "Module 31".
    Press F4 to view Module 31's properties window. Drag across the name "Module 31" to select it, then key in "SelectM". When finished editing, click the "X" on the properties window.

  6. #6
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    88
    Thanks
    58
    Thanked 0 Times in 0 Posts

    Re: Finding macros in modules (Excel 2000)

    After having another lucid moment, I remembered this tip:

    To view the list of all the procedures you have named, in all your open Excel files:
    Press Alt-F8. That opens up the Macro dialog box, which displays the list. Click on the name of the macro (procedure) you want, then click <Run>. Or, double-click on the name and the procedure starts running.

    You can use the "Macros In..." combo box there to choose whether to look in all open workbooks or in just one workbook.

Posting Permissions

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