You can't strip off the source  code if you still want the VBA to work. It's
got to be there.

You can put a password on it to try to stop people looking at it or changing
it.

I sometimes take it a step farther when I don't want users to know
what the code  (subroutines) are named, I would use functions instead
of sub's just so that the subroutines won't be displayed in the Macro
window when a user executes the "Run Macro" command.

Though some purists might say only use function subroutines when a
return value is desired. I use them interchangeably...

What about using the "Private" Keyword to hide the macros from the "Run
Macro" command?

The "Private" sub's will work for the most part, but I usually call
these sub's from other modules or classes, which a private sub won't work.

Adding a parameter to your sub will keep it from being listed in the Macro
window when the user selects Tools >> Macro >> Macros. You don't have to use the
parameter for anything; you just have to include it in the sub declaration and
pass it a suitable value when you call it.

Public Sub test(AnyArg As Variant)
'your code
End Sub

To call it:
Call test(x)

Yes, that's another way of not getting it to show up, but then the
extra typing of passing an argumentthat's involved....

You can set the parameter as Optional and then you don't actually need one
at all.

Unfortunately, no. I just tried it (in 2003). Excel is ahead of you. :-)

However, I would expect that your subroutines would almost all have at least
one parameter. Otherwise, how will they know what to do.

> ... but then the extra typing of passing an argument that's involved....

But you're only doing it once - while you write the code. A small price to
pay for an easy fix.

I think just making it a function instead of a sub is pretty easy, and the
only extra typing required is to go from s-u-b to f-u-n-c-t-i-o-n one time.

I tried it here too, also in Excel 2003.... Try..

You're sort of right... Some don't work..

Sub subName(P1)
Sub subName(P1 as variant)
... Didn't work

Sub subName( p1 as string )
Did

I will be interested to see if it works there as well.

Both

Sub subName(P1)
Sub subName(P1 as variant)

were fine (i.e. not shown) on mine.

