1

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
shiftins
  • 87
  • 8
  • 1
    Without knowing the addin_fn we cannot help. It may not be programed to handle the input, ie require a range and not a value, or it returns an array and not a single number. There are too many things that it could be for us to answer the question with seeing the code behind the addin. – Scott Craner Mar 17 '23 at 22:10
  • Thanks, the function works when called directly, or within a lambda, or passed as an argument to the lambda and then called in the lambda, but does not work when called within a map or makearray. I'll try to find a plugin on the store, and create a reference using it. This particular plugin is part of a private beta, or I'd share it directly. – shiftins Mar 18 '23 at 01:44
  • One more q @ScottCraner -- do you know if it's possible to call addin functions from within a map? – shiftins Mar 18 '23 at 01:48
  • 1
    Found a reproducible case using the **Jira Cloud for Excel** plugin (adds one function, JIRA.JQL expects string). This causes the same calc error when used the same way `=MAP(SEQUENCE(20), LAMBDA(id, JIRA.JQL("user is shiftins")))` – shiftins Mar 18 '23 at 02:17
  • JIRA.JQL will return a range, I'm guessing? Perhaps the add-in function you're using returns a range and functions like MAP or MAKEARRAY expect a scalar for their LAMBDA function argument. In this case a good test would be to make sure your add-in returns a scalar, for example with a JOIN or something like this. – ttarchala Mar 19 '23 at 21:15

0 Answers0