Use formulas from other workbooks?

Question

My group currently uses Excel workbooks to track performance of employees. Each employee has their own workbook and they are identical except for the user entered data. There are custom VBA formulas on this workbook that take the data and generate a score base.

What I would like to do is move these formulas to another workbook, that way if a calculation needs to be changed, it can be changed in one source document, as opposed to hundreds of workbooks.

I'm envisioning something like...

='[Source Formula Workbook.xlsm]'!Formula_A(A1, A2, A3...)

...where A1, A2, A3 are cells in the user workbook and this returns the value of Formula_A from my hypothetical source workbook. What's the right way to accomplish this?

Answer 1

The usual way to do this is to move the VBA functions to an XLA/XLAM addin and store the addin on a network drive. Then each employee needs to use the Excel Addin Manager (or an installer) to add the networked XLA to their Excel.
see http://www.cpearson.com/excel/createaddin.aspx on how to create an addin.
You can create a simple installer XLSM file that uses VBA to add the XLAM to the addins list:

 With AddIns.Add(FileName:=MyNetworkAddInPath)
        .Installed = True
        End With