* * *

Author Topic: user define function  (Read 3963 times)

kjteng

  • Jr. Member
  • **
  • Posts: 96
Re: user define function
« Reply #45 on: June 09, 2018, 08:28:21 am »
New issue:
I get #REF! when I enter formula
                     sheet1!A1    -  in cell A1 of sheet2 (or sheet 3),
                     sheet2!A2    -  in cell A2 of other sheets
but entering    sheet1!A2 in cell A1 of sheet 2 is OK.

If I try to save the workbook, I get circular reference error (see attached)

kjteng

  • Jr. Member
  • **
  • Posts: 96
Re: user define function
« Reply #46 on: June 09, 2018, 08:51:25 am »
Another issue:
(i)
I accidentally keyed in an invalid formula (=BB)  into a cell say A1 of sheet3, an error message pop up (see e1.png attached) and the invlaid formula would not be shown in the worksheet.

After that, whenever I key in something to the worksheet (in any cell), I will get the same error message.

However, if i key in a valid 3D formula in cell A1 eg Sheet1!B12, the worksheet would be back to normal (error message wont popup anymore).


(ii)
Similar ourcome if I key in an invalid 3D formula such as Sheet1!AA in cell A1 of sheet 3
« Last Edit: June 09, 2018, 03:08:04 pm by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 4603
Re: user define function
« Reply #47 on: June 14, 2018, 12:18:49 pm »
I accidentally keyed in an invalid formula (=BB)  into a cell say A1 of sheet3, an error message pop up (see e1.png attached) and the invlaid formula would not be shown in the worksheet.

After that, whenever I key in something to the worksheet (in any cell), I will get the same error message.

However, if i key in a valid 3D formula in cell A1 eg Sheet1!B12, the worksheet would be back to normal (error message wont popup anymore).
This is hard stuff. I tried several approaches, but none of them worked out, mostly things are even getting worse...

There are two elemental issues:
  • While TCustomGrid has a ValidateEntry method which can be used to prevent leaving an erroneous cell, TEdit does not have such a thing. It does have OnEditingDone but this is too late, it is called when the other control is already focused. Does anybody know a cross-platform way to hook into the TEdit method flow to call some kind of "ValidateEntry" method before the control loses focus?
  • Even if this can be solved there is the fundamental question how an error message can be displayed in a messagebox if the error control is not allowed to become unfocused. Or how the application can be closed in the error case by a click on a TButton which also requires a focus change.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus