Freigeben über

How to code a .NET RefEdit Control

  • Artikel

One of the controls frequently used by Excel is the reference edit box. This control is used to gather a range address from the user. The user specifies the range either by typing in its address or by using the mouse and clicking on the actual cells they want to use.

Here's a screenshot of the inbuilt reference edit box:

Whenever the user is required to specify a range, using the reference edit box is my preferred way to gather the address from the user because it enables (what I call) visual selection of ranges. Range addresses can be tedious to work with whereas visually clicking on the cells you want is a much easier and more efficient way of working.  

Custom solutions can also use visual range selection because Excel ships a control called the RefEdit control. This control is implemented in REFEDIT.DLL located in the Office folder (on my laptop this is: C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE12) and mimics the inbuilt reference edit control.

Well that's the good news ... now the bad: the REFEDIT.DLL cannot be used from managed code. In fact, it can only be used from VBA. Years ago, that wasn't a problem because most solutions were VBA based. However, these days we live in a .NET world and more and more solutions are being written in managed code. 

So, what are the options if we want to use the RefEdit control from managed code?

The way I see it, there are three options. Firstly, we could write an XLA containing a VBA userform on which we place the RefEdit control. We would then have to link the managed code and the VBA userform via macros somehow. The downsides of this option are that we  create a mixed code solution i.e., we have some VBA and some managed code. This may or may not be an issue for us.

The second option is to buy a commercial RefEdit control that can be used by .NET WinForms. Actually, I don't know if such a thing exists but let's assume that it does.

The third option is to write a managed code RefEdit control from scratch. This option is probably the most work and might be quite hard. However, I decided to give it a go...

In this post, I present a managed code RefEdit control written in C#. I have implemented the control as a .NET UserControl which can be added into the Visual Studio toolbox and dragged and dropped onto any WinForm just like any other .NET control.

The control I present does not behave exactly like Excel's reference edit boxes but I think it behaves close enough that most people wouldn't notice the difference.

Here's a screenshot of the control in action:

This example uses the managed code addin I presented in a previous post (here) but the control can be used on any WinForm.

You are free to download the control and use it in your solutions. It is totally free (in all senses of the word) and you can take the code and modify it without restriction. All I ask is that you let me know if you improve it or find any bugs so that I can incorporate those improvements for the benefit of the community.

Note: The RefEdit control also uses the ExcelUtility class I published previously in this post.

How to code a RefEdit Control in C#

Let's first think a bit about the behaviour of the RefEdit control. It does some fancy things in very subtle ways. It's one of those wonderful UI concepts that we use everyday without even thinking about its complexities.

When we click on the button in the RefEdit control, the window on which the RefEdit control resides is minimised and all controls on the window are not displayed. Clicking on the button again restores the window to its original state.

 

The other feature to note is how the visual selection works. When the text box in the RefEdit control has focus, the address of the selected cells in Excel is displayed. Even if multiple, discrete ranges are selected the addresses are displayed. The user can even change sheets and select ranges on different sheets. This all takes place while the window containing the RefEdit control is still visible but is in its minimised state and all the time the addresses of all the selected ranges are displayed in the RefEdit control.

These two features are the killer features of the RefEdit control. If we can code these features then we are well on our way to our goal of having a managed code RefEdit control.

So, let's take a look at how we do this.

First, we need to have a function that can minimise its parent window and hide all controls on the window except the RefEdit control. Also, this function needs to remember the window's previous state and restore the window to that state once the user has selected their range.

