# Solved: Calling VBA subroutines in another spreadsheet



## BartonConsultanc (Jun 16, 2010)

How do I call a subroutine in another spreadsheet. 

For example, I have a subroutine called "DoTask" which has arguments Arg1 and arg2. This subroutine is resident in a module called MainModule in a workbook called Standard.xls. 

Within Standard.xls, it is easy to call the subroutine by 

Call DoTask(Arg1,Arg2)

I dont want to copy the subroutine into other worksheets. I just want it in one place where I can access from other spreadsheets called for example Individual.xls.

What code can I put in Individual.xls to call the subroutine. 

I have tried 

Call Standard.xls.Mainmodule.DoTask(Arg1,arg2) and variants of this.

Help would be appreciated.


----------



## Keebellah (Mar 27, 2008)

Welcome to the board.

What you could do is add the module(s) to your Personal.Xls
If you notice, when you start to record a macro it asks where you want it.
If you choose Personal then the macro is placed in that (hidden) worksheet but will be accessibel for all workbooks you open, its 'personal'

If you look in the VBA project you will see it there too and you can then open Standard.xls and copy the modules to the Personal.xls.

Close standard.xls and when Excel closes it will ask you if you wish to save the Persnal.xls.

Now open Excel again and when you choose Marcos, you will see the ones you just added in the list of available Macro's.

Of course you will have to program those macro's in such a way that if the are for certain types of sheets, that you test if it's that specific workboopk and sheet etc etc before executing.

Another way:

Application.Run "<fullpath and name of Excelsheet>!<macroname>"

Check first if the focus remains on the callin workbook which I doubt.


----------



## BartonConsultanc (Jun 16, 2010)

Thanks Hans

Your suggestion did not work, but I found from Just ANswer 
Try:

ReturnValue = Application.Run("Other.xls!Dotask", Arg1, Arg2)

or

ReturnValue = Application.Run("Other.xls!MainModule.Dotask", Arg1, Arg2)


both solve the problem

Thanks 

Dennis Barton


----------



## Keebellah (Mar 27, 2008)

Very good, it's essentially the same, you must have forgotten the '*!*' beacuse that works with one macro, but the arg1 and arg2 variant does the job, perfect 

Do not forget to click the SOLVED button.


----------



## BartonConsultanc (Jun 16, 2010)

Thanks, Where is the Solved button?

Cheers
DB


----------



## Keebellah (Mar 27, 2008)

You see it when you open your post


----------

