Excel MVP Jordan Goldmeier stopped by yesterday for a guest appearance on the podcast. Traditional rules say that a User Defined Function is not allowed to change other cells, but Jordan has found a way to allow UDFs to break the rules. Watch how he uses this technique to create a Rollover effect in cells, first in a periodic table of the elements, and then in a dynamic chart. The link to the periodic table is at Apress/dashboards-for-excel
Transcript of the video:
Learn Excel for MrExcel Podcast, Episode 1813: Jordan Goldmeier, the Rollover Method.
Bill Jelen: Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
This is awesome, here in the studio, we have the newest Excel MVP, Jordan Goldmeier, and he has awesome things to show.
Jordan, how's he?
Jordan Goldmeier: Hey, Bill.
Bill Jelen: Yeah, Jordan?
Jordan Goldmeier: What does the Excel developer put in their hair in the morning?
Bill Jelen: I don't know, what?
Jordan Goldmeier: Sum product.
Bill Jelen: Aah.
[ Laughs ] That's awesome.
Already-- already, this podcast-- this episode of MrExcel podcast is funnier than the other 1812 that we've done.
That's good.
Jordan Goldmeier: Well, thanks, Bill, for having me on.
I'm just going to do some real quick information about myself.
As Bill showed on the cover, I'm writing a book: Dashboards for Excel.
It's probably going to change its name a few more times-- I was thinking, "50 Shades of Excel," I like that.
Bill Jelen: [ Laughs ] That would be a great title.
Jordan Goldmeier: See?
Look, I got another joke in.
Bill Jelen: That's good.
Jordan Goldmeier: I also just want to do real quick about me.
I work for a company in Dayton, Ohio-- the Perduco Group, that's P-E-R-D-U-C-O-- and we do data analytics, healthcare, and we're really excited.
We're doing some new sports analytics.
So we've actually been working with some sports teams, doing scout scheduling and ranking players evaluation, stuff like that.
So, if you're interested in any of that, it's www.perducogroup.com.
Bill Jelen: Alright, so, what do you have today?
Jordan Goldmeier: So, today, we're going to talk about, what I like to call, the Rollover Method.
There's really no official name for it-- it's something that I sort of discovered, one dark and stormy night-- but, what it essentially allows you to do, is execute a macro when your mouse is hovering over a cell.
So, for example, I made this Periodic Table of Elements and, as you can see, when I roll my mouse over these different cells, information changes about the different elements, and I can actually go over here on this periodic table and I can click, and I can roll over, and you see that different element groups are being highlighted.
So, I can actually roll over, I can select them all, if I want-- just to give you a demonstration-- and, by the way, this is all downloadable-- we'll provide the link, http:optionexplicitvba.com/2012/06/- 21/interactive-periodic-table-of-elements-in-excel.
So, we're going to talk about how to do that, and at the heart of this mechanism, is this Hyperlink formula up here.
So, I'm going to start a new sheet, and I'm going to show you how this is done.
Now, what's really interesting about this, is that it actually violates one of the rules in Excel.
So I'm going to teach you how to break one of the rules-- don't tell Microsoft-- but the main rule is that, user-defined functions are not allowed to change the values of other cells.
This has been the common thinking.
In fact, it-- as far as I can tell-- it's appeared in every VBA book I've looked at, and we're going to violate it.
So, the first thing I'm going to do is open up the Visual Basic Editor, and I'm going to create a new module, and, now, I'm going to create a new public, user-defined function.
If you've ever done this before, syntax is pretty simple.
I'm going to call this one, MyRollover (). So, what I'm going to do in this, is I'm going to demonstrate it how we can violate the rules.
And-- let's see, here-- we were on Sheet1, so I'm going to make this very easy.
I'm going to say, sheet1.range, I'm going to put ("A1") in here, I'm going to set the Value to "Rule Broken!" --sheet1.Range("A1") .Value = "Rule Broken!".
So, now I'm going to go back to our spreadsheet, and I'm going to type in the formula that allows us to do this.
So, remember that Hyperlink formula?
We're going to type in =HYPERLINK, and now, here's the trick: In here, I'm going to type (MyRollover()).
So, instead of providing an address, a cell address, a web address, I'm actually going to provide the UDF to it.
So, for now, we're going to leave the friendly_name blank, I'm going to hit enter.
Over here, as you can see, when I rolled my mouse over-- and I'll just delete it here, just so we can see it again-- I roll my mouse over, it says "Rule Broken!" So, now we've actually violated a rule and, in fact, Excel sort of has recognized that we violated that rule, because it gave us this value error, and this is what it does when there's an error.
So, the question is, how do I get rid of that pesky error?
And the way to do that, is to put this IFERROR around it.
if I put this IFERROR around-- and I've found, I don't know if this is necessary to put the same thing that you would put in the IFERROR in the friendly text, but I'm going to do that just to keep things consistent.
Hit Enter.
We'll resize this.
As you can see, it works without-- without-- resulting in an error, so let's put this to good use.
So, let's use this in a real example.
So, I've created a new sheet-- Sheet3-- and what I'm going to show you how to do, is sort of create a Rollover on your own that's going to pick up-- that's going-- that you can use and show different graphs.
So we're going to take something that was, theoretically, "Cool, we broke the rules," now, how can we use breaking the rules to our advantage?
So the way we're going to-- the way I've done this here-- is I've-- you see my mouse is over North, it's showing North, as I put it over South, East, West, it updates.
So, right away, you'll notice that when my mouse is over West-- over this blank spot-- nothing's really happening, so I can act-- well, I can really show you-- in East, or if I put it in South, nothing's happening.
So what I'll do is, if you put Word Wrap on, that will actually fix that-- I don't know why it fixes that, it was kind of a weird discovery on my part, but it does fix that.
So here's how this mechanism works: When I click on North, you see that I have this MyRollover and it's putting the word North in, so it's actually taking what's supposed to be there and it's going to send it to the UDF.
So, over here, you also see that we have this table and then I have this graph here-- it's linked to, sort of, our current selection.
So, as this cell here changes, it's going to do an HLOOKUP on our table over here, and it's going to deposit it here, and then our chart is linked here.
So, how do we get this cell to change?
And the way we do that is, it's in our Rollover method.
So I've actually named this cell Current Region.
And when I do my Rollover, I use this shorthand-- these brackets-- so I say [ CurrentRegion ] = Region-- which is the string passed in.
And, as you can see from here, we're actually passing in that string.
So, basically, passing the string tells it which item to look up, that look up is deposited, and that informs the chart.
So, that's actually-- that basic mechanism is-- how I was able to create that Periodic Table, and in fact, we can-- you can-- do all sorts of things with this-- you can do conditional formatting, so when your mouse hovers over it, it will change color.
There's all sorts of things to do.
Bill Jelen: Okay, so that is a radically, cool method, and when you-- when Jordan-- set this to a few of the other MVPs, I don't think anyone had ever seen anything like this.
A great way to break the rules in a very useful and clever, clever way.
Alright, so again, your book "Dashboards" --and, for example, that's coming out when?
Jordan Goldmeier: Hopefully, February.
Bill Jelen: February 2014.
Jordan Goldmeier: That's the goal, yep.
Bill Jelen: Go out to Amazon right now and Pre-order it, so, check that out.
And, also, your website is?
Jordan Goldmeier: Theperducogroup.com.
Perduco, just in case you're wondering-- because everyone always asks us that-- it means, to deliver.
So, it's a Latin word and we were trying to make a clever name about what we do, so-- Bill Jelen: Clever for all of the people who have taken Latin.
Jordan Goldmeier: [ Laughs ] Yeah.
So, check us out.
If you like what we do, we have our contact information at the bottom, you know, just stay in touch.
Follow my blog-- I also have a blog-- optionexplicitvba.com.
Bill Jelen: All right, that was an amazing trick from Jordan.
I want to thank Jordan for stopping by on his way back to Dayton, Ohio.
And I thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Bill Jelen: Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
This is awesome, here in the studio, we have the newest Excel MVP, Jordan Goldmeier, and he has awesome things to show.
Jordan, how's he?
Jordan Goldmeier: Hey, Bill.
Bill Jelen: Yeah, Jordan?
Jordan Goldmeier: What does the Excel developer put in their hair in the morning?
Bill Jelen: I don't know, what?
Jordan Goldmeier: Sum product.
Bill Jelen: Aah.
[ Laughs ] That's awesome.
Already-- already, this podcast-- this episode of MrExcel podcast is funnier than the other 1812 that we've done.
That's good.
Jordan Goldmeier: Well, thanks, Bill, for having me on.
I'm just going to do some real quick information about myself.
As Bill showed on the cover, I'm writing a book: Dashboards for Excel.
It's probably going to change its name a few more times-- I was thinking, "50 Shades of Excel," I like that.
Bill Jelen: [ Laughs ] That would be a great title.
Jordan Goldmeier: See?
Look, I got another joke in.
Bill Jelen: That's good.
Jordan Goldmeier: I also just want to do real quick about me.
I work for a company in Dayton, Ohio-- the Perduco Group, that's P-E-R-D-U-C-O-- and we do data analytics, healthcare, and we're really excited.
We're doing some new sports analytics.
So we've actually been working with some sports teams, doing scout scheduling and ranking players evaluation, stuff like that.
So, if you're interested in any of that, it's www.perducogroup.com.
Bill Jelen: Alright, so, what do you have today?
Jordan Goldmeier: So, today, we're going to talk about, what I like to call, the Rollover Method.
There's really no official name for it-- it's something that I sort of discovered, one dark and stormy night-- but, what it essentially allows you to do, is execute a macro when your mouse is hovering over a cell.
So, for example, I made this Periodic Table of Elements and, as you can see, when I roll my mouse over these different cells, information changes about the different elements, and I can actually go over here on this periodic table and I can click, and I can roll over, and you see that different element groups are being highlighted.
So, I can actually roll over, I can select them all, if I want-- just to give you a demonstration-- and, by the way, this is all downloadable-- we'll provide the link, http:optionexplicitvba.com/2012/06/- 21/interactive-periodic-table-of-elements-in-excel.
So, we're going to talk about how to do that, and at the heart of this mechanism, is this Hyperlink formula up here.
So, I'm going to start a new sheet, and I'm going to show you how this is done.
Now, what's really interesting about this, is that it actually violates one of the rules in Excel.
So I'm going to teach you how to break one of the rules-- don't tell Microsoft-- but the main rule is that, user-defined functions are not allowed to change the values of other cells.
This has been the common thinking.
In fact, it-- as far as I can tell-- it's appeared in every VBA book I've looked at, and we're going to violate it.
So, the first thing I'm going to do is open up the Visual Basic Editor, and I'm going to create a new module, and, now, I'm going to create a new public, user-defined function.
If you've ever done this before, syntax is pretty simple.
I'm going to call this one, MyRollover (). So, what I'm going to do in this, is I'm going to demonstrate it how we can violate the rules.
And-- let's see, here-- we were on Sheet1, so I'm going to make this very easy.
I'm going to say, sheet1.range, I'm going to put ("A1") in here, I'm going to set the Value to "Rule Broken!" --sheet1.Range("A1") .Value = "Rule Broken!".
So, now I'm going to go back to our spreadsheet, and I'm going to type in the formula that allows us to do this.
So, remember that Hyperlink formula?
We're going to type in =HYPERLINK, and now, here's the trick: In here, I'm going to type (MyRollover()).
So, instead of providing an address, a cell address, a web address, I'm actually going to provide the UDF to it.
So, for now, we're going to leave the friendly_name blank, I'm going to hit enter.
Over here, as you can see, when I rolled my mouse over-- and I'll just delete it here, just so we can see it again-- I roll my mouse over, it says "Rule Broken!" So, now we've actually violated a rule and, in fact, Excel sort of has recognized that we violated that rule, because it gave us this value error, and this is what it does when there's an error.
So, the question is, how do I get rid of that pesky error?
And the way to do that, is to put this IFERROR around it.
if I put this IFERROR around-- and I've found, I don't know if this is necessary to put the same thing that you would put in the IFERROR in the friendly text, but I'm going to do that just to keep things consistent.
Hit Enter.
We'll resize this.
As you can see, it works without-- without-- resulting in an error, so let's put this to good use.
So, let's use this in a real example.
So, I've created a new sheet-- Sheet3-- and what I'm going to show you how to do, is sort of create a Rollover on your own that's going to pick up-- that's going-- that you can use and show different graphs.
So we're going to take something that was, theoretically, "Cool, we broke the rules," now, how can we use breaking the rules to our advantage?
So the way we're going to-- the way I've done this here-- is I've-- you see my mouse is over North, it's showing North, as I put it over South, East, West, it updates.
So, right away, you'll notice that when my mouse is over West-- over this blank spot-- nothing's really happening, so I can act-- well, I can really show you-- in East, or if I put it in South, nothing's happening.
So what I'll do is, if you put Word Wrap on, that will actually fix that-- I don't know why it fixes that, it was kind of a weird discovery on my part, but it does fix that.
So here's how this mechanism works: When I click on North, you see that I have this MyRollover and it's putting the word North in, so it's actually taking what's supposed to be there and it's going to send it to the UDF.
So, over here, you also see that we have this table and then I have this graph here-- it's linked to, sort of, our current selection.
So, as this cell here changes, it's going to do an HLOOKUP on our table over here, and it's going to deposit it here, and then our chart is linked here.
So, how do we get this cell to change?
And the way we do that is, it's in our Rollover method.
So I've actually named this cell Current Region.
And when I do my Rollover, I use this shorthand-- these brackets-- so I say [ CurrentRegion ] = Region-- which is the string passed in.
And, as you can see from here, we're actually passing in that string.
So, basically, passing the string tells it which item to look up, that look up is deposited, and that informs the chart.
So, that's actually-- that basic mechanism is-- how I was able to create that Periodic Table, and in fact, we can-- you can-- do all sorts of things with this-- you can do conditional formatting, so when your mouse hovers over it, it will change color.
There's all sorts of things to do.
Bill Jelen: Okay, so that is a radically, cool method, and when you-- when Jordan-- set this to a few of the other MVPs, I don't think anyone had ever seen anything like this.
A great way to break the rules in a very useful and clever, clever way.
Alright, so again, your book "Dashboards" --and, for example, that's coming out when?
Jordan Goldmeier: Hopefully, February.
Bill Jelen: February 2014.
Jordan Goldmeier: That's the goal, yep.
Bill Jelen: Go out to Amazon right now and Pre-order it, so, check that out.
And, also, your website is?
Jordan Goldmeier: Theperducogroup.com.
Perduco, just in case you're wondering-- because everyone always asks us that-- it means, to deliver.
So, it's a Latin word and we were trying to make a clever name about what we do, so-- Bill Jelen: Clever for all of the people who have taken Latin.
Jordan Goldmeier: [ Laughs ] Yeah.
So, check us out.
If you like what we do, we have our contact information at the bottom, you know, just stay in touch.
Follow my blog-- I also have a blog-- optionexplicitvba.com.
Bill Jelen: All right, that was an amazing trick from Jordan.
I want to thank Jordan for stopping by on his way back to Dayton, Ohio.
And I thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
