SOLVED

Adding new Excel Functions

Brass Contributor

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?

13 Replies

@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.

best response confirmed by Dicky_g141 (Brass Contributor)
Solution

@Dicky_g141 

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:

S0661.png

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!

I am just experimenting with an interpolation function but there are a number of others I plan to write when I've got this one working.
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

@Hans Vogelaar 

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?

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.
Thanks again, Hans, but it appears that the only problem was with the name "INTERPOLATE". It didn't like the mixed-case version and appears to think that the uppercase version is already in use (although it doesn't appear in the all functions list). Renaming it "FOOBAR" seems to have fixed it.
For anyone who is interested in this thread I have found that, by saving my macros in an Excel add-in file (.xalm extension) it propagates into all spreadsheets and the macros can just be used as is, that is without the MYMACRO! prefix.
Note that this isn't true for sharing workbooks using your UDF with a colleague; the location of your UDF is stored in your workbook and if that location is different for your colleague (usually the case!) then the UDF will not work. Hence: https://jkp-ads.com/Articles/FixLinks2UDF.asp
Thanks for the warning, Jan. I have no immediate plans to share these functions but, if I do, I shall invite recipients to re-create them for themselves.
As an aside I wanted to award "likes" to both you and Hans for your helpful contributions. However, while I can apparently view likes awarded by others (not that there are any in this thread), there seems to be no way to award them myself.

@Dicky_g141 

Can't you click on the little thumb-up icon?

Hello Hans. Indeed I can but that just takes me to a view of who has previously liked the posting. It does not allow me to add a like.
Aha! A bit of finger trouble there. It seems that I have been clicking on "Likes" rather than the thumb! Problem sorted thank you.
1 best response

Accepted Solutions
best response confirmed by Dicky_g141 (Brass Contributor)
Solution

@Dicky_g141 

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:

S0661.png

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!

View solution in original post