Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling a procedure that is held in another book (2003)

    While working in one workbook I need to call a procedure that is held within another workbook. The middle line within the following code, where FileEngagementCodes holds the full file address of the workbook in which the code required is held, comes back with subscript out of range or the standard does not support message:

    Workbooks.Open FileEngagementCodes
    Call Workbooks(FileEngagementCodes).Module1.FindUniques
    Workbooks(FileShortEngagementCodes).Worksheets("Ac counts output").Copy after:=ThisWorkbook.Worksheets("Provisions summary")

    Can someone please help with the required syntax

  2. #2
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a procedure that is held in another book (2003)

    Thanks that has worked very well

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

    Re: Calling a procedure that is held in another book (2003)

    You must use Application.Run to call code from another than the active workbook. Assuming that FileEngagementCodes is a string variable holding the workbook path and name, use

    <code>
    Application.Run "'" & FileEngagementCodes & "'!FindUniques"</code>


    or

    <code>
    Application.Run "'" & FileEngagementCodes & "'!Module1.FindUniques"</code>


    Note the use of single quotes around the workbook name.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a procedure that is held in another book (2003)

    I have a supplementary question relating to the following code:

    Application.Run "'" & LongFileNameManAccts & "'!GetFileAddressesMod.SetFileNames(0)"
    LongFileNameEngagementcodes = Application.Run "'" & LongFileNameManAccts & "'!GetFileAddressesMod.fnEngagementCodesListLongFi leName"
    ShortFileNameEngagementcodes = Application.Run "'" & LongFileNameManAccts & "'!GetFileAddressesMod.fnEngagementCodesListShortF ileName"

    In line 1 above I need to pass the variable 0 to the SetFileNames procedure - this syntax does not seem to work
    Line 2 syntax an line 3 syntax both fail at the "'" point
    - can you assist with some better syntax for all 3 lines please

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

    Re: Calling a procedure that is held in another book (2003)

    You can pass arguments by placing them after the name of the macro/procedure/function, separated by commas. Your first instruction becomes:

    Application.Run "'" & LongFileNameManAccts & "'!GetFileAddressesMod.SetFileNames", 0

    In the second and third instruction, you are calling a function, so you must enclose the argument(s) to Application.Run between parentheses:

    LongFileNameEngagementcodes = Application.Run("'" & LongFileNameManAccts & "'!GetFileAddressesMod.fnEngagementCodesListLongFi leName")
    ShortFileNameEngagementcodes = Application.Run("'" & LongFileNameManAccts & "'!GetFileAddressesMod.fnEngagementCodesListShortF ileName")

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a procedure that is held in another book (2003)

    Thanks

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a procedure that is held in another book (2003)

    Hans,

    Is it possible in your example to run a Private Sub attached to a form?

    Your Example:
    Application.Run "'" & FileEngagementCodes & "'!Module1.FindUniques"

    Thanks,
    John

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

    Re: Calling a procedure that is held in another book (2003)

    Not as far as I know. You can make the sub Public, though. You should then be able to run it:

    ' in the userform module:

    Public Sub CommandButton1_Click() ' the default is Private
    Beep
    End Sub

    ' to run it from a standard module:

    Sub Test()
    UserForm1.CommandButton1_Click
    End Sub

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a procedure that is held in another book (2003)

    Thanks and have a great weekend.

    John

Posting Permissions

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