asked on

Call VBA macros / functions in another workbook

I'd like to consolidate some of my VBA code into one workbook and then make the Function call from other workbooks. I use the same functions in several different workbooks and its difficult to maintain code consistency between them.

Here is the current code/call:
Range(Cell).Value = ServiceLaborSF(BegDate, EndDate)

How can I do this?

Thanks

rspahitz

Probably the easiest way is to put it into your Personal Workbook and make the function public (or at least not private)

Public Sub ServiceLaborSF(BegDate As Date, EndDate As Date) As Range' word Public optional
...
End Sub

Alternately, you can put this into ANY workbook and it can be used as long as the workbook is open (which is a good reason to use the Personal Workbook since it's always open.)

Note that if you plan to distribute this to other, the Personal Workbook is not the best choice.

bikeski

ASKER

We have several users accessing several different workbooks at various times. I don't believe this will work.

How does Application.Run work with Functions?

rspahitz

I've never seen that used with functions. However, you could get it to perform at action, like open a workbook (maybe hidden) that has a collection of functions, then you should be able to reference them. I've never tried it so it may have some weird side-effects while it tries to load the book so there may be some timing issues.

So can you simply ensure that users open the "Functions" workbook (rather than Personal workbook)? It could be something done in the Workbook Open even if they use the same workbooks over and over...and, again, that workbook can be set to hidden...but make sure to close it when not needed any more.

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bikeski

ASKER

Straight forward solution with useful links for creating and maintaining the add-on's.

Roy Cox

Pleased to help