Hide Macro's

[Ardy]

Hide Macro's
Is there a possibility to hide created macros within macro window from
the users? This is to prevent users to execute out of sequence code.

Ardy

 

[Trevor Shuttleworth]

Ardy

a couple of ways:

you could use: Option Private Module

or

you could make each of the subroutines private: Private Sub test()

Regards

Trevor

 

[John Coleman]

There might be some way, but I don't know it. A workaround is to
exploit the fact that subs with parameters do not appear in the macro
list. For example,

if you have

Sub Greet()
msgbox "hi"
End Sub

Sub SendGreeting()
Greet
End Sub

then both Greet and SendGreeting will be visible in the macro list,
even if you intend Greeting() to be called by the user and Greet() a
helping sub you *don't* want them to invoke. But - if you replace the
definition of Greet by

Sub Greet(Optional dummy = 0)
MsgBox "Hi"
End Sub

then everything works as before but Greet() no longer appears in the
list.

Hope that helps

-John Coleman

 

[John Coleman]

Forget my post - Trevor's approach is the way to go.

 

[Ardy]

Trevor:
If I make the sub a private one would I still be able to call codes
from diffrent modules within the same worksheet. For example my
worksheet has abt 9 modules each one dose certain things and I am
hevely calling one sub from one module or another. would I still be
able to do this going private sub.

Ardy

 

[thesquirrel]

I have used your way for a long time John and to good success. I found
early on in my learning that items with paramaters don't appear in the
macro window.

I use something like this...

Public Sub Test(x as byte)
<code here>
End Sub


and I call it like this...


<code here>
Dim x as byte
test x
<code here>


I use it and will continue using it because it works

theSquirrel

 

[Trevor Shuttleworth]

I think Private Module is the way to go. From the VBA Help:

Option Private Statement

When used in host applications that allow references across multiple
projects, Option Private Module prevents a module's contents from being
referenced outside its project. In host applications that don't permit such
references, for example, standalone versions of Visual Basic, Option Private
has no effect.

Syntax

Option Private Module

Remarks

If used, the Option Private statement must appear at module level, before
any procedures.

When a module contains Option Private Module, the public parts, for example,
variables, objects, and user-defined types declared at module level, are
still available within the project containing the module, but they are not
available to other applications or projects.

Note Option Private is only useful for host applications that support
simultaneous loading of multiple projects and permit references between the
loaded projects. For example, Microsoft Excel permits loading of multiple
projects and Option Private Module can be used to restrict cross-project
visibility. Although Visual Basic permits loading of multiple projects,
references between projects are never permitted in Visual Basic.


Sub Statement

Declares the name, arguments, and code that form the body of a Sub
procedure.

Syntax

[Private | Public | Friend] [Static] Sub name [(arglist)]
[statements]
[Exit Sub]
[statements]
End Sub

The Sub statement syntax has these parts:

Part Description

Public (Optional). Indicates that the Sub procedure is accessible to all
other procedures in all modules. If used in a module that contains an Option
Private statement, the procedure is not available outside the project.

Private (Optional). Indicates that the Sub procedure is accessible only to
other procedures in the module where it is declared.

Friend (Optional). Used only in a class module. Indicates that the Sub
procedure is visible throughout the project, but not visible to a controller
of an instance of an object.

Static (Optional). Indicates that the Sub procedure's local variables are
preserved between calls. The Static attribute doesn't affect variables that
are declared outside the Sub, even if they are used in the procedure.

name (Required). Name of the Sub; follows standard variable naming
conventions.

arglist (Optional). List of variables representing arguments that are passed
to the Sub procedure when it is called. Multiple variables are separated by
commas.

statements (Optional). Any group of statements to be executed within the Sub
procedure.

Regards

Trevor

 

[Ardy]

thesquirrel:
Thank you for the solution, I have implemented this in most of my code
and modules, it works great. The Private Sub() is also a solution but
it is too late in hte game for this mini app that I am working on, +
Since I am not a true programmer it brings abt a lot of complication in
my current worksheet. I like to thank both for offering solutions to
this problem of mine.

Ardy

 

[Trevor Shuttleworth]

There is often a variety of solutions. If it works for you, that's good
enough.

Regards

Trevor