LAMBDA Examples: Distance between two cities

David Hager
Copper Contributor
Dec 10, 2020
Sameer_Bhide You can save all of your LAMBDA formulas in a workbook and then move/copy a blank worksheet from that workbook to another workbook where you want to use them.
- lori_m
  Iron Contributor
  Dec 10, 2020
  David Hager
  Copying a sheet is a neat trick. XLM functions can't be input in the worksheet but should still be ok within names, this is still in beta however and it looks like a number of things are not quite how they should be yet.
  
  Sameer_Bhide
  That method comes from having spent too much time writing Excel4 macros even before VBA was mainstream! XLM language is long gone, as you say, but the same environment is now playing a key role as the old macro functions were defined via names in much the same way the new LAMBDA function is. Another little known feature of Excel is that every session has a hidden macro workbook which is where XLL function names are stored. In fact it's possible to copy workbook names to this hidden area as follows...
  
  Enter the formula =MyFunc in A1, defined as before, and from the VBA immediate window execute the following command (in R1C1 notation)
  ExecuteExcel4Macro "COPY(!C1,TEXTREF(""C1""))"
  MyFunc is now defined like an XLL function that can be accessed from any open workbook and is available even when the original workbook is closed.
lori_m
Iron Contributor
Dec 09, 2020
Sameer_Bhide
Given a named formula MyFunc in Book1, we should be able to call it from another workbook (as with vba functions):

=Book1!MyFunc(123)

If MyFunc =LAMBDA(x,x) this returns an error on my build but I assume it will work soon. Instead we could test with MyFunc = MyVBAFunc where

Function MyVBAFunc(x): MyVBAFunc = x: End Function

To access MyFunc as an add-in function without the Book1! prefix we would need to press Ctrl+F11 to open a macro sheet and change the definition in the define name dialog to a function. Or equivalently from vba:

Names("MyFunc").MacroType=xlFunction

For a simple test define the name MyFunc:=123 as a function and set the workbook IsAddin property to True. This name is then accessible from any workbook.
- Sameer_Bhide
  Iron Contributor
  Dec 10, 2020
  lori_m
  Wow ! - How did you figure that out - I am going to give this a shot on my install and see how it goes.
  
  Cheers
  Sam
- ExcelExciting
  MVP
  Dec 09, 2020
  All the customs formula made with LAMBDA to be a way to stored/export online & those can be globally accessible to any workbook under that M365 subscription account.
  
  It will be always safe and no loss of function in case of system crash.
  
  Regards, Faraz Shaikh
  - lori_m
    Iron Contributor
    Dec 09, 2020
    ExcelExciting
    Agreed, ideally we could store a library of these functions accessible from any workbook. Function scope should be global like XLL / .js custom functions and not linked to a particular workbook as with VBA udfs.
SergeiBaklan
Diamond Contributor
Dec 09, 2020
Sameer_Bhide

IMHO, lambda management shall be the same for desktop, online and mobile versions.

Forum Discussion

LAMBDA Examples: Distance between two cities

Resources