4

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?

Community
  • 1
  • 1
mandroid
  • 2,308
  • 5
  • 24
  • 37
  • The best way I've found to do this is to share the workbook on the network. Replace the default start path for excel (typically inside the Program Files..Office..Excel..Start up folder to a default workbook (your Source Formula Workbook.xlsm) On.This will make it so that every time excel is launched so is this workbook, you can hide this so no one knows, and copy all macros to the opened workbook. Then close. – user2140261 Oct 23 '13 at 01:31
  • The folder I am talking about is Typicaly `%ProgramFiles(x86)%\Microsoft Office\Office14\XLSTART` replace `%ProgramFiles(x86)%` with `%ProgramFiles%`for 64 bit install. and replace `Office14` with Office and your version of excel. Anything inside that folder will be opened everytime excel is launched. So, you can start there. – user2140261 Oct 23 '13 at 01:39

1 Answers1

7

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
Community
  • 1
  • 1
Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • 2
    I do this all the time for custom functions. This is absolutely the best way. – psubsee2003 Oct 24 '13 at 16:09
  • In the child documents, could I use this code in the Workbook_Open event and anyone using the child workbook would for sure have the add-in installed? – mandroid Oct 24 '13 at 16:50
  • Yes, or instead of adding it the installed addins list if you only want the functions to be available to the child workbooks you could just Open it as a workbook using Workbooks.Open (preferably using the Read-only option) – Charles Williams Oct 24 '13 at 17:24