RefEdit Emulation for .NET

• DLL - Download RefEditControl.zip - 9 KB
• Source Code - Download RefEditControl_SourceControl.zip - 87.93 KB
• Sample - Download refeditsample.zip - 141.4 KB

Introduction  

When I originally posted this article it was simple Proof-Of-Concept article showing that a refedit control can be build to communicate between Excel and your .NET app. I spent some time this past week and developed a reusable UserControl.

To keep things fair, I want to say that not all the ideas in the control are mine. I found some useful tidbits here (MSDN Blog). Although this works, I could not get it to work in Excel 2003. Just changing the PIA references was not enough. It is tied to Office 2007 thru the RibbionUI.

After consolidating my work and using some of the techniques from the MSDN blog. I developed this UserControl that works with Excel 2003.

Background

I started a project last year that required my .NET application to communicate with Excel. The project required that the user be able to select columns from a spreadsheet to be uploaded to SQL.

As everyone who has done Excel VBA programming, the refedit control is great! it allows users to select ranges from a spreadsheet thru the use of the mouse or keyboard.

With the introduction of .NET and VSTO, Microsoft did not release a refedit control. This meant that porting your beloved VBA code, that used a refedit control, to .NET was not going to happen so smoothly.

Like everyone else I hopped right onto Google's search engine and looked up RefEdit Controls for .NET. Guess what? Never found one and all the forums I visited said that Microsoft would not provide one because the RefEdit control is TIGHTLY integrated with Excel. All the examples I found said to us the InputBox. While this is a workaround, it shouldn't be the solution.

Using the Code

The user control consist of two components:

1. Textbox
2. Button

The user control also uses the 11.0 Interop assembly for Excel (Microsoft.Office.Interop.Excel)

Configuring the Control

After you have added the control to the toolbox. You simply drag it to any place on the form. At this time, the control is not functional it requires that you "connect" it to the spreadsheet it is talking to.

Me.Excel2003RefEdit1.Connection = xl

The connection property of the RefEdit control is of Type Excel.Application. This means that you will have to have the Excel Object instantiated on the form.

Dim xl As Microsoft.Office.Interop.Excel.Application

 Private Sub Form2_Shown(ByVal sender As Object,
     ByVal e As System.EventArgs) Handles Me.Shown

        xl = New Microsoft.Office.Interop.Excel.Application
        xl.Workbooks.Add()
        xl.Visible = True

        Me.Excel2003RefEdit1.Connection = xl
        Me.Excel2003RefEdit2.Connection = xl

 End Sub

At this point, this is all the configuration required for the control to work. The rest happens inside the control.

How does it capture the Cell Selection?

Once a "connection" has been established with the spreadsheet. The control is functional. For the control to capture the cell selection in Excel, I created a method that captures the Excel Event. This method is fired when the user selects the refedit control.

Private Sub SinkEvents()
    xlSheet = RefEditExcel.ActiveSheet
    EventDel_SelectionChange = New Excel.DocEvents_SelectionChangeEventHandler(
        AddressOf SelectionChange)
    AddHandler xlSheet.SelectionChange, EventDel_SelectionChange
End Sub

Private Sub txtAddress_Enter(ByVal sender As Object,
    ByVal e As System.EventArgs) Handles txtAddress.Enter
    Call SinkEvents()
End Sub

The method that captures the excel range writes the value to the textbox. It requires the use of a delegate because of Cross Threading.

Delegate Sub WriteData(ByVal value As String)

Private Sub SelectionChange(ByVal Target As Excel.Range)
    Call WriteHeader("'" & Target.Worksheet.Name & "'!" & Target.Address)
    Call NAR(Target)
End Sub

Private Sub WriteHeader(ByVal value As String)
    If Me.InvokeRequired Then
        Me.Invoke(New WriteData(AddressOf WriteHeader), New Object() {value})
    Else
        Me.txtAddress.Text = value
    End If
End Sub

Once the data is written, I make sure to destroy the Excel Reference. This is done using the NAR method.