protected void ResizeParent() {

  Form parentForm = (Form)Parent;

  foreach (Control c in parentForm.Controls)

    c.Visible = _state.ParentMinimised;

  Visible = true;

  if (!_state.ParentMinimised) {

      _state.ParentClientSize = parentForm.ClientSize;

      _state.PrevX = Left;

      _state.PrevY = Top;

      _state.Anchor = Anchor;

      Anchor = AnchorStyles.None;

      parentForm.ClientSize = new Size(Width, Height);

      Left = 0;

      Top = 0;

      _state.ParentPrevBorder = parentForm.FormBorderStyle;

      parentForm.FormBorderStyle = FormBorderStyle.FixedDialog;

      _state.ParentControlBox = parentForm.ControlBox;

      parentForm.ControlBox = false;

    }

   else {

        parentForm.ClientSize = _state.ParentClientSize;

        Anchor = _state.Anchor;

        Left = _state.PrevX;

        Top = _state.PrevY;

        parentForm.FormBorderStyle = _state.ParentPrevBorder;

        parentForm.ControlBox = _state.ParentControlBox;

    }

}

Notice that the first line of code casts the control's Parent to a Form object. This is our first limitation. If this cast fails then our control won't work. Why would the cast fail? It would fail if the parent is not a Form. This means that our RefEdit control's direct parent must be a Form. In other words, we can't place our RefEdit control within another control (such as a group box); it must be placed directly on a Form.

The _state object is an instance of the following struct:

struct RefEditState {

        public Size ParentClientSize;

        public bool ParentMinimised;

        public int PrevX;

        public int PrevY;

        public FormBorderStyle ParentPrevBorder;

        public bool ParentControlBox;

        public AnchorStyles Anchor;

        public Excel.Worksheet Sheet;

        public bool UseSheetAddress;

    }

We use this struct to store state information while we are minimising the parent window. Any property of the parent window that we change during the minimising we first save in the _state object. If the window is to be minimised (this happens when the user clicks on the button in the RefEdit control), all controls except the RefEdit control are hidden and the window is resized so that it is fits snuggly around the RefEdit control. That is, the window is made to look like this:

When the window is to be restored (when the user clicks on the RefEdit button again) we re-apply the settings stored in _state.

Updating the RefEdit Control with the Selected Range

The RefEdit control now resizes and restores its parent window whenever the user clicks on the button. Now we need to display the address of the ranges the user selects in Excel, while the user is selecting them.

So, if the user clicks $A$1 the RefEdit should display $A$1 in its text box. If the user selects the range $A$1:$B$3 the RefEdit should display $A$1:$B$3. Also, we want to support selecting multiple ranges. So, if the user selects $A$1 and $F$3 the RefEdit control should display $A$1,$F$3.  

We detect which ranges the user is selecting by sinking the SheetSelectionChange event on the Excel.Application object.

private void SinkEvents() {

  _con.ExcelApp.SheetSelectionChange += new Excel.AppEvents_SheetSelectionChangeEventHandler(ExcelEvent_SheetSelectionChange);

  _con.ExcelApp.SheetActivate += new Excel.AppEvents_SheetActivateEventHandler(ExcelEvent_SheetActivate);

}

However, we don't want to always sink these events. We only want to sink them while the RefEdit is active. By active I mean the user has either clicked inside the RefEdit's text box or has clicked the RefEdit's button. In other words, we only sink these events while the user is using the RefEdit control.

We trap the Enter event of the RefEdit's address textbox and sink the Excel events whenever this event fires. We also save the active sheet in the _state object. 

void _addressTxt_Enter(object sender, EventArgs e) {

  SinkEvents();

  if (_state.Sheet == null)

    _state.Sheet = ExcelUtility.ApplicationInvoke<Excel.Worksheet>(_con.ExcelApp, "ActiveSheet");

} 

We detach from the events when the stops using the RefEdit control.

private void UnsinkEvents() {

  _con.ExcelApp.SheetSelectionChange -= ExcelEvent_SheetSelectionChange;

  _con.ExcelApp.SheetActivate -= ExcelEvent_SheetActivate;

}

private void _addressTxt_Leave(object sender, EventArgs e) {

UnsinkEvents();

}

