VS.NET Excel Addin Refedit Control

• Download source code - 33.78 KB
• Download control - 15.4 KB

Introduction

This is a fully functional .NET Refedit control for use in VS.NET Excel add-ins.

Background

Having developed Excel add-in solutions for many years, the lack of a usable Refedit control on forms outside the Excel environment has been frustrating. The Refedit control included in this example provides all the functionality of the VBA Refedit solution, plus some valuable extras.

You can simply drop the control onto a VS.NET Windows form that is being used in Excel, and range selection is a simple process.

Using the Code

This example does not include an installation or compiled add-in. To test out the control, load the solution in VS.NET 2008 and click run (F5). When the add-in loads, the example form will automatically appear. You can test the control by changing the various properties on the form (specific properties are in the "refedit" category of the properties). Adding the control requires an additional step (apart from dragging it onto the form). Once a control has been added, it is necessary to assign a reference to the Excel Application to it. When instantiating the form, you need to pass Excel.Application to the constructor. The form class should look like this:

using System.Windows.Forms;

namespace ExampleUserForm
{
    public partial class Form1 : Form
    {
        private readonly Microsoft.Office.Interop.Excel.Application _excel;
        public Form1(Microsoft.Office.Interop.Excel.Application excel)
        {
            _excel = excel;
            InitializeComponent();
            refedit1._Excel = _excel;
        }
    }
}

The constructor then assigns the reference to the Excel.Application to the control(s).

Events

Additional events are included so that the program can interact with Excel.

When the Excel cell changes in the control, the CellChanged event can be called.

/// <summary>
/// The event to raise when the cell reference changes
/// </summary>
[Description("Occurs when the cell reference changes")]
public event refeditCellChangeEventHandler CellChanged;

An example of the usage of CellChanged follows:

private void refedit1_CellChanged(object sender, EventArgs e)
{
    textBox1.Text = refedit.Text;
    textBox2.Text = Excel.get_Range(refedit.Text).Value2;
}

When the User Form restores (after it has been collapsed by the control), the Restore event can be called.

/// <summary>
/// The event to raise when the refedit control restores
/// </summary>
public event refeditRestoreEventHandler Restore;

An example of the usage of Restore follows:

private void refedit1_Restore(object sender, System.EventArgs e)
{
    Button1.Focus();
}

Points of Interest

The Refedit control is still a work in progress, and the next step is to see if there is a way to automatically assign the Excel application (though I doubt there is...).

History

• 24^th March, 2009: Initial version
• 24^th March, 2009: Added the control