Category Archives for Advanced Excel
“Everyday” Random Shuffling with Formulas
In this article, we’ll talk about how to complete random shuffling with only formulas. Shuffling methods via VBA or a dime a dozen. But it’s much harder to perform a formula-based random shuffle since formulas don’t really allow for iteration. Non-VBA methods therefore require we think about these problems differently (what I describe in Dashboards […]
Continue readingA method for quickly importing and working with CSV files (part 2)
This is part 2 in which I explain how I solved the problem. Make sure to read the previous blog posts. Taken together, these show I though through and ultimately solved the problem. Query Tables to the Rescue! To solve this problem, I stumbled on something I rarely use, and that’s our good ol’ friend […]
Continue readingA method for quickly importing and working with CSV files (part 1)
So many folks have asked me to write more on my coding style. So here it is: I’ve developed a two-parter blog post to present my thinking style and how I ultimately solved the problem. The Problem I have a client with several CSV files that must be checked and validated for certain data specifications. […]
Continue readingDeveloping a JSON Excel Add-In With VB.NET
Written by Bjoern Stiel Add-Ins are a great way to increase Excel’s firepower. Add-Ins not only add additional functionality but are also an ideal vehicle for distributing your custom functions across multiple workbooks and users. If writing a new Add-In is on your to-do list anyway, this is the perfect opportunity to look beyond VBA. VBA has […]
Continue readingA VBA Coding Manitesto (Part 2)
In the first part of my manifesto, I outlined why I think we should really change the way we code. The important takeaways of that article were: (1) the Hungarian Notation coding style is old and should no longer be preferred; (2) we cannot simply code for ourselves anymore; and (3) the implications of the […]
Continue readingA VBA Coding Manifesto (Part 1)
A little while ago, I made a blog post on why I think we no longer need Hungarian Notation. Truthfully, it’s not the first time I’ve advocated for doing something against the grain. My blog on the principles for app/game development with Excel argues you should keep most of your procedures in the sheet object […]
Continue readingIt’s time to say “Goodbye,” to Hungarian Notation
Most of us code using the Hungarian notation style when writing VBA code. Basically, that’s when you prefix an abbreviated description of a variables’ type or class in front of its name. For example, the ‘d’ below is a prefix for a variable of type double. You get the idea. 1: Dim dDouble As Double […]
Continue readingIt’s time to say “Goodbye,” to Hungarian Notation
Most of us code using the Hungarian notation style when writing VBA code. Basically, that’s when you prefix an abbreviated description of a variables’ type or class in front of its name. For example, the ‘d’ below is a prefix for a variable of type double. You get the idea. 1: Dim dDouble As Double […]
Continue readingI’m coming to San Francisco!
That’s right, if you’re in the San Francisco bay area, don’t miss an exciting Excel workshop on March 20th, 2014. I’m now a team member of Excel and Access LLC, a full-service Excel and Access training and development consultancy. We’re doing an Excel workshop at the eMetrics Summit and we’d love it if you joined […]
Continue readingI’m coming to San Francisco!
That’s right, if you’re in the San Francisco bay area, don’t miss an exciting Excel workshop on March 20th, 2014. I’m now a team member of Excel and Access LLC, a full-service Excel and Access training and development consultancy. We’re doing an Excel workshop at the eMetrics Summit and we’d love it if you joined […]
Continue readingEffective means of display in Excel
There are many great articles and tutorials on how to build interactive data displays in Excel. However, there are few articles on how best to effectively display data in Excel. I think this is an important subject that every Excel user should at least think about. On the other hand, I personally don’t like any […]
Continue readingYour First Maze
Disclaimer: The following tutorial requires Conditional Formatting which is a feature of Excel 2010 and greater. If you are using an older version of Excel, please note that formatting will not work correctly for you. This article is part of a series called “VBA4Play,” written in conjunction with Excel game developer, Cary Walkin, for his […]
Continue readingHandling Excel Rollover Popups
Various people have written to me asking what they should do about popups generated by the rollover method that don’t go away on their own. Essentially, the rollover method provides for an “on mouse over” style routine but not for a “on mouse out.” The “moat” method One method to address this is to create […]
Continue readingA Dynamically Linked Checkbox Matrix with Microsoft Excel
Edit: sorry for some of the squashed images you see in this and other posts. I’m still working on my layout. In the meantime, click on an image to view it in full if you’re having trouble. Today, I want to show you what I liked to call a “ dynamically linked matrix.” I’ll post […]
Continue readingThe Excel Lab: Soundex Search
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 […]
Continue reading