Aug 12 2021 06:46 AM
Windows 10 64 bit v21H1. Office 365 Excel v2107
I am trying to add additional functions to my worksheets. Online postings (many of which relate to earlier sw versions and are out of date) have got me as far as defining a function in VBA but I cannot seem to get this to work in the associated worksheet or elsewhere. How am I meant to save my code so that I can get it to work?
Aug 12 2021 07:42 AM
@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.
Aug 12 2021 07:49 AM - edited Aug 12 2021 07:50 AM
SolutionIf 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!
Aug 12 2021 08:21 AM
Aug 13 2021 02:26 AM
Aug 13 2021 06:33 AM
Thank you Hans. That seems very helpful but I still seem to be missing something basic. I have entered the following in a module edit window, as you suggest:
Function Interpolate(lwr, upr, lwrval, uprval, x)
Interpolate = lwrval + (x - lwr) * (uprval - lwrval) / (upr - lwr)
End Function
I have also tried adding "As single" to the first line and both variants compile successfully but neither seems to propagate into my worksheet, which refuses to admit to the existence of a function with the name Interpolate. What am I missing?
Aug 13 2021 06:35 AM
Aug 13 2021 06:58 AM
Aug 15 2021 08:03 AM
Aug 16 2021 12:47 AM
Aug 16 2021 04:18 AM
Aug 16 2021 04:54 AM
Can't you click on the little thumb-up icon?
Aug 16 2021 05:47 AM
Aug 16 2021 05:50 AM
May 03 2024 10:56 AM
Hi,
I'm on Mac OS and still a bit stuck. I'm working with a UDF, not a macro. I can create the function and use it with my current workbook, but it only shows up in my list of functions with the PERSONAL.XLSB! prefix. I would love to not have to type all of that every time and I would also like to be able to reference it from different workbooks. Is this possible on Mac OS?
(Should I create a new post/question for this?)
Thanks in advance
May 03 2024 01:12 PM
If you want to be able to use the UDF without a prefix such as PERSONAL.XLSB! you have to store it in an Excel add-in (*.xlam). See for example Method 3. Create an Excel add-in file
Aug 12 2021 07:49 AM - edited Aug 12 2021 07:50 AM
SolutionIf 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!