Calling a Private Sub

[Guest]

This is probably someting painfully simple. But I have a workbook full of
macros, that I don't want the user to be able to access directly through the
macros dialog box. I know I can keep them out of there by using Private Sub
declaration, but I want to be able to call them from other procedures within
the VBA code. Is there a way to call a Private Sub, or is there a way to make
a sub inaccesible directly to the user but otherwise allowing VBA code to
access it?

Thanks,
Ben

 

[JE McGimpsey]

One way:

Use "Option Private Module" at the top of the module.

Note that you can still run the macro if you type the name in the macro
box, but you have to know the name, and you can do that with Private
macros, too.

 

[Jake Marx]

Hi ben,

At the top of each Standard Module, put the following line of code:

Option Private Module

With this statement, even routines declared Public will not show up in the
Run Macros dialog.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Sharad]

Another way is instead of defiing as sub, define as function.

Sub xyz()
End Sub

Make it
Public Function xyz()
End Function

Though limitations, in most cases defining as function should work.

If the user knows the name of the public function
he can enter it as a formula in a cell.
But then again. provided that he knows, 1) it is declared as public
function, 2) he knows the name of the function, 3) he knows public
declared funtions can be entered as a formula in a cell. Chances are
less, don't you think so?

Sharad

 

[Guest]

problem is some of the subs I am calling or inbedded in my sheet objects.
Option module private will not work in those. Certain code I am trying to
Call is the same code run on activex Control changes, and If I try to declare
them as functions, I am given the error of Argument list not corret.

 

[Chip Pearson]

Ben,

Declare the macro as Public, but include an optional and unused
argument.

Public Sub MacroName(Optional Dummy As Integer)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Chip,

Thank you for your suggestion, that will work with regular modules but not
with activex controls with that code assigned, "Procedure Decleration does
does not match description of event or procedure having the same name."

 

[Jake Marx]

ben,

Thank you for your suggestion, that will work with regular modules
but not with activex controls with that code assigned, "Procedure
Decleration does does not match description of event or procedure
having the same name."

What exactly are you talking about here? Are you talking about hiding the
built-in event subroutines like those exposed by ThisWorkbook or worksheet
modules? Those should be hidden by default. If they aren't, that means
someone switched them to Public, in which case you should switch them back
to Private. If you have code from other modules calling those event
subroutines directly, you have bigger problems than users being able to see
them in the Run Macro dialog. Consider moving the code from those event
subroutines to public subroutines or functions, which you can then call both
from the event routines and other modules.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Dick Kusleika]

Ben

Declare your activex code as Private (which Excel should default to when it
sets up the sub and endsub statements. In the activex code, put

Private Sub Activex_Click()
DoOtherSub
End Sub

Where DoOtherSub is the name of a procedure in a standard module.
DoOtherSub will do all the work that the event code used to do.

Sub DoOtherSub (Optional Dummy as Long)
'stuff goes here
End Sub

If you do it this way, you may not need that dummy argument anymore.
Depending on what the event code does, you may want to pass some actual
parameters to DoOtherSub. If, for instance, you reference the control that
was clicked and the worksheet on which it was clicked, you may code as

Sub DoOtherSub(Optional ByVal ws As Worksheet, Optional ByVal ctl As
Variant)

If ws Is Nothing And IsMissing(ctl) Then
Debug.Print "Called from elsewhere"
Else
Debug.Print "Called from event"
End If

End Sub

and this would be called from the event sub as

DoOtherSub Me, Me.CheckBox1