Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling routines in an Add-in

    Hi,

    Now that I've removed my normal.dot, I have some common routines in an add-in which I would like to be able to execute from any template.

    I started by setting an explicit reference to the add-in from each template, but the reference appears to hard code a reference to the location of my version of the add-in. I would want it to be generic for a user.

    Even though it's an add-in available in the startup folder, I can't seem to reference routines in the add-ins from code in the template. Add-in routines are, however, available from "macro on exit" from form fields.

    Is it possible to use something like late binding to reference the routines? Or is there something else I am missing?

    TIA
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  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: Calling routines in an Add-in

    If I create this in a module in Document1, it works:

    Sub test1()
    Call shrinkpic
    End Sub

    ShrinkPic is a procedure in a global template. Is this what you are trying to do? (I wasn't able to use the dotted notation that appears in the Macros/Customize dialogs, although that would have been great to avoid potential ambiguity.)

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

    Re: Calling routines in an Add-in

    Jeff,

    That's what I want to do. But in my case, "shrinkpic" is not in normal.dot or any template which has an explicit reference set, but in a template in the Word start-up folder- and therefore, one which starts up when Word is started.

    I can, as I've found out, use "Application.run macroname:=..." to run a macro, but I can't retrieve values from a function.

    I did find a <A target="_blank" HREF=http://msdn.microsoft.com/library/officedev/odeopg/deovrworddocumenttemplatesvswordaddinsglobaltempla tes.htm>Microsoft article</A>- the last paragraph of which tells me what I am dreading I think.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Calling routines in an Add-in

    Geoff

    If the file is always stored in the Start-Up Path for Word and you need an explicit path... couldn't you determine the startup path with a
    MsgBox Application.StartupPath

    and add the filename after that.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

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

    Re: Calling routines in an Add-in

    Andrew,

    Having determined the path and filename, can I execute the macro in that template using the path? The syntax I've seen for the Application.Run is (quoting from MS help):
    <pre>Application.Run "Normal.Module1.MAIN"
    Application.Run "MyProject.MyModule.MyProcedure"
    Application.Run "'My Document.doc'!ThisModule.ThisProcedure"
    </pre>


    Does that mean that for syntax #3, I can specify the full path (obviously as a variable name)?

    Thanks
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  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: Calling routines in an Add-in

    In the page you referenced, the last cell says:

    No reference is set to an add-in's VBA project when it is loaded. Therefore, although you can call procedures in the add-in project through toolbars, menu items, or macros, you cannot directly call a procedure in an add-in's project from code running in a document unless you explicitly set a reference to the add-in's project.

    What is "code running in a document" and can it be replaced with template procedures/macros? Hmmm... Well, as far as I can tell, there is no problem with procedures, only functions, as you point out.

    I put this in my global template:

    Function MinutesTo(strTime As String) As Long
    Dim lngMinutes As Long
    lngMinutes = (Hour(strTime) * 60) + Minute(strTime)
    MinutesTo = DateDiff("n", Now(), DateAdd("n", lngMinutes, Date))
    End Function

    Then I put this in a module in Document1:

    Sub test2()
    Dim anAddIn As AddIn
    For Each anAddIn In AddIns
    If InStr(1, anAddIn.Name, "myGlobal", vbTextCompare) Then
    VBE.ActiveVBProject.References.AddFromFile anAddIn.Path & _
    Application.PathSeparator & anAddIn.Name
    Exit For
    End If
    Next
    MsgBox MinutesTo(#5:00:00 PM#) & " minutes to quittin' time"
    End Sub

    But it will not compile, and therefore cannot run. Unless there is a way to make it run without compiling first... in fact, there is. Under Tools|Options...|General, if you uncheck Compile on demand, only the module in which code is being executed will be compiled. So you can put your function calls in a separate module from the "initialization" code, run the latter first to create the reference, and then proceed. At least in my little test.

    So in Module1 -

    Sub test3()
    Dim anAddIn As AddIn
    For Each anAddIn In AddIns
    If InStr(1, anAddIn.Name, "myGlobal", vbTextCompare) Then
    VBE.ActiveVBProject.References.AddFromFile anAddIn.Path & _
    Application.PathSeparator & anAddIn.Name
    Exit For
    End If
    Next
    End Sub

    And in Module2 -

    Sub test4()
    MsgBox MinutesTo(#5:00:00 PM#) & " minutes to quittin' time"
    End Sub

    Does this help?

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

    Re: Calling routines in an Add-in

    Jeff,

    It could well help. I will have to check it back at work tomorrow. It certainly looks as if it has promise.

    It seems to me strange that one must have to go through hoops to what appears to be a simple objective- to avoid the use of ANY routine in normal.dot. It seemed logical to me that whatever was available to me in normal.dot should be available in the same way in an add-in. And I have proceeded along a path making that assumption.

    Now I may be able to achieve that. But it's not a simple process. I was under an impression that many people had been able to do the same- hence all the posts, here and elsewhere, saying to "avoid putting anything in normal.dot at any expense". But that may well be too simplistic.

    Has anybody else had problems trying to achieve the same?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  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 routines in an Add-in

    Not in Word, but I did have the same problem in Excel. I completed developing an application that had all the code in an addin. When I went to deploy the application I had all of the problems that have been descrbed here. I ended up ripping out the addin and using Application.Run and putting all of the code in a standard .XLS workbook file that the template loaded from a directory that the install routine pointed to from the registry.
    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling routines in an Add-in

    >No reference is set to an add-in's VBA project when it is loaded. > unless you explicitly set a reference to the add-in's project.


    I goota try THIS one.

    Stand by. There may be a YeeeeeeeeHAAAAAAAAAAAAAAAAAAAH! in a short while.


    Good post!!

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

    Re: Calling routines in an Add-in

    No, using Application.Run you can't get a return value from a function. However, you can change the Function to a Sub, and pass it a variable ByRef and get a value back that way. Not as esthetically nice, but it does work.
    Legare Coleman

  11. #11
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Calling routines in an Add-in

    Geoff

    The following works for me in Word97
    <pre>Dim sPath As String, sCall As String
    sPath = Application.AddIns("AddIn.dot").Path & Application.PathSeparator & "AddIn.dot"
    sCall = sPath & "!ModuleName.MacroName"
    Application.Run sCall</pre>

    Andrew Lockton, Chrysalis Design, Melbourne Australia

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

    Re: Calling routines in an Add-in

    Andrew,

    That works for me too, thank you very much for that.

    I'm sorry I couldn't follow up on yesterday's post, I was almost out the door when I saw it, so didn't have a chance to investigate further then, just to fire off a quick reply. But that works well, and is much better than the way I was going to do it (which would have meant giving every one of 500+ templates a unique project name).
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

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

    Re: Calling routines in an Add-in

    Legare,

    Nice thinking! It works well.

    Thanks for that
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  14. #14
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Calling routines in an Add-in

    Geoff

    Doesn't it strike you that 500+ templates is a tad too many. Is there no way you can roll some together and use autotexts to provide different contents. Maintenance on that many templates is completely beyond anything I would contemplate.

    By the way, I'm in Canberra next week for the AODC conference - are you attending?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

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

    Re: Calling routines in an Add-in

    Andrew,

    >Doesn't it strike you that 500+ templates is a tad too many.

    It does have its advantages this way. It means that the menu for selecting a form comes straight off the directory. If a new form is added, it shows immediately in the window. It also means that users can develop their own forms which quite often require only a little brushing and a little automating.

    Things like this don't come up very often, but when they do it IS a pain.

    The organisation uses forms internally, for members of the general public, and for dealing with other agencies.

    >the AODC conference

    I didn't even know it was on!

    *** Edited: following added ***
    I assume <A target="_blank" HREF=http://www.conference.hyperwrite.com.au/frmain.htm>this</A> is the reference.

    Now I now what it is- not quite my line. I'm a programmer working with VBA rather than a workhorse Word user. But get in contact anyway.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Page 1 of 3 123 LastLast

Posting Permissions

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