Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Private Sub Excecution (XL97:SR2)

    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

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Private Sub Excecution (XL97:SR2)

    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

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Private Sub Excecution (XL97:SR2)

    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

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Private Sub Excecution (XL97:SR2)

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

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Private Sub Excecution (XL97:SR2)

    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

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Private Sub Excecution (XL97:SR2)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Private Sub Excecution (XL97:SR2)

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

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Private Sub Excecution (XL97:SR2)

    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.

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Private Sub Excecution (XL97:SR2)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •