August 8

Needs More Rollover: Quick Tip!

More people are interested in Excel mouse rollovers, which I think is great. I’m especially enthusiastic about Chandoo’s latest dashboard contest where I found out that several contestants used the technique! By the way, there are many great dashboards showcased in the contest. Take a look and make sure to vote!

So here’s the tip. If you’re familiar with the rollover method – and you should be by now, it’s like all I ever blog about these days – you may have noticed that while your mouse is over a cell, Excel is continuously firing the rollover method. For example, let’s take a look at this snippet from Chandoo’s blog:


Public Function highlightSeries(seriesName As Range)
Range("valSelOption") = seriesName.Value
End Function

If you place your mouse over the cell with the HYPERLINK formula that calls this function, the range valSelOption will be continuously written to. This becomes a problem when there’s a lot of complex interaction on your spreadsheet. If, for example, you’ve used a lot of volatile functions (like OFFSET or VLOOKUP), continuously writing to the sheet will mean a recalculation for each cell with the volatile function. Nobody likes a slow spreadsheet. 

The incredibly simple fix to our problem comes from the wonderful book, Professional Excel Development, by  Bullen, Bovey, and Green in their chapter on spreadsheet optimization and speed tricks. Simply test if you’re rewriting the same value over and over again:

Public Function highlightSeries(seriesName As Range)
If Range("valSelOption") <> seriesName.Value Then Range("valSelOption") = seriesName.Value
End Function

In the above code, I test if valSelOption already equals seriesName.Value. If it does, then we do nothing; if not, we write to it so that the next time the function is called with the same value in its argument (which is unpreventable if your mouse is over a cell for even a brief moment second), we can again, relax and do nothing.

Thats it! You’ll likely see an immediate speed improvement, especially if you’re doing lots of complex stuff with your rollover, like a this.


Tags

optionexplicitvba


You may also like

51: Oz du Soleil & the Global Excel Summit 2021

51: Oz du Soleil & the Global Excel Summit 2021

Global Excel Summit 2021

Global Excel Summit 2021
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.

>