Formula evaluation returned #VALUE after custom name is used in the formula

Posted by: wendi on 8 September 2017, 1:51 pm EST

  • Posted 8 September 2017, 1:51 pm EST

    I have defined a custom name for a cell. If I used the custom name in a formula, #VALUE was returned. If I used the cell reference instead of custom name, result was correct. I have screen shots that showed the problems that I have. Please let me know how I can attach them.


     


    Thanks!

  • Replied 8 September 2017, 1:51 pm EST

    Wendi,



    When you are composing your message, you can click on the "Options" tab which has an "Add/Update" button for adding attachments.


    Can you provide more details about the problem that you are encountering.  What formula was used to create the custom name?  What value was in the cells being referenced?  What formula was in the cell that was evaluating to the error?


    There are a couple of things that can cause a #VALUE! error.  Passing the wrong type of argument to an operator or function will cause a #VALUE! error.  Passing the wrong number of arguments to a custom function will also cause a #VALUE! error.

  • Replied 8 September 2017, 1:51 pm EST

    I have summarized the steps that I did in the attached document. I think the screen shots answered all the questions. Please let me know if you need more info from me.


    Thanks!


    2009/04/FormulaEvaluationError.zip
  • Replied 8 September 2017, 1:51 pm EST

    Hello,


    How are you setting up the CustomName? Here is the code I used to do this and your steps worked in my test.


    FpSpread1.Sheets(0).AddCustomName("sex", "$E$2", 0, 0)


    or you can use the following line of code.


    FpSpread1.Sheets(0).AddCustomName("sex", New FarPoint.CalcEngine.CellExpression(1, 4))

  • Replied 8 September 2017, 1:51 pm EST

    Hello Wendi, 

    Thank you for sharing your code. However, we would like to know how are you creating the CustomName? I created a sample application  using the latest Spread 4  version 4.0.3510.2008 in which I added the CustomName to E2 cell using following statement: 

            CType(FpSpread1.ActiveSheet.Models.Data, FarPoint.Win.Spread.Model.DefaultSheetDataModel).AddCustomName("Sex", New FarPoint.CalcEngine.CellExpression(1, 4))

    Try adding the CustomName using the above statement.In case, if the issue persists, please upgrade to the latest build of Spread.

    Regards, 

  • Replied 8 September 2017, 1:51 pm EST

    Thank you for looking into this issue.


    The version of the spread I am using is FarPoint Spread for Windows Forms 4.0 (.Net 3.5)


    Here are the steps that create the custom name "sex".


    1. A custom name is first added by the following code, the custom name that's added is "Gender_C" (the same as the label name in D2 in previous attached document). The custom name is a string.


    fpSpread1.ActiveSheet.AddCustomName(columnDefinition == null ? variableString : columnDefinition.Name, cellFormula.ToString(), 0, 0);


    Result is correct when Gender_C is used in the formula of cell E1.


    2. User then type in "sex" in the NameBox to set a custom name for cell E2. The following code first checks whether a custom name has been defined for the cell. If so, remove the custom name and set it to the new name. The custom name is a string. The following is part of the code that does the  checking and adding.


    private void rangeTextBox_KeyPress(object sender, KeyPressEventArgs e)


    {



    if (e.KeyChar == '\r')


    {


    // if the user presses the enter key, add a range for the currently selected cells


    string rangeName = rangeTextBox.Text.Trim();


    if (rangeName != string.Empty)


    {



    // first, see if the range is already declared


    FarPoint.CalcEngine.Expression expression = fpSpread1.ActiveSheet.GetCustomName(rangeName);


    if (expression == null)


    {


    FarPoint.Win.Spread.Model.CellRange range = fpSpread1.ActiveSheet.GetSelection(0);


    //Need to check whether there's custom name defined for the range already


    //get an IEnumerator that enumerates through the custom names defined in the model


    IEnumerator enm = fpSpread1.ActiveSheet.GetCustomNameEnumerator();


    //FarPoint.CalcEngine.ExternalCellExpression currentExpression;


    FarPoint.CalcEngine.Expression currentExpression;


    while (enm.MoveNext())


    {


    currentExpression = fpSpread1.ActiveSheet.GetCustomName(enm.Current.ToString());


    if (currentExpression is FarPoint.CalcEngine.CellExpression)


    {


    //Remove existing custom name


    if (((FarPoint.CalcEngine.CellExpression)currentExpression).Column == range.Column &&


    ((FarPoint.CalcEngine.CellExpression)currentExpression).Row == range.Row)


    {


    fpSpread1.ActiveSheet.RemoveCustomName(enm.Current.ToString());


    break;


    }


    }


    }


     



    if (range != null)


    {


    FarPoint.CalcEngine.RangeExpression rangeExpression = new FarPoint.CalcEngine.RangeExpression(range.Row, range.Column, range.RowCount, range.ColumnCount);


    try


    {


    fpSpread1.ActiveSheet.AddCustomName(rangeName, rangeExpression);


    }


    catch


    {


    MessageBox.Show("Unable to add range name", this.Presenter.Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);


    }


    }


    }


    Sorry that I can't provide you a sample application. The part that I showed you is from a big application that we are currently developing.


     Thanks again for your help!

  • Replied 8 September 2017, 1:51 pm EST

    Wendi,


    Thanks for your code.  I see the issue now.  Your earlier posts implied that the name "sex" was being defined as the cell expression $E$2.  The code shows that the name "sex" is actually being defined as the range expression $E$2:$E$2.  While this may seem like a small difference, it is actually a very important difference.


    A range expression will evaluate to an array of values.  Since Spread does not support array formulas, attempting to pass an array to an operator will result in the #VALUE! error.  With the name "sex" defined as the range expression $E$2:$E$2, the evaluation of the formula occured as follows...


        step 0: sex<>"M"
        step 1: $E$2:$E$2<>"M"
        step 2: {"M"}<>"M"
        step 3: #VALUE!


    A cell expression will evaluate to a single value.  If the name "sex" were defined as the cell expression $E$2 then the evaluation of the formula would occur as follows...


        step 0: sex<>"M"
        step 1: $E$2<>"M"
        step 2: "M"<>"M"
        step 3: FALSE


    In your code, you could change the following...


        FarPoint.CalcEngine.RangeExpression rangeExpression = new FarPoint.CalcEngine.RangeExpression(range.Row, range.Column, range.RowCount, range.ColumnCount);


    to something like the following...


        FarPoint.CalcEngine.ReferenceExpression rangeExpression;
        if (range.RowCount == 1 && range.ColumnCount == 1)
        {
            range = new FarPoint.CalcEngine.CellExpression(range.Row, range.Column);
        }
        else
        {
            range = new FarPoint.CalcEngine.RangeExpression(range.Row, range.Column, range.RowCount, range.ColumnCount);
        }

  • Replied 8 September 2017, 1:51 pm EST


    Bobbyo,


    I tried your suggestions in my project. The application now works as expected. Thanks for your help!


    Thanks every one for your suggestions and help!

  • Replied 8 September 2017, 1:51 pm EST

    Wendi,


    I am unable to reproduce the problem you are encountering.  Could you provide more details?  What version of Spread are you using?  How did you create the custom name?  Did you call AddCustomName with an Expression or a String?  If you used a String, did you use an absolute reference (e.g. "$E$2") or a relative reference (e.g. "E2")?  If you used a relative reference, what base cell indices did you use?  Could you provide the actual source used to create the custom name?  Or even better, could you provide a sample application that demostrates the problem?

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels