none
Determine if a range is has a name RRS feed

  • Question

  • I am working with VSTO to design an Excel Add-In for Excel 2007.  I wish to do the following:

    Given a range object, I would like to determine whether this object has a name.  I can do this via the Name property of the Range object.  The only problem is that if the range does not have a name, trying to access this property throws a COM Exception.  So to get round this I surround the call to Name by try{} catch{}.  But this is significantly impacting on the performance of the program.

    In a nutshell - is there a way to determine if a range is in fact a named range without having to resort to any try{}catch{} performance busting code?

    Friday, June 4, 2010 10:33 AM

Answers

  • It's a little late in the thread, but I wasn't sure if you are just trying to find whether a Range has a name associated with it, or whether a Range is defined as having a Microsoft.Office.Tools.Excel.NamedRange object associated with it.

    This is a bit of code to search the Worksheet and workbook names, and checks if the current range is a namedRange, and if the current range is a Tools.NamedRange Object.

    Regards

        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
          Application.WorkbookActivate += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookActivateEventHandler(Application_WorkbookActivate);      
        }
    
        void Application_WorkbookActivate(Microsoft.Office.Interop.Excel.Workbook Wb)
        {
          ET.NamedRange nr;
          ET.Worksheet ws;
          Excel.Range rng;
          Application.ActiveWorkbook.SheetSelectionChange +=new Microsoft.Office.Interop.Excel.WorkbookEvents_SheetSelectionChangeEventHandler(ActiveWorkbook_SheetSelectionChange);
          ws = (Application.ActiveSheet as Excel.Worksheet).GetVstoObject();
          rng = (Application.ActiveSheet as Excel.Worksheet).get_Range("b2","c3");
          nr = ws.Controls.AddNamedRange(rng, "firstRange");      
          
        }
    
        void ActiveWorkbook_SheetSelectionChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
        {
          
          ET.Worksheet ws;
          ET.NamedRange nr;
          ET.ControlCollection cc;
          Excel.Range arng;
          Excel.Range brng;
          ws = (Sh as Excel.Worksheet).GetVstoObject();
          foreach (var item in Application.ActiveWorkbook.Names)
          {
            Debug.Print((item as Excel.Name).RefersToLocal as string);
            if (ws.Controls.Contains((item as Excel.Name).Name)) {
              Debug.Print("  --- is a NamedRangeObject");
            }
            arng = (item as Excel.Name).RefersToRange;
            if ((arng.Parent == Target.Parent ))
            {
              brng = Application.Intersect(arng, Target, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
    
              if (!(brng == null))
              {
                if (brng.get_Address(true, true, Excel.XlReferenceStyle.xlA1, false, false) == arng.get_Address(true, true, Excel.XlReferenceStyle.xlA1, false, false))
                  Debug.Print(" --- The Target is a named range");
              }
            }
          }
          
          Debug.Print("-------");
        }
    Thursday, June 10, 2010 11:23 AM

All replies

  • Hello,

    It is hard to detect if the property is accessable, I'm still trying to find a way to detect. However, we could have a work around below.

    We have to go through the Controls collection in Office.Tools.Excel.Worksheet, to see if the named range is created, please check following code:

        Microsoft.Office.Tools.Excel.Worksheet ws = (Application.ActiveSheet as Excel.Worksheet).GetVstoObject() as Worksheet;
               foreach (object ctrl in ws.Controls)
               {
                   if ((ctrl as NamedRange) != null)
                       MessageBox.Show((ctrl as NamedRange).Name.ToString());
               }

    Hope this helps.

    Tim Li

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com



               


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, June 7, 2010 12:31 PM
  • Hi, 

    I am writing to check the status of the issue on your side. Could you please let me know if the suggestion works for you or not? If you have any questions or concerns, please feel free to let me know. I will be more than happy to be of assistance.

     

    Tim Li

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, June 9, 2010 6:16 AM
  • Hi Tim, 

    Thanks for the workaround.  I am using VSTO to create the Add-In.  I seem to only be able to get access to Micrsoft.Office.Interop.Excel objects - when I try to cast Microsoft.Office.Interop.Excel.Worksheet to a Microsoft.Office.Tools.ExcelWorksheet I get an exception (I guess because the Add-In only really deals with Proxy objects).

    Is there any way to access the Controls property using Interop?

    Thursday, June 10, 2010 9:24 AM
  • Yes we could not directly cast bewteen this two name space. But the GetVstoObject method would do the tirck. Please check this document:
    http://blogs.msdn.com/b/eric_carter/archive/2008/07/24/using-vsto-document-features-in-application-level-add-ins.aspx

    And to your last question, I was trying to find the same collection, but so far, I could not get it.

    thanks.

    Tim Li

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, June 10, 2010 9:35 AM
  • It's a little late in the thread, but I wasn't sure if you are just trying to find whether a Range has a name associated with it, or whether a Range is defined as having a Microsoft.Office.Tools.Excel.NamedRange object associated with it.

    This is a bit of code to search the Worksheet and workbook names, and checks if the current range is a namedRange, and if the current range is a Tools.NamedRange Object.

    Regards

        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
          Application.WorkbookActivate += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookActivateEventHandler(Application_WorkbookActivate);      
        }
    
        void Application_WorkbookActivate(Microsoft.Office.Interop.Excel.Workbook Wb)
        {
          ET.NamedRange nr;
          ET.Worksheet ws;
          Excel.Range rng;
          Application.ActiveWorkbook.SheetSelectionChange +=new Microsoft.Office.Interop.Excel.WorkbookEvents_SheetSelectionChangeEventHandler(ActiveWorkbook_SheetSelectionChange);
          ws = (Application.ActiveSheet as Excel.Worksheet).GetVstoObject();
          rng = (Application.ActiveSheet as Excel.Worksheet).get_Range("b2","c3");
          nr = ws.Controls.AddNamedRange(rng, "firstRange");      
          
        }
    
        void ActiveWorkbook_SheetSelectionChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
        {
          
          ET.Worksheet ws;
          ET.NamedRange nr;
          ET.ControlCollection cc;
          Excel.Range arng;
          Excel.Range brng;
          ws = (Sh as Excel.Worksheet).GetVstoObject();
          foreach (var item in Application.ActiveWorkbook.Names)
          {
            Debug.Print((item as Excel.Name).RefersToLocal as string);
            if (ws.Controls.Contains((item as Excel.Name).Name)) {
              Debug.Print("  --- is a NamedRangeObject");
            }
            arng = (item as Excel.Name).RefersToRange;
            if ((arng.Parent == Target.Parent ))
            {
              brng = Application.Intersect(arng, Target, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
    
              if (!(brng == null))
              {
                if (brng.get_Address(true, true, Excel.XlReferenceStyle.xlA1, false, false) == arng.get_Address(true, true, Excel.XlReferenceStyle.xlA1, false, false))
                  Debug.Print(" --- The Target is a named range");
              }
            }
          }
          
          Debug.Print("-------");
        }
    Thursday, June 10, 2010 11:23 AM
  • Hi Guys, 

    Thanks to both of you for your answers.

    Incre-d: This was the approach I took to start with, but found that (given my workbook had several hundred named ranges) it was faster to catch any exception thrown from range.name than to thumb through all of the named ranges every single time I want to check if a specific range is a named range.

    Tim: I'm finding that the named ranges do not appear as part of the worksheet.Controls collection (could this be because the ranges have the scope of the entire workbook?).  I can access the workbook.Names collection, but then I'm back to iterating through Excel.Interop.Name's and using the same approach that Incre-d suggested.  It's faster to catch and throw exceptions than to iterate through the hundreds of named ranges just to determine if the specified range has a name...

    Friday, June 11, 2010 3:51 PM
  • Hi,

    After some research, acutally Application.Names collection would do the job. 

    For more information please refer to this link:
    http://msdn.microsoft.com/en-us/library/bb223967(office.12).aspx

    Any Named Range will store its name in this collection, and we could go through the collection and check the address of each item to see if the address matches the given object.


    Hope this helps.

    Thanks.

    Tim Li

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by spvarapu Monday, January 16, 2012 5:17 AM
    Monday, June 14, 2010 8:54 AM