none
Issue with Validation.Add

    Question

  • I'm creating an excel sheet using VSTO and i have a situation wherein the user needs to enter only decimal values and that particular cell has a format set to percentage.

    Here is my code

    Excel.Range oRange = Globals.Sheet3.Range["TaxRate", missing];

    oRange.Validation.Add(XlDVType.xlValidateDecimal, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, 0.0, 99.9);

    oRange.Validation.IgnoreBlank = false;

    oRange.Validation.ErrorTitle = "Not a valid tax rate";

    oRange.Validation.ErrorMessage = "Please enter a valid tax rate";

    Now, when i execute this i get a wierd exception stating

    Exception from HRESULT: 0x800A03EC


    ************** Exception Text **************
    System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC

    Server stack trace:


    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at Microsoft.Office.Interop.Excel.Validation.Add(XlDVType Type, Object AlertStyle, Object Operator, Object Formula1, Object Formula2)
       at ABC.Sheet3.Sheet3_Startup(Object sender, EventArgs e)

    any help ?

    Thanks, Arumugam

    Wednesday, April 19, 2006 7:44 AM

Answers

  • There's an example of this on MSDN:

    http://msdn2.microsoft.com/en-US/library/microsoft.office.tools.excel.namedrange.validation(VS.80).aspx

    What the sample doesn't seem to make clear is where this code is executing. I originally wrote this code into a button handler.and saw the same error you described.

    However, if you execute the same code inside the startup event it works fine.

    private void Sheet3_Startup(object sender, System.EventArgs e)
    {
        TaxRate.Validation.Add(
            Excel.
    XlDVType.xlValidateDecimal,
            Excel.
    XlDVAlertStyle.xlValidAlertStop,
            Excel.
    XlFormatConditionOperator.xlBetween, 0.0, 99.9);

        TaxRate.Validation.IgnoreBlank = false;
        TaxRate.Validation.ErrorTitle =
    "Not a valid tax rate";
        TaxRate.Validation.ErrorMessage =
    "Please enter a valid tax rate";
    }

    In some respects this seems to make sense but the documentation isn't clear. I've filed a bug on this for you.

    Let me know if for some reason you cannot add validation during startup or you're already doing this and still seeing errors.

    Thanks,

    Ade

     

     

    Saturday, April 29, 2006 5:13 PM

All replies

  • I think this is an Excel LCID proxy related issue. You can read more about this here:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2005_ta/html/OfficeVSTOGlobalization.asp

    I'm trying to figure this out. In the meantime could you tell me what locale your machine is set to and if you have any Office language packs installed.

    Thanks,


    Ade

     

    Thursday, April 20, 2006 5:36 PM
  • Ade,

    My machine is set to "us-en" and i dont have any office language packs installed.

    thanks,

    Arumugam

    Monday, April 24, 2006 6:45 AM
  • There's an example of this on MSDN:

    http://msdn2.microsoft.com/en-US/library/microsoft.office.tools.excel.namedrange.validation(VS.80).aspx

    What the sample doesn't seem to make clear is where this code is executing. I originally wrote this code into a button handler.and saw the same error you described.

    However, if you execute the same code inside the startup event it works fine.

    private void Sheet3_Startup(object sender, System.EventArgs e)
    {
        TaxRate.Validation.Add(
            Excel.
    XlDVType.xlValidateDecimal,
            Excel.
    XlDVAlertStyle.xlValidAlertStop,
            Excel.
    XlFormatConditionOperator.xlBetween, 0.0, 99.9);

        TaxRate.Validation.IgnoreBlank = false;
        TaxRate.Validation.ErrorTitle =
    "Not a valid tax rate";
        TaxRate.Validation.ErrorMessage =
    "Please enter a valid tax rate";
    }

    In some respects this seems to make sense but the documentation isn't clear. I've filed a bug on this for you.

    Let me know if for some reason you cannot add validation during startup or you're already doing this and still seeing errors.

    Thanks,

    Ade

     

     

    Saturday, April 29, 2006 5:13 PM
  • Ade,

    i have the validation code in sheet startup event only and still i get the same error.

    Also, I have tried to add validation in design time of the sheet and it looks like there is a issue in that as well.

    Here's what i did in design time.

    Opened the worksheet,

    1) change the cell format to Percentage and set decimal places to 2

    2) Added a data validation from data menu in excel, and set the validation criteria to decimal and value between 0.0 and 99.9

    3) try entering some non-numeric values (ex: abc) and press enter it throws the validation error and then type some numeric value in the cell (ex: 2) and press enter it will display 200.00%. (it should display 2.00%)

    4) But when i try entering a numeric field at the first time it shows proper value.

    Thanks,

    Arumugam

    Sunday, April 30, 2006 4:19 AM
  • I agree the behavior for % values is odd, it seems like the first value put in after a validation error is incorrectly multiplied by 100. If you really want this to be formatted as a percentage then the upper an lower boundaries should be 0.0 and 0.999 for the data, the formatting will then display this as a correct percentage.

    You might want to follow up on this on the Excel newsgroups, maybe there is a workaround. I was unable to find one, other than storing the number as a decimal between 0 and 100 and then calculating the percentage later.

    General programming issues: excel.programming newsgroup

    http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.excel.programming&lang=en&cr=US

    As for the VSTO programmability issue with adding validation at runtime I can't get this to fail, even with the cell formatted as a %. Is there anything we're missing here? Are you doing anything else in the startup event?

    Thanks,

    Ade

     

     

    Monday, May 01, 2006 5:08 PM
  • I was having same error code when using excel's chart.export function. I figured out it had something to do with user rights or profile. I changed MS Excel application identity at DCOM config from launching user to interactive user and everything works after that. I have no idea what was the problem with ASPNET user...
    Tuesday, May 02, 2006 7:26 AM
  • I didn't realise that you were running this from the ASPNET user account. Provided you undertstand that there are security and performance implications of doing this then I think you're in good shape.

    Thanks,

    Ade

     

    Tuesday, May 02, 2006 8:08 PM