* * *

Author Topic: user define function  (Read 10479 times)

kjteng

  • Full Member
  • ***
  • Posts: 127
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

  • Full Member
  • ***
  • Posts: 127
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: 4966
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

kjteng

  • Full Member
  • ***
  • Posts: 127
Re: user define function
« Reply #48 on: June 20, 2018, 09:37:03 am »
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.
Noted your comment above.
I also noticed that you have amended the code in fpspreadsheetgrid.pas and the test result are follows:
Now if I entered =AA  in TsCellEdit box, I will get 'Unknown identifier' error message, and I have two choices: OK or Abort. If I choose OK, the cursor will be back to the tsCellEdit box for me to make changes----this seems to be ok.

However, if I key in =AA directly in the wsGrid (editor on the cell), I will get 'Unknown identifier' error message with an OK button. I am stuck in this error message window because the same message will be display if I click ok button. The only way to get out is to kill the program from task manager.  Please look into this. TQ
 

kjteng

  • Full Member
  • ***
  • Posts: 127
Re: user define function
« Reply #49 on: June 20, 2018, 12:52:05 pm »
Sorry. Just installed trunk 6513. Now I can get error message with ok and abort button in all cases.
However, when program is closed, I got a long message from heaptrc. See attached pix.  I dont really know what it means (though normally I got a shorter message saying that there is 0 unfreed memory block).
Is it ok to ignore this message?
« Last Edit: June 20, 2018, 05:47:31 pm by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 4966
Re: user define function
« Reply #50 on: June 20, 2018, 07:05:56 pm »
Is it ok to ignore this message?
This indicates a lot of memory leaks. Please report the EXACT steps how to reproduce.

