Call to Sub Routine which exists in seperate workbook

  • Thread starter Thread starter Coby
  • Start date Start date
C

Coby

If I had one workbook open, but the vba modules were in another
workbook which is closed, does anyone know if there is a way for the
codeless workbook to run the subroutine which is in the other
workbook?

Any help is sincerely appreciated.

Coby.
 
Well, the simple way is to open the codeless workbook's VBA module and click
on "Tools/References" at the top. Then select the name of the book with the
code and check it's box. At that point everything in the second book is
available to the first. However it will also automatically open the second
book whenever you open the first if that's a problem.

Once you've checked the box as above, then you just use the subroutine name
in the first book same as if the routine lived in the first book.

Bill
 
Well, the simple way is to open the codeless workbook's VBA module and click
on "Tools/References" at the top.  Then select the name of the book withthe
code and check it's box.  At that point everything in the second book is
available to the first.  However it will also automatically open the second
book whenever you open the first if that's a problem.

Once you've checked the box as above, then you just use the subroutine name
in the first book same as if the routine lived in the first book.

Bill







- Show quoted text -
Click to expand...

Having both workbooks open is not really a problem in the case of my
situation.
I would like to make the call to the sub routine completely via code,
however.

With both workbooks open I kept trying to do Call XYZ_SubRoutine, but
since the module exists in the other workbook I get the compile
error. Perhaps, there is a different way to initiate a sub routine?

Or, I may be able to somehow use the method you suggested, but through
code instead of the user?

Thanks for your input . . . I may have a new approach to try.
 
I think one way is to link a forms button or shape in the codeless to a
macro in the code workbook (e.g. Assign Macro). If the code workbook is
closed, Excel will automatically open it once the linked object is clicked.

I'm not a fan of linking, however. When I need to make a workbok with as
light a macro footprint as possible, prefer to add a couple of macros whose
sole responsibility is to call macros in the code workbook using
Application.Run.
 
Another way is to open the other workbook and call the macro using
application.run (instead of the reference):

Dim OtherWkbk as workbook
set otherwkbk = nothing
on error resume next
set otherwkbk = workbooks("somename.xls") '<-- no drive, no path
on error goto 0

if otherwkbk is nothing then
'it's not open, so open it
set otherwkbk = workbooks.open("C:\folderhere\somenamehere.xls")
end if

application.run "'" & otherwkbk.name & "'!somemacronamehere"
 
Back
Top