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:
- 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.
- 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.
- 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.
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.
'By Christos Samaras
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
Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As _
AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
Set XL = Nothing
Set ThisCAI = Nothing
Public Function IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String
IRibbonExtensibility_GetCustomUI = GetRibbonXML()
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>" & _
GetRibbonXML = sRibbonXML
Public Sub ShowMyFormDellete(ByVal control As IRibbonControl)
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.
The use of add-in is quite simple:
- Select a cell in the desired column, using either the keyboard or the mouse.
- Press the “Delete Rows” button in the Home tab of the Ribbon (it should be the last button on the left of the tab).
- 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).
- 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).
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.
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.