Maybe it is the same which I can reproduce with these steps, but which I could not fix so far:
- Use your demo program (the one posted recently in the "Workbook.ReadFromFile" thread"
- Activate HeapTrc in the Project Options.
- Compile and run.
- Click in cell A1
- Enter '1', press ENTER --> the active cell jumps to A2
- Click in the TsCellEdit box
- Enter '=A1', press ENTER --> Cell A2 displays '1'
- Click in any cell, e.g. B2
- Close the program --> a long list of memory leaks appears.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

wp

  • Hero Member
  • *****
  • Posts: 4966
Re: user define function
« Reply #51 on: June 20, 2018, 07:22:08 pm »
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.
Noted your comment above.
I also noticed that you have amended the code in fpspreadsheetgrid.pas and the test result are follows:
Now if I entered =AA  in TsCellEdit box, I will get 'Unknown identifier' error message, and I have two choices: OK or Abort. If I choose OK, the cursor will be back to the tsCellEdit box for me to make changes----this seems to be ok.

However, if I key in =AA directly in the wsGrid (editor on the cell), I will get 'Unknown identifier' error message with an OK button. I am stuck in this error message window because the same message will be display if I click ok button. The only way to get out is to kill the program from task manager.  Please look into this. TQ
I fixed a lot of the navigation issues in the error case. This is what should work:
- Use the in-place editor of the grid to type an erroneous formula (e.g. '=A')
- Navigate to another cell by click, arrow keys, or complete editing by ENTER key --> the incorrect formula is detected, the parser's error message is displayed, and navigation is reverted, the grid is in the erroneous cell in edit mode again.

This is what is not working:
(1)
-  Use the in-place editor of the grid, type an erroneous formula
- Click into the TsCellEdit box. The error message appears, but the cell is erased - this is not good.

(2)
- Use the TsCellEdit box to type an erroneous formula
- When the error message comes up, press ENTER to close it (don't click "OK") -> the box comes up again and again (However, if you click OK with the mouse, the message box does not appear again.

(3)
- Use the TsCellEdit box to type an erroneous formula
- After typiing don't press ENTER, but click into the cell. The formula is erased. The error message box comes up. Press ENTER or click OK to close the error message. A selection rectangle appears in the grid which follows the mouse. You must click anywhere to return to normal operation.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 127
Re: user define function
« Reply #52 on: June 21, 2018, 11:40:59 am »
(0)
Quote
- Use the in-place editor of the grid to type an erroneous formula (e.g. '=A')
- Navigate to another cell by click, arrow keys, or complete editing by ENTER key --> the incorrect formula is detected, the parser's error message is displayed, and navigation is reverted, the grid is in the erroneous cell in edit mode again.

- I got the same result here


(1)
Quote
-  Use the in-place editor of the grid, type an erroneous formula
- Click into the TsCellEdit box. The error message appears, but the cell is erased - this is not good.

I got the same result here

Note that the cell is not erased but reverted to its original value. If the cell contains 123 before the errorneous formula is entered,  it will revert to 123 after the error message appears. i.e. the user will be editing the cell containing 123 now.  If the cell was empty, it would revert to empty cell (thus the formula appeared to be erased).

This above test result is consistent with the following cases:
(1a)
Goto cell A1, in tsEdit key in =aa then click into cell A1 in the grid. Click OK when error message appears. The cell will revert to the original value of A1. 

(1b)
Goto cell A1, in tsEdit key in =aa then click into cell C1 in the grid. Click OK when error message appears. The cell will revert to the original value of C1.
 
I think the above behaviour is acceptable.

(2)
Quote
- Use the TsCellEdit box to type an erroneous formula
- When the error message comes up, press ENTER to close it (don't click "OK") -> the box comes up again and again (However, if you click OK with the mouse, the message box does not appear again.

I got the same result here


(3)
Quote
- Use the TsCellEdit box to type an erroneous formula
- After typiing don't press ENTER, but click into the cell. The formula is erased. The error message box comes up. Press ENTER or click OK to close the error message. A selection rectangle appears in the grid which follows the mouse. You must click anywhere to return to normal operation.
Same result but see my comment in (1) above


(4) Goto cell A1, do steps in (0) above. When the erroneous cell is back to edit mode, change the formula to =B1
Now we can see the formula B1 in tsEdit whenever we are in cell A1, however cell A1 would not show any value even if B1 contains a valid number.

Same thing would happen if you key in erroneous formula in TsCellEdit then changed it to a valid formula.

(5) At times, heaptrc shows there is unfreed memory block, but I am yet to find the way to reproduce this.

wp

  • Hero Member
  • *****
  • Posts: 4966
Re: user define function
« Reply #53 on: June 21, 2018, 12:50:18 pm »
At times, heaptrc shows there is unfreed memory block, but I am yet to find the way to reproduce this.
I fixed a memory leak related to the new handling of formulas.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 127
Re: user define function
« Reply #54 on: June 22, 2018, 08:15:01 am »
(Y)

wp

  • Hero Member
  • *****
  • Posts: 4966
Re: user define function
« Reply #55 on: June 25, 2018, 12:04:24 am »
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...
Maybe I've got an operating solution now - it based on the old Borland article https://community.embarcadero.com/article/technical-articles/149-tools/12766-validating-input-in-tedit-components and looks ok to me. Now it is no longer possible to exit the CellEdit or edited WorksheetGrid cell while an entered formula is not valid.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 127
Re: user define function
« Reply #56 on: June 25, 2018, 04:21:26 am »
Thanks for the info. Congratulation.

kjteng

  • Full Member
  • ***
  • Posts: 127
Re: user define function
« Reply #57 on: June 25, 2018, 12:43:40 pm »
Quote
Maybe I've got an operating solution now - it based on the old Borland article https://community.embarcadero.com/article/technical-articles/149-tools/12766-validating-input-in-tedit-components and looks ok to me. Now it is no longer possible to exit the CellEdit or edited WorksheetGrid cell while an entered formula is not valid.

Tested. Works.
One  minor comment: on returning to celledit or inplace editor (after closing the error message), sometime the whole edit text is selected, something it is not selected but the cursor remains at its original position.  It seems that this is dependant on (1) how I ended the edit (by pressing enter key, click on grid cell or click on cellEdit); and (ii) how I responded to the error message (press enter key or click OK). 

I tried to but still have not figured out the behaviour pattern.

wp

  • Hero Member
  • *****
  • Posts: 4966
Re: user define function
« Reply #58 on: June 25, 2018, 01:58:53 pm »
I know. The current revision seems to have a more consistent editor behavior.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 127
Re: user define function
« Reply #59 on: June 26, 2018, 03:29:54 pm »
TsCellEdit accept edited text only if the user press enter key and not if the user click on grid. Is there a property need to be set to change this behaviour?

 

Recent

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