Forum Discussion
Adding new Excel Functions
If the function is intended for use in a specific workbook, make sure that you create the code for the function in a standard module in that workbook. A standard module is the type of module that you create by selecting Insert > Module in the Visual Basic Editor.
It won't work if you create the code in the code module of a worksheet or in the ThisWorkbook module.
Make sure that you save the workbook as a macro-enabled workbook (*.xlsm or *.xlsb); if you save it as a .xlsx workbook, all VBA code will be lost.
If the function should be available in all open workbooks, you must create it in your personal macro workbook PERSONAL.XLSB. If you don't have this yet, you must create it by recording a macro in your Personal Macro Workbook:
After that, you can remove the dummy macro that you recorded, and create your own functions.
To refer to a function in your personal macro workbook in a formula, prefix it with PERSONAL.XLSB. For example:
=PERSONAL.XLSB!MyFunction()
or
=PERSONAL.XLSB!MyFunction(A1,A2)
When you quit Excel, it will prompt you to save changes in PERSONAL.XLSB. Answer Yes!
Dicky_g141 What type of calculation are you trying to do using a VBA function? Please be aware, that VBA functions you call from cells will be slooooooow. Chances are you can solve your problem using a combination of existing functions.
- My warning about performance does not go away I'm afraid 🙂 Have you considered the new LAMBDA functions? https://jkp-ads.com/Articles/Excel-lambda-function-convert-vba.asp
- Thank you Jan but I think I'll stick with VBA until I've got that mastered and then look elsewhere if it isn't performing as I want.