CODEWITHSUNDEEP
vbaexcel
JoshD
JoshD

Reputation: 33

Where to save excel vba function to be able to access the function in other workbooks

I've created a function and I would like to be able to open any excel file and use this function just by typing into a cell '=function'. Is this possible and how do I do this? Where do I save the function?

Upvotes: 0

Views: 2753

Answers (4)

Excel Developers
Excel Developers

Reputation: 2825

You can store it either in your Personal.xlsb workbook or in an Excel add-in (.xlam). Search for either of these two to get you on the right track.

Upvotes: 1

Rory
Rory

Reputation: 34055

Save the workbook containing the function as an add-in (either .xlam or .xla depending on your Excel version). You can then install it via the Add-in manager and call it from any workbook.

Note: you don't technically have to save it as an add-in - you can use a regular workbook - but then you will have to prefix the function name with the name of the workbook whenever you call it (e.g. =Personal.xlsb!some_function), and you will have to remember to open the workbook each time (or put it in your XLSTART or other startup folder).

Upvotes: 3

Paulo
Paulo

Reputation: 1498

Well, first of all, you have to enable macros in Excel.

After that, you can open Excel VBA editor with Alt+F11, and create your function there.

Finally, you can use your function in a cell with '=function'.

Anyway, you can try this link to help you with your first vba function: http://www.fontstuff.com/vba/vbatut01.htm

Note: I've already created some functions on Excel, and it always worked for me. But always remember: you must enable macros in Excel.

You can read about macros here: https://support.office.com/en-nz/article/Enable-or-disable-macros-in-Office-documents-7b4fdd2e-174f-47e2-9611-9efe4f860b12

Upvotes: 0

Zeeshan S.
Zeeshan S.

Reputation: 2091

I'm going to provide an answer with the example of Workbook A as wbCompany and Workbook B as wbEmployee.

From what I understood is that you have a function in wbCompany.getEmployeeCount() and you want to use this function in wbEmployee.

Firstly, rename the VBA Projects of both files to prevent duplicate project name. So we will rename the VBA Projects as vbaPrjEmp and vbaPrjCmp for wbEmployee and wbCompany workbooks respectively.

Secondly, you need to add wbCompany as reference to wbEmployee.

  1. In wbEmployee, open the Microsoft Visual Basic for Applications window.
  2. Select Tools > References.
  3. In the References dialog that appears, click on Browse.
  4. In the Add Reference dialog that appears, select Microsoft Excel Files from the Files of type box, select the file that you want to call (in this case wbCompany), and click on Open.
  5. Choose OK to close the References dialog.

Finally in wbEmployee you can now refer/call the function from wbCompany in the following manner:

Sub compareEmpCount()
    msgbox vbaPrjCmp.ThisWorkbook.getEmployeeCount
End Sub

Upvotes: 1

Related Questions