Private Sub Excecution (XL97:SR2)

[jstevens]

Is it possible to execute a private sub from a different workbook. As an example Workbook A contains code that would open Workbook B. Workbook B contains private sub-routines.

Workbook A code would look something like-
Workbooks.Open FileName:="WorkbookB"
'Execute a sub-routine in Workbook B
Close Workbook B

Thanks,
John

[jscher2000]

This works in Excel 2000. Took me a while, and some help from MSDN, to figure out that it was a bang (!) delimiter rather than a period.

Sub HiBoth()
' Run a private sub in this workbook, Book1.xls
Call Bk1Hi
' Run a private sub in a different open workbook, Book2.xls
Application.Run "Book2.xls!Bk2Hi"
End Sub

[jstevens]

I tried your suggestion of Application.Run"Book2.xls!Bk2Hi" and it works for a sub in a module. How about a private sub? I can't get it to work.

John

[jscher2000]

<P ID="edit" class=small>Edited by jscher2000 on 13-Jul-02 11:39.</P>Yes, I tested in normal code modules, putting the word Private before the names of the various subs.

If your procedure is in a class module or form, I think those are handled differently because they are supposed to be sealed containers that only expose what they intentionally expose. If it is in ThisWorkbook, I don't know; don't have any experience putting procedures there and don't know if it, too, is treated like a clas module. <font color=blue>Update: it doesn't work if the Private Sub Bk2Hi is in ThisWorkbook.</font color=blue>

Am I completely confused now?

[jstevens]

You're not the only one confused although I think we are on the right track. The private sub is in a form module such as CommandButton1_Click(). I've been toying with this idea for sometime now. It appears that code like Application.Run ("Book2!CommandButton1_Click") or Application.Run ("Book2!PrivateCommandButton1_Click") may not be possible.

Perhaps someone else can shed some light on this.

John

[jscher2000]

I'm fairly certain that a Private Sub in a form module, like a class module, is truly private. I also wonder whether it is logical for the code to be accessible when the form is not running; even a Public Sub in a form module might be invisible when the form is not running.

What kind of procedure do you need to run from a different workbook's form module? You might be better off putting it in a regular module so all concerned procedures can share.

[charlotte]

A private routine, sub or function, has a scope limited to the container it is in. It can be addressed by other routines in that form, module, or class, but not from outside. That isn't Excel, it's VBA.

[jscher2000]

As far as VB and VBA are concerned, that is 100% true. There appears to be a limited workaround in Word and Excel: Application.Run can execute a Private Sub in a regular code module in a different template/workbook. Perhaps it is better not to rely on this, as there may be unanticipated scoping problems.

[jstevens]

Thanks for everyone's suggestions. I'll place the code in a regular module and call it from a form module. The suggestion of Application.Run from a module does work and this seems to be the resolution.

John