Syntax for calling Lambda via INDIRECT

Iron Contributor

Does anyone know the syntax for calling a Lambda via INDIRECT?  I can write

 

=Calipso!audit(1000)

 

but I cannot write

 

=INDIRECT("Calipso!audit")(1000)

 

nor

 

=INDIRECT("Calipso!audit(1000)")

 

Any pointers?  Thanks!

15 Replies

@ecovonrein 

Afraid that doesn't work, as with any other named functions. As variant you may create for the set of you functions something like

one = LAMBDA(x, x+1);
two = LAMBDA(x, x+2);

callFunction=LAMBDA(str,
    SWITCH( str,
        "one", one,
        "two", two)
);

and call it as

image.png

Thanks. Not great. I was surprised that I can define

audit=Calipso!audit

and call

=audit(1000)

Extrapolating from there, I expected

=INDIRECT("Calipdo!audit")(1000)

to work.

Your SWITCH does not work for me because the list of possible targets is non-exhaustive, as is the places from where the call might come.

Well, there's hoping that MS will get around to fixing it at some point. (It isn't core functionality for me at this mo.)

@ecovonrein 

 

You won't be able to achieve what you want using INDIRECT, though if a macro-enabled workbook is an option then perhaps you can find a workaround using EVALUATE.

 

For example, with cell A1 containing the defined name pertaining to the LAMBDA, a further definition within Named Manager, Eval, say, as:

 

=LAMBDA(ζ,EVALUATE($A$1&"("&ζ&")"))

 

after which you can call, for example:

 

=Eval(1000)

 

within the worksheet.

 

@JosWoolley 

Pretty similar?

= LAMBDA(text, EVALUATE(text))

image.png

Clearly the message is to read to the bottom before embarking upon a solution!

@Peter Bartholomew 

 

Can you clarify? "Read to the bottom" of what precisely?

 

Regards

@JosWoolley 

I just meant scroll to the end of the replies.  It wasn't obvious to me that an old XLM command would work with a Lambda function developed some 30 years later so I simply set out to experiment.

But there is no trace in this thread of the post which your screenshot represents.

Regards

@ecovonrein 

I don't think that will be fixed. No difference with named regular formulae. E.g., if you define mySum as =SUM(range), when =mySum works but =INDIRECT("mySum") doesn't work. INDIRECT() requires references.

I'm not sure why you can't modify SWITCH every time you add new function, but that's your choice.

When, if you don't use Excel on web and macro enabled workbooks are allowed - EVALUATE() as suggested above.

@ecovonrein 

I think I am beginning to lose the thread here.  What is the intended purpose of the indirect?

If the top level formula is a Lambda function, one can pass any other function as a Lambda variable and apply it to such parameter string as one chooses within the inner formulae.  That would be cleaner conceptually than identifying the Lambda as a text string and using INDIRECT.

@ecovonrein 

Since I am not sure I fully understand the use case for INDIRECT, here are some techniques that might be of relevance.  I started with a validation list of function names as text.  That allows me to use validation to select the desired function names.  I then built an array of functions in memory

FunctionList = VSTACK(SINλ, COSλ, TANλ)

where
SINλ = LAMBDA(ϑ, SIN(RADIANS(ϑ)));
COSλ = LAMBDA(ϑ, COS(RADIANS(ϑ)));
TANλ = LAMBDA(ϑ, TAN(RADIANS(ϑ)));

The worksheet formula could then be

= LET(
    Fnλ, XLOOKUP(function, validationList, FunctionList),
    MAP(Fnλ, ϑ, Applyλ)
  )

where
Applyλ = LAMBDA(Fnλ₁, ϑ₁, Fnλ₁(ϑ₁))

If you find having formulas on the worksheet distracting or ugly, you could wrap the whole shooting-match within a further Lambda to give

= Indirectλ(function, ϑ)

image.png

