How to Trap Cell Formula Validation Errors in Numeric Cells

How to Trap Cell Formula Validation Errors in Numeric Cells

Posted by: greygranite on 8 September 2017, 5:06 am EST

• 

  greygranite

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:06 am EST

  In Farpoint Spread for Winforms, is there a way to trap/catch an invalid formula?  The CalcError codes for #NAME?, #REF!, #VALUE! and #DIV/0 are generated and loaded as values, but these are not displayed to the cell.  The currency/number cell's filter appears to hide all of the non numeric characters from displaying.  The FpSpread_EditError and FpSpread_Error events do not appear to get raised when these occur (breakpoints not being hit).

  Is there another event or code that I should look for to be able to catch these and display a popup to the user that they have entered in an invalid formula?

  
  
  Version of Farpoint used:
  
  FarPoint.CalcEngine 4.0.2012.2005
  
  FarPoint.Excel  4.0.2012.2005
  
  FarPoint.Win  4.0.2009.2005
  
  FarPoint.Win.Spread  4.0.2009.2005

  
  
  Thanks,

  Ed Ostrowski

• 

  deepaksharma14

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:06 am EST

  Hello Ed Ostrowski,

  You could use the ISERR,ISERROR or TYPE Function(returns 16 for Error Value) for the specified cell and if returns True you can display a popup to the user that they have entered in an invalid formula.

  I hope it helps.

  Thanks,
  
  

  
  
  

• 

  bobbyo

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:06 am EST

  Ed Ostrowski,

  There is no specialized event that indicates a cell formula has evaluated to a CalcError value.

  There are generalized events that indicate changes to a cell (e.g. ISheetDataModel's Changed event or SheetView's CellChanged event).  You could catch one of these events and check the type of the cell's value.  If the cell's value is a CalcError then you could popup your error message.

• 

  greygranite

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:06 am EST

  Thanks for the reply guys.  Will be sitting on the EditModeOff and checking for the CalcError value there.

  Now a related validation question. Is there a way to tell if the cell's formula is going to make a Circular Reference?  Unfortunately using the Iteration properity is not an option for us.  Since no CalcError is generated for that, I'm stumpted on how to tell.  All that I see is the original value being loaded when it is a circular ref.   Any way to check for this?

• 

  sureshd

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:06 am EST

  Hello,

  I am not sure, if the following information from Spread doucmentation will be helpful for you:

  As with most spreadsheet products (including Excel and OpenOffice), FarPoint Spread solves circular formulas via iterations. During each recalculation cycle, a specified number of iterations are performed. During each iteration, every circular formula is evaluated exactly once. The exact order in which the circular formulas are evaluated during a given iteration can not be assumed by the application. As with most spreadsheet products (including Excel and OpenOffice), circular formulas in FarPoint Spread are intended to be used in scenarios where the iterations converge to the desired solution regardless of the order of evaluation with in a given iteration.
  
  

  If the formula "=COLUMNS(A1:C5)" is in cell C4, no result is returned. In other words, if both the last column and row index of the array are greater than the column and row index of the cell in which the formula resides, the formula cannot be calculated. In this case, the cell C4 is in the range A1:C5. This a circular reference in a formula and so Spread does not evaluate the formula unless iterations are turned on.

  Thanks
  
  

• 

  bobbyo

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:06 am EST

  Ed Ostrowski,

  There is currently no way for the application to determine when a cell's evaluation is skipped due to a circular reference.  During a recalculation cycle, the spreadsheet orders the cells for evaluation based on cell dependencies.  As part of the ordering process, the spreadsheet will determine which cells are part of a circular reference.  If iterations are turned off then the evaluation of the set of cells containing circular references will be skipped.  Since no new value is placed in these cells, there is nothing for the application to check to determine that the cell's evaluation was skipped.

  In the future, we could maybe add an enhancement to fire an event when a cell's evaluation is skipped due to circular references.  But for now there is no way for the application to determine this.

  Bobby

• 

  greygranite

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:06 am EST

  Thanks for the explaination on the order of events.  Appreciate it.

   Ed Ostrowski

• 

  scotts-fp

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:06 am EST

  Ed,

  I wanted to let you know we have added this to the enhancement list for the .NET products of Spread.

• 

  greygranite

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:06 am EST

  Thanks Scott.

• 

  edinsf

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:06 am EST

  Has anything changed in Version 5 with reference to determining if circular references exist?
  
  

• 

  deepaksharma14

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:06 am EST

  Hello,

  There seems to be no property/event added to Spread 5 to get to know about the circular reference for a cell. I will investigate it further and will submit a feature request if required.

   

  Thanks,

   

• 

  jasper

  • Post Options:
  • Link
    Copy

  Posted 29 January 2019, 12:44 am EST

  How about in Spread 10 or 11?