Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call XLA function in Visual Basic Macro ?
"Thibaud Bouquely" wrote in message
... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call XLA function in Visual Basic Macro ?
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" wrote in message ... | 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 ! | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call XLA function in Visual Basic Macro ?
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" wrote: "Thibaud Bouquely" wrote in message ... 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, .... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call XLA function in Visual Basic Macro ?
"Myrna Larson" wrote in message
... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call XLA function in Visual Basic Macro ?
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" wrote: "Myrna Larson" wrote in message .. . 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual basic 6.5 question using combo box and call procedure. | Excel Discussion (Misc queries) | |||
How should I do this (function or Visual basic) | Excel Worksheet Functions | |||
Call a Visual Basic Function with VLookup | Excel Worksheet Functions | |||
inserting subtotal() function in spreadsheet via Visual Basic/macro | Excel Programming | |||
Visual Basic macro to do something that is done trhough an Excel function | Excel Programming |