🐛 In repeating matrix arguments, empty cells are passed in as zero (0): cannot disambiguate from legitimate zero cells

[wh1t3cAt1k]

To reproduce:

• Create a custom function with one argument.
• For the argument, specify type: 'any', dimensionality: 'matrix' and repeating: true.
• Invoke this function like =MYFUNC("A",M1,"B") where M1 is an empty Excel cell.
• Observe the passed in arguments in the debugger:

The second argument contains a numeric zero.

• Invoke this function like =MYFUNC("A", 0, "B").
• Observe the passed in arguments - they are the same.

Actual result:

We cannot disambiguate legitimate zeroes from empty cells.

Expected result:

We expect an undefined value instead of a zero in the first case, or some additional metadata to disambiguate.

Your Environment

• Office on the Web
• Excel Online
• Greenest Edge Chromium

[grangeryy]

Hey @wh1t3cAt1k, notice the behavior is the same for built-in function like SUM(). May I know the detailed scenario why you need to have a separate type for empty cell?

[wh1t3cAt1k]

@grangeryy I agree that SUM() handles zeroes and empty cells in the same way, however that's not my point...

First of all, I'd like to point out that the behaviour seems to be different from VSTO-based add-ins where the distinction exists between empty parameters and zero parameters. This has become a source of incompatibility between our legacy add-in and the new add-in, where we cannot make such a distinction.

Use case: both our legacy, ExcelDNA-based add-in, and the new Office.js-based add-in have a COLLATE() function that combines multiple dynamic arrays together and returns it as a single matrix.

E.g.

COLLATE([1], [2, 3], emptycell, [4]) =
[ ][1]
[2][3]
[ ][4]

(imagine that the arguments are row-like ranges)

As you notice we intentionally skip empty cell arguments in that function. We can do it in the legacy add-in because it passes empty cell arguments as a range containing a single "missing" value.

In the new add-in, however, we obtain:

COLLATE([1], [2, 3], emptycell, [4]) =
[ ][1]
[2][3]
[ ][0]
[ ][4]

Because Office.js converts the empty cell into a zero for some reason.

I cannot even apply a workaround of filtering out zero values here - because I cannot distinguish if the user passed an empty cell, or a cell containing a literal zero value!

[jipinshi]

@wh1t3cAt1k , not sure whether len(A2) can help to filter out.

[wh1t3cAt1k]

