Setting References In VBA
Often you will want to use VBA procedures in one workbook
from another workbook. For example, you may have a useful financial function
in an add-in and you want to be able to call that function from VBA code a
second workbook. To accomplish this, you have to set a reference from the
workbook project that uses the function to the project that contains the
function. This page describes how to set references.
By default, the VBA Project of a workbook is named "VBAProject". Because VBA doesn't handle duplicate project names, you should first give a new project name to the workbook that you will reference by other workbooks. To do this, go to the Tools menu, choose VBA Project Properties, and enter a new name for the project, for example "SampleProject". Click OK and then save the workbook. VBA now knows this project by its project name.
Next, you need to reference that project from the project that will use its functions. Open the workbook that will use the function, and in the VBA editor go to the Tools menu and choose References to display the References dialog, show below.
Scroll down in the list until you find the project name you assigned to the first project. Remember, VBA sees projects by their project name, not their file name, so you will see the project name, not the file name, listed in the list box. Put a check next to that item in the list. Click OK.
Now, you can refer to procedures and variables defined in SampleProject from within the project which references it.