Notice that we also sink the SheetActivate event. We do this because the RefEdit control allows users to select ranges on any sheet; not just the sheet that was active at the time the RefEdit was displayed. Therefore, if the user selects a range on a different sheet we need to include the sheet name in the address of that range. For example, if Sheet1 is active when the RefEdit control is displayed but the user selects $A$1 on Sheet2 then the RefEdit control needs to display 'Sheet2'!$A$1 ... not $A$1.

One way of solving this problem is to make the RefEdit control always include sheet names in range addresses but this makes for long, messy addresses and Excel's reference edit boxes don't behave like that ... so neither should our RefEdit control.

Thus, we only want to include sheet names in the addresses if we have to. If we don't have to then we should omit them.

And that's why we sink the SheetActivate event.

Here's the code that runs when the SheetActivate event and the SheetSelectionChange event fire:

void ExcelEvent_SheetSelectionChange(object Sh, Excel.Range Target) {

  string address = "";

  try {

    Excel.Worksheet sheet = (Excel.Worksheet)Sh;

    if (_state.UseSheetAddress) {

      foreach (Object obj in Target.Areas) {

        if (address.Length > 0)

          address += ",";

        Excel.Range rng = (Excel.Range)obj;

        address += "'" + rng.Worksheet.Name + "'!";

        address += ExcelUtility.RangeInvoke<string>(rng, "Address", true, true, Excel.XlReferenceStyle.xlA1);

        }

      }

    else {

        address += ExcelUtility.RangeInvoke<string>(Target, "Address", true, true, Excel.XlReferenceStyle.xlA1);

    }

    _addressTxt.Text = address;

  }

  catch (Exception) {

  }

} 

void ExcelEvent_SheetActivate(object Sh) {

Excel.Worksheet sheet = (Excel.Worksheet)Sh;

if (!_state.UseSheetAddress)

_state.UseSheetAddress = sheet.Name != _state.Sheet.Name;

}

As the user selects ranges Excel fires the SheetSelectionChange event. If the range that was selected is not on the sheet that was active when the RefEdit control was displayed we append the sheet name to the address, otherwise we don't.

Using the RefEdit Control on a WinForm

The RefEdit control is a normal .NET UserControl so it appears in the Visual Studio toolbox like every other UserControl. You can drag and drop it onto a WinForm as required.

However, when we display a window which contains a RefEdit control we have to do so in a certain, non-standard way in order for the RefEdit control to function properly.

In order for the user to be able to select ranges in Excel while the window containing a RefEdit control is visible, the window must be non-modal. To display a non-modal window we normally invoke its Show()method. We still do that, but we have to invoke the version of Show()that takes a native window handle as a parameter. This handle is the handle of the native window which is the parent window of the non-modal window. When we display a non-modal window in Excel we need to make sure that we tell the non-modal window that Excel is its parent window. This ensures that the window behaves properly (for example, when the user alt-tabs to another application).  

The overloaded Show() method we use actually takes an IWin32Window. IWin32Window interface is defined in the .NET FCL and is used to wrap a native HWND. We have to create a class that implements the IWin32Window interface and supply an instance of that class to the Show() method of the window containing a RefEdit control.

The following code defines a class, NativeWindowWrapper, that implements the IWin32Window interface. 

class NativeWindowWrapper : IWin32Window {

  IntPtr _handle;

  public NativeWindowWrapper(int Hwnd) {

    _handle = new IntPtr(Hwnd);

  }

  public IntPtr Handle {

    get { return _handle; }

  }

}

We use this class when we display a window containing a RefEdit control. We pass an instance of this class into the Show method of the window containing the RefEdit control. 

In the following example we are using the ColumnAnalysisDisplayWindow discussed in a previous blog post ... but the idea is obvious: replace ColumnAnalysisDisplayWindow with your own window.

protected void DisplayStatsWindow() {

  ColumnAnalysisDisplayWindow window = new ColumnAnalysisDisplayWindow();

  window.Show(new NativeWindowWrapper(_connection.ExcelApp.Hwnd));

}

Summary

In this post we've talked about how to code a RefEdit .NET control that mimics the behaviour of Excel's inbuilt reference edit box. I'm not claiming this is absolutely the best implementation we could produce, but I think it's not bad and is fairly easy to do.

