Finding function code

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
OK! So, I've made lots of the macros at various times and they are strewn throughout 48 modules.... and now, wongm003 provided a solution for me that has code for a Public Function and it's in a new module (#49) along with a macro I created on the same day. If I want to edit or copy a macro, I don't have to know what module it's in, I just hit {Alt+{F8}}, pick the name of the macro, click on the Edit button, and there it is. But the function code doesn't show up on that list. So, how do I access the code for that function in the future? I doubt if the next time I want to write a similar code that I'll remember that its in Module 49!
TIA, Krys
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
In the Visual Basic Editor, hit CTRL-F for find
In the find what box, type the word function, a space then the name of your function.
Under Search, change to Current Project
Click on Find Next
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
MORE PROBLEMS - Finding function code

Arrrrrgh!

1.Today, I discovered that I can't find the function in VBA in the manner HOTPEPPER suggests unless I'm already in the correct module... so that won't work. Is there another way to find custom Public Functions?

2 When I tried to use the formula wongm003 gave me to actuate this function in a new worksheet today, I got error message saying that the macros are disabled in that file - EVEN THOUGH I CAN STILL RUN MACROS. Also, all of the Instructions in the Help files that pertain to enabling macros tell me to change the security levels from High to Medium.

A. I never changed the security levels to begin with.

B. To enable macros, the Help instructions say: "In the Tools menu, point to Macro, and then click Security. Click the Security Level tab, and then click the option you want." Unfortunately I can't follow those instructions because there is no "Security" to click on, nor is there a "Security Level Tab". Where does this really live in Excel2000? (And, anyway, I can run macros, just not custom the Public Function!)

HOW DO I RESOLVE THIS! :x

TIA, Krys
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

The spreadsheet that contains this module must be open and you can be in any module or sheet code under this, but you have to change to search in Current Project

The security levels is not in the Visual Basic Editor, it is on the Tools Menu on the spreadsheet page. Click on Tools, then Macro, then Security, select your security level and click OK.
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
ACK! I didn't remember to change the Search parameters to Current Project :oops: so that accounts for that... Thnx!

But, on the other matter... I am going to the Tools Menu on the spreadsheet, not in VBA. When I click Tools > Macro, I get the following options: "Macros..." "Record New Macro..." and "Visual Basic Editor" --- there is no "Security". Even if I go one step further and pick "Macros...", I get the standard Macro window with the Macro List and Buttons (Run, Cancel, Step Into, etc.) NO Security options even under the Options Button.

Am I missing something in my build??? Thnx!! K-
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

What happens if you have a blank spreadsheet open and try it?
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
Same thing... even re-booted but to no avail... any ideas?
:rolleyes: K-
 

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
not one to state the obvious but office will give short menus, this is that is will only show the most recent used menu items, do you have two little arrows pointing down at the bottom of your macro menu, if so click this to show rest of menu,

also if its a work pc you system administrator may have removed this to stop you altering security of macros, right click on an empty space on a toolbar and click customise, click commands, in the left pane click tools, now scroll down the list until you find SECURITY. when you find it drag it back onto your menu

HTH
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
This is PC at home so nothing should be disabled and there are no "recently used" menus... However, I took your suggestion and added Security Button (it never occured to me that there would be a button for that!!!) to a toolbar and VOILA! Everything's cool now... I'm still wondering why the Tool Menu doesn't match the Help instructions and how the security level got changed in the first place! Oh well, won't lose any sleep over it! And, speaking of sleep... I'm off to na-na land... Thanks to both of you for your help on this!!! K-
 

Watch MrExcel Video

Forum statistics

Threads
1,113,859
Messages
5,544,706
Members
410,630
Latest member
Maggie28
Top