0

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!

Andy L
  • 93
  • 6
  • I believe copying a tab from the existing file to the new file (and deleting that tab) creates the named Lambda(s) – P.b Feb 10 '23 at 20:53
  • @P.b it does, but that defeats the purpose of the add-in, I'm hoping to make it so the formulas are available in every workbook I open. – Andy L Feb 10 '23 at 20:59
  • This is a good question, and one that came up when Lambdas were first being beta-tested. You put a lot of effort into creating the lambdas, but how do you build up a share-able library? You don’t want a .xla as this triggers macro warnings and could be a vulnerability. One of the selling points of Lambdas is precisely that they operate within the Excel s/sheet sandbox and can only use existing sheet functions (or other lambdas). I wonder if there needs to be a new Excel xml file format specifically for storing Lambdas and loading them into the Excel session? – DS_London Feb 11 '23 at 09:14
  • You don’t really want to copy Names to the new sheet: this creates a version control nightmare with different sheets potentially having different versions of the lambdas. – DS_London Feb 11 '23 at 09:25
  • 1
    Have a look at the [advanced formula environment addin](https://www.microsoft.com/en-us/garage/profiles/advanced-formula-environment-a-microsoft-garage-project/) – chris neilsen Feb 11 '23 at 20:02

1 Answers1

0

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.

John Hackwood
  • 1
  • 1