There are (no doubt) bugs in the code and situations where the control doesn't work that well. As I encounter and fix these prolbems I'll post updated versions of the control. If you encounter a problem, feel free to drop me a line and tell me about it.

The following points summarise this post:

  • The RefEdit control mimcs Excel's reference edit controls by enabling the user to visually select ranges;
    • The control does not behave exactly like Excel's reference edit boxes but is fairly close; 
  • The control is implemented as a .NET UserControl;
  • The control requires:
    • A reference to the Excel 2007 PIA;
    • The ExcelUtility class defined in my ExcelExtensions COM addin (see below);
  • Windows that contain an instance of the RefEdit control have to be displayed non-modally;
    • And Excel's HWND must be specified as the native owner of the containing window;

Downloading the source code

I have updated my ExcelExtensions addin to include the RefEdit control.

All the source code is included in the ExcelExtensions download found here.

There are two downloads on the Downloads tab. These are:

  • ExcelExtensionsSrc.zip
  • ExcelExtensions.msi

ExcelExtensionsSrc.zip contains the complete source code and ExcelExtensions.msi is the set up program.

Comments

  • Anonymous
    June 24, 2008
    PingBack from http://exceltutoriales.com/?p=985

  • Anonymous
    June 25, 2008
    Thanks.  Looking forward to giving it a go. If the control is placed inside some other non-form container do you see any issue, in principal, of following the parent chain up to to the form and then, perhaps, moving the refedit control into to the form when the selection button is pressed?  It could be moved back to the original control when the user has finished selecting.

  • Anonymous
    June 25, 2008
    The comment has been removed

  • Anonymous
    June 28, 2008
    Note that winforms has a handy .ParentForm property which can probably be used to resolve one of the limitations you mentioned. (Of course a simple loop to walk up the tree until you find the toplevel parent wouldn't be that hard either)

  • Anonymous
    July 09, 2008
    The Refedit Textbox is not updated when the activecell is clicked. Scenario 1: Click a cell (just one cell) in the activeworksheet, clear the Refedit Textbox, click the activecell again, the Refedit Textbox remains empty. Scenario 2: Activate a second worksheet, click the activecell, the Refedit Texbox is not updated.

  • Anonymous
    July 10, 2008
    A few people have pointed out the bug with the ActiveCell. Basically, the RefEdit control only responds to changes in selection which means that if you click on the ActiveCell the RefEdit does nothing. I'll see if I can figure out a solution... Thanks for letting me know

  • Anonymous
    September 21, 2008
    1 . Can i use this in MS excel 2003 2 . Can you provide the Ref edit implemataion alone? 3 .  Will that refedit implementation  works in Excel 2003

  • Anonymous
    September 22, 2008
    Dileep, you should be able to use the control with Excel 2003 but you will have to change the PIA references and there may be a few other changes that follow on. I am planning on providing the RefEdit control as a standalone control.

  • Anonymous
    September 23, 2008
    Hi Gabhan Berry, I have some more doubts to ask also i need your suggestion. I will tell you my requirement , that is i have a C# DLL i am going to implement this Ref edit in my form  and i need to show this form while executing one function in the XLA.So that i have exported one function. Then my concern is How to interact this C# form ( We are not using add-in ) to Excel and How to implement the event handling. Pls give your comments .... and how to support in office 2003

  • Anonymous
    September 24, 2008
    Hai Gabhan Berry, How can we make compatible this project with other lower office versions?. Is that Microsoft.Office.Interop.Excel is necessary to interact with excel?. Do you have any other method which is compatible to lower office version?

  • Anonymous
    April 15, 2010
    Gabhan, >>I am planning on providing the RefEdit control as a standalone >>control. Have You been able to do any work with it? Kind regards, Dennis

  • Anonymous
    March 01, 2012
    I have a question:If the form is a modal form, how to hide it when click refedit button?