Call XLA function in Visual Basic Macro ?

Hello

I want share a personal sub between differents worksheets

could you give me the tips to do this ?

(Exemple : if I do a XLA file with my sub (public)

I can not call it in another module in another worksheet (I certainely
forget something ...)

thanks for your help !

"Thibaud Bouquely" <[email protected]> wrote in message
news:[email protected]...
> I want share a personal sub between differents worksheets
> could you give me the tips to do this ?
> (Exemple : if I do a XLA file with my sub (public)
> I can not call it in another module in another worksheet (I certainely
> forget something ...)

Hi Thibaud,

The way I prefer to do this is to use the Application.Run method. The
general syntax is:

Application.Run "'YourAdd-in.xla'!YourSubName"

Note that the name of your XLA file is surrounded by single quotes. This may
not be necessary depending on the file name, but it won't hurt if it's not
required and it won't work if it is required, so I recommend always using
them. If you need to pass arguments to your sub you just append them as a
comma-delimited list after the XLA name/sub name:

Application.Run "'YourAdd-in.xla'!YourSubName", Arg1, Arg2, ....

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

To call a subroutine in another workbook or addin you have to use the RUN
method (e.g., Run "Book1.xls!SubInWorkbook1") or set a reference to the
other workbook/add-in (Tools, References in the VBE). You might see which
approach works better for you.

--
Jim Rech
Excel MVP
"Thibaud Bouquely" <[email protected]> wrote in message
news:[email protected]...
| Hello
|
| I want share a personal sub between differents worksheets
|
| could you give me the tips to do this ?
|
| (Exemple : if I do a XLA file with my sub (public)
|
| I can not call it in another module in another worksheet (I certainely
| forget something ...)
|
| thanks for your help !
|
|

Hi, Rob:

Just for completeness, if the routine is a function and you want to capture
the return value, you write it as

x = Application.Run("'YourAdd-in.xla'!YourSubName", Arg1, Arg2)

And a caveat from Help: "you cannot pass objects to macros by using the Run
method"

On Wed, 2 Mar 2005 04:11:37 -0800, "Rob Bovey" <[email protected]> wrote:

>"Thibaud Bouquely" <[email protected]> wrote in message
>news:[email protected]...
>> I want share a personal sub between differents worksheets
>> could you give me the tips to do this ?
>> (Exemple : if I do a XLA file with my sub (public)
>> I can not call it in another module in another worksheet (I certainely
>> forget something ...)
>
>Hi Thibaud,
>
> The way I prefer to do this is to use the Application.Run method. The
>general syntax is:
>
>Application.Run "'YourAdd-in.xla'!YourSubName"
>
>Note that the name of your XLA file is surrounded by single quotes. This may
>not be necessary depending on the file name, but it won't hurt if it's not
>required and it won't work if it is required, so I recommend always using
>them. If you need to pass arguments to your sub you just append them as a
>comma-delimited list after the XLA name/sub name:
>
>Application.Run "'YourAdd-in.xla'!YourSubName", Arg1, Arg2, ....

"Myrna Larson" <[email protected]> wrote in message
news:[email protected]...
> And a caveat from Help: "you cannot pass objects to macros by using the
> Run
> method"

Hi Myrna,

I think that's one of those help topics passed down since the beginning
of time and never revised. In Excel 5/95 you couldn't pass or return objects
using Application.Run, but that capability was added to VBA starting with
Excel 97. Try this:

--------------
In Book1.xls
--------------
Public Sub CallMe(ByRef wkbBook As Workbook)
MsgBox wkbBook.FullName
End Sub

--------------
In Book2.xls
--------------
Public Sub PassWorkbook()
Application.Run "'Book1.xls'!CallMe", ThisWorkbook
End Sub

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Hi, Rob:

Thanks for the update. In fact I tried using Application.Run to call the XIRR
function in the ATP, and used Ranges for the arguments, and got no error. I
wasn't sure whether this was because (as Help says), an object is/was
converted to its value, which is possible with a range object.

Myrna


On Wed, 2 Mar 2005 11:12:16 -0800, "Rob Bovey" <[email protected]> wrote:

>"Myrna Larson" <[email protected]> wrote in message
>news:[email protected]...
>> And a caveat from Help: "you cannot pass objects to macros by using the
>> Run
>> method"
>
>Hi Myrna,
>
> I think that's one of those help topics passed down since the beginning
>of time and never revised. In Excel 5/95 you couldn't pass or return objects
>using Application.Run, but that capability was added to VBA starting with
>Excel 97. Try this:
>
>--------------
>In Book1.xls
>--------------
>Public Sub CallMe(ByRef wkbBook As Workbook)
> MsgBox wkbBook.FullName
>End Sub
>
>--------------
>In Book2.xls
>--------------
>Public Sub PassWorkbook()
> Application.Run "'Book1.xls'!CallMe", ThisWorkbook
>End Sub