Routines do not display when list macros
Posted by Frank D on August 07, 2001 11:22 AM
In Excel 97, I cannot see all my subroutines and functions when I select Run Macro.
When I was writing and editing them, sometimes I would get the message,
You are going to reset the project, ok? I had to reset the project to
continue. How can I correct this problem?
The routines are there and I can use the Visual Basic Editor to run them.
I am storing the macros in my personal.xls.
Thanks!
Posted by Damon Ostrander on August 07, 2001 12:21 PM
Frank,
When you select Run Macro, the only procedures that will show are the ones in the (General) section, and only those that are either Subs or are Functions that have only Optional arguments.
This is because Functions with mandatory arguments would have no way of receiving their arguments if called from the Run Macro menu. However, user-defined functions DO show up in the Function helps that you get when you request help on functions while entering them in the formula toolbar.
Happy computing.
Damon
Posted by Frank D on August 08, 2001 8:32 AM
Ok, thanks Damon,
There are no mandatory arguments in them.
Now, the question is "How do you get them
back into the General section?". I assume
they used to be there; since I used to run them.
Posted by Damon Ostrander on August 09, 2001 9:05 PM
Frank,
It does sound like the problem is that the routines are in a worksheet's event code area. The
fact that the routines in this area are triggered by events could cause them to execute when you don't expect them to, thus the error message when you make a change.
If the routines are in the worksheet or workbook event code area rather than in a macro module, the easist way I know of to move them there is with a cut and paste (i.e., open the worksheet's code, cut it, open a macro module, paste). If you do not already have a macro module to paste the code into you will have to create one (Insert -> Module in the VBE).
Hope this helps.
Damon
Posted by Frank on August 14, 2001 6:50 AM
Problem found - Function not Subroutine
Damon,
Thanks! I had to change the subroutine to a function because I was using a userform to run the macro.
Perhaps I should have done an application.run but I haven't been to class yet and I don't have a book; so
I didn't know about that at the time. I had previously cut and pasted with no luck. When I changed the
Function to Subroutine, it appeared. Now, I will have the user use a keyboard shortcut to run the subroutine
until I learn more.
I got the project reset error once. It was because I was in debug and didn't reset before I changed code
elsewhere. I stopped debug and no problem.
Great site!! You have been alot of help.
Frank
