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!
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.
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 FunctionThe 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.
Hi,
ReplyDeleteI tried this mouse rollover technique with success! But I have several thousands of cells with the HYPERLINK formula, "calling" my UDF.
My problem is that I somewhere else in the workbook have a little table that I sort with a little homemade VBA sub. When I sort the table ALL the hyperlink formulas are recalculated and my UDF is run thousands of times. Why is that? The table I sort has no connection or references at all to the HYPERLINKS or the UDF.
I made a little fix on this: In the SORTING sub I put "i=0" in the beginning and "i=1" in the end. In the UDF I condition it to run the code only if i=1.
This saves me a couple of seconds in calculation time, but still the UDF shouldn't be triggerede at all.
Hope you can help me.
Thx in advance.
Thomas
Hi Thomas,
ReplyDeleteI have exactly the same Problem. Your method (which I also came up) seems to work, but I am not sure, if I can handle it, since I Need to make sure, that I am Aware of all possibilities, whenever such an Event could occur. So, it seems, I Need to write a coding engine, to make sure, that upating the worksheet will not raise my Events.
Is there another way to block These Event and to make sure, that only mouseOver Events will be fired (I can't or lets say, I would like to avoid clicks on my cell)?
Micha