Uff. I have multiple very heavy production models. None of those use INDIRECT (or any other ghastly volatile functions). However, each year end during the audit process, we like to poke numbers out of these models, from ancillary auditing spreadsheet. These spreadsheets can use INDIRECT etc since performance is not a concern. I like to construct these little helper sheets with INDIRECT because then I can simply input the current name of the financial model and the audit spreadsheet pokes the right file. (It also means that Excel does not create any hard links that import x MB of data.) Now, I have just spent a few months reprogramming one of these monsters to take full advantage of Lambdas and Spills. Each monster contains multiple company valuations, each in a separate Worksheet. Each Worksheet is a copy of a clean master and I taught the master a little Lambda called "audit" which allows an outsider to ask the Worksheet to return practically any number from its grid by symbol (account number, effectively). Now, it would have been nice to be able to invoke this audit function INDIRECTly from the auditing spreadsheet...

The way I do the same stuff in the previous generation of models is by means of a symbol search (to obtains an A1 reference in the foreign sheet) and a conventional INDIRECT call (with that reference). I was hoping I could do better. But it seems I will have to stick with the old technique. Too bad.

PS: I might try EVALUATE though I am unsure what I might be evaluating.

 

PPS:  You might now understand that in this world, I do not know the names of the branches for your SWITCH - they are the Worksheets of the Workbook.  I would need a VBA macro to auto-generate an appropriate SWITCH per Workbook.  Not that it would help, since we would need your SWITCH in the foreign (auditing) Workbook.  Effectively, that Workbook would need to run the VBA macro to generate an appropriate SWITCH each time the name of the foreign Workbook is updated.  PITA...

This is essentially the same approach that Sergei takes with SWITCH.  It does not work when the target Lambdas are in a foreign Workbook.

 

I have wondered whether the challenge of poking numbers out of a time grid might be a suitable matter for Power Query.  (I don't know this thing, really.)  The grid is highly readable to a human so I would certainly expect the Copilot to be able to do it.  Not that I would know where to start :):)...

@ecovonrein 

I am more used to using a scratch worksheet in the same book for testing/purposes so I have little experience of the scenario you outline.  At least I now have some idea as to how the sheet local names arise for your Lambda functions.  I tend to use workbook scoped Lambdas and keep the sheet localisation to the parameter names rather than the function, but I appreciate that may not be possible.

I do that too, Peter. I have a local scope Lambda "SUMV12" with a very short function signature whose only purpose it is to call "Private.SUMV12" in the global scope, supplying all sorts of local context to the global Lambda with the long signature. The latter is centrally maintained and reloaded each time the sheet is opened, so that we can maintain the core Lambda functions and push out to all spreadsheets.

@JosWoolley @Peter Bartholomew @Sergei Baklan 

Quick update as I am in the middle of our dreaded audit.  There is a way to skin this cat that is not obvious at all.  It all centers on EVALUATE, which (if one thinks about it) isn't greatly different to INDIRECT anyway.

 

But we must not, as was suggested, call EVALUATE from

 

EVAL=LAMBDA(expr,EVALUATE(expr))

 

This does not work because while

 

=EVAL("Calipso!audit(a,b)")

 

will gladly run audit(a,b) in the Worksheet "Calipso" within the current Workbook, it is NOT POSSIBLE to invoke EVAL from a foreign Workbook.  The formula

 

='myWorkbook.xlsm'!EVAL("Calipso!audit(a,b)")

 

will fail - even though the formula

 

='myWorkbook.xlsm'!EVAL("Calipso!A1")

 

works perfectly well.

 

However, if we create EVAL as a tiny UDF in VBA inside myWorkbook.xlsm like so

 

Public Function EVAL(expr As String) As Variant
      EVAL = thisWorkbook.Worksheets(1).Evaluate(expr)
End Function

 

 then the cross-Workbook call

 

='myWorkbook.xlsm'!EVAL("Calipso!audit(a,b)")

 

suddenly works.  The important trick inside this code is the provision of "thisWorkbook.Worksheets(1)" as the explicit context.  The simple code

 

EVAL = Evaluate(expr)

 

otherwise executes in the (wrong) context of the invoking Workbook (and probably returns #REF!).

 

NB:

My Excel is tuned to the European convention and I hence normally separate function arguments with ";".  However, VBA does not alter its syntax depending on locale.  Since EVAL is executed thru VBA, counter-intuitively for a European user the expr must use "," to separate the arguments to the Lambda.

 

PS: 

I am a bit flummoxed because I could have sworn that in a past life I needed to enable Excel 4.0 macros in Trust Center before I could use EVALUATE.  No such problems today.  Odd.