search

OfficeDev / office-js

A repo and NPM package for Office.js, corresponding to a copy of what gets published to the official "evergreen" Office.js CDN, at https://appsforoffice.microsoft.com/lib/1/hosted/office.js.
https://learn.microsoft.com/javascript/api/overview
Other
687 stars 94 forks source link

Custom function with missing parameters doesn't match behavior of built in functions #710

Closed SWDevB closed 3 years ago

SWDevB commented 5 years ago

When a built in function is entered in a cell without mandatory parameters (e.g. "=BAHTTEXT()") excel shows the "There's a problem with this formula" in desktop version and it's not possible to leave the cell without fixing or clearing it. In Excel online it's possible to leave the cell but it will get a red border and indicating a problem on mouse over. For custom functions there is #VALUE! shown instead.

Expected Behavior

For custom functions Excel should behave the same

Current Behavior

MessageBox/red Border != #VALUE!

Context

Excel users who are used to the built-in-function-behavior might not recognize they entered the formula incorrectly and may think there the add-in is the problem

Your Environment

Platform [PC desktop, Mac, iOS, Office Online]: PC Host [Excel, Word, PowerPoint, etc.]: Excel Office version number: 1906 (Build 11727.20210 Click-to-Run) Operating System: Windows 10 Enterprise 1903 18362.239 Browser (if using Office Online): Chrome (running there)

keyur32 commented 5 years ago

@RuoyingLiang can you check on this one please?

SWDevB commented 4 years ago

This still is an issue. Has it been addressed already?

I'd guess experienced Excel users immediately know it's a syntactical error and what to do to fix it in the default function's behavior. If #VALUE! is shown, they probably won't. Unless there is a solution I'm not aware of, this means to enable users to find problems as fast as they are used to, it seems to be necessary to make all parameters optional and provide own error messages for missing optional non-optional parameters.

SWDevB commented 4 years ago

Additionally, when I create a function using the insert function / function argument editor, also #VALUE! is shown as formula result. After clicking OK the correct result is shown in the edited cell.

image

SWDevB commented 4 years ago
SWDevB commented 4 years ago

Actually a good solution would be if Excel itself would provide a message mentioning all missing mandatory parameters. Maybe inside the comment of the cell marking errors.

RuoyingLiang commented 3 years ago

Custom functions now can accept invalid input and return customized message

jasonclg commented 3 years ago

Hi @RuoyingLiang, do you have the doc for the new behavior?

Custom functions now can accept invalid input and return customized message

jasonclg commented 2 years ago

@grangeryy Maybe you could help link the doc for the new behavior?

grangeryy commented 2 years ago

@jasonclg you may find some information HERE

jasonclg commented 2 years ago

@jasonclg you may find some information HERE @grangeryy Looks like I still cannot catch the exception and override the error message when no parameter was applied.