Recent

Author Topic: user define function  (Read 29534 times)

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user defined function
« Reply #60 on: June 26, 2018, 04:03:50 pm »
Problem with 'built-in' function:

ROUND(1.2345,2)  returns 1.23 but
ROUND(a1,b1) returns 1.00 where a1=1.2345 and b1=2

ISBLANK(" ") returns TRUE but
ISBLANK(A1) returns FALSE even if A1 contains only space/spaces

FACT(n) returns correct result if n is a number constant
FACT(A1) returns #VALUE!


3D formula:
countif(sheet2!A1: A5,">10") return same result as countif(A1: A5,">10")
Same problem with countif, countblank etc


I have not tested all the function. Just tested on a few that I used frequently.

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: user defined function
« Reply #61 on: June 26, 2018, 07:22:55 pm »
ROUND(1.2345,2)  returns 1.23 but
ROUND(a1,b1) returns 1.00 where a1=1.2345 and b1=2

FACT(n) returns correct result if n is a number constant
FACT(A1) returns #VALUE!
Fixed along with some similar ones.

ISBLANK(" ") returns TRUE but
ISBLANK(A1) returns FALSE even if A1 contains only space/spaces
The correct result must be FALSE, only if the cell is not allocated or contains only formatting the result is TRUE. Fixed the cases ISBLANK(" ") and ISBLANK("") which both return FALSE in Excel/Calc.

countif(sheet2!A1: A5,">10") return same result as countif(A1: A5,">10")
Fixed. It did not find an exact specification, but my experiments with Excel/Calc show that this type of formulas does not support a sheet range here, i.e. '=COUNTIF(Sheet1:Sheet2!A1:B10, ">10")' is not allowed. Therefore I am returning an error in such a case. A single sheet like in your example, however, is correct.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #62 on: July 01, 2018, 08:14:38 am »
When autocalc is off :-
WorkSheetGrid would display blank cell after a cell formula is keyed in /edited.

In similar situation, ExcelCalc would show the calculated value (but not updated subsequently when the related cells is modified).

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: user define function
« Reply #63 on: July 01, 2018, 10:19:26 am »
I never understood what this partial calculation mode would be good for. It will display inconsistent results somewhere in the end after all.

No, I will not do this. But your post gave me the idea to display the formula in the cells when AutoCalc is off.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #64 on: July 01, 2018, 02:55:08 pm »
I never understood what this partial calculation mode would be good for. It will display inconsistent results somewhere in the end after all.

No, I will not do this. But your post gave me the idea to display the formula in the cells when AutoCalc is off.

Though 'partial calculation' may not show the correct value, it serves two purposes to end user:
1. It let me know that the cell is not a blank cell (without navigating to the particular cell);
2. It helps to me check if I have entered a wrong formula. For example if A1=1 and B1=2, if I keyed into A1+B1 into C1, I would expect 3 to appear in C1. If C1 shows other value, immediately I would know I have keyed in wrong formula.
Also users may be confused if some formula cells (thosed created before the autocalc is turned off)  shows value and some show blank.
I was also confused as the formula cell (with value) suddenly became blank cell when I accidentally entered into edit mode and then exit edit mode (by clicking on other cell).
IMHO showing blank is not a good choice. Your idea of showing formula might be a workable alternative (and may also sovle my next question below).

