For some time now, I’m evaluating my web server log files with Excel 2010. Yes, there are many other good tools for analyzing logfiles on the market and I’m also using some of them. However, Excel 2010 provides the function to easily import and filter the raw data of my logfiles. Unfortunately, with such amount of data, I’m often „loosing“ the line while reading on the screen. Ok, I can surely increase the zoom factor of the sheet or setup borders for the cells; my wish was, however, to have a tool which highlights the current line or column. As Excel does not provide such a feature, I decided to implement my own tool in VBA. The following screenshot shows the tool in action:
The Add-In is available for free including the source code. At the end of this article, you will find the download link. In the following I will briefly present the Add-In and give some explanations about the code. Please note that I developped this tool for my own purposes; so I did not performed professional test series on different operations systems or with different Excel versions. The tool works fine on my Windows 7 system and runs in Excel 2003, 2007 and 2010 without problems. And please also note, that not all possible events may be triggered by the tool.
In a first step, I checked out which functions I would like to implement; the following features seemed to be useful:
- The tool may offer a choice of whether rows, columns or both should be highlighted.
- There should be a way to set the number of cells which should be highlighted on the left, right, above and below the active cell. Highlighting the entire active row, column, is not very useful for performance reasons.
- It would be nice if the highlight color could be chosen.
Then I have created a prototype by creating a Userform and inserting some controls, as shown by the screenshot below:
I used simple static text labels for the color bars and manually setted a background color for them. When initializing the Userform, following code is called:
As you can see, the first function which is called is „mlfpApiMenueRemove()“. This function uses some Windows API functions to remove the close button on the upper right corner of the Userform at runtime. You can find this function in the „MLP_Api“ module. Then the variable „mlvhHighlight“ is instanciated; later more about this function. The Add-In stores some setting in a sheet which are read and assigned to the appropriate controls.
Ok, while developing the Add-In I asked myself, what can be the best method for highlighting the cells. One possibility is to set a background color or a border for the affected rows and columns. However, this will result in relatively complicated code, as I have to remember the previous state of the cells. A simplier is to use rectangular shapes for highlighting the columns and rows. Of course, this can only work, if the shapes are not filled with a background color.
Another goal I had, was to highlight the cells of the current active worksheet in the current active workbook. So, I needed to implement a class for triggering the Excel events on a global level, when selecting a workbook or a sheet.
The class „MLC_Highlight“ implements these functions. The class referenciates to an Application object and triggers the appropriate events, as you can see in the code excerpt below.
The event handlers „Class_Initialize()“ and „Class_Terminate()“ are performing some tasks when the class is created and terminated. The other handlers are triggering some events; in the following how this should work:
- If the user selects a sheet in the active workbook, the shapes which have been created in the previous sheet should be deleted and new shapes should be created in the new active sheet.
- If the user selects another workbook, the shapes may also be deleted in the previous workbook and recreated in the active sheet of the current active workbook.
- If the user closes or saves the active workbook, the shapes should be deleted before.
- If the user clicks on a cell in the active sheet, the shapes may be moved to a new position relatively to the selected cell.
The events handlers in the code above always call the „mlfhAction(…)“, which was just only created for a better overview in the code. Here the code for this function:
As you can surley see, the function performs some actions, dependant from the value of the variable „Action“. For example, when activating an Excel sheet or workbook, a call to the „Create(…)“ function is made or in case the shapes should be deleted, a call to „Delete(…)“ is made. The two different types of rectangles (horizontal and vertical) are stored to the array „mlvhShapes()“. This array contains two elements of my user defined type mlthShape“. This type defines the variable „Recreate“, which is used for determining if the shape should be re-created. The type also contains more variables for the color, for the number of cells to highlight and for the names of the worksheet and the workbook. Let’s now have a close look on „Create(…)“:
In a first step, some parameters of one „mlvhShapes()“ item are filled. Then the code checks whether the targeted sheet is protected or not. If yes, the code exits, if not the code calculates the starting coordinates, the height and the width of the shape using the functions „mlfhLeft(…)“, „mlfhTop(…)“, „mlfhHeight(…)“ and „mlfhWidth(…)“. Finally some properties like the line weight or the transparency are set. Let’s go the function calculating the width:
The function checks for which type of rectangle (horizontal or vertical) the width should be calculated. This is very simple for the vertical shape; we just need to retrieve the width of the current cell. For the horizontal rectangle, we need to find the cell located „Offset“ columns away from the current cell on the right side. Please note, that I’m only counting the visible columns. If an error occures, then we know that the end of the sheet is reached and the loop is also exited.
Now, let’s go to a very interesting part of the code: the function for deleting a shape.
This function checks, if a rectangle was created and deletes it. This worked well in my most test cases. However, if I the active workbook contains a protected sheet and this sheet is active, it is not possible to delete a shape in a sheet from another workbook. The
„Shape(…).Delete“ function does not work and throws an error. Fortunately the user „ransi“ in my favourite german office forum „Office-Lösung.de“ had a solution for this problem: just use the undocumented function „DrawingObjects(…).Delete“. This work well; thanks „ransi“.
Finally I had to implement the code in the Userform; in the following the code for the checkbox for activating or deactivating the highlighting for the rows:
The shape is here also created by using the „Create(…)“ function when checking the checkbox and deleted by using the „Delete(…)“ function when unchecking the checkbox. The other controls are implemented in a similar manner.
The Add-In creates a new command bar in Excel 2003; in Excel 2007 and 2010, you should find a new entry on the Add-Ins Tab of the ribbon. And the Add-In saves itself when the main dialog is closed. You can download the Add-In by clicking on the following link:
Update on 04/25/2013:: as someone asked for a version which does not use the Windows API, I commented out the API-Functions. So the Add-In may also work on 64 Bit Excel.
Please note, that I can not give any support for the Add-In and I do not give any warranty of any kind for the Add-In. You use it at your own risk; of course you may close important documents before testing the Add-In on your computer. If you like to give me some feedback and suggestions or in case you find a bug, please write me. Last but not least, the link to the post from „ransi“.