Private Sub NAR(ByVal ComObj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(ComObj)
        ComObj = Nothing

        GC.Collect()
        GC.WaitForPendingFinalizers()
    Catch ex As Runtime.InteropServices.COMException
        MessageBox.Show(ex.Message)
    End Try
End Sub

When the user clicks out of the control, the control releases its handle on the Excel Event.

Private Sub UnSinkEvents()
    RemoveHandler xlSheet.SelectionChange, EventDel_SelectionChange
End Sub

Private Sub txtAddress_Leave(ByVal sender As Object,
    ByVal e As System.EventArgs) Handles txtAddress.Leave
    Call UnSinkEvents()
End Sub

How do you Minimize/Maximize the control? 

The resizing of the control is done through the following method. It uses a structure to store the data and use.

Private Sub ResizeParent()

        For Each c As Control In ParentForm.Controls
            c.Visible = ControlDisplay.ParentMinimized
        Next

        Visible = True

        If Not ControlDisplay.ParentMinimized Then
            Me.btnState.Image = My.Resources.RefEdit1
            ControlDisplay.ParentClientSize = ParentForm.ClientSize
            ControlDisplay.ControlPrevX = Left
            ControlDisplay.ControlPrevY = Top
            ControlDisplay.ControlAnchor = Anchor

            Anchor = AnchorStyles.Left
            ParentForm.ClientSize = New Size(Me.Width, Me.Height)
            Left = 0
            Top = 0

            ControlDisplay.ParentPrevBorder = ParentForm.FormBorderStyle
            ParentForm.FormBorderStyle = FormBorderStyle.FixedDialog
            ControlDisplay.ParentControlBox = ParentForm.ControlBox
            ParentForm.ControlBox = False
        Else
            Me.btnState.Image = My.Resources.RefEdit0
            ParentForm.ClientSize = ControlDisplay.ParentClientSize
            Anchor = ControlDisplay.ControlAnchor
            Left = ControlDisplay.ControlPrevX
            Top = ControlDisplay.ControlPrevY
            ParentForm.FormBorderStyle = ControlDisplay.ParentPrevBorder
            ParentForm.ControlBox = ControlDisplay.ParentControlBox
        End If
    End Sub

The control can also be resized by using the keyboard shortcut (F4). One benefit that this keyboard shortcut has that the Excel refedit control does not have, is that the (F4) shortcut will resize in both directions. The Excel refedit only shrinks the control. This will Shrink and Grow.

Private Sub txtAddress_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtAddress.KeyDown
    If e.KeyCode = Keys.F4 Then
        Call DoResize() 'Method that calls the ResizeParent Method
    End If
End Sub

COM Object Management 

To make sure that the Excel.exe process can be killed when the application is closed. I make sure to release any COM objects that the control may be using. I manage this when the control is being disposed.

Private Sub RefEditControl_Disposed(ByVal sender As Object,
    ByVal e As System.EventArgs) Handles Me.Disposed
    Try
        If RefEditExcel IsNot Nothing Then
            RemoveHandler xlSheet.SelectionChange, EventDel_SelectionChange

            Call NAR(xlSheet)
            Call NAR(RefEditExcel)
            Call NAR(Connection)

            EventDel_SelectionChange = Nothing
            ControlDisplay = Nothing
        End If

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

End Sub

Points of Interest

You must also make sure that you dispose of the Excel COM objects used outside of the control.

I am sure there a some bugs that need to be addressed, and I will continue to improve this control. If you find anything, please advise on how you fixed it so that I can update the control.

History

• v1.0 - Initial Release
• v1.1 - Added Sample and Source code
• v1.2 - Release of UserControl along with its supporting source code and examples.
• v1.3 - Updated control to allow it to be stretched or shrunk.
• v1.4 - Fixed:  A bug introduced when stretching or shrinking the control.
         - Added: The ability to minimize/maximize the control using the standard keyboard
                      shortcut (F4)