Results 1 to 13 of 13
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Calling a procedure in another workbook (97-2000)

    I want to be able to call a Public subroutine in another workbook from a subroutine in the current
    workbook.

    Documentation says that this can be done using the syntax

    vbaProjectName.ModulkeName.SubroutineName

    This I have tried in assorted formats and it does not work.

    Maybe I have the syntax wrong or I have missed the plot completely.

    I know that I can do this using Application.Run(cccc) but I wanted to use the alternate suggested method.

    Can anyone tell me how this is done.

    As an Example, suppose I am writing a subroutine in a document called TestVB.xls
    I want to Call a subRoutine called ShowInfo
    that is in a Module called basGeneral
    in a workbook called MyMacroLib.xls

    Sub CallOutside()

    SubroutineCallGoesHereButWhatIsIt?

    End Sub

    I have also tried to set a reference to the Library Workbook in Tools References as a third alternative but no joy. Would this final method work if it was an XLA Add-In instead of a Standard XLS

    Any answers gratefully received.

    Andrew Walker - andrew@d-l-r.co.uk
    Andrew

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calling a procedure in another workbook (97-2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Andrew

    I just wanted to ask that you make sure that the workbook that contains the code you want to run is open.

    If that is the case, then it should work, OK for you.

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calling a procedure in another workbook (97-2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Again Andrew

    Sorry I missed this one thing, you may need to try this kinda syntax:

    Application.Run Workbooks("Workbook Name").Name & "<font color=red>!</font color=red>Name of Sub"

    Try this, unless your typing missed it.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Calling a procedure in another workbook (97-2000)

    Have you included the full path to the workbook ?

    Andrew C

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Calling a procedure in another workbook (97-2000)

    In reply to both your mails.
    The workbook is open yes.
    I have already used Application.run method and this works fine.

    However my question was different.
    According to VBA books you should be able to call the routine from another project
    using the syntax

    Call VBAProjectName.ModuleName.ProcedureName

    Unfortunately I cannot get this alternate syntax to work at all.
    It works ok within the same Project if you have two routine with the same name but in different modules.

    It is meant to work with subroutines in other open projects as well.

    I cannot make it work like this though.

    Maybe it is not possible, in which case I will stick with Application.run

    However if it is possible, I'd love to know how to do it.
    Andrew

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Calling a procedure in another workbook (97-2000)

    Yes

    I don't want to use APPLICATION.RUN

    If I want to call a routine in another module where there are two routines with the same name within the overall project I can successfully use the syntax

    VBAProject.ModuleName.ProcedureName

    It works fine

    I want to use this notation to a procedure in a module in another project
    The book says I can, my attempts say I cannot.

    Is there a notation I can use to do this.
    If there is, How do I reference the Project ?
    This is the Bit it doesn't Like

    For Example
    I want to call a subroutine called ShowInfo
    In a Module called basGeneral
    In an open book called Personal.XLS

    I know the syntax for Application.Run - It works fine

    How do I write the alternate syntax ?

    [Personal.xls].basGeneral.GetInfo
    I even tried this with the Path. Can't see why it needs that though since it's in memory.

    I have even named the Project in personal.xls to be VBAProjectPersonal

    I tried VBAProjectPersonal.basGeneral.GetInfo

    No joy here either.

    According to John Walkenbachs book Excel 2000 Power Programming it can be done
    but he doesn't give a specific example.

    I am getting round to the opinion it cannot.

    But if ANYONE knows otherwise I'd love to know the correct reference method
    Andrew

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calling a procedure in another workbook (97-2000)

    Hi Andrew,
    You can do this as follows:
    Let's say you have a project called Utilities (doesn't matter what the workbook is called as long as it's open) with a module called modTestCode and a procedure called calltest.
    First, select Tools-References, locate Utilities and check it.
    Then in your calling module use the syntax:
    Utilities.modtestcode.calltest
    and it should run.
    Hope that helps.
    (Note: make sure the routine you're calling is not Private)
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a procedure in another workbook (97-2000)

    I think that you need to set a Reference to the workbook containing the macro before you can use that method to execute the macro. You use the References command on the Tools menu in the VBE to do that.

    However, I highly recommend using the Run method instead. I have found that if I set the Reference and use your method, then if you make any changes in the module containing the macro, then you have to go to every workbook that uses the Reference and remove the Reference and then re-establish it before it will work again. I have found no disadvantage to using the Run method.
    Legare Coleman

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Calling a procedure in another workbook (97-2000)

    I'd come to the conclusion that using the Run variant was a darned site easier.

    I might alternatively just convert the library file to an ADD-IN and distribute that way.

    Thanks - Andrew
    Andrew

  10. #10
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Calling a procedure in another workbook (97-2000)

    I've had a go at this thanks. However it doesn't seem to be bullet proof and depending upon project names it appears to get confused at times and cannot find the project.
    Typical Microsoft really. here's a feature that kinda work some of the time.

    I've come to the conclusion that using the Run variant was is a darned site easier.

    I might alternatively just convert the library file to an ADD-IN and distribute that way.

    Thanks for the help anyway - Andrew
    Andrew

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a procedure in another workbook (97-2000)

    Using an Addin for macros that you are calling from other workbooks has the same disadvantage. You still need a Reference to the workbook to call them, and they still break if you make any changes in the addin so that you have to remove and re-establish the Reference in every workbook that calls a macro.

    Can I be curious and ask why you are so dead set against using the Run method? If there is a disadvantage I would like to know what it is.
    Legare Coleman

  12. #12
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Calling a procedure in another workbook (97-2000)

    No I don't have a problem with the RUN method.
    I have always used it in the past and it works fine. I will continue to use it in the future too.
    "If it ain't bust then don't fix it."
    I was just curious from an academic point of view to find out if there was an alternative method.
    The texts I had read from Microsoft and other sources suggested the alternate method.
    Had this worked ok it would have been nice because it follows a more object based approach to the programming (as much as VB allows) rather than a function based method like using the API.
    It will all change when they force .NET on us anyway. Fortunately not immediately though.
    Thanks for your help.
    Andrew

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a procedure in another workbook (97-2000)

    What API are you talking about? The Run method is a method in the Excel Object Model, it has nothing to do with the Windows API.
    Legare Coleman

Posting Permissions

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