Calling Excel Add-In functions from within MAP or MAKEARRAY

96 Views Asked by At

Using Office365 desktop I'm trying to call add-in functions from within either MAP or MAKEARRAY so it can spill alongside an existing spill. I don't know how many rows will be in the spill, so being dynamic is somewhat of a requirement.

I've tried a few different approaches that always ultimately return the same #CALC! Error: Unsupported Calculation - This calculation is not supported by this version of Excel.

Qs:

  1. Can you call add-in functions from within MAP or MAKEARRAY (which I suspect is MAP-like under the hood)? Example below.
  2. The error said "not supported by this version of Excel". Is there a version that does support it?
  3. Are there strategies for handling this if you cannot call from with a MAP?

These examples work if the LAMBDA calls a native Excel function but not if it calls an Add-In function.

Contrived examples with inline lambdas (A1 is setup case with 5 rows, and the remaining columns iterate or use A1#)

(note about D1 and G1 below -- you will get #NAME! error because INSTALLED_ADD_IN.FUNCTION is placeholder for where an add-in function call would happen. D1 and G1 error on both inline lambda and named lambdas in name manager)

(works) A1: =SEQUENCE(5)

(works) B1: =MAP($A1#, LAMBDA(item, item))

(works) C1: =MAP($A1#, LAMBDA(item, MOD(item,2)))

(error) D1: =MAP($A1#, LAMBDA(item, INSTALLED_ADD_IN.FUNCTION(item)))

(works ) E1: =MAKEARRAY(ROWS($A1#), 1, LAMBDA(row, col, row))

(works) F1 =MAKEARRAY(ROWS($A1#), 1, LAMBDA(row, col, MOD(row,2)))

(error) G1: =MAKEARRAY(ROWS($A1#), 1, LAMBDA(row, col, INSTALLED_ADD_IN.FUNCTION(row)))

Example using above cols + named lambdas

Create named Lambda in name manager.

name: addin_fn
refers to: =LAMBDA(id, INSTALLED_ADD_IN.FUNCTION(id))

(works) H1: =addin_fn($A1) // not an array output or iterator, just the single lambda call

(error) I1: =MAP($A1#, addin_fn)

(error) J1: =MAKEARRAY(ROWS($A1#), 1, LAMBDA(row, col, addin_fn(row)))

Other things I've tried:

  1. Passing the LAMBDA as a parameter, and then calling it from within the MAP/MAKEARRAY internal LAMBDAs
  2. Using LETs to create local references to the Add-In function inside and outside of the MAP/MAKEARRAY lambdas, and both calling these directly as well as chaining LETs and passing to the internal LAMBDAs as far down the chain as possible
  3. Using all of the above with variations of addin_fn(INDEX(array, row)) to remove internal references of the array to try and avoid lifting errors
0

There are 0 best solutions below