I forgot to put "Optional" in. :-(

For the following .........

Option Explicit
Sub subMain()
subOptP1AsString
End Sub
' ------------------------------------
Sub subOptP1(Optional p1)

End Sub
' ------------------------------------
Sub subOptP1AsVariant(Optional p1 As Variant)

End Sub
' ------------------------------------
Sub subOptP1AsString(Optional p1 As String)
MsgBox "\"
End Sub

........... When I look at Tools/Macros..
subMain
subOptP1
subOptP1AsVariant

.......... All show up

But subOptP1AsString don't!

Curious don't you think.

I am still waiting for solution to my problem.
I have a 100X300 matrix which contians text and I have find out how many rows in
the matrix have a combination of common cells. For example:
a b c d e f
a b c w f r
a n d c g b
Here combination of cells a,b & C are repeated twice and a,n & d once. Is
possible to write macro in excel  which can do this count.
I need a quick solution. Please help.

Why ask me specifically? People will give you an answer if THEY want
to.

And why are you replying to someone else's post to ask a question on a
completely different topic.

> I need a quick solution. Please help.

Frankly, NO. Not until you follow normal group etiquette and ask your
question properly. Please read
http://www.catb.org/~esr/faqs/smart-questions.html

It's probably only optional variant parameters that Excel makes visible.

More specifically, if you step into a sub with an option variant parameter
from the macro popup or directly in the VBE, the parameter has a type of
Variant/Error and is listed as missing..

But if you go into the VBE and try to step into a sub with an optional
string or integer parameter, you'll find that Excel won't do anything.

Although the missing variant parameter supposedly has type "Error", a sub
with an optional parameter of type Error doesn't get in the list.

As I understand it, variant parameters are the only ones that will actually
be marked as missing if omitted. Optional parameters of other types will be
initialised to zero, empty, etc, when omitted. For some reason, this seems
to make a difference.

Must'a been one of those realistic dreams one sometimes has.

So, from the comments, I understand that the Macro names will be
visible in 'the menu' even if password-ed unless:

They are functions.
They declare variables - sometines [I couldn't follow the thread to
fully understand the limits of this phenomenon]

I hadn't thought about the Macro list, but that's not too important as
long as the code  itself is difficult to get to (I have a few of the
password cracking macros). If I want to be more secretive, I can
Find/Replace all names with cryptc names before "publishing".

Dave - FYI:
Your "must pass vars to subs" comment is correct in the first
level. However... You could have a sub which gets data from sheets
and replaces it there..sort of "sucking parameters into" a sub rather
than "passing to". But, then again, I'm the guy who uses
predominantly globals. (;-(


And I'm now working on my File read/Write code and it is coming  along
just fine thanks to comments and examples on the group.

I don't think this was what was said (or at least I hope it wasn't).
Hopefully the word used was "variant", not "variable".

I think it had to do with parameters of type Variant. Variants can
basically hold anything Excel can put into a variable or parameter, and this
includes "missing", when optional parameters are omitted.

Other optional parameters (e.g. integers) must still always hold a proper
value. If the "optional" clause doesn't provide the default, then Excel
will. But with optional Variant parameters, Excel will just poke a code  for
"missing" into it.

For some reason, Excel then decides that such subroutines should be visible,
even though ones with optional parameters of other types are not made
visible.

===================

Password protection prevents the user from seeing the VBA code - not from
using it. Therefore it needs to keep the macros visible as they were before
a password.

Sorry. You're confusing my intent because I said variable instead of
parameter (to be passed).

I know what Variant variables/parameters are.

What I meant was that I didn't follow the discussion as far as
which type(s) of parameters, which, when put in the Sub parameter list
, will cause the macro to NOT appear in the macro menu list.

It would make sense that a macro will not appear in the macro menu
if it requires input parameters because there is no way to pass
parameters from the menu, therefore it must be an 'internal' sub.
However, the discussion about Variant parameters lost me as far as
whether/when they cause the macro to be seen / not seen in the menu list.

Perhaps I don't need to understand this.
Idea:
If I could make it so that (input) parameters are required in all
macros (some will be dummys), then (if the above is true) no macros
would appear in the menu list. This may be an idea, but this is not
critical. I just want to hide my code  from others. It's just a "you
can have my sheet and use it for free, but you can't see my code" sort
of thing.

I was hoping that since it is compiled code, the source  could be
blown away after compiling it and VBA would just use the compiled
version of the code.

In sort of reverse order ...

> I was hoping that since it is compiled code, the source  could be
> blown away after compiling it ...

It's not really compiled code. It's still a source-level interpreter, but
it does store away intermediate partial compilations to use if the source
environment doesn't change. Sounds a bit unclear? That's because I'm a bit
unclear. It always seems to do some sort of source code  check -
particularly the first time you run the code after loading the sheet - but
it definitely does store some intermediate information away with the
worksheet. I've never delved more deeply into it.

But the end result is that you can't consider it as compiled code.

> I just want to hide my code from others. It's just a "you
> can have my sheet and use it for free, but you can't see my code" sort
> of thing.

If you want people to use your code, but not see it, then it needs to be
accessible (at the "call" level) to them. This is what a password gives
you.

> However, the discussion about Variant parameters lost me as far as
> whether/when they cause the macro to be seen / not seen in the menu list.

No problem. The discussion did range a bit. IIRC it went a bit like this
...

- Subs with no parameters can be seen.

- Subs with parameters can't be seen, but it's a nuisance to have to use
dummy parameters on the calls.

- What about making them optional, so that they don't need to actually be
provided in the calls? Yes that's fine - optional String or Integer or
other parameters can be omitted in the calls, but the routine is still not
visible.

- But optional Variant parameters do not stop the Sub being hidden. Why the
difference?

- Shouldn't be any difference, because both forms of optional parameters
allow the Sub to be called without parameters, so why aren't both types
visible?

- Here it got technical and obscure, and I for one was speculating, rather
than speaking authoritatively. But optional Variant parameters are
definitely implemented differently inside Excel to optional parameters of
other types. The other types (when omitted) will be given default values;
Variant parameters (when omitted) will be given "missing" status. A subtle
difference, but probably this is triggering the difference.

In summary, an optional String or Integer parameter will cause the Sub not
to be visible, and you don't need to provide that parameter in other calls
to it.

> It's not really compiled code. It's still a source-level
interpreter, but it does ...

It sure is fast for interpreted code...but it must surely be
tokenized along with other speed improvements. I guess I took the
"compile" in the menu at its word.


> If you want people to use your code, but not see it, then it needs
to be accessible (at the "call" level) to them. This is what a
password gives you.

All the code  I have been getting help for here, is run from
buttons and value changes. No need to see the macro list to run, so
that't why I was hoping.

The rest i'll save  in one of my Help files. Thanks for the clear
clarification.

Maybe this will help.

www.programmersheaven.com/.../vbvm.htm