Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Christchurch, Canterbury
    Posts
    122
    Thanks
    27
    Thanked 1 Time in 1 Post

    Application.run with variables (Word XP)

    The Help file says you can pass variables:
    expression.Run(MacroName, varg1, varg2, varg3, varg4, varg5, varg6, varg7, varg8, varg9, varg10, varg11, varg12, varg13, varg14, varg15, varg16, varg17, varg18, varg19, varg20, varg21, varg22, varg23, varg24, varg25, varg26, varg27, varg28, varg29, varg30)

    But whenever I try I get an error 438.

    (Now, don't tell me to reference & just Call - that was the original code but now people have moved to Citrix and files are stored in paths with the username it means that each person has to be set individually to their own master template otherwise each tempalte that references the master pulls in a new copy of it - causing a range of problems)

    Now, without the parameters it worked fine:
    Application.Run "LA2307.Rates.SSColBalance"

    but Application.Run "LA2307.Rates.SSColBalanceParam",varBalanceToSettl e gives an error 438 - Object doesn't support this property or method which is real useful.

    The other end says:
    Public Sub SSColBalanceParam(varBalanceToSettle As Variant)
    SSColumnBalance
    varBalanceToSettle = cDebitColumn - cCreditColumn
    End Sub

    I've also tried :
    Application.Run MacroName:="LA2307.Rates.SSColBalanceParam", varg1:=varBalanceToSettle
    ie used the named parameters and a variant (although the help file shows a string)

    Any ideas?

  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: Application.run with variables (Word XP)

    Does it make any difference if you omit "LA2307.Rates." from your code?

    A most likely irrelevant thought: Is varBalanceToSettle a global variable that you are purposely changing in multiple procedures? If so, maybe you could rewrite your Public Sub as a Function.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Boston, Massachusetts, USA
    Posts
    389
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.run with variables (Word XP)

    You can pass variables, as in the following:
    <pre>Sub MacroToCall(var As Variant)
    var = 100 - var
    MsgBox CStr(var)
    End Sub
    '
    Sub CallOtherMacro()
    Dim var As Variant
    var = 99
    Application.Run "MacroToCall", var
    End Sub
    </pre>

    Can you post the original code? Sounds like something else is going on here.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Application.run with variables (Word XP)

    Can you actually run a procedure with an argument if the procedure is located in a module in another document? I can't make it work in Word (it works fine in Excel, with the restriction that all arguments are treated By Value.)

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Application.run with variables (Word XP)

    Judith,

    The mention of not being able to set a reference to a global template on a Citrix setup caught my attention, 'cause it doesn't jibe with my experience: at my former firm, everyone was on Citrix, we had a global startup template and most of the workgroup templates had a reference set to it - this worked fine.

    I just checked with a former colleague to refresh my memory, and basically how it works is that they've got both the workgroup and startup templates directories, mapped to a specific drive name - so this path is the same for every user (so for example, startup templates are located at "T:wrdStartup".) Each user's profile setup includes pointing to the mapped directory locations for workgroup and startup templates.

    This method worked great and we were able reference the global template and call procedures in it with arguments, etc. Would something like this be feasible in your environment?

    Gary

  6. #6
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Boston, Massachusetts, USA
    Posts
    389
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.run with variables (Word XP)

    Hi Hans,

    I was able to get it to work by omitting the Project and Module name. Even if the Sub was declared as Private, I was able to call it from any other open Project, without specifying Project or Module name:

    In one open template project:
    <pre>Sub MacroToCall(var As Variant)
    MsgBox var
    End Sub
    </pre>


    ... and from any module in any other open template (or document) project:
    <pre>Sub testcall()
    Application.Run "MacroToCall", 1234
    End Sub
    </pre>


    I was also able to get it to work, again from any other open project, by specifying the module name, but omitting just the Project name.

    I'm certainly a bit confused here, and am glad I don't typically use Application.Run, except from COM, where it seems to work as expected.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Application.run with variables (Word XP)

    Oh, wait, I was using a document. If I use a template, I see the same as you do. This is very strange too - why are we able to run a Private procedure in another module?

  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: Application.run with variables (Word XP)

    I think the scope would be the same as what the user sees in the Macros dialog: document, attached template, Normal.dot, global templates, and COM Add-ins. I would have doubts about other open documents or templates which are not in the normal scope, but it could be made to work with a reference to the file name (e.g., the "Filename ! Procedure" syntax).

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Application.run with variables (Word XP)

    If I have Document1 open, and Template2, where Template2 is NOT the attached template of Document1, code in a module in Document1 can run a private procedure in a module in Template2. That seems strange to me.

    I cannot make it work at all with a filename reference.

  10. #10
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Christchurch, Canterbury
    Posts
    122
    Thanks
    27
    Thanked 1 Time in 1 Post

    Re: Application.run with variables (Word XP)

    Brilliant - the combination of dropping the "LA2307.Rates" and using a function did the trick. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    That leaves the question how how to pass variables. Nevermind.

  11. #11
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Christchurch, Canterbury
    Posts
    122
    Thanks
    27
    Thanked 1 Time in 1 Post

    Re: Application.run with variables (Word XP)

    We have a setup where each user has their private copy of the templates & startup files. This means we can deploy at anytime without having to get everyone to log off - and means we don't have contention issues. The only overhead is a little extra storage and a copy in the login script. Worked really well until recently.

  12. #12
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Christchurch, Canterbury
    Posts
    122
    Thanks
    27
    Thanked 1 Time in 1 Post

    Re: Application.run with variables (Word XP)

    Ah ha - my code had a string not a variant. Using a variant in the called code and omitting the LA2307.Rates. part of the syntax work.

    Go figure. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Thanks every one.

  13. #13
    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: Application.run with variables (Word XP)

    Privacy isn't what it used to be. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    I guess I haven't used the bang notation in Word, just in Excel. Someday someone will gather all the posts on this and create one coherent help file. But until then... must get back to work.

  14. #14
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Application.run with variables (Word XP)

    I only get the result you noted (1) if Template2 is the ActiveDocument or (2) if Template2 is loaded as a global template (not just open) -- although I'm still surprised (as you were) that a private sub would be available to another document or template in either of those cases.

    Like Jefferson, I'd expect that, using Application.Run, you could call any sub that was available in the Macros dialog, but a private sub in Template2 doesn't show in the Macros dialog regardless of whether Template2 is open or loaded globally.

    This doesn't have that "by design" smell to me.

  15. #15
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Application.run with variables (Word XP)

    Sounds like you may have gotten Application.Run working OK, in any case just want to clarify my previous note: although each user's set of templates is in a location with a specific mapped drive name that's the same for all users, the templates actually reside under each user's individual profile (is that what you mean by "private copy"?) so they do each have their own copies of the templates - with the mapped drive name the same for each user, Word seems to have no trouble finding the global template which has been referenced by a workgroup template.

    I don't think deployment was an issue at my old firm either - each user's set of templates on their profile would be selectively updated via a login script. And as to updating the template directory stores on the servers themselves, they would do the updates late at night when there was the smallest possible number of users, but because there were multiple servers for each location, any users who happened to be logged on could have their Citrix sessions dynamically switched to another server, without their session having to be ended - so they could move around any users who happened to be on, until all of the servers had gotten the updates.

    Gary

Page 1 of 2 12 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
  •