tag:blogger.com,1999:blog-67350889858177609692024-03-14T10:29:38.770-04:00OPTION EXPLICIT VBAEVERYONE!<br>
<br>
We've moved to <a href="http://www.optionexplicitvba.com">optionexplicitvba.com.</a> Please follow future developments from the new website.Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.comBlogger51125tag:blogger.com,1999:blog-6735088985817760969.post-75378195888376984102013-07-10T10:23:00.000-04:002013-07-10T10:23:16.982-04:00We're moving!<div dir="ltr" style="text-align: left;" trbidi="on">
What can I say? <i>Blogger stinks. </i>I've slowly started moving everything from this blog to Wordpress. The new site address will be:<div>
<br /></div>
<div>
<span style="font-size: large;"><a href="http://optionexplicitvba.wordpress.com/">http://OptionExplicitVBA.WordPress.com</a></span></div>
<div>
<br /></div>
<div>
For now, I'll still be updating Blogger and letting it feed into WordPress. I'll let you know when my migration is complete. Don't worry. I plan to keep this site around so you don't have to update your bookmarks. If you link to me, this would be a good time to update your links. </div>
<div>
<br /></div>
<div>
Farewell Blogger. I'd love to tell you we had something special, but what we had was frustrating, and this breakup is long overdue. </div>
</div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com1tag:blogger.com,1999:blog-6735088985817760969.post-68560939488226865172013-05-11T23:25:00.001-04:002013-05-12T19:00:56.320-04:00Excel Fortune Cookie (…in bed?)<p><a href="http://lh4.ggpht.com/-TtVLTXnf3_8/UY8LpkaeunI/AAAAAAAAAXQ/Mab5Fh7dB0g/s1600-h/image%25255B9%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-aE0aSZJY2aE/UY8Lqe7DUsI/AAAAAAAAAXY/CXsjgX4KgLw/image_thumb%25255B5%25255D.png?imgmax=800" width="735" height="465"></a></p> <p>Here’s a fun little fortune cookie program. The fortunes are generated from a list of random words. I developed four different grammars for the fortunes, which you can find in the backend data. (Is “grammars” the right word? I’m not sure.) Some of the fortunes, like the one above, seem to make sense. Here are a few I’ve captured, which appear thoughtful and humorous, perhaps even poetic. </p> <ul> <li>The ugliest way finds evil. <li>If you satisfy this empty girl generously, you are the one who … does it even matter?. <li>Who wants the answer to be 42? The mammoth formula does! <li>Who finds true love? The beautiful water does! <li>The easy government gives up now. <li>Want the story now? Reluctantly face your family.</li></ul> <p>Most fortunes, however, come out as nonsense. For example:</p> <ul> <li>Want the head now? Frenetically melt the day.</li></ul> <p>That’s great advice. </p> <p><strong>Feeling lucky? You should. </strong></p> <p><a href="https://docs.google.com/file/d/0B1OBNnu3ZbL0dmpRbFVveVl2eFk/edit?usp=sharing" target="_blank">Fortune Cookie.xlsx</a> (If the file doesn’t download immediately from Google drive, press the black down arrow in the upper left of the screen, under the menu.)</p> Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com15tag:blogger.com,1999:blog-6735088985817760969.post-43349289923963596352013-05-10T22:32:00.001-04:002013-05-10T22:32:10.086-04:00Informal Poll: Your screen resolution of choice?<p>Most of my work is developed for wide screens monitors. How about you? I usually develop my work for 1366 x 768 resolution, but sometimes I’ll have to develop for different resolutions to accommodate my clients. <a href="http://optionexplicitvba.blogspot.com/2011/10/one-size-fits-all.html" target="_blank">Sometimes, I’ll include a fit-to-screen button that works will about 80 percent of the time.</a> </p> <p><strong>So let’s do an informal poll</strong>. In the comments section, post your screen resolution of choice – and any tips you have for accommodating resolutions different from yours. </p> <p>Also, have a great weekend!</p> Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com7tag:blogger.com,1999:blog-6735088985817760969.post-2548904525988326922013-05-04T00:26:00.001-04:002013-05-04T10:43:08.984-04:00The Excel Lab: Soundex Search<p>Every once in a while, I have a spreadsheet that sits in my ‘lab’ for way too long. At a certain point, I have to admit that I’m never going to finish it completely or do a full blog post on it. I’ve decided just to release one such spreadsheet as is. Perhaps you will be the one to carry it forward? (or, write to tell me I’ve wasted my time?)</p> <h1>Soundex Search </h1> <p>I while ago, I read about a phonetic algorithm called <a href="http://en.wikipedia.org/wiki/Soundex" target="_blank">Soundex</a>. Soundex is useful (well, there’s some argument about that) for searching through a list of surnames while compensating for common misspellings in those names. Using a <a href="http://home.utah-inter.net/kinsearch/Soundex.html" target="_blank">dummy dataset and UDF developed by Richard Yanco</a>, I created the following:</p> <p><a href="http://lh5.ggpht.com/-5YzVClNEdAA/UYSNt4dhUOI/AAAAAAAAAW0/Pf7i6ipQBpA/s1600-h/Soundex%25255B3%25255D.gif"><img title="Soundex" style="display: inline" alt="Soundex" src="http://lh3.ggpht.com/-dfwWMeuUPYs/UYSN5-q7IMI/AAAAAAAAAW8/pYK8m9qMUJE/Soundex_thumb%25255B1%25255D.gif?imgmax=800" width="628" height="446"></a></p> <p>As the animation demonstrates, you can type in your search term into the ActiveX textbox and both the listbox and graph will automatically update to show you relevant search results and corresponding values. I’ve added some additional search functionality beyond Soundex as well. For each name in the dataset, I’ve assigned a random value to plot. </p> <p>I’m rather unconvinced of the usefulness of this. I’ll put it you to see if you have any luck with it. For what it’s worth, the mechanism is formula driven. I didn’t use VBA except to set the original Soundex values for each surname (which, you could also probably do without VBA if you were so inclined.) </p> <p><strong>Download the source file:</strong></p> <p><a href="https://docs.google.com/file/d/0B1OBNnu3ZbL0NzBfX056a3RZR2M/edit?usp=sharing" target="_blank">soundex.xlsm</a></p> Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com5tag:blogger.com,1999:blog-6735088985817760969.post-56825809537380332172013-05-02T08:11:00.001-04:002013-05-02T08:11:48.228-04:00Two Great Applications of Rollovers<p>I want to highlight two applications of the rollover technique that are really quite phenomenal. </p> <h1>Square Charts and Treemaps in Excel</h1> <p>If you haven’t seen the work of the <a href="https://sites.google.com/site/e90e50/documento-plinius/frankens-team" target="_blank">Frankens Team</a> yet – you’re missing out. They’re a group of…<em>geniuses</em>, really…who investigate and push Excel’s limit. Very recently, they wrote on the development of square charts and treemaps in Excel combined with the rollover method as shown below. </p> <p><img src="http://adatkerteszet.hu/static/SquareChart.gif" width="552" height="231"></p> <p><a href="https://sites.google.com/site/e90e50fx/home/square-chart-and-treemap-in-excel" target="_blank">Read more about it, here.</a> And be sure to look through the rest of their catalogue. </p> <h1>Excel Boilerplates</h1> <p>Mark Kubiszyn of <a href="http://www.excelboilerplate.co.uk/" target="_blank">Excel Boilerplate</a> and <a href="http://kubiszyn.co.uk/">Kubiszyn.co.uk</a> used the technique in the development of two very slick boilerplate spreadsheets. His works adds a much needed streamlined process to creating and inserting new hyperlinks – as well, he’s expanded the ways in which you can link from hyperlinks. <a href="http://www.excelboilerplate.co.uk/boilerplate_documentation/index.html" target="_blank">Please do take a look at his work, here.</a> Also take a look at his <a href="https://www.youtube.com/playlist?list=PL2obB8I9aN1ZwdR2-Av1h4RJv2fepn3Un" target="_blank">YouTube videos on the subject</a>. </p> <p><img src="http://www.excelboilerplate.co.uk/boilerplate_documentation/assets/images/metro_menu_boilerplate.png" width="531" height="388"></p> <h1>Want to see more rollovers?</h1> <p><a href="http://optionexplicitvba.blogspot.com/2012/09/the-excel-rollover-mini-faq.html" target="_blank">Check out the downloadable demos section of the rollover FAQ!</a></p> Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com3tag:blogger.com,1999:blog-6735088985817760969.post-21581288160684660922013-04-21T15:24:00.001-04:002013-04-22T10:43:47.372-04:00Is Excel to Blame? (and some updates)<p>First, the updates. </p> <p>If you’re an avid follower or even a casual reader, you’ve probably noticed the formatting of this blog seems inconsistent, even amateur at times. At least, that’s how I see it. Maybe it’s me, but Blogger just doesn’t seem to correctly handle anything I want it to do. I’ve recently heard, too, that some folks have had trouble publishing comments. I don’t really know what’s going on; Blogger just sucks, I guess. I think I’m going to switch to WordPress in the future. When that day comes, I’ll let everyone know. </p> <p>Right now, I’m writing this from Windows Live Writer. Hopefully, it does a better job than blogger’s editor. We’ll see when this is posted. </p> <p>Ok, now a book update.</p> <p>My publisher told me I’ve sold 32 copies of my book on preorder. <em>Holy Crap! </em>Thanks guys. Some readers have told me Chapters 1 and 2 are already available if you preorder through the alpha program. I’ve received some really good feedback about them. Thanks to the folks who’ve read them and reached out to me. I’ll be adding a link to the book at the top of the blog. Unfortunately, because blogger sucks, the mere addition of another link will mess up my layout. Then I’ll have to spend a few hours redoing everything. Therefore, it may a while after I poste this before the link appears. Just hold tight. </p> <h1>Is Excel to Blame?</h1> <p>If you’ve been following the news recently, two major events have brought focus on the use of Excel for modeling and research. The first is the <a href="http://en.wikipedia.org/wiki/2012_JPMorgan_Chase_trading_loss" target="_blank">London Whale incident</a>, in which JPMorgan employed a spreadsheet based model for their default credit swaps. According to JPMorgan’s <a href="http://files.shareholder.com/downloads/ONE/2272984969x0x628656/4cb574a0-0bf5-4728-9582-625e4519b5ab/Task_Force_Report.pdf" target="_blank">internal report</a>, “Spreadsheet-based calculations were conducted with insufficient controls and frequent formula and code changes were made.” Specifically, </p> <blockquote> <p>…further errors were discovered in the Basel II.5 model, including, most significantly, an operational error in the calculation of the relative changes in hazard rates and correlation estimates. Specifically, after subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR, 129 although it is unclear by exactly what amount, particularly given that it is unclear whether this error was present in the VaR calculation for every instrument, and that it would have been offset to some extent by correlation changes. It also remains unclear when this error was introduced in the calculation.</p></blockquote> <p>The other event is this <a href="http://www.peri.umass.edu/236/hash/31e2ff374b6377b2ddec04deaa6388b1/publication/566/" target="_blank">this critique</a> of a paper called <em>Growth in a Time of Debt</em> by Harvard economists, Reinhart and Rogoff. (I’m not an economist by the way, so if I make mistake in what follows, feel free to let me know.) From what I gather, Reinhart and Rogoff show that countries with incredibly high public debt (in excess of 90% of gross domestic product, I think), suffer from hampered economic growth. In the critique, authors Herndon et al accuse Reinhart and Rogoff of selective exclusion and coding errors. But what’s really become headlines is the fact that Reinhart and Rogoff used Excel for their analysis. </p> <p>The argument is that Excel just isn’t a good program to use for data analysis. My response to this has been, “It’s a poor craftsmen who blames his [or her] tools.” To be fair, I actually stole this response from <a href="http://peltiertech.com/WordPress/" target="_blank">Jon Peltier</a>. I really think it concisely describes how I feel. So kudos to Jon. I used the quote in a response on <a href="http://andrewgelman.com/2013/04/16/memo-to-reinhart-and-rogoff-i-think-its-best-to-admit-your-errors-and-go-on-from-there/" target="_blank">Andrew Gelman’s blog</a>. I excerpt part of my response below because I think it’s a good lead-in to my overall point. </p> <blockquote> <p>I remember in my elementary matrix algebra class some years ago, we had to perform Guassian elimination by hand. After the first test, it became clear to me I wasn’t paying close enough attention to my operations. Sometimes, if I wrote down a number too quickly, my hand would “skip” on the sheet of paper and make it appear as if I wrote a negative number. Imagine if I told my professor, “it’s not my fault, my paper isn’t rough enough to provide the adequate friction to perform these operations.” Or: “My pencil breaks off too much graphite, which makes my mistakes too hard to detect.” Or even: “My calculator’s buttons are too close. I can’t help fat fingering operations.” Even if could show that the pen, paper, and calculator actually contributed to my making mistakes, do you think my professor would (or should) care? Let me put it to you this way: What IF Reinhart and Rogoff had used only pen and paper? Should we blame BIC for making a pen unsuitable for analysis? Should we blame Mead for making unsuitable paper? I don’t think so. Reinhart and Rogoff’s analysis was faulty because they were inattentive. That’s not Excel’s fault. <p>JPMorgan, according to their internal review, knew the potential for error in the Excel model that ultimately became the London Whale scandal. Managers had even recommended that the model be automated and audited, but never followed through. Why not? Why wasn’t the model thoroughly tested when it provided results that appeared counterintuitive? Because they liked the model’s results and its potential for big returns. <p>Reinhart and Rogoff—what makes them so different? Are we expected to believe that Excel is responsible for their not rigorously unpacking and verifying their data and model? To me, a more reasonable answer is they really liked their results; and their reviewers really liked their results; and economists looking for more ammunition against government spending loved their results. Their results meant big impact. <p>That’s my thesis: there’s too much pressure to make an impression, to be different, to be sensational. We’re less likely to be skeptical of work that plays on our own confirmation biases, especially if it’s going to be huge. That same sensationalism, by the way, is behind the blame-excel bandwagon. Here are some headlines: “Quote of the day: Excel error destroys the world” from Mother Jones; “Microsoft Excel: the ruiner of Global Economies?” from arstechnica. To me, blaming Excel feels like more causation creep. Sure, Excel was used both by JPMorgan and Reinhart and Rogoff—but so what?</p></blockquote> <h1></h1> <h1>The real problem</h1> <p>The real problem concerns how we, as a society, treat data and research. For some reason, data is given a privilege status in our society. We aren’t as skeptical of it as we should be. We believe first and question later; that is, only if things go wrong—and, at some point, they will. When our news presents a study to us, we tend to interpret the results as being hard facts and not the output of a model that is potentially as flawed as the Humans who devised it. We look to technology to solve our problems, and when technology “fails” to meet our expectations, we toss it out as if it were a consumable good, like an old cell phone. But this view of technology, as the great solver—or destroyer—of the world’s complex problems is foolhardy at best and catastrophic at worse. It removes responsibility from our institutions and companies to make something better with time, instead of simply making it newer. It removes the responsibility from us to apply rigor to our own work. </p> <p>Sensationalism drives the backlash against Excel. And don’t get me wrong, there are many things to criticize Excel for. However, that someone did not provide the necessary rigor to their own work is not Excel’s fault. In fact, while everyone is arguing over whether Excel provides the necessary information assurance, they’ve missed a recent addition to Excel’s capabilities that should have everyone concerned. </p> <p>Specifically, <a href="http://blogs.office.com/b/microsoft-excel/archive/2013/04/11/public-preview-of-geoflow-for-excel-delivers-3d-data-visualization-and-storytelling.aspx" target="_blank">Microsoft’s recently unveiled GeoFlow add-in</a>. </p> <p><img src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-44/1374.IMAGE-01.png"></p> <p>From a technology standpoint, the fluid-like movement of anything in Excel is likely to dazzle; it certainly feels impressive. But before you make a flyover tour of your data, consider that this type of visualization has lots of problems.</p> <p>According to the write up in the Office Blog, GeoFlow should allow you to: </p> <ul> <li><b>Map Data:</b> Plot more than one million rows of data from an Excel workbook, including the Excel Data Model or PowerPivot, in 3D on <a href="http://www.bing.com/blogs/Site_Blogs/b/search/archive/2013/04/11/geoflow.aspx">Bing maps</a>. Choose from columns, heat maps, and bubble visualizations. <li><b>Discover Insights:</b> Discover new insights by seeing your data in geographic space and seeing time-stamped data change over time. Annotate or compare data in a few clicks. </li></ul> <p>Discover <em>new</em> insights? No, I don’t think so. We’ve been able to place data on maps for as long as I can remember. And mapping how data changes overtime can probably be better presented using a timeseries plot. What Microsoft isn’t telling you is that plotting a column chart in a 3-d environment makes values hard to discern and occludes data from view. Virtual environments are useful when the physical environment is critical to understand the data within (like, say, in a flight simulation training program). But the data presented above is only hurt by us the use of three dimensions. Truly, what do we gain by adding a horizon which only limits data in the distance is it would in real life? Why should we choose to see less and not more? </p> <p><a href="http://www.neowin.net/news/microsoft-launches-geoflow-preview-for-excel-2013-3d-visuals-for-data" target="_blank">Neowin’s blog on GeoFlow best sums up Microsoft’s motivation</a>:</p> <blockquote> <p>Let's face it; Microsoft's <a href="http://www.neowin.net/news/microsoft-launches-geoflow-preview-for-excel-2013-3d-visuals-for-data#">Excel spreadsheet</a> program is certainly useful but <strong>it's also kind of dull to look at a flat 2D row of numbers</strong>. <strong>Isn't there some way that someone could turn these dull strings of numbers into something more exciting?</strong> Microsoft thinks the answer to that question can be found in a newly launched Excel add-on, GeoFlow. [emphasis added]</p></blockquote> <p>Let’s be concerned about <em>this</em>. Let’s not argue over which technology best allows us to forgo a certain level of rigor in case we make a mistake in our analysis. Instead, let’s focus our concern on technologies that research shows cannot deliver on what they promise. In my book, I argue that vendors are trying to remove the decision making process from us. They want us to trust their gimmicky programs and tell us that we humans are too error prone to make decisions on our own. <em>We need data visualization and dashboards to make the decisions for us</em>, they argue. Things have just gotten too big, and too complex. </p> <p>It’s true we Humans are error prone. But we can analyze our actions and intentions, and start anew. If our Excel analysis is wrong, we need the ability to unpack and analyze what’s going on. When something pretends to do the analysis for us, to show us new insights like never before, let’s be skeptical. What happens when we rely on a technology that does not allow us to apply rigor? what will happen when it doesn’t deliver? should we stop using it and throw it away? I can see why people view that as the only option.</p> <p>We should be happy that Excel doesn’t do the work for us. Because when we make mistakes, and when we learn form our mistakes, that’s progress. That’s making the world better, not just newer. </p> Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com3tag:blogger.com,1999:blog-6735088985817760969.post-69871318038152374102013-04-11T16:30:00.000-04:002013-04-20T23:31:28.962-04:00Book Updates<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7Z9eGRC3pVSIktu5h8EBr9nvZvV5_xCnnnmd1ga-4sZj0kvS2Qm3p1UsoLvyozcaOapc3Lv8CBpE46XyIO97JAa2cs2NHXIrGbdoqDZ3nQFA4m_DZIgemJOntmuTbtUNx3NiBIKP8NGI/s1600/81Ckz3-WYuL._SL1500_.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7Z9eGRC3pVSIktu5h8EBr9nvZvV5_xCnnnmd1ga-4sZj0kvS2Qm3p1UsoLvyozcaOapc3Lv8CBpE46XyIO97JAa2cs2NHXIrGbdoqDZ3nQFA4m_DZIgemJOntmuTbtUNx3NiBIKP8NGI/s320/81Ckz3-WYuL._SL1500_.jpg" width="259" /></a></div>
I figured I'd break hibernation to give everyone a small, quick update on the book. According to the original dates agreed upon in my contract with Apress, I should be close to finishing my book by now. Well, internet, <i>I'm not close at all</i>. I still have a lot more work ahead of me. The new release date is September 29th, and I do believe the book will be complete by then. As well, the name might change at least nine more times before it's finally printed. How about <i>Fifty Shades of Excel</i>?<br />
<br /><br />
<h2>
What to expect</h2>
<div>
Above all, this is a book about Excel development of dashboards and interactive reports. If you're looking for a short book that gives you just enough to make something and move on, this isn't the book for you; although, there are many good books that do this (see <a href="http://www.amazon.com/Bill-Jelen/e/B001JP1GMG/ref=ntt_athr_dp_pel_1">Bill Jelen's Mr. Excel Library</a>, <a href="http://www.amazon.com/John-Walkenbach/e/B000APG96Y/ref=sr_ntt_srch_lnk_1?qid=1365709489&sr=1-1">John Walkenbach's Mr. Spreadsheet</a> series, and <a href="http://www.amazon.com/Excel-2007-Dashboards-Reports-Dummies/dp/0470228148/ref=sr_1_1?s=books&ie=UTF8&qid=1365709826&sr=1-1&keywords=dashboard+reports+dummies+michael+alexander">Michael Alexander's Excel 2007 Dashboards & Reports</a>, for example). My book is aimed at filling what I believe is a dearth of quality printed material on the subject of Excel development for building modern tools. <i>Professional Excel Development </i>was the last latest and greatest comprehensive book on really developing tools with Excel. It is my favorite book on Excel development, and a lot of my work owes to what I've read. (There are however many terrific blogs on Excel development, which I believe is in response to the lack of print material.) </div>
<div>
<br /></div>
<div>
I hope you enjoy the book, I really do. So, in the interest of full disclosure, I do want to highlight what <b>won't</b> be in the book. As of right now, there won't be any Pivot Tables. Sorry for all you Pivot Table lovers out there. Here's the skinny: when you use Pivot Tables for dashboards, you give up a lot of control. I think I make a pretty good case in my book, but I welcome feedback if you disagree. By extension, that also means no slicers. Look, I think slicers are very, very promising concerning where Microsoft wants to take Excel, but I kind of hate them. (Oh no, I've said too much.) They take up a lot of screen real estate and encourage people to make dashboards that are 80% slicers and 20% actual information. That's not good. Also, there will no pie charts. </div>
<div>
<br /></div>
<div>
Most importantly, this book will be about empowering you as an Excel developer. A lot of people think that you can only do cool stuff in Excel by using VBA. I say, VBA is only part of the whole package. Formulas are a part of it, too. But your mind, your ability to think critically and creatively, will ultimately be the most important tool in your arsenal. Right now, there are a lot of companies who want us to pay big money for infrastructure and data visualization tools we don't need. I argue that you and I can do a better job than many of these vendors by doing the work ourselves in Excel. That is my motivation to write this book.</div>
<div>
<br /></div>
<div>
And now I bet you just can't wait to get your hands on a copy!</div>
<br />
<h2>
Pre-ordering</h2>
<div>
So if you're really,<i> really</i> interested in the getting a copy as soon as possible, there are a few options available. </div>
<div>
<br />
<h3>
Through Apress</h3>
<br />
First, if you're into the e-book thing, you can go to my <a href="http://www.apress.com/9781430249443">publisher's website</a> and order a copy through their Alpha Book program. The Alpha Book program allows you to see each chapter as they are approved by my editors. The chapters will still be in development, so if you're the type who likes giving feedback, you could email me directly with your thoughts. If you do choose to do the Alpha Book program, and you do frequently email me with solid feedback about each chapter you've read, I can maybe refund your purchase or even send you a signed copy of a print book. </div>
<div>
<br /></div>
<div>
Take the refund. I have a terrible signature. </div>
<div>
<br /></div>
<div>
<h3>
Through Amazon</h3>
</div>
<div>
You can also order the book through Amazon below its bookstore price. The discount seems to change every few weeks, but this is the lowest I've seen it so far. If you follow the link below, I will get an infinitesimal referral fee. </div>
<div>
<br /></div>
<br />
<iframe frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?lt1=_blank&bc1=FFFFFF&IS2=1&bg1=FFFFFF&fc1=000000&lc1=0000FF&t=opexvb-20&o=1&p=8&l=as1&m=amazon&f=ifr&ref=qf_sp_asin_til&asins=1430249447" style="height: 240px; width: 120px;"></iframe>
<br />
<br />
<h2 style="text-align: left;">
</h2>
<div style="text-align: left;">
***</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
More updates coming soon. Thanks again to Winston Snyder for writing his awesome articles while I've been away. If you want to contribute an article, drop me a line.</div>
</div>Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com0tag:blogger.com,1999:blog-6735088985817760969.post-13174126136312264672013-02-17T08:51:00.000-05:002013-02-17T09:01:52.686-05:00Are Your Formulas All "Wrapped" Up?The other day, there was a discussion on one of the LinkedIn Excel Groups regarding wrapping formulas in the formula bar. Today. I'll take a look at wrapping and indenting formulas complete with screen shots and a workbook you may download at the end.<br />
<br />
<h2>
<span style="color: #990000;"><u>The Data</u></span></h2>
First, I'll need some data. I use Dick Kusleika's <a href="http://dailydoseofexcel.com/archives/2010/06/17/generate-sample-data/" target="_blank">Random Data Generator</a> to get some quick data. I selected the fields I needed to generate and voila! 20K records very quickly.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgU-ElAThavx6GDufmUIA52GADkABd4WMcUbo4n1tb5XB4LMouGTFBQPUKMogJzLtiVItW6_tgVQLlG_Sfc8Dj-4R88QEA0ZHtlFilRMxtICwCKLMBkK81n1Y50sA0qYaACduEjf5ybvVyn/s1600/endimgData.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgU-ElAThavx6GDufmUIA52GADkABd4WMcUbo4n1tb5XB4LMouGTFBQPUKMogJzLtiVItW6_tgVQLlG_Sfc8Dj-4R88QEA0ZHtlFilRMxtICwCKLMBkK81n1Y50sA0qYaACduEjf5ybvVyn/s1600/endimgData.PNG" height="155" width="640" /></a></div>
<br />
I converted the entire range of data to an Excel Table. I named the table, "tblData"<br />
<br />
<h2>
<span style="color: #990000;"><u>The Analysis</u></span></h2>
To analyze the data I went to another sheet and setup some criteria fields based on the Field Headers.<br />
Think of this section as a Page Field in a Pivot table. You do not necessarily have to setup your analysis in this manner. It is just a structure I use as it makes sense to me.<br />
<br />
"Page Field" - Any criteria that is common to all formulas may be left in his section<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9t5YuObgaaI4ltAIR35wloyRo4l7JgvkQVKpKWD_Vt_s6fuqhILo2BiNapNPMbAzEAs0FDvdfOSytIQRvpZfH4Ow3etLMmhbFETYUtKHW1zQ3hW3qZZCZ-MoSiTHMFb1JiMQ86kkIw-pN/s1600/endimpPageField.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9t5YuObgaaI4ltAIR35wloyRo4l7JgvkQVKpKWD_Vt_s6fuqhILo2BiNapNPMbAzEAs0FDvdfOSytIQRvpZfH4Ow3etLMmhbFETYUtKHW1zQ3hW3qZZCZ-MoSiTHMFb1JiMQ86kkIw-pN/s1600/endimpPageField.PNG" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
"Row Labels" <br />
- Any criteria that is unique to each formula may be placed in this section<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpFrcFYi1kjgQKkFJZ8ufQUH8b5wfObVwloCXyPAiYyz4W-0K8NWNJDg609teekzwwE0zvNiHZ_5QMRr_y48xZjdkhl7QJ_IZP5l3Y4JkbfQR-zTPd8CDtph3dmBkcxIk7Skf0nSbASMVe/s1600/endimgRowLabel.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpFrcFYi1kjgQKkFJZ8ufQUH8b5wfObVwloCXyPAiYyz4W-0K8NWNJDg609teekzwwE0zvNiHZ_5QMRr_y48xZjdkhl7QJ_IZP5l3Y4JkbfQR-zTPd8CDtph3dmBkcxIk7Skf0nSbASMVe/s1600/endimgRowLabel.PNG" height="62" width="400" /></a></div>
<br />
<br />
<br />
<h2>
<span style="color: #990000;"><u>The Criteria</u></span></h2>
I entered all of the crieria in each cell that I am interested in<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWeIR_0xvD8-LGvR1TxJRS2jIYVR0xaqZ9Fp8D15IFa2FV8zLiVSvCY2IQgITsqRh-vUD8vSR4jeHstLIOU8iTIdIxXNNX6u8c1EFdKXo_hZhA_M1vyGk0xmua7eWt2tp6D4dgT_N2ba_3/s1600/endimgCriteria.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWeIR_0xvD8-LGvR1TxJRS2jIYVR0xaqZ9Fp8D15IFa2FV8zLiVSvCY2IQgITsqRh-vUD8vSR4jeHstLIOU8iTIdIxXNNX6u8c1EFdKXo_hZhA_M1vyGk0xmua7eWt2tp6D4dgT_N2ba_3/s1600/endimgCriteria.PNG" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<h2>
<span style="color: #990000;"><u>The Formula - Long Form</u></span></h2>
I wrote a formula using Excel Tables and Structured References<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjR8jGcoh49y2unTSYMArT5aRrH2rt-RJ9vt8QEcCiE8zoq3YpBuakLEEETrQzm5Prih8m28ZYo5tWpihG96qZ-iFPCdQUTNZETCAxFE295eOeEmjUTLrRdHYbbDCvSn99MEbYL9sY3aWCQ/s1600/endimgFrmlaNotWrapped.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjR8jGcoh49y2unTSYMArT5aRrH2rt-RJ9vt8QEcCiE8zoq3YpBuakLEEETrQzm5Prih8m28ZYo5tWpihG96qZ-iFPCdQUTNZETCAxFE295eOeEmjUTLrRdHYbbDCvSn99MEbYL9sY3aWCQ/s1600/endimgFrmlaNotWrapped.PNG" /></a></div>
<br />
Not too bad. But it is a little long and hard to read. The image causes us to have to scroll left right on the blog post to see the whole thing so we lose clarity of what is on the left. I'll "wrap" the formula in the formula bar to see if that clears things up.<br />
<br />
<h2>
<span style="color: #990000;"><u>The Formula - Wrapped Form</u></span></h2>
To "Wrap the formula in the formula bar, click before the text where you want to insert a break, then hold down the [ALT] key and press [ENTER]. I'm going to add a break for each Field/Criteria set so each set is on its own line in the formula bar.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgin-aE9EGdbO14aod1nMVAk4l0dSxv_a3xZjIVogZ4M6pgoAKQw_4kACY-N2RxPKAllFShb-dolgs8vSXqByW5DsLAnmoaFjPmzMBqMZD6i5Uf_B-W2dqn2nL90OItNw7K5R6luK_o1EWa/s1600/endimgFrmlaWrap1.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgin-aE9EGdbO14aod1nMVAk4l0dSxv_a3xZjIVogZ4M6pgoAKQw_4kACY-N2RxPKAllFShb-dolgs8vSXqByW5DsLAnmoaFjPmzMBqMZD6i5Uf_B-W2dqn2nL90OItNw7K5R6luK_o1EWa/s1600/endimgFrmlaWrap1.PNG" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Looking better. I'll see what it looks like if I indent each line as well. To indent a line, use the space bar to mve the text to the right.<br />
<br />
<h2>
<span style="color: #990000;"><u>The Formula - Wrapped And Indented Form</u></span></h2>
I indented all lines after the first line to line up the name of the table.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEge56hz9mdmOw6UhLSGkOmTi3hyphenhyphen49HaLH5shBCisWm0BnNajQw-q3rhszSgmvwq1h-qFMmD2CDnnahtGHzEVHgR_uAoXf_UDDER-fzW3ripDU1PTK-oYRE7-mvtHeaPX_q4LJ24tuULBwTa/s1600/endimgFrmlaWrap2.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEge56hz9mdmOw6UhLSGkOmTi3hyphenhyphen49HaLH5shBCisWm0BnNajQw-q3rhszSgmvwq1h-qFMmD2CDnnahtGHzEVHgR_uAoXf_UDDER-fzW3ripDU1PTK-oYRE7-mvtHeaPX_q4LJ24tuULBwTa/s1600/endimgFrmlaWrap2.PNG" height="219" width="320" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
I think this version is the easiest to read and understand.<br />
<br />
<h2>
<span style="color: #990000;"><u>The result</u></span></h2>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdlrGFf2_1WU2xhXLTn3rszc9end2ARA-1MrNSu9hSWK4Zk1fd3PH9GgW-Bs0apnB7wnWX_2Wq_F6dqI-XH83NzyNwvOXg0ngGL6tjXttR7d3D9EC27gga9MH_TyVDqO3TQ8eRJz5n7cI_/s1600/imgResultFinal.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdlrGFf2_1WU2xhXLTn3rszc9end2ARA-1MrNSu9hSWK4Zk1fd3PH9GgW-Bs0apnB7wnWX_2Wq_F6dqI-XH83NzyNwvOXg0ngGL6tjXttR7d3D9EC27gga9MH_TyVDqO3TQ8eRJz5n7cI_/s1600/imgResultFinal.PNG" height="324" width="640" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
I drilled in to the data, In this instance, there was only 1 line item that met the criteria so validation of the formula results in this instance was easy.<br />
<br />
Download a copy of the <a href="https://skydrive.live.com/#cid=FB86D3D43FBD123B&id=FB86D3D43FBD123B%21292" target="_blank">workbook</a><br />
<br />
Let us know how you use wrap and indent in your formulas in the comments section below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi097_mw7JOMDxdvP6D181A_QO_-lV3VM87Q-elKLCp82g4Ypr9aVbjpLBw2cMGuTc5kZuscgK8NVvMi-SNnXgGIvmq9wB_MX8Gw5w9XEUnA36lno60FB6k_7HXyjaXQVddHyUYpvTtThh8/s1600/Contact.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi097_mw7JOMDxdvP6D181A_QO_-lV3VM87Q-elKLCp82g4Ypr9aVbjpLBw2cMGuTc5kZuscgK8NVvMi-SNnXgGIvmq9wB_MX8Gw5w9XEUnA36lno60FB6k_7HXyjaXQVddHyUYpvTtThh8/s1600/Contact.PNG" height="112" width="640" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Winston Snyderhttp://www.blogger.com/profile/08697603479820895889noreply@blogger.com3tag:blogger.com,1999:blog-6735088985817760969.post-29552114412394235742013-02-08T22:15:00.000-05:002013-02-08T22:43:00.125-05:00Gangnam Cell Styles...DeleteA reader on one of the LinkedIn Excel Groups recently asked how to delete cell styles from a workbook. Today, I'll show a possible solution.<br />
<h2>
<span style="color: #990000;"><u>The Cell Styles Group:</u></span></h2>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjckzCgZUYwginCrWHEJnv8Lt3YHrnJA1wExYxT2z8KGDYiOcY072aAlS9nh54iPG-ThHmIQ6ZPGXXVxtIllpxnSn9ou6ZsbBzn0IVhdEUCBCUVYJpHtOfLUyY8NnH2ZzRnB9cusWxFvQ_4/s1600/StylesIntro.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjckzCgZUYwginCrWHEJnv8Lt3YHrnJA1wExYxT2z8KGDYiOcY072aAlS9nh54iPG-ThHmIQ6ZPGXXVxtIllpxnSn9ou6ZsbBzn0IVhdEUCBCUVYJpHtOfLUyY8NnH2ZzRnB9cusWxFvQ_4/s1600/StylesIntro.PNG" height="297" width="640" /></a></div>
<br />
<br />
<br />
<h2>
<span style="color: #990000;"><u>List Cell Styles:</u></span></h2>
I opened a workbook, Alt+F11 for the Visual Basic Editor and cooked up the code below. I then opened another workbook and made sure the first sheet was active by clicking on cell $A$1.<br />
<br />
I then ran this code:<br />
<br />
<pre style="background: rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: 100%; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">1: Option Explicit
2: Sub ListStyles()
3: 'List all styles in a workbook
4: Dim C As Range
5: Dim rng As Range
6: Dim i As Long
7: Dim lRows As Long
8: With Application
9: .ScreenUpdating = False
10: .EnableEvents = False
11: End With
12: With ActiveWorkbook
13: 'Add a temporary sheet
14: .Sheets.Add before:=Sheets(1)
15: 'List all the styles
16: For i = 1 To .Styles.Count
17: ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = _
18: .Styles(i).Name
19: Next i
20: End With
21: 'Tidy up
22: 'Destroy objects
23: Set rng = Nothing
24: Set C = Nothing
25: 'Excel environment
26: With Application
27: .DisplayAlerts = True
28: .EnableEvents = True
29: End With
30: End Sub
</code></pre>
<br />
This returned 47 different styles. I am not going to list them all here. They are included in the workbook that you may download at the end of the post. Your workbook may have more or less depending on any customizations you have already made to your workbook or if you are working on a workbook you received from someone else.<br />
<br />
Once you have a list of the styles, you may edit the list for thes styles you wish to keep or leave them all on the list if you wish to delete them all.<br />
<br />
Let's see what happens if we delete all of them, shall we? This will only impact Cell Styles. It will not impact Table Styles or Chart Styles.<br />
<h2>
<span style="color: #990000;"><u>Styles Group Before Delete:</u></span></h2>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnsMUWACgbNINzyvAAqXTpEU0PgDFq5mTShyIdz3c1m7MZci7xhuLfhYIWiHXqlWJEFecZEJFLxCnuN0cGpcwLqLkPpky1UFTQd1t-A6lX_spQ5HGzER72GZrUQIxZwovtKVq9l7jEN1aR/s1600/StylesBeforeDelete.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnsMUWACgbNINzyvAAqXTpEU0PgDFq5mTShyIdz3c1m7MZci7xhuLfhYIWiHXqlWJEFecZEJFLxCnuN0cGpcwLqLkPpky1UFTQd1t-A6lX_spQ5HGzER72GZrUQIxZwovtKVq9l7jEN1aR/s1600/StylesBeforeDelete.PNG" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<h2>
<span style="color: #990000;"><u>Delete Cell Styles:</u></span></h2>
Here's the code I am going to use to delete all cell styles from the cell styles group.<br />
<br />
<pre style="background: rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">1: Option Explicit
2: Sub ClearStyles()
3: 'Deletes all styles from the active workbook
4: Dim lRows As Long
5: Dim C As Range
6: Dim rng As Range
7: With Application
8: .ScreenUpdating = False
9: .EnableEvents = False
10: .DisplayAlerts = False
11: End With
12: 'Make sure to click on sheet with list of styles to be deleted
13: 'Assumes list begins in $A$1
14: With ActiveSheet
15: lRows = .Cells(Rows.Count, 1).End(xlUp).Row
16: Set rng = Range(.Cells(1, 1), Cells(lRows, 1))
17: End With
18: With ActiveWorkbook
19: For Each C In rng
20: On Error Resume Next
21: .Styles(C.Text).Delete
22: .Styles(C.NumberFormat).Delete
23: Next C
24: End With
25: 'Tidy up
26: 'Destroy objects
27: Set rng = Nothing
28: Set C = Nothing
29: 'Excel environment
30: With Application
31: .ScreenUpdating = True
32: .DisplayAlerts = True
33: .EnableEvents = True
34: End With
35: End Sub
</code></pre>
<br />
<h2>
<span style="color: #990000;"><u>Bam!!</u></span></h2>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBz-cNlzV3Kmqwc8-ran9JpBXhuSYKlg0lY-GsrZ2HB-KKgBYYE1HbfFdWg9BYmu-irnyUI3BkMR5RubYBznVpMcwxQXetI-OfSgDvKllcV5OQUrbsrQWAhIKBvHR3LdQ7v2hb-Yk9rkiY/s1600/StylesAfterDelete.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBz-cNlzV3Kmqwc8-ran9JpBXhuSYKlg0lY-GsrZ2HB-KKgBYYE1HbfFdWg9BYmu-irnyUI3BkMR5RubYBznVpMcwxQXetI-OfSgDvKllcV5OQUrbsrQWAhIKBvHR3LdQ7v2hb-Yk9rkiY/s1600/StylesAfterDelete.PNG" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Deleted 46 out of 47. Apparently cannot delete all styles or at least the "Normal" style.<br />
<span style="color: black;"></span><br />
<span style="color: black;">Chart styles still work</span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwivfXi_qPPM5GVwIHMsaK2DxjGvpit52GiFxiH_4tyfuIBBolgXRNrBXHmnTg5Ty69D5DrReoyqT10SoIw3L-540ML07HCSr1txbBaqOcpGwq_4Tl7iwTECz90WzzAPjFVGnxfHD20NUv/s1600/ChartOkAfterDelete.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwivfXi_qPPM5GVwIHMsaK2DxjGvpit52GiFxiH_4tyfuIBBolgXRNrBXHmnTg5Ty69D5DrReoyqT10SoIw3L-540ML07HCSr1txbBaqOcpGwq_4Tl7iwTECz90WzzAPjFVGnxfHD20NUv/s1600/ChartOkAfterDelete.PNG" height="242" width="400" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<div style="text-align: left;">
</div>
<div style="text-align: left;">
<span style="font-size: xx-small;"> </span></div>
<div style="text-align: left;">
<span style="color: black;"></span> </div>
<div style="text-align: left;">
<span style="color: black;">Excel Table styles still work</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQccKF1Dt4Ly7EizoyI8UCyysHfZK8-YNGzLDPRFN1IQ-Bi9LTCUvH7ATfe4wBZVz1dJOei-kCrzgHvza6fqdwOIR9X-nLZC72PDrdC2QfMcMdJ6tmoOnMSoVdxTj2uw9_nIYc53p0dmFr/s1600/TableOkAfterDelete.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQccKF1Dt4Ly7EizoyI8UCyysHfZK8-YNGzLDPRFN1IQ-Bi9LTCUvH7ATfe4wBZVz1dJOei-kCrzgHvza6fqdwOIR9X-nLZC72PDrdC2QfMcMdJ6tmoOnMSoVdxTj2uw9_nIYc53p0dmFr/s1600/TableOkAfterDelete.PNG" height="224" width="320" /></a></div>
<br />
<br />
<br />
<br />
You may now add any custom styles to your workbook. But please, no Gangnam Style :-)<br />
(Sorry Psi)<br />
<br />
<br />
<br />
<br />
<br />
<br />
More on Styles and VBA from <a href="http://www.jkp-ads.com/Articles/styles06.asp" target="_blank">Jan Karlel Pieterse</a><br />
Download the Cell Styles workbook <a href="https://skydrive.live.com/#cid=FB86D3D43FBD123B&id=FB86D3D43FBD123B%21290" target="_blank">here</a><br />
<br />
How do you work with Cell Styles and VBA? Let us know in the comments section.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibx2R6hZ_h9k_aemcUOGfcHrIenZ3JjHd03PQ85ry83Uh2vBp1SCgTXzXZsrFl8VC6_U8drOhgGFV5H4jSuF3Bw7J2CcDD_n1RFdoAF_v8EUWL0Q_hqeo_kLyhDDyjKbPKZFB4wb8pcmfE/s1600/Contact.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibx2R6hZ_h9k_aemcUOGfcHrIenZ3JjHd03PQ85ry83Uh2vBp1SCgTXzXZsrFl8VC6_U8drOhgGFV5H4jSuF3Bw7J2CcDD_n1RFdoAF_v8EUWL0Q_hqeo_kLyhDDyjKbPKZFB4wb8pcmfE/s1600/Contact.PNG" height="113" width="640" /></a></div>
<br />
<br />Winston Snyderhttp://www.blogger.com/profile/08697603479820895889noreply@blogger.com0tag:blogger.com,1999:blog-6735088985817760969.post-17812973783954549332013-02-02T19:08:00.001-05:002013-02-02T19:27:18.863-05:00Are You Series(ous)?<div class="separator" style="clear: both; text-align: left;">
Do you work with different series in your Excel charts?</div>
<br />
Here is what I would like the final chart to loo like. Note the white dashed lines in front of the columns:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglFkUYlPzJsWIpxFbw1eQ3ep2g5mxmBLp8ZFuF3iMPBJo8OpGW3EGhxJaLMHyOdVZUqyc3sJ09Cx_38n1WZRqLmkbzqatfCGQCi-FKBQY-zgwC3JTcwWMTdazbGvL15WLwdUnccQABbPWy/s1600/Series_AfterConvertFakeHGridLines&Format&AddMajorGridLines.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglFkUYlPzJsWIpxFbw1eQ3ep2g5mxmBLp8ZFuF3iMPBJo8OpGW3EGhxJaLMHyOdVZUqyc3sJ09Cx_38n1WZRqLmkbzqatfCGQCi-FKBQY-zgwC3JTcwWMTdazbGvL15WLwdUnccQABbPWy/s1600/Series_AfterConvertFakeHGridLines&Format&AddMajorGridLines.PNG" height="240" width="640" /></a></div>
<h4>
<span style="color: #990000;"><u>Setup:</u></span></h4>
I started with the chart I ended with in my last post, <a href="http://optionexplicitvba.blogspot.com/2013/01/points-of-interest.html" target="_blank">Points Of Interest.</a><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSyNlKrNVKGoJoAQGW8kINU7D6Gj9hZRr7ZZKRyQWM1wA68Mo81z_gxZuzMleIkcES0ylSqrOQjvC1HUgfsKFRXJzx7wXZd3_ws4FX9EQyhE5xJNcMoivRybpjl17MAEeCHMzDTMIhVCsa/s1600/Series1.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSyNlKrNVKGoJoAQGW8kINU7D6Gj9hZRr7ZZKRyQWM1wA68Mo81z_gxZuzMleIkcES0ylSqrOQjvC1HUgfsKFRXJzx7wXZd3_ws4FX9EQyhE5xJNcMoivRybpjl17MAEeCHMzDTMIhVCsa/s1600/Series1.PNG" height="239" width="640" /></a></div>
<br />
<br />
<br />
I then added some data to a separate worksheet in my workbook to replicate the horizontal gridlines.<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
The horizotal gridline scale is in units of 10, so I will use the same scale for my series' that I create for the "fake" gridlines.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiAPiuNefAbJI0cX3BXmQlphuKAiLVHiLOv_gxHsDRM3ZlK6qJWbgPwaHg2SgldTCi1JP-UIKuaG3l-wPviriy6tRnHFcAmNZ2oIwcaLUVDKCegUAWRvRYqIZ_aJPS252TgoYwkqfKzvLQ/s1600/Data_F.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiAPiuNefAbJI0cX3BXmQlphuKAiLVHiLOv_gxHsDRM3ZlK6qJWbgPwaHg2SgldTCi1JP-UIKuaG3l-wPviriy6tRnHFcAmNZ2oIwcaLUVDKCegUAWRvRYqIZ_aJPS252TgoYwkqfKzvLQ/s1600/Data_F.PNG" /></a></div>
<br />
<br />
<u></u><br />
<u></u><br />
<u></u><br />
<u></u><br />
Note in Col A that I used the exact same scale that I used for Series 1 which is what was used to plot the column charts (Really 1 chart)<br />
<br />
Also note that I put data that is used for formatting on a separate worksheet from "Value Data". This makes it easier to maintain data as it needs to updated overtime<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTRJL5VqY-cALW1NEPDjTs0UG7SDekjkB42bxn1lvf_hjRKARHNMKGP9_DzRMToHFuu4Xep8m4GRrKP8SUh0JXMGDxl8n7JOtFnwWLHAyrqYd94Njkikq8FWsit2xsk-tAmUF6CHGQiSAc/s1600/Setup1.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTRJL5VqY-cALW1NEPDjTs0UG7SDekjkB42bxn1lvf_hjRKARHNMKGP9_DzRMToHFuu4Xep8m4GRrKP8SUh0JXMGDxl8n7JOtFnwWLHAyrqYd94Njkikq8FWsit2xsk-tAmUF6CHGQiSAc/s1600/Setup1.PNG" height="183" width="640" /></a></div>
<br />
<br />
<h4>
<span style="color: #990000;"><u>1. Horizontal gridlines</u></span></h4>
I am going to add fake horizontal gridlines to the chart, so first I'l remove the current horizontal gridlines. <br />
<ul>
<li>Click on the chart (This will activate the Chart Tools Group on the Ribbon)</li>
<li>Click on "Layout" in the chart tools group</li>
<li>Click on gridlines</li>
<li>Click on Primary Horizontal Gridlines</li>
<li>Click on "None"</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBHwt1pkcejWJD5LIfG2WHDL0pnoP4m-YrIGJ8miM5UbG_fXsBDI9jfEuX99bFJy0RSPnprkm8y4FU97xwvhpwgFiIaupioEz2utbkXkwBlMXWVeS0Q_c7slbseKqLp3i0gfQxUgndKxIl/s1600/Series2.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBHwt1pkcejWJD5LIfG2WHDL0pnoP4m-YrIGJ8miM5UbG_fXsBDI9jfEuX99bFJy0RSPnprkm8y4FU97xwvhpwgFiIaupioEz2utbkXkwBlMXWVeS0Q_c7slbseKqLp3i0gfQxUgndKxIl/s1600/Series2.PNG" height="238" width="640" /></a></div>
<br />
<br />
<h4>
<span style="color: #990000;"><u>2. Add "Fake" horizontal gridlines</u></span></h4>
I copied all data from the formatting tab including the horizontal axis column, clicked on my chart and pasted the new series.<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiChairEK0Wp9b2bpKPtLoNoX8wKS8rmtGLpRqv128helARpxYkNtdwbpf6RYhwltACk6Cw8JdW6DSwpfFIjqbM1x41tK28ARXu49IHVNltkEiRdanCl4Ibjdg3zVP8etnHF_yAZWtAGlkj/s1600/Series_AfterAddFakeHGridLines.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiChairEK0Wp9b2bpKPtLoNoX8wKS8rmtGLpRqv128helARpxYkNtdwbpf6RYhwltACk6Cw8JdW6DSwpfFIjqbM1x41tK28ARXu49IHVNltkEiRdanCl4Ibjdg3zVP8etnHF_yAZWtAGlkj/s1600/Series_AfterAddFakeHGridLines.PNG" height="242" width="640" /></a> I could click on each of the new series on the chart and change the chart type from column to line and apply all of the formatting. But I would like to do it with VBA. So I'll step through each part of the process separately</div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<h4>
<span style="color: #990000;"><u>3. Change the chart type:</u></span></h4>
First I need to chage the chart type for each series that is not a value series which was series 1. So I'll loop through the SeriesCollection starting at number 2<br />
<br />
<pre style="background: rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">1: Option Explicit
2: Sub SetChartType()
3: Dim wb As Workbook
4: Dim ws As Worksheet
5: Dim i As Integer
6: Set wb = ThisWorkbook
7: Set ws = wb.Worksheets("Sheet1")
8: With ws
9: .ChartObjects(1).Activate
10: For i = 2 To .ChartObjects(1).Chart.SeriesCollection.Count
11: .ChartObjects(1).Chart.SeriesCollection(i).ChartType = xlLine
12: Next i
13: End With
14: 'Tidy up
15: Set ws = Nothing
16: Set wb = Nothing
17: End Sub
</code></pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiy91LrnloZNRsL294EoKqZjB6u5rs4p9_go6EJiJ7r3SESXi5wcAlai2nBIKBgr_zNxLlVV8Wa2Tv0EnHCrth0b9zO0m3gtdQQjA6qx0hiZEJIXGAszB217xodYWhgKIyN556dqKvDm6qN/s1600/Series_AfterConvertSeriesFrom+ColumnsToHGridLines.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiy91LrnloZNRsL294EoKqZjB6u5rs4p9_go6EJiJ7r3SESXi5wcAlai2nBIKBgr_zNxLlVV8Wa2Tv0EnHCrth0b9zO0m3gtdQQjA6qx0hiZEJIXGAszB217xodYWhgKIyN556dqKvDm6qN/s1600/Series_AfterConvertSeriesFrom+ColumnsToHGridLines.PNG" height="241" width="640" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<h4>
<span style="color: #990000;"><u>4. Format the color of the lines</u></span></h4>
I would like each of the horizontal lines to be white<br />
<br />
<pre style="background: rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">1: Option Explicit
2: Sub SetChartColor()
3: Dim wb As Workbook
4: Dim ws As Worksheet
5: Dim i As Integer
6: Dim lWhite As Long
7: Set wb = ThisWorkbook
8: Set ws = wb.Worksheets("Sheet1")
9: lWhite = RGB(255, 255, 255)
10: With ws
11: .ChartObjects("Chart 1").Activate
12: With ActiveChart
13: For i = 2 To .SeriesCollection.Count
14: .SeriesCollection(i).Select
15: With Selection
16: .Format.Line.ForeColor.RGB = lWhite
17: End With
18: Next i
19: End With
20: End With
21: 'Tidy up
22: Set ws = Nothing
23: Set wb = Nothing
24: End Sub
</code></pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEia4kmBAkkuv3LFkKipdP6B8BmwvBpPrAfBMg_D04CtxRyT2UrGEMaVXk8ejcY0pvV7z6bd1pd8ZyXFcywTqZw003gmpQaNj8AN0IxosKiXeh89Dxm9WH43HT30t523jbPSgCSEvBs3AUo2/s1600/Series_AfterAddFakeHGridLinesSetWhite.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEia4kmBAkkuv3LFkKipdP6B8BmwvBpPrAfBMg_D04CtxRyT2UrGEMaVXk8ejcY0pvV7z6bd1pd8ZyXFcywTqZw003gmpQaNj8AN0IxosKiXeh89Dxm9WH43HT30t523jbPSgCSEvBs3AUo2/s1600/Series_AfterAddFakeHGridLinesSetWhite.PNG" height="238" width="640" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<h4>
<span style="color: #990000;"><u>5. Change the line type:</u></span></h4>
The chart is looking pretty good. But the lines are a little thick, I would like something more subdued. I played around a bit with different line weights and dash styles until I found values that I liked<br />
<br />
<pre style="background: rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">1: Option Explicit
2: Sub SetLineProperties()
3: Dim wb As Workbook
4: Dim ws As Worksheet
5: Dim i As Integer
6: Dim lWhite As Long
7: Set wb = ThisWorkbook
8: Set ws = wb.Worksheets("Chart")
9: lWhite = RGB(255, 255, 255)
10: With ws
11: .ChartObjects("Chart 1").Activate
12: With ActiveChart
13: For i = 2 To .SeriesCollection.Count
14: .SeriesCollection(i).Select
15: With Selection
16: .Format.Line.Weight = 1.5
17: .Format.Line.DashStyle = msoLineRoundDot
18: End With
19: Next i
20: End With
21: End With
22: 'Tidy up
23: Set ws = Nothing
24: Set wb = Nothing
25: End Sub
</code></pre>
<br />
I added the major horizontal gridlines back to the chart and made them very thin and light grey<br />
<br />
<h4>
<span style="color: #990000;"><u>The final chart:</u></span></h4>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglFkUYlPzJsWIpxFbw1eQ3ep2g5mxmBLp8ZFuF3iMPBJo8OpGW3EGhxJaLMHyOdVZUqyc3sJ09Cx_38n1WZRqLmkbzqatfCGQCi-FKBQY-zgwC3JTcwWMTdazbGvL15WLwdUnccQABbPWy/s1600/Series_AfterConvertFakeHGridLines%2526Format%2526AddMajorGridLines.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglFkUYlPzJsWIpxFbw1eQ3ep2g5mxmBLp8ZFuF3iMPBJo8OpGW3EGhxJaLMHyOdVZUqyc3sJ09Cx_38n1WZRqLmkbzqatfCGQCi-FKBQY-zgwC3JTcwWMTdazbGvL15WLwdUnccQABbPWy/s1600/Series_AfterConvertFakeHGridLines%2526Format%2526AddMajorGridLines.PNG" height="240" width="640" /></a></div>
<br />
<br />
The lines may be a little thin for your tastes, just adjust the line weight until you get the effect you are trying to achieve.<br />
<br />
I hope you enjoy the post and that you find something of value in it.<br />
<br />
Additional resources:<br />
<ul>
<li><a href="http://chandoo.org/wp/2012/12/06/tax-burden-chart-excel/" target="_blank">Chandoo</a></li>
<li><a href="http://peltiertech.com/Excel/Charts/ArbitraryGridlines.html" target="_blank">Jon Peltier</a></li>
<li><a href="http://msdn.microsoft.com/en-us/library/office/bb241008(v=office.12).aspx" target="_blank">Chart type enumeration - Excel 2007</a></li>
<li><a href="http://msdn.microsoft.com/en-us/library/office/aa432639(v=office.12).aspx" target="_blank">MsoLineDashStyle enumeration - Excel 2007</a></li>
</ul>
How do you use the SeriesCollection in your VBA or work with series in your charts? Let us know in the comments below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiMaP7HwuRA3FR2ulISy5TYOX_Z_jl0ba5ChtSZ-OMQ8jQ7PV8e9wIfhaL2dzflPnGptM_Jt6LXV9KbFjROIMEpmgzi1EAgToZtr8kMmpjszIwTZMVHutcZ6lL7yYY5-xNECIkN8HgC8ZY7/s1600/Contact.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiMaP7HwuRA3FR2ulISy5TYOX_Z_jl0ba5ChtSZ-OMQ8jQ7PV8e9wIfhaL2dzflPnGptM_Jt6LXV9KbFjROIMEpmgzi1EAgToZtr8kMmpjszIwTZMVHutcZ6lL7yYY5-xNECIkN8HgC8ZY7/s1600/Contact.PNG" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Winston Snyderhttp://www.blogger.com/profile/08697603479820895889noreply@blogger.com0tag:blogger.com,1999:blog-6735088985817760969.post-82391133826243021102013-01-26T18:41:00.001-05:002013-01-26T21:39:22.408-05:00Points Of Interest<div class="MsoNormal" style="margin: 0in 0in 8pt;">
<span style="font-family: Arial;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Hello World! (Sorry, I could not resist <span style="font-family: Wingdings; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-char-type: symbol; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin; mso-symbol-font-family: Wingdings;"><span style="mso-char-type: symbol; mso-symbol-font-family: Wingdings;">J</span></span>) I was recently asked by our host, Jordan, if I would be interested in being a guest author here at Option Explicit VBA. I quickly and humbly accepted. I will strive to do my best to add something of value. Let’s dive right in.</span><br />
<span style="font-family: Arial;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">I was inspired the other day by Chandoo’s post on his blog in regards to </span><span style="font-family: Arial, Helvetica, sans-serif;"><a href="http://chandoo.org/wp/2012/12/06/tax-burden-chart-excel/" target="_blank">tax burden</a></span><span style="font-family: Arial, Helvetica, sans-serif;"> as well as Jared’s subsequent submission regarding </span><a href="http://chandoo.org/wp/2012/12/20/monitoring-monthly-service-levels-using-excel-charts/" target="_blank"><span style="font-family: Arial, Helvetica, sans-serif;">service levels</span></a><span style="font-family: Arial, Helvetica, sans-serif;">. Both charts use a consistent color across what appears to be different series in panel charts that are arranged closely together.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> In fact, they are not, the area charts are one series with blank rows or columns inserted in the data range to create the separated effect. Here is a sample initial column chart I created using the same concept</span></span></div>
<div class="separator" style="clear: both; margin: 0in 0in 8pt; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWExMai5cLwA0mDZpc9JhP3UICvATOdDUrUIsZCGWkurlq-CJDX7er7b8ZLruPo_9GaYXJj0oX9QDPRf1PTxdxAm4-H54IUh5XvU8QkBlYn_cZyAQMr_69711vmd8zn2yJGvSqAyM1k5V8/s1600/Points_Chart1.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWExMai5cLwA0mDZpc9JhP3UICvATOdDUrUIsZCGWkurlq-CJDX7er7b8ZLruPo_9GaYXJj0oX9QDPRf1PTxdxAm4-H54IUh5XvU8QkBlYn_cZyAQMr_69711vmd8zn2yJGvSqAyM1k5V8/s1600/Points_Chart1.PNG" /></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="font-family: Arial, Helvetica, sans-serif;"></span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="font-family: Arial, Helvetica, sans-serif;"></span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="font-family: Arial, Helvetica, sans-serif;"></span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="font-family: Arial, Helvetica, sans-serif;"></span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="font-family: Arial, Helvetica, sans-serif;"></span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="font-family: Arial, Helvetica, sans-serif;"></span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="font-family: Arial, Helvetica, sans-serif;"><div class="separator" style="clear: both; margin: 0in 0in 8pt; text-align: left;">
So far, so good - but I would like each "Series" to have a different color. I selected some data points and changed the fill color</div>
</span></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjK97gXdD0GYVFL-pMdOdeVrc53oHlrntXQZyQBOv4yPLeYOpuInW-ToHrpG2x193whGUGt17-4Bv4TwxwQrpLRXIYHuUshWxTF4ZFSoVwmCtxSBVfjI150WAbgen5FjJJ3TiFpMF4Gvmdk/s1600/Points_Chart2.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjK97gXdD0GYVFL-pMdOdeVrc53oHlrntXQZyQBOv4yPLeYOpuInW-ToHrpG2x193whGUGt17-4Bv4TwxwQrpLRXIYHuUshWxTF4ZFSoVwmCtxSBVfjI150WAbgen5FjJJ3TiFpMF4Gvmdk/s1600/Points_Chart2.PNG" /></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br />
<br style="clear: both;" />Looking good, but I'll need to manually select an additional 22 data points and change the fill color for each point. It gets worse if I want to add additional, "Series" to the chart or decide to go back and change a color - more manual work!<br />
<br />
So I thought to myself, "Self, there must be an easier way!" The good news is that there is an easier way through VBA! Let's cook up some code (Option Explicit VBA - Remember?)<br />
<br />
I only have one ChartObject with one SeriesCollection, so that part is straight forward. But there are many points in the SeriesCollection to be considered. Additionally, I dont want to plot anyting or add color to anything for points 13 and 25 where I have blank rows in my data.<br />
<br />
So, I want to do something with points 1-12, 14-25, 27-38. Sounds like a good candidate for a Select Case..Case..End Select structure.</span><br />
<span style="font-family: Arial;"></span><br />
<span style="font-family: Arial;"></span><br />
<span style="font-family: Arial;"><pre style="background: rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
Option Explicit
1: Sub ColorDataPoints()
2: Dim wb As Workbook
3: Dim ws As Worksheet
4: Dim i As Integer
5: Dim lBlue As Long
6: Dim lRed As Long
7: Dim lGreen As Long
8: Set wb = ThisWorkbook
9: Set ws = wb.Worksheets("Sheet2")
10: lBlue = RGB(79, 129, 189)
11: lRed = RGB(192, 0, 0)
12: lGreen = RGB(155, 187, 89)
13: With ws
14: For i = 1 To .ChartObjects(1).Chart.SeriesCollection(1).Points.Count
15: Select Case i
16: Case 1 To 12
17: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lBlue
18: Case 14 To 25
19: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lRed
20: Case 27 To 38
21: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lGreen
22: End Select
23: Next i
24: End With
25: 'Tidy up
26: Set ws = Nothing
27: Set wb = Nothing
28: End Sub
</code></pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHdBymfSsPRpEN_u0hqwCznUYsBl2Da-1ZIUmP3j8BifFZeJGiilb0RGUg8KWsTb06b5waGbpE1cxmtVzn07MMYFNDAk_JEIdF30BuyYEE95PgjdtxSmbdTDHfvDLMLBieVnviDiQ67gOJ/s1600/Points_Chart3.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHdBymfSsPRpEN_u0hqwCznUYsBl2Da-1ZIUmP3j8BifFZeJGiilb0RGUg8KWsTb06b5waGbpE1cxmtVzn07MMYFNDAk_JEIdF30BuyYEE95PgjdtxSmbdTDHfvDLMLBieVnviDiQ67gOJ/s1600/Points_Chart3.PNG" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Now I have a chart with one x-axis and what appears to be 3 different series, when in fact, it is one. Perhaps more importantly, I have a process that requires very little updating as my needs change to display more "Series" or to change colors.<br />
<span style="font-family: Arial;"></span><br />
<span style="font-family: Arial;">More on the <a href="http://msdn.microsoft.com/en-us/library/office/aa174284(v=office.11).aspx" target="_blank">Points Collection</a>.</span><br />
<span style="font-family: Arial;"></span><br />
<span style="font-family: Arial;">Download the <a href="https://skydrive.live.com/#!/edit.aspx?cid=FB86D3D43FBD123B&resid=FB86D3D43FBD123B%21288&app=Excel" target="_blank">workbook</a> .</span><br />
<span style="font-family: Arial;"></span><br />
<span style="font-family: Arial;">How do you work with points in your charts and VBA? Let us know in the comments section.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrYVX2dM6fplbxxbxMKVz2StCH_YYmAp6mdF7OAyEiKWNEhsQ7v6yHql62dkfPWQxfCp3m21I-aDDG8l6Ha7jaPDfIp-ZFawJIhS_0DUE2CTCwBbZ8TN9WWSHBGPT0AdUGFpKiAeUyBFlX/s1600/Contact.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrYVX2dM6fplbxxbxMKVz2StCH_YYmAp6mdF7OAyEiKWNEhsQ7v6yHql62dkfPWQxfCp3m21I-aDDG8l6Ha7jaPDfIp-ZFawJIhS_0DUE2CTCwBbZ8TN9WWSHBGPT0AdUGFpKiAeUyBFlX/s1600/Contact.PNG" /></a></div>
</span><br />Winston Snyderhttp://www.blogger.com/profile/08697603479820895889noreply@blogger.com0tag:blogger.com,1999:blog-6735088985817760969.post-79175865635962613562012-12-24T17:54:00.001-05:002013-01-03T17:45:32.436-05:00Directly link Excel form controls to backend data with dynamic references<div dir="ltr" style="text-align: left;" trbidi="on">
Form controls are great for reporting information about groups of items, like a list of programs or accounts. They are often used on Excel dashboards and reports that demand interactive capabilities. One such type of capability provides the user with a list of items to choose from. When the user makes a selection, a macro is executed that populates a table holding referenced values. Those values are linked to a series of form controls on the frontend. This interactivity is displayed below:<br />
<div>
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlhVNommFoHXaaRj4N5d5B9BpUWF3iXy3ONr8od4Ds67B0QxEBIw9mOyMyb0jOAqSptEK-ZdydaoH10YedaOr80TJMNtbl7jXcmuLrYjckxtjfzku0sGZjjUdCZMDPCDnGU33ahP4PS2I/s1600/Step1.png" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlhVNommFoHXaaRj4N5d5B9BpUWF3iXy3ONr8od4Ds67B0QxEBIw9mOyMyb0jOAqSptEK-ZdydaoH10YedaOr80TJMNtbl7jXcmuLrYjckxtjfzku0sGZjjUdCZMDPCDnGU33ahP4PS2I/s1600/Step1.png" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Step 1</td></tr>
</tbody></table>
<div>
<br />
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUKyi-AdUgG-aQO_1orHOeKkFfVVpvoODtPhlCErhMjmRqeTfUMXePPLdglv6vwPBmqs_fys4aSLVHDkJnE4o_O5dfagR5H55ST6rzUu4keCVJbXTFnMCHthnOP0G6ZgoRcJJDwZtO5Co/s1600/Step2.png" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUKyi-AdUgG-aQO_1orHOeKkFfVVpvoODtPhlCErhMjmRqeTfUMXePPLdglv6vwPBmqs_fys4aSLVHDkJnE4o_O5dfagR5H55ST6rzUu4keCVJbXTFnMCHthnOP0G6ZgoRcJJDwZtO5Co/s1600/Step2.png" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Step 2</td></tr>
</tbody></table>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFjgTAnVSu4Epg9IvYpS7M_WnGuX02rKWbCYBk0MuYcCVE8TWJGATGxiI_TRGig0KSanUMrikdjAqqHNtgBfdJDIHEvEv6udufNPqzsiEg7641oOsZ2o0Nl71jH3fbgd2YAg6YrvzI7ao/s1600/Step3.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFjgTAnVSu4Epg9IvYpS7M_WnGuX02rKWbCYBk0MuYcCVE8TWJGATGxiI_TRGig0KSanUMrikdjAqqHNtgBfdJDIHEvEv6udufNPqzsiEg7641oOsZ2o0Nl71jH3fbgd2YAg6YrvzI7ao/s1600/Step3.png" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Step 3<br />
<br />
<div style="text-align: left;">
</div>
</td></tr>
</tbody></table>
<div>
There is a final step, which I haven't included. The user would make changes to the project under the Options table. They would press a "Save" button and their changes would be copied from the Linked Values table back onto the backend data in the column corresponding to the selected project using VBA.<br />
<br />
<b>The No-VBA way</b><br />
<br />
There's nothing wrong with this method in and of itself, but I want to propose a method that requires no VBA. The advantage of this new method is that it links directly to the data itself and bypasses the need for the Linked Values table. We can do this by allowing the form controls to take advantage of dynamic references.<br />
<br />
Typically, form controls can only do direct, absolute references. You cannot, for example, use VLOOKUP or INDEX within the source field of a form control. However, you can use a named ranges.<br />
<br />
<b>Let's do it!</b><br />
<br />
First, we give that 'index' field above a named. How about <b>selection</b>? Next, we create four named ranges to correspond to the form control checkboxes. Stage_1 to Stage_4 are those new named ranges. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisoRBKwRKtUWsuZxkim-Et-qEQouTe-wetVLxKqbDhI_K447cLdGlEhhgiGMP1cQZ_smkbpMaS4sCU7numvWo0ydr9EqAieOQ3lJSB-BvtzQkIZdM4qdLCu4Ny9sQGDLPADDgzJ_oCSuE/s1600/Named+References.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisoRBKwRKtUWsuZxkim-Et-qEQouTe-wetVLxKqbDhI_K447cLdGlEhhgiGMP1cQZ_smkbpMaS4sCU7numvWo0ydr9EqAieOQ3lJSB-BvtzQkIZdM4qdLCu4Ny9sQGDLPADDgzJ_oCSuE/s1600/Named+References.png" /></a></div>
As you can see from the picture, I use the fourth row to connect to checkbox Stage 4 and the <b>selection</b> value to inform Excel to pull from the fourth column in the backend data (which is Project 4, if you recall).<br />
<br />
Finally, I can simply link these named ranges to their associated checkboxes:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZ-vcHDW8Z7J5IKzsA8HhLkXQB4vyDeoPtrGtMu3ucLv1jH3RuyFxKxJBYahsLCBLR3UIc1Dfl0UbKHWQSbM9XUI3PkX9CyDBnt9a9aUgrefOuhgIPP6tYo_n0fDGpwdLmMUkvkdi6v24/s1600/Check+box+link.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZ-vcHDW8Z7J5IKzsA8HhLkXQB4vyDeoPtrGtMu3ucLv1jH3RuyFxKxJBYahsLCBLR3UIc1Dfl0UbKHWQSbM9XUI3PkX9CyDBnt9a9aUgrefOuhgIPP6tYo_n0fDGpwdLmMUkvkdi6v24/s1600/Check+box+link.png" /></a></div>
<br />
Using this method, changes to the checkbox automatically change the backend data. There is no intermediate table required -- like the Linked Values table above -- to interface between the frontend and the backend.<br />
<br />
<br />
That's all for now - have a happy and health holiday season! <br />
<br />
<b>Update 25 December:</b><br />
Make sure to see the download file - <a href="https://docs.google.com/open?id=0B1OBNnu3ZbL0cEhOa1NRUjhlQmM">Direct Links.xlsm</a>.</div>
</div>
</div>
</div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com0tag:blogger.com,1999:blog-6735088985817760969.post-68075738515259469022012-11-28T22:29:00.002-05:002012-11-29T22:14:04.356-05:00CUBE: The Rollover Method Returns! Spin a cube in a 3d environment with your mouse! <div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhapJrGUkrnBmGShnTTsfXXrJPirAU5sZ_AwXK1dnihyQqAnONT6-HzJi4JH5GH8aHKfMpIHY8hxmozKRkCCr6h_WADWJkqo736GgMe1Kb9bBZEFq0mCCVYvs2R5Ufu2orkxJu-MiXY9TU/s1600/Microsoft+Excel+-+Rotate+Cube+Rollover.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhapJrGUkrnBmGShnTTsfXXrJPirAU5sZ_AwXK1dnihyQqAnONT6-HzJi4JH5GH8aHKfMpIHY8hxmozKRkCCr6h_WADWJkqo736GgMe1Kb9bBZEFq0mCCVYvs2R5Ufu2orkxJu-MiXY9TU/s200/Microsoft+Excel+-+Rotate+Cube+Rollover.png" width="186" /></a></div>
Ok, so I know I promised no more updates, but this one is too cool. Basically, I <a href="http://www.andypope.info/charts/3drotate.htm">combined Andy Pope's 3d rotation tutorial</a> with the <a href="http://optionexplicitvba.blogspot.com/2012/09/the-excel-rollover-mini-faq.html">rollover method</a>. The result is a free-floating cube which you can rotate by passing your mouse over it. Here's a choppy-ish video of what's going on. I promise however that it run smoothly on your computer. The choppiness is a result of the video's frame rate.<br />
<div>
<br /></div>
<div>
<!-- copy and paste. Modify height and width if desired. -->
<object data="http://content.screencast.com/users/jpo645/folders/Jing/media/b138a09f-a7bc-449b-b16f-b48aea132123/jingswfplayer.swf" height="481" id="scPlayer" type="application/x-shockwave-flash" width="456">
<param name="movie" value="http://content.screencast.com/users/jpo645/folders/Jing/media/b138a09f-a7bc-449b-b16f-b48aea132123/jingswfplayer.swf" />
<param name="quality" value="high" />
<param name="bgcolor" value="#FFFFFF" />
<param name="flashVars" value="thumb=http://content.screencast.com/users/jpo645/folders/Jing/media/b138a09f-a7bc-449b-b16f-b48aea132123/FirstFrame.jpg&containerwidth=456&containerheight=481&content=http://content.screencast.com/users/jpo645/folders/Jing/media/b138a09f-a7bc-449b-b16f-b48aea132123/2012-11-28_2214.swf&blurover=false" />
<param name="allowFullScreen" value="true" />
<param name="scale" value="showall" />
<param name="allowScriptAccess" value="always" />
<param name="base" value="http://content.screencast.com/users/jpo645/folders/Jing/media/b138a09f-a7bc-449b-b16f-b48aea132123/" />
Unable to display content. Adobe Flash is required.
</object>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
The mechanism is similar the the <a href="http://optionexplicitvba.blogspot.com/2012/10/interactive-united-states-2012.html">election map</a> and the <a href="http://optionexplicitvba.blogspot.com/2012/08/details-on-demand-bring-up-details-on.html">details-on-demand</a> graphs: the chart is placed on top of the hot spots to get the mouse's coordinates. In this case, the rollover pulls in the mouse's location and initiates a timer. The timer adjusts the rotation angles based on the mouse's location. Additionally, some fancy calculations in the timer process procedure give the rotation a "slowing down" effect. I urge you to open on the Visual Basic Editor and do some dissection, if you desire. Remember though, the Timer API is a bit finicky. Make sure to save often in case your Excel crashes while in the middle of your investigations.<br />
<br />
The download file: <a href="https://docs.google.com/open?id=0B1OBNnu3ZbL0NlFxM1ZPcm1EWms">Rotate Cube Rollover.xlsm</a><br />
<div>
<br />
Have fun.</div>
<div>
<br /></div>
</div>
</div>
<iframe allowfullscreen="allowfullscreen" frameborder="0" height="315" src="http://www.youtube.com/embed/4aGDCE6Nrz0" width="420"></iframe></div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com1tag:blogger.com,1999:blog-6735088985817760969.post-75858478696981983632012-11-26T22:05:00.001-05:002012-11-26T22:05:42.833-05:00Taking a small break<div dir="ltr" style="text-align: left;" trbidi="on">
These last few months have been great to this humble blog - and to me, your humble blogger. We've received a lot of traffic, especially for posts concerning the <a href="http://optionexplicitvba.blogspot.com/2012/09/the-excel-rollover-mini-faq.html">rollover method</a> (40k pageviews overall as of today!). Also, I got married in October! <div>
<br /></div>
<div>
In pains me to tell you this, but I'm taking a break until next year (you probably already noticed my absence). In August, I signed on with Apress publishing to write a book about developing Excel applications and dashboards. I'll be honest with you - I'm woefully behind schedule, and I really need to catch up. Writing this book has been a dream of mine. So I really need to focus on it. </div>
<div>
<br /></div>
<div>
In my last post, I said a tutorial on how I created the Election map would be forthcoming. At this rate, I'm not sure when that will be. Thankfully, Robert Mundigl of <a href="http://clearlyandsimply.com/">ClearlyAndSimply.com</a> has written about the very same technique the Election map uses in his phenomenal article, <a href="http://www.clearlyandsimply.com/clearly_and_simply/2012/11/roll-over-tooltips-and-web-actions-on-a-microsoft-excel-dashboard.html">Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard</a>. I can't really thank Robert enough for this. His article is a pleasure to read (as are his other articles) with explanations that are thorough and thoughtful; certainly, they surpass anything I've ever written on the subject. So, if you haven't read <a href="http://www.clearlyandsimply.com/clearly_and_simply/2012/11/roll-over-tooltips-and-web-actions-on-a-microsoft-excel-dashboard.html">Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard</a>, please make your way there as soon as possible. </div>
<div>
<br /></div>
<div>
Another person I need to mention is Bert van Zandbergen who has been my silent partner-in-crime concerning investigations into the rollover method. He has toyed and tampered with the rollover method producing spreadsheets that are different but always interesting. I mean, just take a look at these:</div>
<div>
<ul>
<li><a href="https://docs.google.com/open?id=0B1OBNnu3ZbL0RllmTGk1ZXJZSEk">Dynamic Circular Arrow</a></li>
<li><a href="https://docs.google.com/open?id=0B1OBNnu3ZbL0RUNJVkI4MldDaU0">Dynamic Half-Filled Circles</a></li>
</ul>
</div>
You can contact Bert at: klvzndbrgn (at) gmail.<div>
<br /></div>
<div>
Finally, the people I really need to thank are you, my readers. This blog wouldn't be what it is without you. </div>
<div>
<br /></div>
<div>
I won't be gone forever. My facebook and twitter will be alive again soon. Make sure to check them out.</div>
<div>
<br /></div>
<div>
Oh yeah, and a happy holidays to everyone :)</div>
</div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com1tag:blogger.com,1999:blog-6735088985817760969.post-36486738344037551842012-10-02T21:06:00.003-04:002012-10-11T19:19:10.430-04:00Interactive United States 2012 Presidential Scoreboard in Excel <div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: left;">
<object data="http://content.screencast.com/users/jpo645/folders/Jing/media/c63fbfd1-690d-4daf-be09-cd074c2ac566/jingswfplayer.swf" height="523" id="scPlayer" type="application/x-shockwave-flash" width="1058">
<param name="movie" value="http://content.screencast.com/users/jpo645/folders/Jing/media/c63fbfd1-690d-4daf-be09-cd074c2ac566/jingswfplayer.swf" />
<param name="quality" value="high" />
<param name="bgcolor" value="#FFFFFF" />
<param name="flashVars" value="thumb=http://content.screencast.com/users/jpo645/folders/Jing/media/c63fbfd1-690d-4daf-be09-cd074c2ac566/FirstFrame.jpg&containerwidth=1058&containerheight=523&content=http://content.screencast.com/users/jpo645/folders/Jing/media/c63fbfd1-690d-4daf-be09-cd074c2ac566/2012-10-02_2047.swf&blurover=false" />
<param name="allowFullScreen" value="true" />
<param name="scale" value="showall" />
<param name="allowScriptAccess" value="always" />
<param name="base" value="http://content.screencast.com/users/jpo645/folders/Jing/media/c63fbfd1-690d-4daf-be09-cd074c2ac566/" />
Unable to display content. Adobe Flash is required.
</object></div>
<br />
Alright, here's another map-based rollover. The instructions are pretty simple: click on a state to toggle through its party selection. Click on one of the three selections above to change scenarios.<br />
<br />
In the next month or so, I'll be putting together a tutorial on this. But in the meantime, have some fun. Download. Ask questions if you need.<br />
<br />
For more on these types of maps, checkout:<br />
<a href="http://www.clearlyandsimply.com/clearly_and_simply/2009/06/choropleth-maps-with-excel.html">Choropleth Maps with Excel</a><br />
<br />
Download here:<br />
<a href="https://docs.google.com/open?id=0B1OBNnu3ZbL0cXhSb0dGbm50ZU0">Election Map.xlsm</a><br />
<br />
How about another screenshot?<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQKQrHPGIVDfKSpIB1lzsghJ5TfDnkKHrKUPvgCW7yjIqSDijrbzx505ewRBE6MfrwssIqWD63s_H0bRws_6O6ahPZLpedXWD9P5HHsNLAnE_Vk0shW3iIbB_-6H_eXTPlnBhC6JFoGZI/s1600/Microsoft+Excel+-+Election+Map_2012-10-03_08-14-33.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQKQrHPGIVDfKSpIB1lzsghJ5TfDnkKHrKUPvgCW7yjIqSDijrbzx505ewRBE6MfrwssIqWD63s_H0bRws_6O6ahPZLpedXWD9P5HHsNLAnE_Vk0shW3iIbB_-6H_eXTPlnBhC6JFoGZI/s1600/Microsoft+Excel+-+Election+Map_2012-10-03_08-14-33.jpg" /></a></div>
</div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com11tag:blogger.com,1999:blog-6735088985817760969.post-40462091998995233932012-09-27T14:54:00.001-04:002012-09-27T14:54:34.581-04:00Recommendation: Excel School + Excel Hero Academy Bundle<div dir="ltr" style="text-align: left;" trbidi="on">
Chandoo (of <a href="http://chandoo.org/">Chandoo.org</a>) and Daniel Ferry (of <a href="http://excelhero.com/">ExcelHero.com</a>) are kicking-off <a href="http://chandoo.org/wp/2012/09/25/introducing-excel-school-excel-hero-academy-bundle/">their new Excel School and Excel Hero Academy</a>. If you <i>really want</i> to learn Excel—and I mean <i><b>learn</b></i> Excel—I recommend their new courses. <br />
<br />
It’s true; I’ve never taken a course from either of them. However, much of what you see on this website is a direct result of their work, their tutorials, their fearless trailblazing into Excel’s unknown. All of my work (not just the stuff on this blog, but my professional work too) owes a great debt to both Chandoo.org and ExcelHero.com. I can only imagine the great work they do online is expanded in their classes. In all honesty, I would sign up too if I wasn’t so pressed for time in the short term. But if <i>you</i> have an interest in learning Excel and taking it to the next level, I can’t think of two better people to teach you how. Believe me.<br />
<br />
I’m not a paid spokesperson. I won’t receive anything for referring you. In fact, unless Chandoo or Daniel views this blog article (here’s hoping I’m important enough for them to follow!), they won’t even know I’ve made a recommendation. But I believe that strongly in their work.<br />
<br />
So sign up for the <a href="http://chandoo.org/wp/2012/09/25/introducing-excel-school-excel-hero-academy-bundle/">Excel School & Excel Hero Academy Bundle</a>. I think it’s well worth the money. </div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com0tag:blogger.com,1999:blog-6735088985817760969.post-84253865850104702562012-09-20T13:27:00.000-04:002012-10-11T19:22:30.319-04:00Handling Rollover Clicks Without Using the Worksheet_SelectionChange Event<div dir="ltr" style="text-align: left;" trbidi="on">
You probably noticed a "bug" while playing around with my <a href="http://optionexplicitvba.blogspot.com/2012/06/period-table-of-elements-in-excel.html">Interactive Periodic Table of Elements in Excel</a>. When you click down into the Table of Elements (or really, anywhere on the sheet), one of the chemical classifications will become "selected," despite your not having clicked on it directly. This happens because I've used the ScrollArea property to set the clickable bounds of the spreadsheet to just the range containing the chemical classifications. When you click on one of those classifications, Excel handles the "click" through the Worksheet_SelectionChange event. So even if you don't click into any part of the clickable area defined by the Worksheet's ScrollArea property, Excel can still fire the Worksheet_SelectionChange event.<br />
<br />
And then there's this other bug, too: If you try to click on one of those classifications more than once (without clicking onto something else first), you'll notice that nothing happens. This is because your "selection" hasn't changed (that black selector is still in the same spot).<br />
<br />
Such are the problems when using the Worksheet_SelectionChange to handle rollover interactions. So the fix? <i>Don't use the Worksheet_SelectionChange.</i><br />
<i><br /></i>
As Chandoo demonstrates in his <a href="http://chandoo.org/wp/2012/08/14/bolt-vs-rest-excel-visualization/">One race, Every medalist ever – Interactive Excel Visualization</a> article, the rollover UDF can take ranges as input parameters. Take a look at this sample UDF below:<br />
<br />
<code>
Public Function RolloverSelection(Index As Integer, curRange As Range)<br />
'....<br />
<br />
End Function<br />
<br />
</code>
<br />
Notice that the second parameter will take a range as its argument. That means on my Excel spreadsheet, in cell D5, I could write something like this cell for my rollover formula:<br />
<br />
<code>=IFERROR(HYPERLINK(RolloverSelection(2, <span style="background-color: #f4cccc;">D5</span>),4),4)</code>
<br />
<br />
See that highlighted portion? I'm just passing in a reference to the cell that holds the formula.<br />
<i><br /></i>
<i>Ok, so where am I going with all of this?</i> Well, remember that whenever we click on a "clickable" cell (that is, a cell allowed within the desired ScrollArea), the black selector changes. But how then do we find if the the selector has changed its location? We use the Selection object, of course! Thus, to test if the user has clicked onto one of our rollovers, we simply test if the address of the curRange (that's D5 above) is the same as the address of the selector. In other words:<br />
<br />
<code>
Public Function RolloverSelection(Index As Integer, curRange As Range)<br />
<br />
'Do other stuff here<br />
<br />
If curRange.Address = Selection.Address Then<br />
<br />
'Handle clicks inside of here<br />
<br />
End If<br />
<br />
End Function<br />
<br />
</code>
<br />
Using the above function, we are able to fix both problems described that stem from using the Worksheet_SelectionChange event. In addition, we provide an even more robust mechanism for handling user clicks.<br />
<br />
***<br />
<br />
So here's the funny story surrounding this post. I was updating a dashboard I had worked on previously this year. As I was trying to become once again acclimated to my previous work, I couldn't understand how my code was handling user clicks. There was no code in my Worksheet_SelectionChange. So after some investigation, I saw the code above and it all came back to me. But holy crap, I must have been on something. I didn't even remember to use it for the Periodic Table of Elements. Sheesh. </div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com0tag:blogger.com,1999:blog-6735088985817760969.post-5854955713270738962012-09-19T16:01:00.002-04:002012-09-19T16:15:45.809-04:00Investigations in "Illegal Activities" with User Defined Functions<div dir="ltr" style="text-align: left;" trbidi="on">
A group of fellow Excel experts who call themselves the <a href="https://sites.google.com/site/e90e50/documento-plinius/frankens-team">Frankens Team </a>were encouraged by the <a href="http://optionexplicitvba.blogspot.com/2011/04/rollover-b8-ov1.html">rollover method</a> to investigate other known instances in which Excel has allowed for the impossible; namely, allowing UDFs to change the value of other cells.<br />
<br />
I invite everyone to view their terrific analysis: <a href="https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell">Excel formula to change the value of another cell?</a><br />
<br />
Also, take a look around the site (which is mostly in Italian - so get that Google translator ready!) for some great Excel stuff, like <a href="https://sites.google.com/site/e90e50fx/home/creative-and-advanced-chart-design-in-excel">Creative and advanced chart design in Excel</a>.<br />
<br />
By the way, what would <i>you</i> call this type of chart?<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/iM5cjGAwlUxgz57wwAANGA33uATIxShMJVIo76wp3UR3dgFDEGmpHzl7QAKSVbGWhkQAYCFb-RmtHbK6PJj4ZCybfcCO2jI0tyG25vOmSNJzIumhDo4" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="268" src="http://1.bp.blogspot.com/iM5cjGAwlUxgz57wwAANGA33uATIxShMJVIo76wp3UR3dgFDEGmpHzl7QAKSVbGWhkQAYCFb-RmtHbK6PJj4ZCybfcCO2jI0tyG25vOmSNJzIumhDo4" width="400" /></a></div>
<div style="text-align: center;">
Courtesy E90E50 fx</div>
:)</div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com0tag:blogger.com,1999:blog-6735088985817760969.post-51009508225238788322012-09-16T21:23:00.000-04:002013-03-14T11:11:24.232-04:00The Excel Rollover Mini FAQ<div dir="ltr" style="text-align: left;" trbidi="on">
As more people are use the rollover method (or “Interactive Hyperlinks” as Chandoo calls them), I thought it might be helpful to answer common questions I've seen popping up on forums. So, in no particular order, I present the Excel Rollover Mini FAQ!<br />
<div class="MsoNormalCxSpMiddle">
<o:p></o:p></div>
<div class="MsoNormalCxSpMiddle">
<br /></div>
<h2>
<b>1. Are there example articles and spreadsheets on how to use Excel rollovers?</b></h2>
<div class="MsoNormalCxSpMiddle">
<b><br />
</b></div>
<div class="MsoNormalCxSpMiddle">
Here’s a list: </div>
<div class="MsoNormalCxSpMiddle">
<b>Articles and Tutorials</b></div>
<a href="http://optionexplicitvba.blogspot.com/2011/04/rollover-b8-ov1.html">How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell</a> by Me<br />
<a href="http://optionexplicitvba.blogspot.com/2012/06/how-to-highlighting-cells-using.html">How to: highlighting cells using the rollover technique in Excel</a> by Me<br />
<a href="http://chandoo.org/wp/2011/07/20/interactive-dashboard-using-hyperlinks/">Interactive Dashboard in Excel using Hyperlinks</a> by Chandoo<br />
<div>
<a href="http://chandoo.org/wp/2011/07/25/video-on-interactive-dashboard-using-hyperlinks/">Video Tutorial on Interactive Dashboard using Hyperlinks</a> by Chandoo</div>
<div>
<a href="http://www.get-digital-help.com/2012/08/07/use-a-mouse-hovering-technique-to-create-an-interactive-chart/">Use a mouse hovering technique to create an interactive chart</a> by Oscar of Get-Digital-Help.com</div>
<a href="http://optionexplicitvba.blogspot.com/2012/08/needs-more-rollover-quick-tip.html">Needs More Rollover: Quick Tip!</a> by Me<br />
<div>
<a href="http://monsieur-excel.blogspot.fr/2012_07_01_archive.html">Une macro sensible à la souris</a> by Monsieur Excel (this one is in French!)<br />
<a href="http://optionexplicitvba.blogspot.com/2012/09/handling-rollover-clicks-without-using.html">Handling Rollover Clicks Without Using the Worksheet_SelectionChange Event</a> by Me<br />
<a href="http://www.clearlyandsimply.com/clearly_and_simply/2012/11/roll-over-tooltips-and-web-actions-on-a-microsoft-excel-dashboard.html">Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard</a> by Robert Mundigl of ClearlyAndSimply.com<br />
<div>
<div>
<div class="MsoNormalCxSpFirst">
<b><br /></b>
<b>Downloadable Demos</b><br />
<a href="http://optionexplicitvba.blogspot.com/2012/10/interactive-united-states-2012.html">Interactive United States 2012 Presidential Scoreboard in Excel</a> by Me</div>
<a href="http://chandoo.org/wp/2012/08/14/bolt-vs-rest-excel-visualization/">One race, Every medalist ever – Interactive Excel Visualization</a> by Chandoo</div>
<div>
<a href="http://optionexplicitvba.blogspot.com/2012/08/interactive-map-in-excel-using-rollovers.html">Interactive Map in Excel using Rollovers</a> by Me</div>
<div>
<a href="http://optionexplicitvba.blogspot.com/2012/08/guest-post-rollover-for-months-and-years.html">Guest Post: Rollover for Months and Years</a> by Bert van Zandbergen (klvzndbrgn (at) gmail <span style="background-color: white; color: #555555; font-family: arial, sans-serif; font-size: 13px; white-space: nowrap;">)</span></div>
<div>
<a href="http://optionexplicitvba.blogspot.com/2012/08/details-on-demand-bring-up-details-on.html">Details on Demand: Bring up details on a graph with Excel rollovers</a> by Me</div>
<div>
<a href="http://optionexplicitvba.blogspot.com/2012/06/period-table-of-elements-in-excel.html">Interactive Periodic Table of Elements in Excel</a> by Me</div>
<div>
<a href="http://optionexplicitvba.blogspot.com/2012/06/rollovers-galore.html">Rollovers for Gantt Charts</a> by Me</div>
<div>
<a href="http://optionexplicitvba.blogspot.com/2012/05/guest-post-1-dynamic-funnel-chart.html">Dynamic Funnel Chart</a> by Bert van Zandbergen<br />
<div class="MsoNormalCxSpMiddle">
<b><br />
</b><br />
<b>Related</b><br />
<a href="http://optionexplicitvba.blogspot.com/2012/09/investigation-in-illegal-activities.html">Investigations in "Illegal Activities" with User Defined Functions</a> by The Frankens Team<br />
<b><br /></b></div>
<div class="MsoNormalCxSpMiddle">
If you know of others or would like me to add an informative article you've written, go ahead and email me a link. <o:p></o:p></div>
<div class="MsoNormalCxSpMiddle">
<br /></div>
<h2>
<b>2. Do rollovers work in Excel 2003 and in versions previous?</b></h2>
<div class="MsoNormalCxSpMiddle">
<strike>No, I don't think so. I haven't tested this for myself, but comments on other blogs suggest that the method only works for versions 2007 and 2010. Excel 2013 is not yet out, so while I think it will work in that version, we will have to wait and see for ourselves.</strike><br />
<br />
[updated 9/21/2012]<br />
<br />
<b>Maybe.</b> I might have been wrong about what I said earlier. You see, I've always used IFERROR to surround the HYPERLINK formula, but I forgot IFERROR didn't exist for Excel until version 2007. This formula by <a href="http://www.linkedin.com/in/zstanojevic">Zoran Stanojević</a> might conceivably work in Excel 2003:<br />
<br />
<div style="text-align: center;">
<code>=REPT("",ISERROR(HYPERLINK(getCoordinates(....),"")))</code><br />
<code><br /></code>
<br />
<div style="text-align: left;">
Additionally, Zoran writes:<br />
<br /></div>
</div>
<blockquote class="tr_bq">
I always avoid version specific functions when they are unnecessary because they potentially weakening the universal solution.</blockquote>
<br />
That's an incredibly good point. Still, I'm probably not going to give up IFERROR anytime soon :).<br />
<br />
But, if you are you are employing the rollover method on your own spreadsheet--and you're concerned with backward compatibility issues--definitely consider using his formula above. Then come back and share the results with your good buddy, Jordan.<br />
<br />
Also, I had an email conversation with someone recently who attempted to run my introduction to the rollover method file, "<a href="http://www.keepandshare.com/doc/3733067/snakey-xlsm-march-28-2012-4-53-pm-37k?da=y">Snakey</a>", in Excel 2013 preview. He said the file eventually crashed Excel 2013. However, it was the <i>preview</i> version, so whether the rollover method works in the final version still remains to be seen. For this blog's sake, I hope the rollover method works correctly in Excel 2013.<br />
<h2>
<b>3. Why/how do rollovers work? I thought User Defined Functions could not change other values on a spreadsheet. I've also heard this functionality doesn't exist in Excel.</b></h2>
<div class="MsoNormalCxSpMiddle">
As far as I can tell the rollover method <i>shouldn't exist</i>. I think it works by taking advantage the Hyperlink formula in a way unforeseen by the original developers. Intentional or not, the functionality I believe has opened a door to some cool new possibilities with Excel. </div>
<div class="MsoNormalCxSpMiddle">
<br /></div>
<div class="MsoNormalCxSpMiddle">
I can only speculate why rollovers work the way they do. My best guess is that when your mouse floats over a hyperlink, Excel goes out behind the scenes to check if the hyperlink exists and loads the address in memory in anticipation of a click. This piece of memory probably goes into a generic callback procedure which knows how to handle the different actions that fill the callback queue. Because a User Defined Function is supplied instead of a URL or reference on the spreadsheet (as is normally the case when use Hyperlink) the callback function treats the UDF like any other and executes it immediately without knowing that it came from the Hyperlink formula. </div>
<div class="MsoNormalCxSpMiddle">
<br /></div>
<div class="MsoNormalCxSpMiddle">
I could be wrong about all of this, but that's my (rather uneducated) guess. If any of you know better, I welcome your feedback. If I'm wrong, let me know. My feelings won't be hurt. </div>
<div class="MsoNormalCxSpMiddle">
<br /></div>
<h2>
<b>4. Is there a way to handle multiple rollovers on the same sheet?</b></h2>
<div class="MsoNormalCxSpMiddle">
Yes. Simply create another rollover function in your module and point it to the new rollover function in your module. As far as I can tell, there is no limit to how many rollovers your spreadsheet can employ at any given time. </div>
<div class="MsoNormalCxSpMiddle">
<br /></div>
<h2>
<b>5. When my mouse is over the hyperlink, Excel continuously fires the macro which is slowing everything down. Is there anything I can do about this? </b></h2>
<div class="MsoNormalCxSpMiddle">
Yep, try <a href="http://optionexplicitvba.blogspot.com/2012/08/needs-more-rollover-quick-tip.html">this.</a></div>
<div class="MsoNormalCxSpMiddle">
<br /></div>
<h2>
<b>6. I want the <i>entire cell </i>to become a rollover hotspot, but the user defined function only fires when my mouse is on the text of the hyperlink - how can I make the entire cell a hotspot?</b></h2>
<div class="MsoNormalCxSpMiddle">
Enable wordwrap in each cell containing the rollover method.</div>
<div class="MsoNormalCxSpMiddle">
<br /></div>
<h2>
<b>7. My rollovers used to work perfectly, but now they don't work at all! Recently I added some formulas like INDEX, VLOOKUP, and SUM to the hyperlink rollover formula. Now everything appears without error, but the macro no longer fires. What happened? I need to make my formulas dynamic!</b></h2>
<div class="MsoNormalCxSpMiddle">
Rollovers appear like regular formulas but they are not. When you add other formulas to them, they tend to not work as planned (they may work if you select the cell and hit Enter - but that's not what you want). However, you <i>can</i> make your rollover functions dynamic, but you cannot do it by adding formulas directly to the rollover formula. </div>
<div class="MsoNormalCxSpMiddle">
<u><br />
</u></div>
<div class="MsoNormalCxSpMiddle">
Here's what's I've found. <u>Apparently, rollover formulas work exactly like references used for dynamic charts, dynamic labels, and form controls.</u> Let's take the chart example. When you want to make a dynamic chart, you must have your chart series refer to a range containing the dynamic data. You can't place an INDEX function directly into your chart's SERIES function, but you can have your chart point to a set of data that uses the INDEX function. You'll have to create the same mechanism when using rollovers. So, if you want to make your hyperlink formula change its "friendly text" based something the user does, place a reference to the range with your dynamic content in the friendly text parameter of the HYPERLINK formula and the return error parameter of your IFERROR formula. </div>
<div class="MsoNormalCxSpMiddle">
<br /></div>
<div class="MsoNormalCxSpMiddle">
It's really no different than what you would do when working with a dynamic chart. But unlike in charts and form controls - Excel won't give you a warning when you've placed stuff into the rollover formula that shouldn't be there - instead, your rollovers just won't work. </div>
<div class="MsoNormalCxSpMiddle">
<b><br />
</b><br />
<h2 style="text-align: left;">
<b>8. How do I handle clicks on my Rollover cells?</b></h2>
You can use the Worksheet_SelectionChange event, but I prefer <a href="http://optionexplicitvba.blogspot.com/2012/09/handling-rollover-clicks-without-using.html">this method</a><b>.</b><br />
<b><br /></b></div>
<h2>
<b>9. Do rollovers work on non-Windows machines, like Macs?</b></h2>
<div>
I don't think so. Rollovers require Visual Basic for Applications and the latest versions of Office for Macs don't include the ability to create or run macros, from what I'm told. </div>
<div>
<br /></div>
<div>
If you're a mac user and have success using the rollover method, I'd certainly love to hear from you about it.</div>
<h2>
<b>Can I add to this list?</b></h2>
<div class="MsoNormalCxSpMiddle">
Sure! Email me or post thoughts, examples, advice, etc. </div>
<br /></div>
</div>
</div>
</div>
</div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com26tag:blogger.com,1999:blog-6735088985817760969.post-47988513817673394082012-08-29T19:28:00.000-04:002012-08-29T22:44:19.092-04:00Interactive Map in Excel using Rollovers<div dir="ltr" style="text-align: left;" trbidi="on">
Alright, so this seemed like the next logical step for the <a href="http://optionexplicitvba.blogspot.com/2011/04/rollover-b8-ov1.html">rollover</a> method:<br />
<br />
<!-- copy and paste. Modify height and width if desired. -->
<object data="http://content.screencast.com/users/jpo645/folders/Jing/media/926e9b4d-2285-4210-97ae-27fe15934d12/jingswfplayer.swf" height="481" id="scPlayer" type="application/x-shockwave-flash" width="770">
<param name="movie" value="http://content.screencast.com/users/jpo645/folders/Jing/media/926e9b4d-2285-4210-97ae-27fe15934d12/jingswfplayer.swf" />
<param name="quality" value="high" />
<param name="bgcolor" value="#FFFFFF" />
<param name="flashVars" value="thumb=http://content.screencast.com/users/jpo645/folders/Jing/media/926e9b4d-2285-4210-97ae-27fe15934d12/FirstFrame.jpg&containerwidth=770&containerheight=481&content=http://content.screencast.com/users/jpo645/folders/Jing/media/926e9b4d-2285-4210-97ae-27fe15934d12/2012-08-29_1914.swf&blurover=false" />
<param name="allowFullScreen" value="true" />
<param name="scale" value="showall" />
<param name="allowScriptAccess" value="always" />
<param name="base" value="http://content.screencast.com/users/jpo645/folders/Jing/media/926e9b4d-2285-4210-97ae-27fe15934d12/" />
Unable to display content. Adobe Flash is required.
</object><br />
<br />
This one is kinda complicated, I admit. Unfortunately, I didn't really take the time to clean up the spreadsheet file for others to follow (I don't really have the time these days). Sorry. But try to take it apart - and ask me questions if you have them.<br />
<br />
I've canvassed some other folks from the Excel community to see if they would want to do a video tutorial of this - and I think that's what it would take.<br />
<br />
Have fun!<br />
<a href="https://docs.google.com/open?id=0B1OBNnu3ZbL0VXFvZFFCa1J3UTA">Mapper.xlsm</a></div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com10tag:blogger.com,1999:blog-6735088985817760969.post-18620464101551729122012-08-27T22:46:00.001-04:002012-08-28T08:41:52.990-04:00Details on Demand: Bring up details on a graph with Excel rollovers<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
So, as usual, I've been experimenting more with the <a href="http://optionexplicitvba.blogspot.com/2011/04/rollover-b8-ov1.html">rollover technique</a>. Last night, I started experimenting with adding the capability to rollover a point on a chart (instead of just a worksheet cell) to see if I could have a label appear - or just some event fire - upon the mouse entering a chart's datapoint's "hotspot." The goal was to improve upon previous attempts to provide details-on-demand for charts. Such attempts usually required that you create a reference to a chart object and use chart sheets. Personally, I don't like chart sheets.<br />
<br />
In the end, I moved beyond just firing an event when your mouse hovers over a data point; instead, I created the functionality to select a rectangle of data points to display information about them. See:<br />
<br /></div>
<!-- copy and paste. Modify height and width if desired. -->
<object data="http://content.screencast.com/users/jpo645/folders/Jing/media/f3b55027-4190-41ce-9be7-831c70af31e5/jingswfplayer.swf" height="368" id="scPlayer" type="application/x-shockwave-flash" width="588">
<param name="movie" value="http://content.screencast.com/users/jpo645/folders/Jing/media/f3b55027-4190-41ce-9be7-831c70af31e5/jingswfplayer.swf" />
<param name="quality" value="high" />
<param name="bgcolor" value="#FFFFFF" />
<param name="flashVars" value="containerwidth=588&containerheight=368&content=http://content.screencast.com/users/jpo645/folders/Jing/media/f3b55027-4190-41ce-9be7-831c70af31e5/2012-08-27_2222.swf&blurover=false" />
<param name="allowFullScreen" value="true" />
<param name="scale" value="showall" />
<param name="allowScriptAccess" value="always" />
<param name="base" value="http://content.screencast.com/users/jpo645/folders/Jing/media/f3b55027-4190-41ce-9be7-831c70af31e5/" />
Unable to display content. Adobe Flash is required.
</object><br />
<br />
Because I'm now writing a book, I don't really have time to go through what I did at length. But here's a short summary.<br />
<br />
1. I first made every cell into a square of the same size, which you can learn how to do by reading my "<a href="http://optionexplicitvba.blogspot.com/2011/07/completing-square.html"><i>Most </i>Squares Method</a>."<br />
<br />
2. With each cell the same size, I now had a grid that I could turn into"hot spots" for the mouse rollover. I laid out my chart to use 36 (0 - 35) squares horizontally and 17 (0 - 16) vertically. There was really no good reason for why I picked these numbers; my choice was pretty arbitrary. However, for this method, the more squares you use, the more hotspots you create; thus, more squares means more precision.<br />
<br />
3. I then created a mouse rollover technique to capture the row and column numbers set above. Using these numbers, I mapped them on to the charts grid (so for row two, I would do 2/17 * y-axis value to get an approximate mapping). With these mapping I could approximate where the hotspots would light up certain points that were within them<br />
<br />
4. I didn't want the user to be able to click on the graph and change its values. So I took a shape and placed it on top of the graph and assigned it to fire a macro on click. <b>You can't see the shape because the fill color is fully transparent. Reread that last sentence and note that I did not say I used "no fill color." When you assign a macro to a shape, if you select "no fill," Excel lets you select anything that is contained by the shape as if it's not there. That would mean the user would be able to select the graph, which is exactly what I didn't want. For the shape above, I simply set its transparency to 100%.</b><br />
<b><br /></b>
5. Finally, to make a long story short, the shape when clicked fires the macro that allows the user to draw the rectangle.<br />
<br />
I know right now my work isn't perfect (some data points aren't selectable for example depending upon where you start the rectangle), but I like what I have now and don't have much more time to work on it. I'll leave it to you to put it to good use.<br />
<br />
Let me know if you make something cool.<br />
<br />
<b>Download File</b><br />
<a href="https://docs.google.com/open?id=0B1OBNnu3ZbL0UHFEelJJVEVLR1E">Details on Demand Rollover.xlsm</a><br />
<br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com4tag:blogger.com,1999:blog-6735088985817760969.post-15109514672866508972012-08-24T18:02:00.000-04:002012-08-24T18:02:27.205-04:00About Me<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div>
A big thank you to everyone! This blog has achieved over 20,000 page views, which is quite an accomplishment for this humble blogger. Looking through my work, I realize that I've never formally introduced myself. </div>
<div>
<br /></div>
<div>
So, here goes. </div>
<div>
<br /></div>
My name is Jordan Goldmeier and I live in Dayton, Ohio. I work for a small start-up consulting company, The Perduco Group, developing analytical applications many of them in Microsoft Excel. Later this year, I am getting married to wonderful, incredibly smart theology student named Katherine. I'm also currently writing a book on Excel dashboards for Apress publishing.<br />
<br />
Also, I have two cats:<br />
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaWwQOtCNwXcQH_RQfO-N6DSYWE7di5n_JO8isEjDcN6Y5l_GwArJGdlWlKTwDmIo1tGYYEwaDkamc4NrWzCxLB-JLRCLPvpRo1oCv1aQz_cEziUtc3IFEjTUy4lK0mNPTomZSQDZPmV4/s1600/RUFUS400.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="268" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaWwQOtCNwXcQH_RQfO-N6DSYWE7di5n_JO8isEjDcN6Y5l_GwArJGdlWlKTwDmIo1tGYYEwaDkamc4NrWzCxLB-JLRCLPvpRo1oCv1aQz_cEziUtc3IFEjTUy4lK0mNPTomZSQDZPmV4/s400/RUFUS400.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="text-align: left;"><b>Rufus</b></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="text-align: left;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="text-align: left;">and</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEih_Y45LPS658qH39lZ364lFQOI8QDh13_xPDjZo3e5dwZxG8gO911wVYhqdczZorFp2yONVnRIe6MUS0ptMd7HKh-tfqetXfCFdTwnHX6hiF2ofujI3Tq3006YE5Z0VuKGPhNnGGk1_V4/s1600/Theo.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEih_Y45LPS658qH39lZ364lFQOI8QDh13_xPDjZo3e5dwZxG8gO911wVYhqdczZorFp2yONVnRIe6MUS0ptMd7HKh-tfqetXfCFdTwnHX6hiF2ofujI3Tq3006YE5Z0VuKGPhNnGGk1_V4/s320/Theo.jpg" width="240" /></a></div>
<div style="text-align: center;">
<b>Theo</b></div>
<div>
<br /></div>
<div>
And then there's Katherine's dog, <b>Katie</b>:</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiK5gnPEOxpr9_hEroEbX4jZKE0_kXUF1mvIMk647l3vjQ3NiINNz80mh_-VWaTjFr4YhE_vTz_xffJCOO5cFTIT0erF3emrkXOJILdA1ioJF2uhaT94bogpSiuPRtTFMRaBc1ApfecjNY/s1600/katiedog.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiK5gnPEOxpr9_hEroEbX4jZKE0_kXUF1mvIMk647l3vjQ3NiINNz80mh_-VWaTjFr4YhE_vTz_xffJCOO5cFTIT0erF3emrkXOJILdA1ioJF2uhaT94bogpSiuPRtTFMRaBc1ApfecjNY/s400/katiedog.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both;">
<br /></div>
<div class="separator" style="clear: both;">
***</div>
<div class="separator" style="clear: both;">
<br /></div>
<div class="separator" style="clear: both;">
As I went through the information Google Analytics collects about my blog, I saw people from all over the world have visited my blog. Wherever you're from - don't be a stranger - introduce yourself! And if you're in the Dayton area and haven't said "hi" yet - here's your chance!</div>
<div class="separator" style="clear: both;">
<br /></div>
<div class="separator" style="clear: both;">
jpo645 (at) gmail (.com)</div>
<div class="separator" style="clear: both;">
<br /></div>
<div class="separator" style="clear: both;">
Look forward to hearing from you. Comment, email - let's connect. </div>
<div class="separator" style="clear: both;">
<br /></div>
<div class="separator" style="clear: both;">
-Jordan</div>
</div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com6tag:blogger.com,1999:blog-6735088985817760969.post-39676962365382519342012-08-19T10:15:00.001-04:002012-08-19T10:16:02.612-04:00Guest Post: Rollover for Months and Years<div dir="ltr" style="text-align: left;" trbidi="on">
Reader Bert van Zandbergen sent me a cool modification to the instruction file in <a href="http://optionexplicitvba.blogspot.com/2012/06/how-to-highlighting-cells-using.html">How to: highlighting cells using the rollover technique in Excel</a> to include years and months - not just rows and columns. He writes:<br />
<div>
<br />
<blockquote class="tr_bq">
<span style="vertical-align: baseline;">I am an enthousiast reader of the blog 'Option Explicit'. I changed the formula (see below) and made a version for showing Years and Months. Now you can use this tool for Management Information. This fantastic tool, combined with Named Dynamic Ranges gives the ultimate solution for making awesome interactive dynamic Excel charts. As soon as possible I will show you an example. <b style="font-family: Calibri; font-size: 10.5pt;"> </b></span></blockquote>
<br />
<blockquote class="tr_bq">
<span style="font-family: Calibri; font-size: 11pt; font-weight: bold;">Bert
van Zandbergen, Beekbergen</span><span style="font-family: Calibri; font-size: 11pt; font-weight: bold;"> </span><span style="font-family: Calibri; font-size: 11pt; font-weight: bold;">- The
Netherlands </span></blockquote>
<br />
You can download the file, here: <a href="https://docs.google.com/open?id=0B1OBNnu3ZbL0RllIOEM3RTh0U00">Rollover_MI model version 1.xlsm</a><br />
<br />
Thanks Bert!<br />
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com0tag:blogger.com,1999:blog-6735088985817760969.post-78086981467444386342012-08-07T22:22:00.000-04:002012-08-18T22:42:31.647-04:00Needs More Rollover: Quick Tip!<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
More people are interested in Excel mouse rollovers, which I think is great. I'm especially enthusiastic about <a href="http://chandoo.org/wp/2012/07/30/excel-salary-survey-contest-results/">Chandoo's latest dashboard contest</a> 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 <a href="http://chandoo.org/wp/2012/08/03/excel-salary-dashboards-voting/">make sure to vote</a>!<br />
<div>
<br /></div>
<div>
So here's the tip. If you're familiar with the <a href="http://optionexplicitvba.blogspot.com/2011/04/rollover-b8-ov1.html">rollover method</a> - and you should be by now,<i> it's like all I ever blog about these days</i> - 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 <a href="http://chandoo.org/wp/2011/07/20/interactive-dashboard-using-hyperlinks/">Chandoo's blog</a>:</div>
<code>
<br />Public Function highlightSeries(seriesName As Range)<br />Range("valSelOption") = seriesName.Value<br />End Function</code><br />
<br /></div>
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. <i>Nobody likes a slow spreadsheet. </i><br />
<br />
The incredibly simple fix to our problem comes from the wonderful book, <i><a href="http://www.amazon.com/gp/product/0321508793/ref=as_li_qf_sp_asin_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=0321508793&linkCode=as2&tag=opexvb-20">Professional Excel Development</a></i>, 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:<br />
<br />
<div>
<code>
Public Function highlightSeries(seriesName As Range)<br />If Range("valSelOption") <> seriesName.Value Then Range("valSelOption") = seriesName.Value <br />End Function </code><br />
<br />
In the above code, I test if valSelOption <i>already</i> 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.<br />
<br />
Thats it! You'll likely see an immediate speed improvement, especially if you're doing lots of complex stuff with your rollover, like a <a href="http://optionexplicitvba.blogspot.com/2012/06/period-table-of-elements-in-excel.html">this</a>.<br />
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com2tag:blogger.com,1999:blog-6735088985817760969.post-7846332756706865952012-08-03T17:21:00.001-04:002012-08-03T17:21:47.246-04:00Consulting Services<div dir="ltr" style="text-align: left;" trbidi="on">
You may have noticed that I tend to update this blog less frequently than some of the more prominent Excel and VBA bloggers. The reason is that I blog on the side rather than full time. In fact, I work for a wonderful startup consulting company called The Perduco Group. Here’s what we’re about:<br /><br /><blockquote class="tr_bq">
Perduco is Latin for "to lead through" or "leadership.” The Perduco Group leverages a number of key competencies in data structures, programming, operations research, and business intelligence to provide an overall analytical solution to the customer − delivering performance from data to decision. The fundamental goal is to provide a useable product founded in credible analytics to support organizations and change the way by which they do business. The Perduco Group is focused on providing organizations value by leveraging our technical skills and industrial and defense experience with our client-focused, results-oriented delivery approach.</blockquote>
<div>
<br /></div>
If you’re interested in taking your spreadsheets to the next level, optimizing your efficiency with operations research, or implementing a new BI system drop me line at my work email, Jordan.Goldmeier (@) ThePerducoGroup (.com) and I can let you know what we’re all about.<br /><br /><div>
As a follow-up to the above, I am gathering resumes on folks with experience in Excel, VBA, data analysis, and data visualization. If you’re interested in this type of work, feel free to send me your resume either at the email above or my personal one at the bottom of this page. The only condition is that you must be a US citizen. </div>
</div>Anonymoushttp://www.blogger.com/profile/04996346032917731702noreply@blogger.com1