Call XLA function in Visual Basic Macro ?

[Thibaud Bouquely]

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 !

 

[Rob Bovey]

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

 

[Jim Rech]

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
| 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 !
|
|

 

[Myrna Larson]

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"

 

[Rob Bovey]

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

 

[Myrna Larson]

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