When autorecalc is off with goAlwayShowEditor option,  the inplace editor will always show blank (or the value displayed in cell) . To edit the formula, I have to press ESC follwed by F2 key?

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: user define function
« Reply #65 on: July 01, 2018, 03:29:23 pm »
When autorecalc is off with goAlwayShowEditor option,  the inplace editor will always show blank (or the value displayed in cell) . To edit the formula, I have to press ESC follwed by F2 key?
I don't understand. Did you already try the new version? I never use goAlwaysShowEditor, even without it you can start typing immediately once the grid is focused. The only important option is goEditing which must be active to allow editing at all. The annoying feature of the LCL grids, however, is, that the left/right arrow keys end edit mode and move to the previous/next cell if the cursor is at the first/last character of the edited cell.
« Last Edit: July 01, 2018, 03:36:11 pm by wp »

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #66 on: July 01, 2018, 05:26:54 pm »
When autorecalc is off with goAlwayShowEditor option,  the inplace editor will always show blank (or the value displayed in cell) . To edit the formula, I have to press ESC follwed by F2 key?
I don't understand. Did you already try the new version? I never use goAlwaysShowEditor, even without it you can start typing immediately once the grid is focused. The only important option is goEditing which must be active to allow editing at all. The annoying feature of the LCL grids, however, is, that the left/right arrow keys end edit mode and move to the previous/next cell if the cursor is at the first/last character of the edited cell.
Just install SVN6542.  Works as expected. Thanks.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #67 on: July 02, 2018, 10:44:07 am »
I cannot WriteNumber(r1, c1, 123.45) if cell R1C1 already contain a formula. 

However, GetCell(r1, c1).^.NumberValue := 123.45 can overwrite the existing formula with 123.45


WriteFormular(r1, c1,'');  //or WriteBlank(r1, c1)
WriteNumber(r1, c1, 123.45)  works too.
 
(Same for WriteText method).

May I know what is the correct way to change the cell content ?

I also tried to clear the cell content first by EraseCell method but found that erasecell only take effect after I clicked on another cell.  Is this the normal behaviour?
« Last Edit: July 02, 2018, 06:47:36 pm by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: user define function
« Reply #68 on: July 05, 2018, 02:57:17 pm »
I cannot WriteNumber(r1, c1, 123.45) if cell R1C1 already contain a formula. 
Should be fixed in current trunk.

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: user define function
« Reply #69 on: July 05, 2018, 03:12:05 pm »
I also tried to clear the cell content first by EraseCell method but found that erasecell only take effect after I clicked on another cell.  Is this the normal behaviour?
EraseCell is a low-level function and does not perform notification. Use DeleteCell instead. I know it is hard to tell which one is the correct method...

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #70 on: July 06, 2018, 02:41:18 pm »
EraseCell is a low-level function and does not perform notification. Use DeleteCell instead. I know it is hard to tell which one is the correct method...
Thank you master WP for the fix.

I tried to install laz_fpspreadsheet_crypto.lpk but got this 'packages not found' error (see attached)
May I ask where can I find the dcpcyt package ?

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: user define function
« Reply #71 on: July 06, 2018, 02:58:56 pm »
The easiest way is to install dcpcrypt via Online Package Manager.

You only need the crypto package if you want to read Excel 2007 files (exactly this version, not just xlsx in general) in which passwords to remove workbook/worksheet protection are used (http://wiki.lazarus.freepascal.org/FPSpreadsheet#Passwords). It does not open generally encrypted files! So, its use is rather limited.

You may want to read https://forum.lazarus.freepascal.org/index.php/topic,36075.msg240803.html to learn about the background why it is there. I did not put any further work into the encryption thing, thus I am not 100% sure if the information provided is correct.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #72 on: July 06, 2018, 04:11:11 pm »
The easiest way is to install dcpcrypt via Online Package Manager.

You only need the crypto package if you want to read Excel 2007 files (exactly this version, not just xlsx in general) in which passwords to remove workbook/worksheet protection are used (http://wiki.lazarus.freepascal.org/FPSpreadsheet#Passwords). It does not open generally encrypted files! So, its use is rather limited.

You may want to read https://forum.lazarus.freepascal.org/index.php/topic,36075.msg240803.html to learn about the background why it is there. I did not put any further work into the encryption thing, thus I am not 100% sure if the information provided is correct.
Thanks for the link and useful info.  After reading and with better understanding of the package, I think I do not need to install crypto.

 

TinyPortal © 2005-2018