MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Removing the VBA SourceCode from the Workbook

  Asked By: Phil    Date: Jan 25    Category: MS Office    Views: 1222

I thought I saw this here and saved it, but searching my files, the
group and the Net, I'm coming up empty...
Perhaps just a wishful dream, but I sure recall the author's
caution to save the file *with* source first. And, yea, I realize I
better be 100% sure it is error free or able to gracefully handle errors.

Anybody have a reference for the steps to compile then strip-off
the Excel VBA source code, so it can't be seen in the Editor anymore?



21 Answers Found

Answer #1    Answered By: Kathleen Adams     Answered On: Jan 25

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

Answer #2    Answered By: Jacob Bouchard     Answered On: Jan 25

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...

Answer #3    Answered By: Theresa Watson     Answered On: Jan 25

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

Answer #4    Answered By: Andrew Levensky     Answered On: Jan 25

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.

Answer #5    Answered By: Ella Oliver     Answered On: Jan 25

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)

Answer #6    Answered By: Alok Iit     Answered On: Jan 25

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

Answer #7    Answered By: Lucinda Hall     Answered On: Jan 25

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

Answer #8    Answered By: Rosie Brooks     Answered On: Jan 25

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.

Answer #9    Answered By: Fabiano Ferrrari     Answered On: Jan 25

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.

Answer #10    Answered By: Kara Hicks     Answered On: Jan 25

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 )

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

Answer #11    Answered By: Jens Brown     Answered On: Jan 25


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

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

Answer #12    Answered By: Darrel Nelson     Answered On: Jan 25

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

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

Option Explicit
Sub subMain()
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..

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

But subOptP1AsString don't!

Curious don't you think.

Answer #13    Answered By: Jake Evans     Answered On: Jan 25

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.

Answer #14    Answered By: Virgil Foster     Answered On: Jan 25

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

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

Answer #15    Answered By: Penny Clark     Answered On: Jan 25

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.

Answer #16    Answered By: Mohammed Evans     Answered On: Jan 25

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.

Answer #17    Answered By: Abelard Fischer     Answered On: Jan 25

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


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.

Answer #18    Answered By: Birke Fischer     Answered On: Jan 25

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.
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.

Answer #19    Answered By: Thomas Thompson     Answered On: Jan 25

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

> 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

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

- 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

- 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.

Answer #20    Answered By: Morris Hughes     Answered On: Jan 25

> 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

Answer #21    Answered By: Leo Evans     Answered On: Jan 25
Didn't find what you were looking for? Find more on Removing the VBA SourceCode from the Workbook Or get search suggestion and latest updates.