22 Dec 2019

Jordan Goldmeier’s rollover technique runs a VBA User Defined Function when you hover the mouse over a hyperlink formula. Let’s take a closer look!

 

Overview Video

It’s cool to see Jordan and Bill Jelen discuss it (video) but I want to know exactly how it works.

 

PeriodicTable.xlsm

 

 

 

 

 

 

 

Jordan’s UDF rollover Excel file is amazing! Tons of functionality! Let’s examine the basic concept.

 

Rollover Technique Basics

I’ll share several Excel files starting with the most basic concept.

Excel file 01 UDF Rollover Basics. File extension is xlsm (macros).

The Ingredients

4 items: vba udf code, hyperlink formula, named range and a number.

  1. add UDF ‘RolloverSquare’ in a vba module
  2. cell K9 formula =IFERROR(HYPERLINK(RolloverSquare(K100),K100+1),K100+1)
  3. create named range Xindex in cell K98 (starting value of 0)
  4. cell K100 contains a number (to be formula later on)

 

 

 

 

 

 

 

Let’s Test It!

Remember: cell K98 is initially set to zero. Now hover your mouse over cell K9.

Voila! Cell K98 now has the value of cell K100+1. We see the behavior. Let’s look at the parts.

 

Hyperlink Formula

To understand it better I simplified it to =HYPERLINK(RolloverSquare(K100))

It results in an error but hovering over it runs the UDF! 

 

UDF Xindex value?

Public Function RolloverSquare(XIndex As Integer)
If XIndex <> Range(“XIndex”).Value + 1 Then Range(“XIndex”).Value = XIndex + 1
End Function

What is the value of Xindex above? Where does it come from before If evaluates it?

 

To answer this I added MsgBox (XIndex) as seen below:

Public Function RolloverSquare(XIndex As Integer)
MsgBox (XIndex)
If XIndex <> Range(“XIndex”).Value + 1 Then Range(“XIndex”).Value = XIndex + 1
End Function

The message box returns 85 from cell K100.

 

File 02 UDF Rollover shows both values, Xindex named range(K98) and K100, and then changes cell K98.

 

 

 

 

 

 

This also helps to understand the vba If statement:

If XIndex <> Range(“XIndex”).Value + 1 Then Range(“XIndex”).Value = XIndex + 1

The If always returns true and assigns K100 +1 to named range “XIndex” (cell K98).

 

Add Rollover Cells

I added the HYPERLINK function to more cells to see K98’s value change. I removed the Msgbox.

 

 

 

 

 

 

 

Excel file 03 UDF Rollover (hover cells added) Security settings might turn off the hyperlinks.

 

Having Fun

Let’s use Jordan’s rollover concept for a mini game (04g UDF Rollover Technique (game)).

Start in the bottom right, navigate the maze and click the WIN button in the top left. Game over if you touch any blue cell.

 

 

 

 

 

 

 

Add/remove blue hyperlink cells to change the maze. It’s been oddly interesting designing this 🙂

(with touch screen you can click WIN right after clicking Start button…where’s the fun in that?)

 

PeriodicTable.xlsm

Let’s review a few more details about Jordan’s amazing PeriodicTable.xlsm

Element Information

Sheet ‘Data’ contains the descriptive information for all the elements. Jordan’s concept could be used for dashboards.

Conditional Formatting

Various formula based rules add to the look of Jordan’s PeriodicTable.xlsm masterpiece.

Locking Scroll Area

When you first open the file you’ll notice that the scroll area is locked. How? In the Visual Basic Editor ‘ThisWorkbook’ (under ‘Microsoft Excel Objects’) contains this:

Private Sub Workbook_Open()
 Sheet1.ScrollArea = “$A$1:$W$40”
End Sub

The code above runs every time you open the file. Add apostrophes in front of each line, save, close and reopen so that it won’t run when the file opens allowing you to freely move around the screen.

More VBA

‘Sheet 1 (Table)’ and ‘Module1’ below contain vba code worth exploring.

 

 

 

 

 

 

 

About Jordan

Jordan is a Data Scientist, visualization expert, author and keynote speaker.

See Jordan’s Excel.TV profile to learn more about him. You can also follow him on Twitter and on Facebook(Excel.TV).

 

 

 

About Me

My name is Kevin Lehrbass. I’m a Data Analyst.

I live in Markham Ontario Canada. I can contribute a good deal of my technical knowledge to this:

“hmm…that looks interesting. How exactly does it work?”

Post a comment