Tuesday, 9 July 2013



Delete Rows Based On ActiveCell's Value (Excel Add-In)

Introduction

During the previous week, a colleague of mine asked me to write a VBA code in order to delete some rows in a spreadsheet based on a cell’s value. He had some experimental data and he wanted to remove the “noise” – the non-acceptable values – from his dataset. I developed a small Excel add-in for him, but, during the weekend I decided to go one step further.

My experiment was to convert the original VBA add-in into a COM add-in using the “outdated” Visual Basic 6.0.  I am sure that some of you might know that the VB 6.0 and VBA language are quite similar. However, there were some difficulties during this “experiment” that I would like to stress out: 
  1. The use of Ribbon. Starting from the 2007 version, Excel no longer uses menus, but all commands are organized in tabs. The Ribbon can be customized with xml code. In this case the xml customization was done in VB6.0 by writing a small function that creates a string with the necessary xml code. You can find the code below.
  2. Writing the “Excel code” using late binding. The add-in should operate in different Excel versions (2007, 2010 and 2013), so I had to write the VB without referencing to a specific Excel library (12.0, 14.0 or 15.0). This was done by writing the code using early binding (so, with intelli-sense on), and then change it to late binding.
I suppose that you might wonder what is the benefit of writing a COM-add in with the “old” VB 6.0? In my opinion there are two obvious advantages:
  • The code is protected since is compiled. Writing directly an add-in directly from Excel using VBA is quite easier, but, the security of VBA project is quite low. When you distribute a COM add-in, contrary to Excel, the source code remains at your computer.
  • The syntax of VB6.0 is quite similar to VBA, so you don’t have to make so many changes in the original code that was probably written in VBA. The conversion of VBA code to .NET code is far much harder if you want to develop your COM add-in using the latest version of Visual Studio. 
Although you might not have been persuaded from the above “advantages", it is always good to know that - at least - there is another alternative for writing COM add-ins (for the modern version of Office), even with an “outdated” development platform.


Ribbon customization through VB 6.0

Below you will find the Visual Basic code that I wrote in order to create a custom button at the Home tab of the Ribbon.

Option Explicit
Implements IRibbonExtensibility

'By Christos Samaras
'Date: 7/7/2013
'http://www.myengineeringworld.net
   
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal _
            ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
   
    Set XL = Application
    Set ThisCAI = AddInInst
      
End Sub

Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As _
        AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
    
    Set XL = Nothing
    Set ThisCAI = Nothing
    
End Sub

Public Function IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String
      
      IRibbonExtensibility_GetCustomUI = GetRibbonXML()
      
End Function

Public Function GetRibbonXML() As String
    
    Dim sRibbonXML As String

    sRibbonXML = "<customUI xmlns=""http://schemas.microsoft.com/office/2006/01/customui"" >" & _
                "<ribbon>" & _
                "<tabs>" & _
                "<tab idMso=""TabHome"">" & _
                "<group id=""customGroup1"" label=""Delete"" insertAfterMso=""roupEditingExcel"">" & _
                "<button id=""customButton1"" label=""Delete Rows"" size=""large"" imageMso=""CellsDelete"" onAction=""ShowMyFormDellete"" />" & _
                "</group >" & _
                "</tab>" & _
                "</tabs>" & _
                "</ribbon>" & _
                "</customUI>"
  
   GetRibbonXML = sRibbonXML
  
End Function

Public Sub ShowMyFormDellete(ByVal control As IRibbonControl)

    frmDelete.Show
   
End Sub


How to use it

First of all, the add-in is in reality a dll file. So, before using the add-in you must register the dll file.  In the download section below you will find apart from the dll file an Excel workbook which will help you to register the dll file. More information about the workbook you can find here. After you complete the dll registration process you can simply open the Excel and the add-in should be loaded automatically.

Delete Rows Button

The use of add-in is quite simple:
  1. Select a cell in the desired column, using either the keyboard or the mouse.
  2. Press the “Delete Rows” button in the Home tab of the Ribbon (it should be the last button on the left of the tab).
  3. Fill the lower and the upper value on the form and press the “Delete Rows” button. Automatically, each row in the ActiveCell’s column that has a value WITHIN the defined range will be deleted (if lower value <= cell value in the ActiveCell’s column <= upper value then row is deleted).
  4. Alternatively, if you check the “Keep Rows” checkbox you will notice that the “Delete Rows” button’s caption becomes “Keep Rows”. So, now the code reverses its operation and deletes each row that is OUTSIDE of the defined range (if cell value in the ActiveCell’s column < lower value or cell value in the ActiveCell’s column > upper value then row is deleted).

Demonstration video

The short video below shows you how to register the dll file and how to use this COM add-in so as to delete some data.



Download it from here


The zip file contains the COM add-in (dll file), as well as an Excel workbook that will help you registering the add-in. The add-in was tested on Excel 2007 & 2010 (32 bit), but should also work on Excel 2013.


Read also

Register & Unregister A DLL File Through VBA

Did you like this post? If yes, then share it with your friends. Thank you!



Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: tel. +30-6973513308, e-mail , Facebook , Twitter , Google+ and Linkedin. Full CV