Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a Private Sub
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a Private Sub
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. In article , ben wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a Private Sub
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] ben wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a Private Sub
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a Private Sub
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. "Sharad" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a Private Sub
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 "ben" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a Private Sub
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." "Chip Pearson" wrote: 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 "ben" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a Private Sub
ben,
ben wrote: 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] |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a Private Sub
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 -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com ben wrote: 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." "Chip Pearson" wrote: 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 "ben" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Private Sub() - Sub() | Excel Discussion (Misc queries) | |||
Private sub | New Users to Excel | |||
Private Sub Worksheet_Activate() | Excel Programming | |||
Calling a private sub | Excel Programming | |||
calling private subs | Excel Programming |