Excel MVP Jordan Goldmeier created a cool holiday card in Excel using Excel VBA to create falling snow flakes. Download the card from http://optionexplicitvba.com/2013/12/22/excel-holiday-card/
I asked Jordan's permission to share the workbook with you on today's podcast. In this episode, you will see how to create a custom type in VBA that has several properties. When Jordan's macro simulates the falling snow, he is managing an array of 150 Snowflake types - a very fast way to manage a large number of items in Excel. Also - Jordan keeps the red Happy Holidays message on top by using conditional formatting.
Thanks to Jordan for sharing this macro with everyone!
I asked Jordan's permission to share the workbook with you on today's podcast. In this episode, you will see how to create a custom type in VBA that has several properties. When Jordan's macro simulates the falling snow, he is managing an array of 150 Snowflake types - a very fast way to manage a large number of items in Excel. Also - Jordan keeps the red Happy Holidays message on top by using conditional formatting.
Thanks to Jordan for sharing this macro with everyone!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel form MrExcel podcast episode 1842.
Jordan Goldmeier Happy Holidays.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
This week i got a great holiday card from Excel MVP Jordan Goldmeier.
You might remember Jordan, he was on the podcast about a month ago with his great rollover trick I said Jordan this is so cool do you mind if I share it on the podcast, so we're gonna come out here to this URL you're gonna look right down there in the YouTube description to get that URL click download file, download real quick.
Now, before you open this in Excel if you've never used macros before, you're going to do Alt+T+M+S, and make sure your macro settings are disabled with notification, the second one not the first one if you have the first one, we've all better use this and then open the file in Excel.
All right, couple things make sure that you enable editing and then finally enable Macros.
Now, here this is really cool Jordan has created a holiday card where we can click let it snow and the snow starts to fall and so, you can sit there and just watch this instead of the fireplace, the cable show, shows tomorrow you can watch the snow starts to accumulate and then quit snowing and finally plow snow.
Now, this is cool to watch right, but of course for those of you into VBA, the one of the nice things is that Jordan has unprotected all the code.
The worksheet is protected, but you can un-protect it and see what's going on and so first thing the, is clever the snowflakes are actually 150 different objects and I'm typing the number one here you see that Jordan has had conditional formatting that if you put the number one, you get a red cell.
So, he's able to use conditional formatting to display the message in fact you probably come in here and change the 0's and the 1's and change the message is something else provided you stay within this range here or change the range to be something larger, but I thought what was fascinating let's go back to Alt+F11, take a look at the code a couple of things I picked up here from Jordan.
First thing he created a new type called SnowFlake that has three properties current row, current column and the color if you'll notice the there's four possible colors that appear the white is the most common, but there's also kind of two shades of blue that show up.
So, we have those properties three properties for each snowflake and then does a snow array using that type of snowflake later on we dims that re-dim that it have 150 different items.
So, now we have 150 items and Jordan is rather than checking every cell in the range which is probably what I would have done just has to check 150 items see where they are he hasn't randomly fall anywhere from one to ten cells so where's the random times 10 and then checks to see what's going on in that cell.
So, that's why sometimes you'll see that there are snowflakes that will make it through like in this range right here because if the snowflake is currently here, it might randomly fall there, might randomly fall there, randomly fall here and then make it through the L, but if you have something really really long here it's never going to make it through that.
So, this these cells will never get filled in you know so kind of interesting the algorithm that Jordan has.
I brought back to Jordan, you know this is typical at the MVP Summit the Excel team shows us something really cool and then we all pile on with new feature requests.
I'm like Jordan you should add a button here that says west wind right, that would allow the snowflakes that start to move to the right and then potentially you could get some depending on how strong the wind was.
I guess you could get some things happening there let's go back to it to the code Alt+ F11 and you know actually I realized by changing the the current column here right now, he's just doing rand if we did that random times three or random times ten, you could actually get some wind going in there.
So, take a look at the code if you're interested in VBA, plows snow all he has to do is set the original color back to one.
So, each snowflake is a different color index and of course in the conditional formatting no matter what color you choose the conditional formatting wins it was actually kind of interesting once you unprotect and you select these and have it start snowing you'll be able to see kind of that some snowflakes actually fall into the red and then get stuck.
So, it's a, it's a fascinating it's fascinating, to watch, but if you're into VBA, it's good to go take a look and see the tricks that Jordan use so check that out again the link is right down there in the YouTube description download the file.
So, thanks to Jordan for that happy holidays to everyone is watching.
Thanks again to Jordan for that sharing that great file.
Thanks to you for stopping by.
We'll see you next time for another nercast from MrExcel.
Learn Excel form MrExcel podcast episode 1842.
Jordan Goldmeier Happy Holidays.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
This week i got a great holiday card from Excel MVP Jordan Goldmeier.
You might remember Jordan, he was on the podcast about a month ago with his great rollover trick I said Jordan this is so cool do you mind if I share it on the podcast, so we're gonna come out here to this URL you're gonna look right down there in the YouTube description to get that URL click download file, download real quick.
Now, before you open this in Excel if you've never used macros before, you're going to do Alt+T+M+S, and make sure your macro settings are disabled with notification, the second one not the first one if you have the first one, we've all better use this and then open the file in Excel.
All right, couple things make sure that you enable editing and then finally enable Macros.
Now, here this is really cool Jordan has created a holiday card where we can click let it snow and the snow starts to fall and so, you can sit there and just watch this instead of the fireplace, the cable show, shows tomorrow you can watch the snow starts to accumulate and then quit snowing and finally plow snow.
Now, this is cool to watch right, but of course for those of you into VBA, the one of the nice things is that Jordan has unprotected all the code.
The worksheet is protected, but you can un-protect it and see what's going on and so first thing the, is clever the snowflakes are actually 150 different objects and I'm typing the number one here you see that Jordan has had conditional formatting that if you put the number one, you get a red cell.
So, he's able to use conditional formatting to display the message in fact you probably come in here and change the 0's and the 1's and change the message is something else provided you stay within this range here or change the range to be something larger, but I thought what was fascinating let's go back to Alt+F11, take a look at the code a couple of things I picked up here from Jordan.
First thing he created a new type called SnowFlake that has three properties current row, current column and the color if you'll notice the there's four possible colors that appear the white is the most common, but there's also kind of two shades of blue that show up.
So, we have those properties three properties for each snowflake and then does a snow array using that type of snowflake later on we dims that re-dim that it have 150 different items.
So, now we have 150 items and Jordan is rather than checking every cell in the range which is probably what I would have done just has to check 150 items see where they are he hasn't randomly fall anywhere from one to ten cells so where's the random times 10 and then checks to see what's going on in that cell.
So, that's why sometimes you'll see that there are snowflakes that will make it through like in this range right here because if the snowflake is currently here, it might randomly fall there, might randomly fall there, randomly fall here and then make it through the L, but if you have something really really long here it's never going to make it through that.
So, this these cells will never get filled in you know so kind of interesting the algorithm that Jordan has.
I brought back to Jordan, you know this is typical at the MVP Summit the Excel team shows us something really cool and then we all pile on with new feature requests.
I'm like Jordan you should add a button here that says west wind right, that would allow the snowflakes that start to move to the right and then potentially you could get some depending on how strong the wind was.
I guess you could get some things happening there let's go back to it to the code Alt+ F11 and you know actually I realized by changing the the current column here right now, he's just doing rand if we did that random times three or random times ten, you could actually get some wind going in there.
So, take a look at the code if you're interested in VBA, plows snow all he has to do is set the original color back to one.
So, each snowflake is a different color index and of course in the conditional formatting no matter what color you choose the conditional formatting wins it was actually kind of interesting once you unprotect and you select these and have it start snowing you'll be able to see kind of that some snowflakes actually fall into the red and then get stuck.
So, it's a, it's a fascinating it's fascinating, to watch, but if you're into VBA, it's good to go take a look and see the tricks that Jordan use so check that out again the link is right down there in the YouTube description download the file.
So, thanks to Jordan for that happy holidays to everyone is watching.
Thanks again to Jordan for that sharing that great file.
Thanks to you for stopping by.
We'll see you next time for another nercast from MrExcel.
