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

    Calling Function in another Workbook (03)

    Normally I can use a call to a function of the active workbook by using: ThisWorkbook.MyFunction

    How can I call a function to a different workbook that is opened?

    Thanks,
    John

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

    Re: Calling Function in another Workbook (03)

    Say that the workbook is named OtherBook.xls, with a macro MyMacro in a module named Module1. You can call the macro as follows from another workbook:

    Application.Run "OtherBook!MyMacro"

    or

    Application.Run "OtherBook!Module1.MyMacro"

    If you have a function MyFunction with an argument MyArg, you can assign the return value as follows:

    MyVar = Application.Run("OtherBook!MyFunction", 37)

    Note the way the argument is specified.

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

    Re: Calling Function in another Workbook (03)

    Hans,

    Can you clarify what you mean by, "with an argument MyArg,"

    I notice that the line of code: MyVar = Application.Run("OtherBook!MyFunction", 37) contains 37 which I intreprete as the argument but do not know what the 37 represents.

    Thanks,
    John

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

    Re: Calling Function in another Workbook (03)

    Here is an example. Suppose you have a function

    Public Function MyFunction(MyArg As Integer) As Integer
    MyFunction = MyArg * 3 + 1
    End Function

    The function MyFunction has an argument MyArg of type Integer. Normally, you'd call it like this:

    MyVar = MyFunction(37)

    This instruction passes 37 as MyArg; the result will be that MyVar is assigned the value 112 (= 3 * 37 + 1).

    If you want to call MyFunction from another workbook, you cannot use the above syntax. Instead, use the syntax from my previous reply.

    Note: the 37 is just an example, it has no intrinsic meaning.

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

    Re: Calling Function in another Workbook (03)

    It is much clearer now. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks,
    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
  •