none
Range.FormatConditions.Add throws MissingMethodException

    Question

  • I'm trying to migrate a fully functional system that was developed in VS2005 with VSTO using Office 2003. I installed everything I figured I'd need to and it sort of almost works cleaning in VS2008 using Office 2007 now. One significant snag is bugging me. I use the Range.FormatConditions object and I find that Range.FormatConditions.Delete() works fine, but Range.FormatConditions.Add throws a MissingMethodException. Weirdly, it throws this exception before the method in which I make the call is even accesible to me in the debugger, and it took a fair while commeninting out selctive blocks of code to nail it to this one line.

    This has me bamboozled. First of all why it's missing and secondly why I can't even single step to that line but an exception is thrown way way in advance in the method that calls the method that contains that line.

    I most genuinely hope someone can help me here as this is frustrating my comprehension here. I could just run without using FormatConditions I guess although it compromises some of the functionality and visual feedback, but I wish above all to understand what is happening both with the alleged oversight (it seems FormatConditions.Add is not supported in the Microsoft Visual Studio Tools for the Microsoft Office System (version 3.0 Runtime) Service Pack 1 but moreover that the way it interacts with the debugger is bizarre with an excreption thrown in a place seemingly unrelated to the line which contains the code it refers to and which if commented out actually prevents the exception.

    I have all the servcie pack 1s I can fid installed, and to forestall any versioning questions here's what VS2008 reports re: Installed Products:

    Microsoft Visual Studio 2008
    Version 9.0.30729.1 SP
    Microsoft .NET Framework
    Version 3.5 SP1

    Installed Edition: Enterprise

    Microsoft Visual Basic 2008   91904-270-0894061-60687
    Microsoft Visual Basic 2008

    Microsoft Visual C# 2008   91904-270-0894061-60687
    Microsoft Visual C# 2008

    Microsoft Visual C++ 2008   91904-270-0894061-60687
    Microsoft Visual C++ 2008

    Microsoft Visual Studio 2008 Team Explorer   91904-270-0894061-60687
    Microsoft Visual Studio 2008 Team Explorer
    Version 9.0.30729.1

    Microsoft Visual Studio 2008 Tools for Office   91904-270-0894061-60687
    Microsoft Visual Studio 2008 Tools for Office

    Microsoft Visual Studio Team System 2008 Development Edition   91904-270-0894061-60687
    Microsoft Visual Studio Team System 2008 Development Edition

    Portions of International CorrectSpell™ spelling correction system © 1993 by Lernout & Hauspie Speech Products N.V.  All rights reserved.

    The American Heritage® Dictionary of the English Language, Third Edition Copyright © 1992 Houghton Mifflin Company.  Electronic version licensed from Lernout & Hauspie Speech Products N.V.  All rights reserved.


    Microsoft Visual Studio Tools for Applications 2.0   91904-270-0894061-60687
    Microsoft Visual Studio Tools for Applications 2.0

    Microsoft Visual Web Developer 2008   91904-270-0894061-60687
    Microsoft Visual Web Developer 2008

    Crystal Reports    AAJ60-G0MSA4K-68000CF
    Crystal Reports Basic for Visual Studio 2008


    Dotfuscator Professional Edition   
    Dotfuscator for Visual Studio.  Copyright (C) 2002-2008 PreEmptive Solutions LLC

    Hotfix for Microsoft Visual Studio 2008 Team Explorer - ENU (KB946260)   KB946260
    This hotfix is for Microsoft Visual Studio 2008 Team Explorer - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/946260.

    Hotfix for Microsoft Visual Studio 2008 Team Explorer - ENU (KB946458)   KB946458
    This hotfix is for Microsoft Visual Studio 2008 Team Explorer - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/946458.

    Hotfix for Microsoft Visual Studio 2008 Team Explorer - ENU (KB947171)   KB947171
    This hotfix is for Microsoft Visual Studio 2008 Team Explorer - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/947171.

    Hotfix for Microsoft Visual Studio 2008 Team Explorer - ENU (KB947180)   KB947180
    This hotfix is for Microsoft Visual Studio 2008 Team Explorer - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/947180.

    Hotfix for Microsoft Visual Studio 2008 Team Explorer - ENU (KB948816)   KB948816
    This hotfix is for Microsoft Visual Studio 2008 Team Explorer - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/948816.

    Hotfix for Microsoft Visual Studio Team System 2008 Development Edition - ENU (KB944899)   KB944899
    This hotfix is for Microsoft Visual Studio Team System 2008 Development Edition - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/944899.

    Hotfix for Microsoft Visual Studio Team System 2008 Development Edition - ENU (KB945282)   KB945282
    This hotfix is for Microsoft Visual Studio Team System 2008 Development Edition - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/945282.

    Hotfix for Microsoft Visual Studio Team System 2008 Development Edition - ENU (KB946040)   KB946040
    This hotfix is for Microsoft Visual Studio Team System 2008 Development Edition - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/946040.

    Hotfix for Microsoft Visual Studio Team System 2008 Development Edition - ENU (KB946308)   KB946308
    This hotfix is for Microsoft Visual Studio Team System 2008 Development Edition - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/946308.

    Hotfix for Microsoft Visual Studio Team System 2008 Development Edition - ENU (KB946344)   KB946344
    This hotfix is for Microsoft Visual Studio Team System 2008 Development Edition - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/946344.

    Hotfix for Microsoft Visual Studio Team System 2008 Development Edition - ENU (KB946581)   KB946581
    This hotfix is for Microsoft Visual Studio Team System 2008 Development Edition - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/946581.

    Hotfix for Microsoft Visual Studio Team System 2008 Development Edition - ENU (KB947173)   KB947173
    This hotfix is for Microsoft Visual Studio Team System 2008 Development Edition - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/947173.

    Hotfix for Microsoft Visual Studio Team System 2008 Development Edition - ENU (KB947540)   KB947540
    This hotfix is for Microsoft Visual Studio Team System 2008 Development Edition - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/947540.

    Hotfix for Microsoft Visual Studio Team System 2008 Development Edition - ENU (KB947789)   KB947789
    This hotfix is for Microsoft Visual Studio Team System 2008 Development Edition - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/947789.

    Hotfix for Microsoft Visual Studio Team System 2008 Development Edition - ENU (KB948127)   KB948127
    This hotfix is for Microsoft Visual Studio Team System 2008 Development Edition - ENU.
    If you later install a more recent service pack, this hotfix will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/948127.

    Microsoft Visual Studio 2008 Team Explorer - ENU Service Pack 1 (KB945140)   KB945140
    This service pack is for Microsoft Visual Studio 2008 Team Explorer - ENU.
    If you later install a more recent service pack, this service pack will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/945140.

    Microsoft Visual Studio Team System 2008 Development Edition - ENU Service Pack 1 (KB947888)   KB947888
    This service pack is for Microsoft Visual Studio Team System 2008 Development Edition - ENU.
    If you later install a more recent service pack, this service pack will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/947888.

    Microsoft Visual Studio Team System 2008 Development Edition - ENU Service Pack 1 (KB948484)   KB948484
    This service pack is for Microsoft Visual Studio Team System 2008 Development Edition - ENU.
    If you later install a more recent service pack, this service pack will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/948484.

    Microsoft Visual Studio Team System 2008 Development Edition - ENU Service Pack 1 (KB948560)   KB948560
    This service pack is for Microsoft Visual Studio Team System 2008 Development Edition - ENU.
    If you later install a more recent service pack, this service pack will be uninstalled automatically.
    For more information, visit http://support.microsoft.com/kb/948560.

    Visual Studio Package Load Analyzer   1.0
    A utility that analyzes package load failures in Visual Studio.
    Friday, January 16, 2009 3:19 AM

Answers

  • Hallo Bernd

    Mmm. Is it possible that one or more optional parameters were added to the Add method? (From discussions among Excel folks, I seem to recall that the functionality was changed in 2007). If that's the case, then the method signature will have changed. In that case I would have expected a compile error. But possibly this line of code is in a module that's not compiled immediately, but "on-the-fly". That would also explain why you're getting the error at an "odd place", rather than on that line.
     
    I recommend you compare the 2003 and the 2007 methods very carefully, to see what might have been changed.
    Cindy Meister, VSTO/Word MVP
    Friday, January 16, 2009 8:18 AM

All replies

  • Hallo Bernd

    Mmm. Is it possible that one or more optional parameters were added to the Add method? (From discussions among Excel folks, I seem to recall that the functionality was changed in 2007). If that's the case, then the method signature will have changed. In that case I would have expected a compile error. But possibly this line of code is in a module that's not compiled immediately, but "on-the-fly". That would also explain why you're getting the error at an "odd place", rather than on that line.
     
    I recommend you compare the 2003 and the 2007 methods very carefully, to see what might have been changed.
    Cindy Meister, VSTO/Word MVP
    Friday, January 16, 2009 8:18 AM
  • Cindy,

    Thanks for the lead. Very helpful! Indeed I checked on-line documentation with no joy, but then intellisense on the VBA of Excel 200 and 2007 respecitvely and yes this method gained 4 arguments. Doh! The feedback is totally lousy and cryptic!

    So, I added the four optional arguments as Type.Missing in C# and still no joy. This time indeed a compile time error not a run time error. Scratched my head. Slept on it. Then checked my references.

    Indeed the reference to Microsoft.Office.Interop.Excel comes in to flavours, 11.0 and 12.0 when you have all this stuff installed as I described. I was using 11.0 and if I remove that and use 12.0 it works with the 4 extra arguments. Cool. How to interpret bizarre COM error feedback 101 ... ! Sheesh.

    Now I have another problem to consider and may just work aroudn it manually. I would like this to run with whatever Office version is installed! And aside from this one method so far (and one other similar issue) that is currently true and works. So now I want at run time to check the version of Office and call the appropriate method.

    Alas, I can't have references to both 11.0 and 12.0 flavours of Microsoft.Office.Interop.Excel at once, so depending upon which one I have referenced I can compile only the one version. Thatis a run time check seems not to be an option and I need to do a compile time test, so a compile option and two versions of the built product, one for office 2003 and one for office 2007. Which strikes me as weird given that I can have a single build that sensibly works with either office 2003 or 2007 based one what is installed it seems but for this one method (Adding format conditions). It is hard to believe somehow that this is the case. Which tempts me to research a run time test more closely (but i Have only so much time to port this and can probably live with a compile time test for now and posisbly for ever).

    Thanks again!
    Project Manager, Manager of Support, Quality Assurance and Documentation
    Monday, January 19, 2009 10:26 PM
  • Bernd Wechner said:

    Cindy,

    Thanks for the lead. Very helpful! Indeed I checked on-line documentation with no joy, but then intellisense on the VBA of Excel 200 and 2007 respecitvely and yes this method gained 4 arguments. Doh! The feedback is totally lousy and cryptic!

    So, I added the four optional arguments as Type.Missing in C# and still no joy. This time indeed a compile time error not a run time error. Scratched my head. Slept on it. Then checked my references.

    Indeed the reference to Microsoft.Office.Interop.Excel comes in to flavours, 11.0 and 12.0 when you have all this stuff installed as I described. I was using 11.0 and if I remove that and use 12.0 it works with the 4 extra arguments. Cool. How to interpret bizarre COM error feedback 101 ... ! Sheesh.

    Now I have another problem to consider and may just work aroudn it manually. I would like this to run with whatever Office version is installed! And aside from this one method so far (and one other similar issue) that is currently true and works. So now I want at run time to check the version of Office and call the appropriate method.

    Alas, I can't have references to both 11.0 and 12.0 flavours of Microsoft.Office.Interop.Excel at once, so depending upon which one I have referenced I can compile only the one version. Thatis a run time check seems not to be an option and I need to do a compile time test, so a compile option and two versions of the built product, one for office 2003 and one for office 2007. Which strikes me as weird given that I can have a single build that sensibly works with either office 2003 or 2007 based one what is installed it seems but for this one method (Adding format conditions). It is hard to believe somehow that this is the case. Which tempts me to research a run time test more closely (but i Have only so much time to port this and can probably live with a compile time test for now and posisbly for ever).


    Some of this I've addressed in my answer to your reply in a different thread, which I answered before reading this one.

    Given this information, I'd test replacing the application-sensitive methods with Reflection, which supports optional parameters (just leave out those 4 new ones in the args[] parameter of the InvokeMember method). Compile against the Office 2003 PIAs. Then install on a 2007 system. The 2007 PIAs should automatically re-direct from the 2003 references.

    Cindy Meister, VSTO/Word MVP
    Tuesday, January 20, 2009 12:43 PM
  • Well, I got late binding working for me then. The whole project is early bound except these two methods:

    public static FormatCondition AddConditionValue(Range R, XlFormatConditionOperator ConditionOperator, string Formula) 
     return (FormatCondition)R.FormatConditions.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, (object)R.FormatConditions, new object[] { XlFormatConditionType.xlCellValue, ConditionOperator, Formula }); 
     
    public static FormatCondition AddConditionExpression(Range R, string Formula) 
     return (FormatCondition)R.FormatConditions.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, (object)R.FormatConditions, new object[] { XlFormatConditionType.xlExpression, Type.Missing, Formula }); 
     

    which I now use int he project elsewhere. So far so good. The project references the 11.0 version of the Excel Interop library and builds and runs fine so far on machines equipped with either 2003 or 2007. I can't wait to see what other surprises lay ahead of of in the migration.

    Project Manager, Manager of Support, Quality Assurance and Documentation
    Thursday, January 22, 2009 1:36 AM
  • Well, using late binding sorted my problem. Thanks!

    My problem was a bit odd, in that using neither 4 parameters or 8 parameters worked when using V11 or V12 references - it was complaining about an incorrect number of parameters, throwing a TargetParameterCountException.

    Switching to the late binding version worked.

    Thanks!

    Monday, February 09, 2009 1:05 PM
  • Well, I got late binding working for me then. The whole project is early bound except these two methods:

    public static FormatCondition AddConditionValue(Range R, XlFormatConditionOperator ConditionOperator, string Formula) 
     return (FormatCondition)R.FormatConditions.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, (object)R.FormatConditions, new object[] { XlFormatConditionType.xlCellValue, ConditionOperator, Formula }); 
     
    public static FormatCondition AddConditionExpression(Range R, string Formula) 
     return (FormatCondition)R.FormatConditions.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, (object)R.FormatConditions, new object[] { XlFormatConditionType.xlExpression, Type.Missing, Formula }); 
     

    which I now use int he project elsewhere. So far so good. The project references the 11.0 version of the Excel Interop library and builds and runs fine so far on machines equipped with either 2003 or 2007. I can't wait to see what other surprises lay ahead of of in the migration.

    Project Manager, Manager of Support, Quality Assurance and Documentation


    Thanks for this! Stumbled over the same problem and your answer saved me a lot of time.

    But I'd like know why this issue wasn't avoided by providing an additional "Add" method (e.g. FormatConditions.Add2(...)) taking 8 arguments for interop libaries version 12.0 and higher. This would have been too simple, wouldn't it? :-/

    Thursday, November 24, 2011 1:29 PM
  • When I got the Error message:

    Method not found:"Microsoft.Office.Interop.Excel.Formatcondtion
    Microsoft.Office.Interop.Excel.Formatcondtions.Add(Microsoft.Office.Interop.Excel.FormatcondtionType,System.Object,System.Object,System.Object)

    I changed the  early binding to late binding and it worked.

    My code is:           

    xlSheet.Names.Add("Greenbar", "=MOD(ROW(),2)=1")
    Dim dataRng As Excel.Range = DirectCast(xlSheet.Range(xlSheet.Range("A2"), xlSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)), Excel.Range)
    Dim Type As Excel.XlFormatConditionType = Excel.XlFormatConditionType.xlExpression
    Dim Formula1 As Object = "=IF(Greenbar, TRUE)"
               
    Dim returnValue As Object ' instead of Excel.FormatCondition
    'Dim formatConditions1 As Excel.FormatConditions = dataRng.FormatConditions 
     Dim formatConditions1 As Object = dataRng.FormatConditions
     returnValue = formatConditions1.Add(Type, , Formula1)
     returnValue.Interior.ColorIndex = 35

    Wednesday, February 29, 2012 9:45 PM