none
How to find if Excel cell name is referred in formulas in other cells

    Question

  • Hi,

    We have a Excel Addins for our application. One of the functionality is, we are adding icons through code in various cells and give a name to cell (like tmp_GUID) so next time when document is opened, we can put the icon back to the proper cell. When excel cell is given a name and if it is referred in formulas(e.g. Sum formula) in other cells then formulas are using new cell name. e.g. instead of A1 formulas are using tmp_GUID. now we support icon deletion if not required. same time we do cleanup and remove the new cell name. so whereever new cell name is referred in formulas are showing error #NAME? because formulas are referring a cell name which is deleted.

    Before delete, I want to find out if this new cell name is referred in current excel anywhere in the formulas? Is there any good approach?

    Thanks in advance,

    Chirag

    Thursday, May 31, 2012 1:33 PM

Answers

  • Hi ChiragP,

    Thanks for posting in the MSDN Forum.

    In my opinion, that you need to iterator all of the cells to see whether it have formulas and then your need to know the nameRange in your work book to see whether it in the formulas' range. The key is know how to access the namedRange. I provide a snippet which use to access the NameRange in add-in project.

    I hope it can help you.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Ribbon;
    using Microsoft.Office.Tools.Excel;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Windows.Forms;
    
    namespace ExcelAddIn8
    {
        public partial class Ribbon1
        {
            private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
            {
    
            }
    
            private void button1_Click(object sender, RibbonControlEventArgs e)
            {
                Worksheet wst = (Worksheet)Globals.Factory
                    .GetVstoObject(Globals.ThisAddIn.Application
                    .ActiveWorkbook.Worksheets[1]);
                MessageBox.Show(wst.Controls.Count.ToString());
                foreach (var o in wst.Controls)
                {
                    if (o is NamedRange)
                    {
                        NamedRange nr = (NamedRange)o;
                        MessageBox.Show(nr.Address[true, true, 
                            Excel.XlReferenceStyle.xlA1]);
                    }
                }
            }
        }
    }

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by ChiragP Monday, June 04, 2012 6:20 PM
    • Unmarked as answer by ChiragP Monday, June 04, 2012 6:20 PM
    • Marked as answer by ChiragP Wednesday, June 06, 2012 6:34 PM
    Monday, June 04, 2012 7:50 AM

All replies

  • Hi ChiragP,

    Thanks for posting in the MSDN Forum.

    In my opinion, that you need to iterator all of the cells to see whether it have formulas and then your need to know the nameRange in your work book to see whether it in the formulas' range. The key is know how to access the namedRange. I provide a snippet which use to access the NameRange in add-in project.

    I hope it can help you.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Ribbon;
    using Microsoft.Office.Tools.Excel;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Windows.Forms;
    
    namespace ExcelAddIn8
    {
        public partial class Ribbon1
        {
            private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
            {
    
            }
    
            private void button1_Click(object sender, RibbonControlEventArgs e)
            {
                Worksheet wst = (Worksheet)Globals.Factory
                    .GetVstoObject(Globals.ThisAddIn.Application
                    .ActiveWorkbook.Worksheets[1]);
                MessageBox.Show(wst.Controls.Count.ToString());
                foreach (var o in wst.Controls)
                {
                    if (o is NamedRange)
                    {
                        NamedRange nr = (NamedRange)o;
                        MessageBox.Show(nr.Address[true, true, 
                            Excel.XlReferenceStyle.xlA1]);
                    }
                }
            }
        }
    }

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by ChiragP Monday, June 04, 2012 6:20 PM
    • Unmarked as answer by ChiragP Monday, June 04, 2012 6:20 PM
    • Marked as answer by ChiragP Wednesday, June 06, 2012 6:34 PM
    Monday, June 04, 2012 7:50 AM
  • Hi ChiragP,

    Any update?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, June 05, 2012 6:15 AM
  • Hi Tom, thanks for the help.
    Wednesday, June 06, 2012 6:38 PM