Formulas validation

[Jean-Francois Girouard]

Hi

I am using the GridControl with formulas. Is there any way to trap formula validation errors? For exemple, if I enter "=(A1*2", I get a popup saying "mismatched parentheses". I would prefer to intercept this message, display a custom error message, and take action to correct the problem, but I can''t find a way for my code to trap or handle this.

A similar thing happens when the formula "==" is entered, a popup displays "Index was outside the bounds of the array".

Thanks,
Jean-François Girouard

[Administrator]

One way you can do this is to handle the Currentcellvalidating event, and in your handler test the new text for validity using GridFormulaEngine.IsFormulaValid.

private void gridControl1_CurrentCellValidating(object sender, CancelEventArgs e)
{
	GridCurrentCell cc = this.gridControl1.CurrentCell;
	GridStyleInfo style = this.gridControl1[cc.RowIndex, cc.ColIndex];
	string proposedValue = cc.Renderer.ControlText;
	if(style.CellType == "FormulaCell" && proposedValue.StartsWith("="))
	{
		GridFormulaEngine engine = ((GridFormulaCellModel)this.gridControl1.CellModels["FormulaCell"]).Engine;
		string parsedFormula = "";
		string errorMessage = "";
		string computedValue = "";
		if(!engine.IsFormulaValid(proposedValue, out parsedFormula, out errorMessage, out computedValue))
		{
			MessageBox.Show(string.Format("error: {0}", errorMessage));
			e.Cancel = true;
		}
	}
}

[Jean-Francois Girouard]

I use the version 3.0.1.0 and I don''t have the method IsFormulaValid() available in the GridFormulaEngine object. Is it only available in more recent versions? Is there another way to validate the formulas with version 3.0.1.0?

If it matters, I''m using VB.Net.

Thanks

[Administrator]

The method was added in the 4.x code base. If you upgrade to the latest release, then you will have access to it.

If you want to try to do this type of validation with an earlier release, then you would have to use reflection to access internal methods that were not exposed in the earlier releases. These methods are now public in 4.x and a new IsFormulaValid method has been added to explicitly facilitate what you want to do.