@jipinshi , I need to understand the contents of A2 (whether it's a legitimate zero or an empty cell) in the context of my running custom function that works with A2.

I don't think we can or should invoke other functions there...

Moreover, it's not just us who use this function, it's hundreds of our clients - probably we could apply the workaround if it was just us...

But here, it's a question of function-result-compatibility between two add-ins, one of which cannot provide the same result because it cannot distinguish empty from zero.

(Just to reiterate, the old COM-based addin receives a proper missing value for an empty cell)

[grangeryy]

@wh1t3cAt1k thanks for more details! If I understand it correctly, the requirement here is CF should know about the cell type, especially could distinguish between empty cell and "0", which is not supported yet. We will use an item to track this in our feature list: FEATURE 4796240

[wh1t3cAt1k]

Yes, correct! The COM-based add-in with ExcelDNA provides us with special values in case it is a "non-value": https://docs.excel-dna.net/reference-data-type-marshalling/

In particular:

The highlighted values are what maybe you guys will want to support, generically, in Office.js. (not sure what are the corresponding raw values that come through the C API)

[grangeryy]

@wh1t3cAt1k internally this ask is still tracking in our backlog. To make it more visible to people who have the same ask, we recommend you posting the requirement in Ideas Forum (which is a new version of "UserVoice").

[ghost]

This issue has been automatically marked as stale because it is marked as needing author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment. Thank you for your interest in Office Add-ins!

[ghost]

This issue has been closed due to inactivity. Please comment if you still need assistance and we'll re-open the issue.

[wh1t3cAt1k]

@grangeryy @keyur32 I disagree that this was closed as needing author feedback. The issue is still there and it's a legitimate one.

Even in VBA there are functions like ISBLANK that allow one to distinguish blank cells (probably by testing the underlying "missing" value), Office.js on the other hand works counter-intuitively with matrix arguments.

Therefore we're blocked from completely migrating from legacy add-ins to the new cross-platform architecture.

I do consider this a bug and not a feature request, can we please reopen it?

[wh1t3cAt1k]

@grangeryy @Wenjun-Gong just to give you a heads-up friends that this issue still pesters us.

At Velixo, we often use custom functions to collect & prepare certain data for subsequent uploading (e.g. =WRITEBACK("42", 0, A1:B3)), and it can contain a mixture of different data types. By necessity, it's a repeating matrix argument.

For the upstream that we're writing to, it makes a big difference if we're trying to send null, or we try to send zero.

And with this bug in place, we cannot distinguish between them, except by re-reading the Excel ranges through the API (which kind of defeats the purpose of formula-based data collection approach).

Since this is 1.5 years old, I hope you'd be able to dedicate some attention to this item... 🙏

To avoid breaking changes for existing add-in developers, we can introduce a new manifest setting for custom functions like <StrictEmptyValues>true</StrictEmptyValues> that would work for both scalar and matrix arguments, and will distinguish between 0, "a value that has not been specified" and "empty cell value" (see https://docs.excel-dna.net/reference-data-type-marshalling/)

As a disclaimer, for our purposes, it would be enough to at least distinguish between the two cases:

• a real number 0
• either an empty cell or a non-specified value. Both of which can be passed as undefined to our CF code.

However, if you decide to do it in a more granular way like introducing special symbols for ExcelMissing and ExcelEmpty, it works for us too.

[grangeryy]

@wh1t3cAt1k the ask is still in tracking in our backlog, but we don't have short-term plan yet. Have you ever posted this feature in the Ideas Forum? I didn't find an existing item (maybe I was using the wrong key words). You can socialize your ideas and ask other community members to upVote, which will help us prioritize according to the votes. Thanks!

[wh1t3cAt1k]

@grangeryy @Wenjun-Gong to be honest I believe this could be considered by the team outside of the external feature voting process, because it is an issue of inconsistency between XLL add-ins and Office.js add-ins.

I believe for XLL->JS migration purposes, if that is strategic goal for Microsoft, just like it is for us, the two platforms should exhibit the same behaviour in a non-breaking way!

I have posted an idea as per your request, but I nevertheless would appreciate if independently, your product team could tie it to one of the strategic epics you may have ("Ensure that Office.js platform is consistent with XLL") and include it in your shorter-term implementation plans.

Thanks a lot for all your help, as usual!

P.S. I just consulted the ideas forum and it doesn't look very "alive" to me. There are just 4 items marked as in progress, all fairly old ones (2022 or earlier), and there is not a single completed idea that relates to the Excel area... Ideally we'd like a interaction channel with you that would be more responsive!

[jigarbagadai]

@wh1t3cAt1k I am agree with you, in our use-case we also need differentiate between blank cell value or 0 value in custom UDF, as of now we unable to differentiate. I am totally agree with suggestion which you provided driven through manifest file so it will not break existing user. hopefully msteam will give more attention this issue and include in upcoming release.

[wh1t3cAt1k]

Testing ScriptLab snippet:

/**
 * Logs a matrix argument to the console.
 * @customfunction
 * @param {any[][]} arg
 * @returns {number} A constant value.
 */
function foobar(arg) {
  console.log(JSON.stringify(arg));

  return 42;
}

Testing scenario

Pass a 2x2 empty range into the function.

Results:

Excel for Mac 16.87 (Beta Channel):

[[null,null],[null,null]] as expected

Excel Desktop for Windows (Beta Channel, Version 2407 Build 16.0.17820.20000) 64-bit:

[[null,null],[null,null]] as expected

Excel Online 16.0.17823.42304:

[[0,0],[0,0]] not fixed

@adrianwu8516 as you requested, I have run the tests on multiple platforms and it seems only Excel Online is missing! But my Excel Online is in current channel. If the fix can already be observed on the beta channel, please let me know how I can do that; or maybe it was not done, and the partner team still needs to check in the fix?

[XuanZhouMSFT]

Hi @wh1t3cAt1k , this feature is still under release in each platform and it's not 100% roll out. Please wait for some days for the release completely. We will keep updated once it finished.

[XuanZhouMSFT]

Hi @wh1t3cAt1k, we have finished the rollout for this feature, please have a try in online and desktop(Win32 build >= 17723 and Mac build >= 16.86.506.0)

[wh1t3cAt1k]

Trying in Excel Online:

Fixed on all platforms! Thank you so much @XuanZhouMSFT!