Archive for September, 2009

Named Ranges – research paper at EuSpRIG 2009

September 11, 2009

Co authored a paper recently, “An Exploratory Analysis of the Impact of Named Ranges on the Debugging Performance of Novice Users” (http://arxiv.org/abs/0908.0935) which presents evidence that might call into question the unsupported contention that Named Ranges are a good idea.

The paper is discussed in an industrial context by the good people at Financial Mechanics:
http://www.fi-mech.com/the+swamp+fox/dispatches/?p=222

Reading Named Ranges in Excel using C#

September 1, 2009

Couldnt find any good examples of reading Named Ranges from a workbook, but eventually figured it out. Here some of the code.


private Excel.Workbook m_workbook;
object missing = Type.Missing;

        public void testNamedRangeFind()
        {
            m_workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
            int i = m_workbook.Names.Count;
            string address = "";
            string sheetName = "";

            if (i != 0)
            {
                foreach (Excel.Name name in m_workbook.Names)
                {
                    string value = name.Value;
                    //Sheet and Cell e.g. =Sheet1!$A$1 or =#REF!#REF! if refers to nothing
                    string linkName = name.Name;
                    //gives the name of the link e.g. sales
                    if (value != "=#REF!#REF!")
                    {
                        address = name.RefersToRange.Cells.get_Address(true, true, Excel.XlReferenceStyle.xlA1, missing, missing);
                        sheetName = name.RefersToRange.Cells.Worksheet.Name;
                    }
                    Debug.WriteLine("" + value + ", " + linkName + " ," + address + ", " + sheetName);
                }
            }

        }

The following references are included:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using System.Diagnostics;
using System.Windows.Forms;
using System.IO;

Excel Find method example using C#/VSTO

September 1, 2009

Found a good example of finding text or formula values in Excel using C#, can be viewed here.

There is a simple change I would make to the example: the first item found is actually the
last instance of the item in the worksheet being searched, so I used the following –

// If Find doesn’t find anything, rgFound will be null
if (rgFound != null)
{
     // Save the address of the first found item –
    // it will be used in a loop terminating condition.
    sFirstFoundAddress = rgFound.get_Address(
                   true, true, Excel.XlReferenceStyle.xlA1, missing, missing);

   // Continue finding subsequent items using FindNext
    rgFound = ws.Cells.FindNext(rgFound);
    string sAddress = rgFound.get_Address(
                    true, true, Excel.XlReferenceStyle.xlA1, missing, missing);

    // Start a loop that calls FindNext until
   // the first found cell is found again
   while (!sAddress.Equals(sFirstFoundAddress))
   {

         //do something with each found item

   }

//Finally, get the first found cell – which will be
//the most bottom right found cell, and do something with it
r1 = ws.get_Range(sFirstFoundAddress, missing) as Excel.Range;