Click here to Skip to main content
15,868,016 members
Articles / Productivity Apps and Services / Microsoft Office

RefEdit Emulation for .NET

Rate me:
Please Sign up or sign in to vote.
4.75/5 (14 votes)
27 Oct 2012CPOL6 min read 91.3K   3.8K   29   32
A simple implementation of a ref edit control for .NET

Introduction   

When I originally posted this article it was a simple Proof-Of-Concept article showing that a RefEdit control could be built to communicate between Excel and a .NET application.  

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 through the RibbionUI. I spent some time and built a control for Excel 2003 and provided it here.  

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

RefEditControl/Example_1.png

Update to Article

After many moons I have had the chance to return to this component. I read through the comments below and decided to update the component to work with Office 2007 (Office 2010 will be next). The latest control contains many fixes and enhancements.

Although I am still providing the Excel 2003 component, this article reflects the changes that exist in the Excel 2007 component.  

Note: The Excel 2003 component is not being updated and has been discontinued. 

It has been upgraded to .NET 4.0. 

Background 

I started a project a few years ago 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 knows, the RefEdit control is great! it allows users to select ranges from a spreadsheet through 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. 

The Control's Layout and Dependencies 

 I decided to use a user control as the base of the component. The RefEdit control is built using the following components: 

  • Textbox 
  • Button  

The control is dependent on the Microsoft.Office.Interop.Excel Dll. It is important to download the component based on the version of Office you have installed on your machine. The following versions are currently available for download: 

  • Office 2003 
  • Office 2007  

Using the Control

Now that we got all of the formal dialog and warnings out of the way, let us change our focus to the fun part of the article: THE CODE! 

Configuring the Control 

After you have added the control to the toolbox. You simply drag and drop it to any place on the form. 

At this point, you must "connect" the control to the Excel spreadsheet it will be "communicating" with. The control provides a property "ExcelConnector" that MUST be set for the control to work properly. 

VB
Me.Excel2007RefEdit1.ExcelConnector = 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. 

VB
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.Excel2007RefEdit1.ExcelConnector = 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? 

When the user selects the TextBox of the RefEdit control, I capture the SelectionChange event of the Excel Worsheet via the AddHandler method. 

VB
Private Sub txtAddress_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtAddress.Enter
        If ExcelConnector Is Nothing Then Return

        If Me.Address <> String.Empty Then xlSheet.Range(Me.Address).Select()
        AddHandler xlSheet.SelectionChange, AddressOf SelectionChange

End Sub 

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

VB
Private Delegate Sub WriteValue(ByVal value As String)

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

Private Sub WriteData(ByVal value As String)
    If Me.InvokeRequired Then
        Me.Invoke(New WriteValue(AddressOf WriteData), New Object() {value})
    Else
        Me.txtAddress.Text = value
        Me.Address = value
        RaiseEvent Changed(Me, New System.EventArgs())
    End If
End Sub 

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

VB
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 RefEdit control, the control releases its handle on the Excel Event. 

VB
Private Sub txtAddress_Leave(ByVal sender As Object,
    If ExcelConnector Is Nothing Then Return
    RemoveHandler xlSheet.SelectionChange, EventDel_SelectionChange
End Sub 

The AddHandler and RemoveHandler are handled in the Enter and Leave events of the TextBox to accomodate for the use of multiple controls on a form. 

How do you Minimize/Maximize the control?  

RefEditControl/Example_2.png 

To minimize or maximize the control, the user clicks on the RefEdit control button. The method _Resize collapses the userform to only display the RefEdit control as show above.  

VB.NET
Private Sub _Resize()

   ' Manages the BeforeResize Event
   Dim args As New EventArgs.BeforeResizeEventArgs With {.DisplayState = Me.DisplayState}
   Call OnBeforeResize(args)
If args.Cancel Then Return

   For Each c As Control In ParentForm.Controls
       If Not TypeOf c Is Excel2007RefEdit Then
           c.Visible = DisplayState.IsParentMinimized
       End If
   Next

   Me.Visible = True

   If DisplayState.ActualParent IsNot Nothing Then
       Me.ParentForm.Controls.Remove(Me.MemberwiseClone)
       DisplayState.ActualParent.Controls.Add(Me)
       DisplayState.ActualParent = Nothing
   Else
       If Not TypeOf Me.Parent Is Form Then
           DisplayState.ActualParent = Me.Parent
           Me.ParentForm.Controls.Add(Me)
       End If
   End If

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

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

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

   DisplayState.IsParentMinimized = Not DisplayState.IsParentMinimized

   ' Raises the AfterResize event
   RaiseEvent AfterResize(Me, New EventArgs.AfterResizeEventArgs With {.DisplayState = DisplayState})

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. 

VB.NET
Private Sub txtAddress_PreviewKeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.PreviewKeyDownEventArgs) Handles txtAddress.PreviewKeyDown
   If e.KeyCode = Keys.F4 Then
       Call _Resize()            
       Me.txtAddress.Focus()
    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.

