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.
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020
