In the Flow

During the development of FlowBreeze 3.0, I wasted a lot of time mucking around with different approaches to emulating the RefEdit control from C#. If you’re not familiar with it, the RefEdit control looks something like this on a typical Excel form:

(btw, this is actually the after image. the range address would not be present until after the step in the next image.)

Clicking it, you can then select the range with your mouse, as such:

Looking around the net, I found two viable options that would work in C#. One was Gabhan Berry’s article How to code a .NET RefEdit Control. This is a nicely done control, but unfortunately it had two major drawbacks. First, it relied upon a DLL he had created to make working with Excel easier. Second, it was written from the perspective of someone working with Excel externally, whereas I was building a COM add-in. I try to avoid distributing third party libraries where possible – especially if I have to read through the license terms and make sure that I am allowed to distribute the library with a commercial application. The second issue isn’t a show stopper, but it was going to take a decent amount of code re-writing to get it to work with my application.

The other control I came across was RefEdit Emulation for .NET on Code Project. Like Gabhan’s control, this provides a range selector with a small footprint. The major down side, though, was that it only works for non-modal parent forms. This control may be usable on a modal form with a little more work, but by this time I had spent way more time trying to create a range selector then I had originally planned.

Application.InputBox to the Rescue

The old standby alternative to the RefEdit control has been the Application.InputBox() method. In this case, Application refers to the Excel application reference. Instead of the RefEdit range selector with the small footprint, you get a larger forms that looks like this:

The other drawback to the InputBox is that the parent control doesn’t automatically minimize. If you hide the parent before calling InputBox(), the range selector will not become the topmost form and receive focus. In order to activate the range selector, the user would have to notice the flashing menu item on their task bar. The same problem exists if you try to set the parent form’s window state to minimized.

In order to deal with this, I created a little utility function to resize the parent form to the minimum window size, and then “hide” the parent at the base of the Excel window, looking something like this:

Here is the sample code, including a calling button click event, the utility functions to return either the Excel.Range or the range address, as well as an all-purpose method to release COM objects. Due to the general suckiness of this blog format, you will probably need to copy and paste this into an editor to see the full width of the code.

#region ---- Select range ----------------------------------------

private void btnSelectRange_Click(object sender, EventArgs e)
{
    string prompt = "Select the range";
    string title = "Select Range";
    try
    {
        string address = Utilities.PromptForRangeAddress(this, title, prompt);
        if (!String.IsNullOrEmpty(address))
        {
            txtBaseShapeCell.Text = address;
        }
    }
    catch
    {
        MessageBox.Show("An error occurred when selecting the range.", "Range Error");
    }
}

#endregion

#region ---- static utility methods ----------------------------------------
// Requires:
// using System.Runtime.InteropServices;
// using System.Windows.Forms;

DllImport("user32.dll")]
[return: MarshalAs(UnmanagedType.Bool)]
private static extern bool GetWindowRect(IntPtr hWnd, out RECT lpRect);

[StructLayout(LayoutKind.Sequential)]
private struct RECT
{
    public int Left;
    public int Top;
    public int Right;
    public int Bottom;
}

public static string PromptForRangeAddress(Form form, string title, string prompt)
{
    Size windowSize = form.Size;
    form.Size = SystemInformation.MinimumWindowSize;
    Point location = form.Location;
    SetFormPositionForInputBox(form);

    string rangeAddress = string.Empty;
    Excel.Range range = null;
    try
    {
        range = XL.App.InputBox(prompt, title, Type.Missing, Type.Missing,
                                Type.Missing, Type.Missing, Type.Missing, 8 ) as Excel.Range;
        if (range != null)
            rangeAddress = range.get_AddressLocal(Office.MsoTriState.msoFalse,
                                                  Office.MsoTriState.msoFalse,
                                                  Excel.XlReferenceStyle.xlA1,
                                                  Office.MsoTriState.msoFalse,
                                                  Type.Missing);
    }
    catch
    {
        throw new Exception("An error occured when selecting the range.");
    }
    finally
    {
        form.Location = location;
        form.Size = windowSize;
        MRCO(range);
    }
    return rangeAddress;
}

public static Excel.Range PromptForRange(Form form, string title, string prompt)
{
    Size windowSize = form.Size;
    Point location = form.Location;
    form.Size = SystemInformation.MinimumWindowSize;
    SetFormPositionForInputBox(form);

    Excel.Range range = null;
    try
    {
        range = XL.App.InputBox(prompt, title, Type.Missing, Type.Missing,
                                Type.Missing, Type.Missing, Type.Missing, 8  ) as Excel.Range;
    }
    catch
    {
        throw new Exception("An error occured when selecting the range.");
    }
    finally
    {
        form.Location = location;
        form.Size = windowSize;
    }

    return range;
}

public static void SetFormPositionForInputBox(Form form)
{
    int x = form.Location.X;
    bool isSet = false;
    try
    {
        System.Diagnostics.Process[] processes = System.Diagnostics.Process.GetProcessesByName("Excel");
        if (processes != null && processes.Length > 0 && processes[0] != null)
        {
            RECT rect;
            IntPtr ptrXL = processes[0].MainWindowHandle;
            if (!ptrXL.Equals(IntPtr.Zero) && GetWindowRect(ptrXL, out rect))
            {
                form.Location = new Point(x, rect.Bottom - SystemInformation.MinimumWindowSize.Height);
                isSet = true;
            }
        }
    }
    finally
    {
        if (!isSet)
        {
            form.Location = new Point(x, 0);
        }
    }
}

public static void MRCO(object obj)
{
    if (obj == null) { return; }
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
    }
    catch
    {
        // ignore, cf: http://support.microsoft.com/default.aspx/kb/317109
    }
    finally
    {
        obj = null;
    }
}

#endregion