Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Portland, Maine, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Call sub procedure (VBA/Word 2002)

    I am creating a series of templates (30-40) that are all going to need the same code. Instead copying this code into each template, I would like to have each template call the procedure from another template. I can't get this to work. Is there a way to do this?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call sub procedure (VBA/Word 2002)

    One way is to put the code in a template that is located in Word's StartUp directory.

    Another way is to compile the code into an ActiveX DLL and instantiate the class in each template to allow access to the code.
    Each project would need a reference to the DLL.

  4. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Portland, Maine, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call sub procedure (VBA/Word 2002)

    Everything I have done has been in just VBA - I haven't created DLL's. I use either VB or Office Develope do this, correct?

  5. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call sub procedure (VBA/Word 2002)

    If you do not have VB or Office DEveloper, then you are limited to the first alternative, i.e., putting the template in the STartUp directory.

    Using ActiveX DLLs created with VB 6 to achieve both code security and better exceution speed.
    I've not compared this with the results of creating the DLLs in Office Developer.

    Another advantage of using VB 6 DLLs is that you can use features of VB 6 not in Office 97 VBA and the resultant DLL can be used with Office 97, 2000 and XP.

  6. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Portland, Maine, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call sub procedure (VBA/Word 2002)

    I am going to use a template in the startup folder - at least for now.

    Here is the syntax:

    Application.Run "'MyTemplates'!StartIt.StartHere"
    MyTemplates - name of template in Word's startup folder
    StartIt - module name
    StartHere - procedure name

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

    Re: Call sub procedure (VBA/Word 2002)

    In your series of template you can set a reference in VB (Tools, References and browse to the file location) to your template holding the common code. To do this succesfully you may need to change the Project name of the common template (in VB select the template project and from View Properties change the name).

    You should then be able to call on the common code, and if your other templates do not contain macros with the same name(s) you should be able to do so without prefixing the macro name with the template name.

    Andrew C.

  8. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call sub procedure (VBA/Word 2002)

    If the macro is in a template in the startup folder, you need to make the macro Public, then ypu call the macro by using only

    StartHere

  9. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Portland, Maine, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call sub procedure (VBA/Word 2002)

    Thank you for your help!!

    For this client I have 55 templates. Each of the templates could be on one of four different letterheads . Each of these templates has a one line autonew macro: StartHere. Starthere is a public sub in a template in the startup folder. The macro calls a dialogue to choose which of the four letterheads to use. So - I have 55 different templates each with 4 different possible headers - and only one procedure. So later modifications to the letterheads or the macros are centralized and easy to manage.

  10. #9
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call sub procedure (VBA/Word 2002)

    Andrew's approach is a variation on the same theme. It has an advantage that the location is kept with the template, and cannot be changed by the user. A user can change the startup file location via Tools, Options. That can be locked down, it depends on your LAN people. If you LAN people can't lock down the start location, I'd go for Andrew's way. Otherwise Howard's way is fine. We have good LAN people at my site- so the startup folder works well for us.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

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