Consulting

Results 1 to 13 of 13

Thread: Solved: Calling macro in another project

  1. #1
    VBAX Regular
    Joined
    Jan 2006
    Posts
    56
    Location

    Solved: Calling macro in another project

    Hi,
    I'd like to call a macro from a different project so that I can reuse code. Both macros are in different TemplateProjects in Word's startup folder. The example I see in VB Help uses square brackets and a project file :

    [Project X.vba][ModuleName]MacroName()

    Since I want to call one subroutine from a macro in another module, I tried the following :

    Call [GeneralOptions.dot][AutoExec]AddShortcut(x, y, z)

    where x,y,z are the arguments passed to the function AddShortcut in the macro AutoExec in my TemplateProject GeneralOptions.dot in the startup folder.

    The above syntax doesn't work on OSX 10.2.8 and Office/Word 10.1.6. I get Sub/Function not found.

    How do I call a macro in one TemplateProject from another Template Project in OSX ? Am I missing the path to the Template Project ?

    Any ideas would be greatly appreciated!


    Ed

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by mansky
    Hi,
    I'd like to call a macro from a different project so that I can reuse code. Both macros are in different TemplateProjects in Word's startup folder. The example I see in VB Help uses square brackets and a project file :

    [Project X.vba][ModuleName]MacroName()

    Since I want to call one subroutine from a macro in another module, I tried the following :

    Call [GeneralOptions.dot][AutoExec]AddShortcut(x, y, z)

    where x,y,z are the arguments passed to the function AddShortcut in the macro AutoExec in my TemplateProject GeneralOptions.dot in the startup folder.

    The above syntax doesn't work on OSX 10.2.8 and Office/Word 10.1.6. I get Sub/Function not found.

    How do I call a macro in one TemplateProject from another Template Project in OSX ? Am I missing the path to the Template Project ?

    Any ideas would be greatly appreciated!

    Ed
    You would have to set a reference to the otherwook workbook in your workbook for that to work.

  3. #3
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    I'm not sure if this would work on the Mac, but have you tried looking up the Run method?

    The Help's example is:
    [vba]Application.Run "'My Document.doc'!ThisModule.ThisProcedure"[/vba]

    It doesn't say anything about not being able to use named arguments in Word, (it can't in Excel,) so I'm assuming you could adapt it to:
    [vba]Application.Run "'My Document.doc'!ThisModule.ThisProcedure(x,y,z)"[/vba]
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  4. #4
    VBAX Regular
    Joined
    Jan 2006
    Posts
    56
    Location

    Run method and Reference Object Library

    Hi Ken,
    The Run method on the Mac is also limited to subroutines/functions w/o arguments as stated in the VBA Help manual:


    "Note Only public Sub procedures that take no arguments, which includes all procedures generated by the macro recorder and all procedures you can run from the Macros dialog box, can be run by using the Run method."

    I've tried specifying the project by name with and without a postfix, but no go. I couldn't get any of them to work.

    I know that the VBA 10.1.6 (Office X.1.6) is missing the split and join functions for example. Is it possible that VBA on the Mac is missing the "Reference Object Library" needed to get VBA to do the linkage at runtime to external symbols in other projects?

    Ed

  5. #5
    VBAX Regular
    Joined
    Jan 2006
    Posts
    56
    Location
    Hi xld,
    I saw that point made in the VBA Help file, but am unsure how to do that in Word?
    I don't see the Microsoft Office Reference Object Library 8.0 on the Mac. I believe I have everything loaded from the Office CDROM.

    Ed

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by mansky
    Hi Ken,
    The Run method on the Mac is also limited to subroutines/functions w/o arguments as stated in the VBA Help manual:


    "Note Only public Sub procedures that take no arguments, which includes all procedures generated by the macro recorder and all procedures you can run from the Macros dialog box, can be run by using the Run method."

    I've tried specifying the project by name with and without a postfix, but no go. I couldn't get any of them to work.

    I know that the VBA 10.1.6 (Office X.1.6) is missing the split and join functions for example. Is it possible that VBA on the Mac is missing the "Reference Object Library" needed to get VBA to do the linkage at runtime to external symbols in other projects?

    Ed
    I thought the Mac's Applicatiuon.Run could take 2 arguments.

    Have you tried setting a reference to the project and calling it directly?

  7. #7
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by mansky
    I know that the VBA 10.1.6 (Office X.1.6) is missing the split and join functions for example. Is it possible that VBA on the Mac is missing the "Reference Object Library" needed to get VBA to do the linkage at runtime to external symbols in other projects?
    Hi Ed,

    Honestly, I couldn't tell you. I don't have access to a Mac at all. Some stuff works, some stuff doesn't, and some just needs to be dealt with a little differently, as far as I can see. Unfortunately, without the ability for me to get my hands on a mac, I'm not going to be much worth to you.

    I'll "call" a couple of our Mac experts, though, and see if they can answer the question for you.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  8. #8
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Quote Originally Posted by mansky
    Hi Ken,
    The Run method on the Mac is also limited to subroutines/functions w/o arguments as stated in the VBA Help manual:

    I know that the VBA 10.1.6 (Office X.1.6) is missing the split and join functions for example. Is it possible that VBA on the Mac is missing the "Reference Object Library" needed to get VBA to do the linkage at runtime to external symbols in other projects?

    Ed
    Howdy. "Reference Object Library" is missing in the MAc VBA (based on VB5, and even limited) and most other Libraries are missing as well. This is frustrating in many ways. This also means that the very slick, ultra-fast Find-Replace that I use in XL 2002/2003 is useless in XL 2004 on Mac OS X 10.3.9.

    It may be that AppleScript could be written to help in this regard, although I have not done so. BlueCactus would be the one to help in doing something along those lines.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  9. #9
    VBAX Regular
    Joined
    Jan 2006
    Posts
    56
    Location

    Some libraries found

    Hi Ken and shades:
    Thanks for the replies and suggestions. I didn't think of Applescript, will look into that now. I'm coming from a FORTRAN/C/Perl (and others!) background, so I am actually more comfortable going out into Applescript and then Perl from VBA. At least until I get enough VBA under my belt

    In the meantime, I continued to look around and indeed found the Object library I thought should be there. The "References" dialog window I found under "Tools"/"References" in the VBE. I already had the following libraries checked:

    Visual Basic for Applications
    Microsoft Word 10.0 Object library
    OLE Automation
    Microsoft Forms 2.0 Object Library
    Microsoft Office 10.0 Object Library

    The following libraries/templates were unchecked:

    Normal
    TemplateProject
    TemplateProject
    TemplateProject
    Microsoft Excel 10.0 Object Library
    Microsoft Graph 10.0 Object Library
    Microsoft PowerPoint 10.0 Object Library
    VBA Converter
    Microsoft Visual Basic for Application Extensibility

    The 4 TemplateProject entries I assume for for the 4 templates I have in the Word startup folder. I would asume that if I wanted to have "references" to the code modules in these 4 templates in the startup folder available to other custom templates in "My Templates" (for example), that these items should be checked. Is this correct? Also, is the use of the term "reference" in the VBE similar to pointer in C ?

    Also, what are the last two libraries: VBA Converter and MS Visual Basic for App Extensibility for ? Should they be checked as well?

    Thanks for the help!

    Ed

  10. #10
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi Ed,

    I don't know any C, so can't guarantee the correct answer on pointers. Setting a reference basically creates a link to another dll library of code. Ie, you could set a reference in Word to all of the Excel object model, contained in a dll file. Does that sound similar to C pointers?

    As far as the references, if you're just going from Word to Word, you shouldn't need any additional references set at all.

    VBA Converter is a new one to me. I'm going to assume that it is something Mac specific, unless someone says otherwise. Possibly to ensure compatibility with VBA on the Mac?

    The VBE Extensibility library allows you to access the VBE objects, properties and methods. So if you want to write VBA to write VBA, add/remove code modules, etc..., that's the one you'd need.

    Sorry I can't be more help...
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  11. #11
    VBAX Regular
    Joined
    Jan 2006
    Posts
    56
    Location

    Libraries

    Hi Ken,
    Thinking more about the general issue, "references" in Word VBA are not pointers, but rather entire libraries. That is, when one "checks" the checkbox for a particular item displayed in the "Tools/References" window in VBE, the macros and toolbars are then available for calling in the other project, which is akin to using the "-L/-l" pair in specifying a particular library file to link to at load time via the command line compilation statement (eg. gcc).

    I am able to "check" the VBE Extensibility library and include it as a "Reference". However, I get an error message:"Name conflicts with existing module, project or object library" when I try to check the "VBA Converter" library. Obviously, there's more going on here for me to work out.


    Thanks for the help!

    Ed

  12. #12
    VBAX Regular
    Joined
    Jan 2006
    Posts
    56
    Location

    Got It!

    Hi guys,
    I've made some progress in getting my code to work. Specifically, I've figured that I had, in VBE, to assign a unique name to the Project (TemplateProject in my cases) under "Tools/Project [TemplateProject] properties ..." before I could add that Project as a Reference in another project. Once I added the Reference to the desired TemplateProject in the target Project, I could compile the macro that called the macro in the other TemplateProject just fine.

    For clarity, here's an example that works on the Mac:
    [VBA]
    Sub LoadXrefTemplate()
    Dim TmpPath As String, TmpName As String

    TmpPath = "Macintosh HD:Applications:Microsoft Office X:Templates:My Templates:"
    TmpName = "Xref.dot"

    Documents.Add Template:=TmpPath & TmpName, _
    DocumentType:=wdNewBlankDocument, Visible:=True

    Call LoadXref.ThisDocument.Document_New

    End Sub

    [/VBA]

    The above code snippet is in a macro in Normal.dot and is called to attach a template (template filename=Xref.dot, TemplateProject name = LoadXref).

    The code I want executed is in the event procedure Document_New of the ThisDocument class module of the attached template.

    What I found, after experimenting, was that the first name in the Call statement above needed to be the unique name of the TemplateProject, and neither the default name (always TemplateProject), or the filename of the actual template file.

    With these requirements satisfied, the call statement works fine. Whether using a Call statement is the best way to raise an event is another thread .

    Thanks for all the help!


    Ed

  13. #13
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Great news, Ed! Thanks for posting back as well.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





Posting Permissions

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