How to save Lambda formula as Excel Add-In (or how to easily convert to VBA function?)

Question

I made some very complex Lambda formulas which I use frequently to validate UPC Check digits, and convert UPCs from UPC-E to UPC-A format

I tried to set them up as named ranges, then save the workbook as an Add-In, and followed all the steps I could find to add that add-in into my Excel, however it doesn't look like it keeps the named ranges at all (which is where Lambda formulas are stored)

Is there any way to get around this and still save these Lambda formulas as an Add-in? I really don't want to have to re-create the entire complex formula in a module, but it seems I may have to do that in order to have the formulas available in every workbook I open

Alternatively, if there's any way within a VBA function for me to use my existing Lambda formula, I would love that, but I'm not sure if that is possible as I know that VBA is a quite different language than Excel's formulas. I considered making a macro which instead would just add those named ranges to my workbook, but hoping for an easier solution.

Please let me know if you have any tips for this!

Answer 1

What about maintaining a Template,that’s what I do, means they are there in your new books and a quick way to access them for manual copying across to existing workbooks.

2nd idea is use autocorrect to store them with a shortcut word. Autocorrect is application level and there is VBA you can find to copy your AC shortcuts to a backup workbook and reimport them if your PC crashes.

Answer 2

I have only recently discovered the world of Lambdas, preferring VBA Macros and user-defined functions (UDFs). I have found that you can have a library of Lambdas which can be used by other spreadsheets. Create a spreadsheet which only contains Lambda functions and save it as an add-in and make sure it is ticked in the available add-ins within the developer tab.

You can use the Lambda functions in any workbook that you open after that but you have to prefix the function with the add-in file name:

If your add-in file is called F.xlam and you have a called UPC_E then you can access it using

• F.xlam!UPC_E(parameters and calculations as normal)

You will only get 'Enable Content' message if there are macros/UDFs in your add-in spreadsheet.

Anyone else can also use the functions in the same way as long as they have the add-in available on their machine.

I know it is probably too late for the questioner but it may be useful for anyone else searching for an answer.

So, I hope this helps someone.

Artificial Intelligence used in the response? No. Actual Intelligence used in the response? Debateable.