VB.NET
Private Sub Excel2007RefEdit_Disposed(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Disposed
   
   If xlApp Is Nothing Then Return

   RemoveHandler xlSheet.SelectionChange, AddressOf SelectionChange

   Call NAR(xlSheet)
   Call NAR(xlBook)
   Call NAR(xlApp)
   Call NAR(ExcelConnector)

   DisplayState = Nothing

End Sub 

Available Events and Properties 

With the release of the Excel 2007 component. I have provided the following events:

  • Changed - This event is fired after a selection is made on the Excel spreadsheet.
  • DropButtonClicked - This event is fired after the DropButton is clicked.
  • BeforeResize - This event is fired before the resize is executed. This event can be cancelled.
  • AfterResize - This event is fired after the resize has been executed. 
I also included the following Public Properties:
  • Address - The RefEdit's currently selected value.  
  • ImageMinimized - The image displayed when the control has been minimized. 
  • ImageMaximized - The image displayed when the control has been maximized.
  • IncludeSheetName - Indicates if the worksheet name should be included in the selected range. 
  • ShowRowAbsoluteIndicator - Shows the row absolute indicator ($) in the selected range.
  • ShowColumnAbsoluteIndicator - Shows the column absolute indicator ($) in the selected range. 

I thought it beneficial to expose the following Textbox properties:

  • Font
  • Forecolor
  • RightToLeft  
And the following Button properties:

  • FlatAppearance
  • FlatStyle 

Points of Interest 

These are things I learned from the development of this control: 

  • It is recommended you dispose of the Excel COM objects used by the .NET application.
  • If you want the currently selected cell to be used by the control, you must click out and back in of the cell. The reason behind this is the SelectionChange event only fires if the cell selected is different. (I am looking for a workaround for this still).   

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 wample 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)
  • v2.0 - Added: Office 2007 Integration and updated the control with new features. 
    - Fixed: The control can now be used inside of other containers such as TabControls. 
  • v2.1 - Added: Several Public Properties (see above).
    - Fixed: The control would not always be displayed when maximizing, if the control was in a
                        container like a groupbox or tabcontrol. 
  • v2.2 - Added: Exposed some textbox and button properties that I believe are relevant (see above) 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
United States United States
I am a .NET/Office/SQL programmer. Although by day I work for as a software developer for a big distribution company, by night I enjoy creating things. My major nocturnal project is Office Ribbon Editor (http://www.leafcreations.org).

I have dabbled in such languages as java, c, c++, php, cf, objective C and even cobol. At this moment, I am sticking with the .NET languages.

Comments and Discussions

 
QuestionHow to use this control in VSTO (Excel addin) Pin
atulbiz7-Jun-18 0:20
atulbiz7-Jun-18 0:20 
AnswerRe: How to use this control in VSTO (Excel addin) Pin
jkluge18-Dec-20 10:11
jkluge18-Dec-20 10:11 
QuestionWork on the active workbook Pin
Member 1369248123-Feb-18 0:40
Member 1369248123-Feb-18 0:40 
AnswerRe: Work on the active workbook Pin
atulbiz11-Jun-18 1:34
atulbiz11-Jun-18 1:34 
QuestionCan I press Enter after selecting the range Pin
The StarHunter21-May-17 23:15
The StarHunter21-May-17 23:15 
QuestionHave to minimise before selecting cell Pin
Member 1314537929-Apr-17 6:12
Member 1314537929-Apr-17 6:12 
AnswerRe: Have to minimise before selecting cell Pin
jkluge18-Dec-20 5:19
jkluge18-Dec-20 5:19 
QuestionControl works fine on development machine .... Pin
John Do11-Mar-14 13:06
John Do11-Mar-14 13:06 
AnswerRe: Control works fine on development machine .... Pin
jkluge18-Dec-20 4:47
jkluge18-Dec-20 4:47 
QuestionAlways a New Instance of Excel? Pin
Faraz.Online2-May-13 18:38
Faraz.Online2-May-13 18:38 
GeneralMy vote of 5 Pin
fredatcodeproject31-Oct-12 6:56
professionalfredatcodeproject31-Oct-12 6:56 
AnswerRe: My vote of 5 Pin
pablleaf1-Nov-12 3:30
pablleaf1-Nov-12 3:30 
GeneralMy vote of 4 Pin
fredatcodeproject27-Oct-12 7:19
professionalfredatcodeproject27-Oct-12 7:19 
GeneralRe: My vote of 4 Pin
pablleaf29-Oct-12 2:10
pablleaf29-Oct-12 2:10 
@fred,

The article was in mid update. The DLL is available, I just checked. Sorry about that.
QuestionNew Release? Pin
DDim0076-Jan-11 12:17
DDim0076-Jan-11 12:17 
AnswerRe: New Release? Pin
pablleaf23-Oct-12 9:42
pablleaf23-Oct-12 9:42 
Generalcontrol loses visibility (focus, maybe?) Pin
Roger Pharr6-Nov-10 9:26
Roger Pharr6-Nov-10 9:26 
GeneralRe: control loses visibility (focus, maybe?) Pin
Roger Pharr6-Nov-10 15:27
Roger Pharr6-Nov-10 15:27 
GeneralRe: control loses visibility (focus, maybe?) Pin
pablleaf10-Nov-10 5:37
pablleaf10-Nov-10 5:37 
QuestionA problem with the control Pin
Oscarsss1-Oct-10 1:15
Oscarsss1-Oct-10 1:15 
AnswerRe: A problem with the control Pin
pablleaf23-Oct-12 10:12
pablleaf23-Oct-12 10:12 
QuestionStupid Question; how do I access the range value from within my code? Pin
rmorrow24-Nov-09 1:52
rmorrow24-Nov-09 1:52 
AnswerRe: Stupid Question; how do I access the range value from within my code? Pin
ssyladin20021-Dec-09 4:34
ssyladin20021-Dec-09 4:34 
GeneralRe: Stupid Question; how do I access the range value from within my code? Pin
Bryan Ruddy6-Apr-11 3:54
Bryan Ruddy6-Apr-11 3:54 
GeneralRe: Stupid Question; how do I access the range value from within my code? Pin
Bryan Ruddy6-Apr-11 7:49
Bryan Ruddy6-Apr-11 7:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.