https://help.libreoffice.org/7.1/en-US/text/scalc/01/12120300.html?System=WIN&DbPAR=CALC&HID=modules/scalc/ui/erroralerttabpage/ErrorAlertTabPage#bm_id3156024 There are 3 levels of validation errors in Calc: 1) Stop (error box, reject entry) 2) Warn (warning box with No as default button) 3) Info (question box with Yes as default button) In addition you can define a macro to handle failed validations. The documented StarBasic definition Function ExampleValidity(CellValue as String, TableCell as String) has 2 flaws: 1) CellValue should be named CellFormula because the passed string is a formula expression as entered into the formula bar (property "FormulaLocal"). 2) The macro is declared as a function without a return value. In fact, the entry is rejected when the macro function returns False and accepted in case of True. A better example macro looks like this: Function ExampleValidity(CellFormula as String, TableCell as String) Dim msg as string Dim iAnswer as integer Dim MB_FLAGS as integer msg = "Invalid value: " & "'" & CellFormula & "'" msg = msg & " in table: " & "'" & TableCell & "'" msg = msg & Chr(10) & "Accept anyway?" MB_FLAGS = MB_YESNO + MB_ICONEXCLAMATION + MB_DEFBUTTON2 iAnswer = MsgBox (msg , MB_FLAGS, "Error message") ExampleValidity = (iAnswer = IDYES) End Function
Created attachment 173376 [details] Validation example based on suggested example macro The validation in the green area accepts integers > 0. In case of failure the validation macro asks if the value should be accepted anyway. A red conditional format marks the entries where the validation prompt has been answered with Yes.
Hi Andreas! Thanks for reporting this issue. I agree with you that the example can be improved. I also tested your code and it works. There's just one thing I don't understand. Do you have any idea why the macro deals with the cell formula instead of accessing the value directly? (I would like to understand it so I can document it better) The fact that the macro works with the cell formula leads to weird situations, for instance (see the attached file): - Column A has some values - Column C has a validity rule to accept only integer values that are <= 100; if an error occurs, I call the macro you proposed - Note that if the user inputs values directly, everything works as expected; but if a formula is entered, the formula result is not validated; for example, in cell C5 the result of the formula is 50 (which is <= 100) but still I get an error. So regardless of the formula result, the validation rule returns an error. Hence, I don't know why LO implemented this feature using the cell formula. I know that this is not related to the macro you proposed. But I think it's an important point to address when updating the help page.
Created attachment 173386 [details] Cell Formula Example
With or without macro, the validation feature evaluates the literal user input (the formula). As far as I know, there is no way to catch formula results, no calculation event or anything like that.
It makes sense... LO does not evaluate the formula before validating cell content. My only concern about using "CellFormula" instead of "CellValue" is that it would cause the same confusion in users. Maybe they'll start thinking LO should evaluate formulas before validating the cell value. How about we use the proposed code, but keeping CellValue to avoid confusion? Hence, the new example would be: Function ExampleValidity(CellValue as String, TableCell as String) as Boolean Dim msg as string Dim iAnswer as integer Dim MB_FLAGS as integer msg = "Invalid value: " & "'" & CellFormula & "'" msg = msg & " in table: " & "'" & TableCell & "'" msg = msg & Chr(10) & "Accept anyway?" MB_FLAGS = MB_YESNO + MB_ICONEXCLAMATION + MB_DEFBUTTON2 iAnswer = MsgBox (msg , MB_FLAGS, "Error message") ExampleValidity = (iAnswer = IDYES) End Function
Created attachment 173395 [details] Test validation formulas Indeed, a condition is a formula expression (or two), and it is evaluated.
OK, but the user input is not evaluated and this is confusing me. Name it CellValue. The Boolean return value is more important.
A patch has been proposed in Gerrit: https://gerrit.libreoffice.org/c/help/+/118715
Rafael Lima committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/help/commit/19c8e31a7159740f3a2048e459b0237786d0a995 tdf#143211 Improve macro example in Validity check
Verified in the current Help for 7.3. https://help.libreoffice.org/7.3/en-US/text/scalc/01/12120300.html?System=WIN&DbPAR=CALC&HID=modules/scalc/ui/erroralerttabpage/ErrorAlertTabPage#bm_id3156024