6

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. 

So, here goes. 

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.

Also, I have two cats:

Rufus

and

Theo

And then there's Katherine's dog, Katie:

***

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!

jpo645 (at) gmail (.com)

Look forward to hearing from you. Comment, email - let's connect.  

-Jordan

Author: Unknown

0

Reader Bert van Zandbergen sent me a cool modification to the instruction file in How to: highlighting cells using the rollover technique in Excel to include years and months - not just rows and columns. He writes:

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.           

Bert van Zandbergen, Beekbergen  - The Netherlands 

You can download the file, here: Rollover_MI model version 1.xlsm

Thanks Bert!

Author: Unknown Labels: Advanced Excel, Project Management, Rollovers

2

More people are interested in Excel mouse rollovers, which I think is great. I'm especially enthusiastic about Chandoo's latest dashboard contest 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 make sure to vote!

So here's the tip. If you're familiar with the rollover method - and you should be by now, it's like all I ever blog about these days - 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 Chandoo's blog:


Public Function highlightSeries(seriesName As Range)
Range("valSelOption") = seriesName.Value
End Function

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. Nobody likes a slow spreadsheet. 

The incredibly simple fix to our problem comes from the wonderful book, Professional Excel Development, 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:

Public Function highlightSeries(seriesName As Range)
If Range("valSelOption") <> seriesName.Value Then Range("valSelOption") = seriesName.Value
End Function

In the above code, I test if valSelOption already 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.

Thats it! You'll likely see an immediate speed improvement, especially if you're doing lots of complex stuff with your rollover, like a this.

Author: Unknown Labels: Advanced Excel, Excel Development, Excel Tips, Rollovers, VBA

1

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:

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.

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.

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.

Author: Unknown

3

When I'm all done with my Excel application, I'll usually want to flip off background error checking - that is, I'll tell Excel to stop showing those little green triangles that appear in cells. Don't get me wrong, those little green alerts can be useful - but they are rarely so in a finished product where I know my layout and formulas are correct. I just want to tell Excel, "thanks for the help, but stop annoying me already!" Indeed, these green alerts appeared on other computer screens when users opened my Periodic Table of elements file, which I found annoying.

The problem is that while I can tell those green triangles to go away on my instance Excel by going into Excel Options (or simply by clicking "ignore"), that won't fix the problem when my file is loaded onto other computers. The way around this is some VBA and the workbook open and close events. 

So, in my ThisWorkbook object in the VBA window, I wrote this:

Option Explicit




Private Sub Workbook_Open()

    Application.ErrorCheckingOptions.BackgroundChecking = False

End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.ErrorCheckingOptions.BackgroundChecking = True

End Sub

There are actually several types of background error checking that go on in a workbook. Excel allows you to disable these separate error checks by themselves if don't want to disable everything. Specifically, you can modify background checking options for empty cell references, error calculations, inconsistent formulas, and omitted cells, among others. For example, you might simply write:

Application.ErrorCheckingOptions.OmittedCells = False

if you only want Excel to stop monitoring for formula patterns that appear to omit cells that Excel thinks should be included in the formula. This will work so long as BackgroundChecking is still True. If you set BackgroundChecking to False like in the example above, Excel will cease all attempts to second guess your work (which can make your life easier, sometimes). 

To read more, see:

The ErrorCheckingOptions Object

Author: Unknown Labels: Excel Tips, Excel Tricks, Programming, VBA