none
cell.validation.add() crash in excel 2016 RRS feed

  • Question

  • my C# code will use an excel instance, but before I run any micro inside the excel, I will set below property of the excel instance in C# side.

    • Body text cannot contain images or links until we are able to verify your account.

    As u can see above, after I set the excel instance property, I will run macro TestDataValidationAdd inside excel 

    • Body text cannot contain images or links until we are able to verify your account.


    Reproduce Env.

    Win10 (OS Build 10586.545) + Excel2016 Latest Version (Version: 16.0.7167.2060)

    • Body text cannot contain images or links until we are able to verify your account.

    Build Env.

    Visual Studio 2010 (Version 10.0.40219.1 SP1Rel)

    • Body text cannot contain images or links until we are able to verify your account.

    Reproduce Step

    1. Copy DataValidationTest.xlsm to D:\

    2. Run DataValidationTest.exe

    3. First you will see popup dialogue with "Hi"

    4. Click OK, you will see above Error Dialogue which is due to cell.validation.add() crash somewhere.

    5. However, in Win7 + Excel 2010. All goes well and you can see the dialogue with "Bye" without above crash popup.

    Source Code Example:

    http://pan.baidu.com/s/1jIhlFlc

    All missing picture above due system not allow me submit capture

    http://pan.baidu.com/s/1c2BkfMK

    • Edited by MickingWang Monday, September 19, 2016 10:53 AM
    Monday, September 19, 2016 10:51 AM

Answers

  • I could reproduce your issue, in Office2010, the app works while Office2013 and 2016 not.

     

    >>I have step debug the code, it is really crash at validation.add() function.

     

    You could run the macro in the Excel application instead of this automation app.

    The macro runs fine, indicating the method is correct and other code in your automation application causes the issue.

    And according to the comment in your code, I think you have already found that using WindowState=xlNormal works. Only when setting xlMinimized and Visible=false, it causes the defined error.

     

    >>The product whole way need minimized, so can't set to xlNormal

     

    If you want the Excel application visible or not?

    In fact, if you want it invisible, the WindowState status would not affect it. And now xlMinimized causes the issue, why don’t set to xlNormal(xlMaximized also works)

    If you want the Excel app minimized, you need to set Visible = true, however it would cause another issue: you must open the app to click the OK let the macro run.

    I would insist on setting xlNormal with excelApp.Visible = false

     


    Wednesday, September 21, 2016 3:27 AM
    Moderator

All replies

  • Hi,

    "Application-defined or object-defined error" causes from excelApp.WindowState and excelApp.Visible instead of cell validation.

    If you set the WindowState=xlMinimized, you are unable to set Visible = false.

    Please use xlNormal if you want to set Visible = false.

    Tuesday, September 20, 2016 2:27 AM
    Moderator
  • Hi Celeste

    thx for the reponse.

    First, I have step debug the code, it is really crash at validation.add() function.

    Secondly,

    All the code working fine in Win7 + Office 2010.

    I'm porting an old project to Win10 + Office 2016 and suddently find this problem. The product always crash at the validation.add() function which looks no relationship to the .WindowState or .Visible. But unfortunately it really does.

    Thridly, The product whole way need minimized, so can't set to xlNormal. Any other suggestion?

    Thx a lot.

    Tuesday, September 20, 2016 7:18 AM
  • I could reproduce your issue, in Office2010, the app works while Office2013 and 2016 not.

     

    >>I have step debug the code, it is really crash at validation.add() function.

     

    You could run the macro in the Excel application instead of this automation app.

    The macro runs fine, indicating the method is correct and other code in your automation application causes the issue.

    And according to the comment in your code, I think you have already found that using WindowState=xlNormal works. Only when setting xlMinimized and Visible=false, it causes the defined error.

     

    >>The product whole way need minimized, so can't set to xlNormal

     

    If you want the Excel application visible or not?

    In fact, if you want it invisible, the WindowState status would not affect it. And now xlMinimized causes the issue, why don’t set to xlNormal(xlMaximized also works)

    If you want the Excel app minimized, you need to set Visible = true, however it would cause another issue: you must open the app to click the OK let the macro run.

    I would insist on setting xlNormal with excelApp.Visible = false

     


    Wednesday, September 21, 2016 3:27 AM
    Moderator