Macro Not Showing in Alpha List

[Kay9835]

I've been working on a fairly big project for several weeks. So far it has
18 Sub modules and a few Procedures. Today I created a new Sub (called
SortRsltWorkArea), which just happens to have four arguments.

When I attempted to test the macro, I was unable to initiate it via either
F5 or F8. F8 just gives me a "ding" sound (as does "step into" via the Debug
menu). F5 (or the equivalent from the Run menu) presents me with the window
that shows all the macros for this project in alpha order. SortRsltWorkArea
is not listed.

It DOES exist however; it shows up as Module18 in the Project-VBAProejct
window.

I subsequently created a sub (TestSort) that successfully calls
SortRsltWorkArea, and if I Step Into (F8) TestSort it steps through
SortRsltWorkArea too.

I'm assuming that I accidentally set something in SortRsltWorkArea that is
causing this behavior, but I'm not having any success figuring out what that
something is.

I've tried copying the code from SortRsltWorkArea into a different (new) sub
and deleting the original version, with no success. I also tried changing
the name.

I'm running Excel 2007 - but since I haven't had this problem with any of
the other subs I've created for this project, that doesn't seem likely to be
significant.

Any ideas?? It's not a big deal now - I'll remember that it's there.
However, when I (or, worse yet, someone else) needs to do maintenance on this
thing a year or more from now, I suspect this is likely to cause trouble.

 

[Robert Flanagan]

Macros with arguments must be called by other macros and can not be run
directly.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Sam Wilson]

You can't test it without telling it what values to use for the parameters.
Think of it as testing a function without putting any values in.

The flip-side of this is useful though, if you want to include a macro in a
workbook but don't want it to be visible to a user you can put in a dummy
parameter, unused by the macro, which keeps it out of sight.

Sam

 

[FS]

Hi,

Any ideas??

You cannot test your macro within the VBE but you can within Excel using
the dialog box called by Alt+F8.

The command to set is something like :

'MacroName "param1","param2","param3"'

Quick example :

Sub ZZZ(S As String, X As Double, D As Date)
MsgBox S & " : " & X * 10 & " (" & D & ")"
End Sub

In the dialog box :

'ZZZ "Number","12,3","15/12/09"'

(Maybe you'll have to replace commas with points according to your
system parameters).

FS
--
Frédéric SIGONNEAU
Modules et modèles pour Excel :
http://frederic.sigonneau.free.fr/

Kay9835 a écrit :