Category Archives: Development

Effective 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 set of rules that say this key performance indicator or metric or chart must always be used. If we think about the best books on English, say The Elements of Style, or even the best books on data visualization, say The Visual Display of Quantitative Information, we find neither author posits their principles as hard and fast rules. Rather, they submit a set of guidelines, heuristics to help us test and decide. The Excel developer world should have such guidelines, to which I present the following for effective means of display in Excel [1]:

– Mutual Exclusivity

– Common Interpretation

– Sufficiency

Mutual Exclusivity

Mutual exclusivity speaks to presenting data and metrics whose different meanings do not overlap. I say “meanings,” here, because there may be instances in which metrics overlap, but their meanings do not. Consider a simple income statement. We all know Gross Profit = Revenue – Costs. If we were to ignore meaning when applying the test of mutual exclusivity, we might venture to only include two of these measures, since the third measure is only one algebraic calculation away. (For instance, we could divine Gross Profit in our heads by only present Revenue and Costs.) In this sense, there is surely overlap in all three of these metrics. But in our understanding of business performance, showing all three is the best option because the meaning they impart is separate and independent.

On the other hand, take a look at the following chart.

clip_image001

The addition of a total measure into the Unit Sales chart does little help us divine regional sales differences. Indeed, the total is already plotted to a certain extent – it’s the aggregation of the other regions. Now consider the average measure. The data variation between regions is such that we can gauge the average with our eyes (perhaps not with granularity, but the additional metric does not help us with this either). I argue these two metrics provide an unjustifiable overlap already inherent within the presented regional sales differences. (But you may want to include these metrics for good reason, and we’ll deal with a way to do that by the end of this article.)

clip_image002

Common Interpretation

Common interpretation refers the organizational, institutional, and even societal paradigm through which we understand and interpret data. That may have been a mouthful, but it simply means this: all the people looking at the same metrics must understand them in the same way.

This is especially important for metrics created by and for a specific organization or market. Most of us agree on how to calculate the Current Ratio (we should, there’s really only one way to do it). On the hand, a single organization could come up with its own metric. Consider a performance metrics that results in a score within the boundaries of zero and one. For those familiar with the metric, the results are easy to interpret. But for those on the outside, there may be puzzlement. And unless everyone agrees, placing it on your dashboard or report is sure to generate confusion.

I speak from experience. I’ve watched organizations argue over such metrics and what they mean. When this happens it’s time to take a second look at the measure. Or, consider this scenario: an organization rates its employees once a year. One underperformer receives a 3 out of 5 each year, which according to the company definition of its own metrics is a “good” score. However, compared to his 5 out of 5 peers, he is an underperformer. The employee is fired for underperforming but sues the organization since a 3 out of 5 is considered good on paper. Who’s right? This is an example of an uncommon interpretation.

Sufficiency

Sufficiency speaks to whether there are too many or too few metrics to sufficiently deliver the message required. Sufficiency is related to mutual exclusivity. However, sufficiency deals with the extraneous and insufficient inclusion of metrics. For example, you may find some white space on your dashboard that could be filled in with even more information. If that information doesn’t aid in the understanding of that data already included therein—don’t add it! The whitespace might feel like empty real estate, but not every metric needs a neighbor.

Let’s go back to the first chart presented. Here again, average and total are the problem, but removing them might also present an insufficiency in what information should be delivered. If the aim of the chart is to present the intra performance of regional sales, then the inclusion of these metrics certainly inhibit our understanding. For example, the inclusion of the total requires we stretch the chart range out considerably, thus dampening the variation between each region. But we can still present this information in a way that doesn’t compete with its message while presenting a sufficient amount of information. The chart below demonstrates one such way you could use in a pinch.

clip_image003

Conclusion

The most important thing to remember is that these aren’t hard and fast rules. Consider them as a guide as you continue your Excel journey. As we are now merely scratching of what Excel can do, effective means of data display will serve us well.

————————————————————-

[1] Adapted from the field Value Focused Thinking and quite specifically from the work of Gregory Parnell.

Parnell, G. S., Chapter 19, Value-Focused Thinking Using Multiple Objective Decision Analysis, Methods for Conducting Military Operational Analysis: Best Practices in Use Throughout the Department of Defense, Military Operations Research Society, Editors Andrew Loerch and Larry Rainey, 2007. link

Advertisements

Creating a Spreadsheet-Based Wizard

First of all, happy spreadsheet day! It was on this date today that VisiCalc was first shipped to the masses. The rest, as they say, is history.

So let’s talk about creating a spreadsheet-based wizard. By “spreadsheet-based,” I mean no Userforms. That’s right. There are many good Excel tutorials out there on developing wizards with UserForms, and I can certainly see their appeal. For example, if you are creating an add-in for Excel, then a wizard created with UserFroms is the best way to go.

However, if you are making a spreadsheet-based decision tool or dashboard, I say you should opt for a spreadsheet-based wizard. For one, I think you can make them more quickly. And second, UserForms can carry unnecessary bloat. Finally, I’ve found UserForms to be somewhat unpredictable in terms of layout across different monitors and resolutions. That said, a spreadsheet-based wizard might still look off on different monitors, but I think using the spreadsheet as a canvass makes this problem easier to prevent, diagnose, and fix.

So this is what a spreadsheet-based wizard looks like.

Spreadsheet Wizard

Pretty simple, eh? You’ll be amazed by how quickly you can throw what of these together.

So here’s how it works. Each panel or view is a named range of group of cells stretching across all rows. The Next and Back buttons simply show and hide these views accordingly. Below, I’ve unhidden each view in the example wizard and then zoomed out so their names are displayed. Note the naming and ordering scheme utilized. If I were to add another panel at the end, I’d call it Wizard.View5. This ordering scheme, as we shall see, is what makes these wizards so simple and easy.

image

Traversing the wizard

The Next and Back buttons employ pretty similar code. The Next contains a test to see if we’ve reach the end of our panel set; the Back contains a test to see if we’re at the beginning panel. There’s some extra stuff to test which view we’re in (View 2 requires some special instruction), but it’s all pretty simple.

Below, I’ve excerpted the procedure called when Next is pressed.

Public Sub GoNext()
Dim index As Integer
index = [Helper.CurrentPageIndex]
index = index + 1

If index > [Helper.TotalPages] Then Exit Sub
Wizard.Range(“Wizard.View” & index).Columns.Hidden = False

If index = 2 Then
DisplayCheckboxes
Else
HideCheckboxes
End If

If index > 1 Then
Wizard.Range(“Wizard.View” & index – 1).Columns.Hidden = True
End If

[Helper.CurrentPageIndex] = index
End Sub

 

The way we know and manage which view we’re currently looking at is through a helper cell on a Helper tab (the Helper tab is in the example file). This cell holds a number representing the index of the view we’re looking at. I’ve named this cell Helper.CurrentPageIndex. In addition, I’ve also stored a variable to keep track of total views, Helper.TotalPages. In this example file, I’ll update the total manually when I add a new view (by typing in the new total). But you could automate this process if you felt so inclined. In fact, you should.

In this example, Helper.CurrentPageIndex stores a number between 1 and 4 inclusive. If it equals 1 then we’re looking at the first view. When I press Next, the index is incremented and the next panel is displayed; the previous panel is then hidden. As you see from the code, I simply need to read in the index stored in Helper.CurrentPageIndex. This is why the numbering scheme is so great: the index is the only specific information required to act as a pointer to each view.

In fact, with this setup, my panels don’t even need to be in order. Of course, you should try to plan ahead so that you aren’t making view out of order – going out of order seems like a headache to me – but the option is there if you need. While I’m thinking about it, you could even change that last panel to something like Wizard.ViewFinished. Then you would make as many additional panels as you wanted thereafter. You would just need something in your code to test when you’ve reach that second to last panel so that users are directed toward Wizard.ViewFinished. It wouldn’t be hard, just one more IF statement. The numbering mechanism makes this part easy.

View Content

Editing each view is also fairly simple. If you want to see all views at once, you can simply unhide everything. When you’re ready to hide them again, pressing the Next and Back buttons in each direction should bring you back to hiding/showing each view as necessary. (In the example file, try unhiding everything and then pressing the Next and Back buttons – you’ll see what I mean.)  And, just like with UserForms, you place inside the view what you want for Excel to display when it’s in view. For input cells, it’s just a matter of creating a border around a cell range. Pictures and shapes do require the extra step of selecting the Move and size with cells option from within the properties settings.

image

The only tricky items are form controls, which are sometimes really weird about sizing. This weirdness is only confounded by Excel 2013’s properties menus. Take a look at the picture below. If you’re looking in the Format Control dialog box (on the left), the Move and size with cells option appears disabled. Now compare that to Excel 2013’s new properties pop-up thingy on the right. The option is now available.

image

In any event, form controls seem to become displaced across the different resolutions and dpi settings of individual machines. Sometimes they’ll get caught overlapping between two different view panels. When this happens, funkiness ensues. The form control’s size gets mangled. Sometimes Excel ends up copying that form control to that same spot over and over again. You find that you have 10 different checkboxes with the same name. If this has happened to you before, then you know the frustration. For the dubious who’ve never experienced this problem, it’s real. I’m not making it up. I swear.

So your best defense when using form controls on ranges that you intend on showing and hiding is to anchor each control to certain place on the spreadsheet. In this case, I’ve defined a section of cells and called them Wizard.CheckboxAnchor. I’ve also named each check box something like Check1, Check2, Check3 … etc.

image

When I want to view these checkboxes, I’ll call the DisplayCheckboxes procedure. In my Back and Next buttons, I’ve incorporated a test in the GoNext and GoBack procedures to show/hide the checkboxes for each view accordingly. For example, we only need to see these checkboxes when the second step is in view. Scroll up to the code listing view this test. Below, I’ve excerpted the DisplayCheckboxes procedure which is called when Step 2 is in view.

Private Sub DisplayCheckboxes()
Dim i As Integer

For i = 1 To [Wizard.CheckboxAnchor].Rows.Count
Dim currentCheckbox As Excel.Shape
Set currentCheckbox = Me.Shapes(“Check” & i)
With [Wizard.CheckboxAnchor].Rows(i).Cells
currentCheckbox.Width = .Width
currentCheckbox.Height = .Height
currentCheckbox.Top = .Top
currentCheckbox.Left = .Left
End With
currentCheckbox.Visible = True
Next i
End Sub

 

Notice how this code is very  similar to the view controller mechanism from in the GoNext procedure. Again, I group similar spreadsheet objects together by name. I use the suffix as an Id.

Information Panes

I use some conditional formatting in the left information pane to highlight which step I’m looking at. And, on the right, I have a bit of dummy text that would otherwise act as instructions. Take a look at the formula below. I’m not using any VBA directly to change the Instruction text. In this setup, instruction text for each step is stored in the Helper tab in the Instructions Table.

image

Thoughts

I like this method for creating wizards because it’s quick to cook up. Once you’re pleased with your design, you can save it as a simple template. Later it becomes a boilerplate from which to create different wizards for your different projects. You’re also not limited to this type of layout, of course. You could place the information panes on the top instead – you would hide/display rows rather than columns. Personally, I’ve used both and prefer the layout employed in this example.

Left for you to do

I’ve created a simple example that does require more work before it can be deployed.

Specifically, for each panel, it’s a good idea to activate the first cell or input item. This will always place the selector in view. Second, the input items in this example aren’t linked to anything. You’ll want to link these to some backend database or spreadsheet tab devoted to storage – especially if you expect a user to use the wizard several times over to create a list of items. You’ll also want to clear out data entry each time you start over with the wizard. Finally, it might be a good idea, when testing for certain view in the GoNext and GoPrevious procedures,  to use constants instead of literal numbers. I leave these taskings to you.

One last thought, I promise. As you’ve probably noticed, I’ve moved away from Hungarian notation for naming spreadsheet objects. The idea of using a “dot” for named ranges came from a blog post I had read on Charley Kyd’s blog. I’ve tried really hard to find that article again, but I can’t seem to locate it. (So the former link just goes to his blog home page.) In any event, I think it’s a good idea to name your wizard items following this nomenclature. In the input section of the first panel, consider giving the First Name input a name of something like “Wizard.View1.FirstName,” or “Wizard.Introduction.FirstName.” I find when you refer to named ranges like these in your code – and even in formulas – they’re so much easier to read and understand than something like “valFirstName.”

Happy spreadsheet day.

Here’s the download file:

Wizard Example

Development Principles for Excel Games and Applications

This is part of a series called “VBA4Play,” written in conjunction with Excel game developer, Cary Walkin, for his blog. Many thanks to Cary for inviting me to write a few tutorials for his series.

My development approach is different from Cary’s. To be sure, there are many different development styles when in comes to Excel and, for that matter, in the software development world. However, my principles are ones I hold fast to. You may disagree with them, and I welcome you to challenge me in the comments should you feel inclined. But it’s far more important you understand these principles and why I believe in them. Think of it as an Elements of Style for Excel development. (For the unfamiliar, The Elements of Style is English style guide with a list of rules and principles to English writing style.)

Principle 1: Render unto Excel the things that are Excel’s, and unto VBA only the things that require VBA

You can do most everything with VBA, true; but you shouldn’t use VBA if you don’t have to. For example, it’s tempting to store your all your program’s global variables in a module. This methods brings the advantage of accessibility: the variables can be accessed by any procedure. However, these variables are also “freed” from memory whenever your code errors out or whenever you tell Excel to “Restart” your code. When this memory is dumped, you must start over: usually, you must create a “new” or “restore” method that reinitiates these variables. I don’t prefer this method.

Spreadsheets are inherent storage spaces. What I like to do is create a new spreadsheet tab that stores all my variables. In the figure below, I’ve created a sheet called “Calc” to store my variables and calculations. I’ve given named the cell C3, “Calc.Left.” Note that periods are allowed in named ranges. It gives named ranges an object-oriented feel.

image

When I want to manipulate the variable as it exists now on a spreadsheet, I can use the bracket-based shorthand range syntax, for example:

[Calc.Left] =  [Calc.Left] – 1

Msgbox [Calc.Left]

In addition, I can access this variable anywhere in my code as I’ve done above and anywhere in my workbook using =[Calc.Left].

But it goes beyond variables. You can color cells using Conditional Formatting (provided you are using Excel 2010 or greater) rather than addressing their location through VBA and changing their backgrounds colors. You can address a cell’s value using something like =INDEX([GameMap], [Character.Row], [Character.Column]) rather than using Sheet1.Cells([Character.Row], [Character.Column]).Value.

You can even set the location of a character on a game map by quickly manipulating the [Character.Row] variable. In fact, you can even assign a scroll bar form control to the [Character.Row] cell and change a character’s location without ever writing a single bit of code. I’ve done this in the example below.

Form Control Example

As you can see, it’s pretty slick. Like I said, no VBA code is required do this.

I know what you’re thinking: ok, that’s just another way to do the same thing. Well, yes, that’s true. But it’s also a much faster and more organized way of doing the same thing. In the finest book ever published on VBA and Excel application development, Professional Excel Development, Bulley et al show reducing the amount of VBA instructions Excel must execute can deliver performance enhancements. Consider for a moment how many instructions are required to move a character on a game board. You have to store the character’s location in a global variable. You have to select the cell in which he (or she!) will go. You have to write the smiley to the new location. And you have to clear out the old location. A lot of this can be handled on your behalf by Excel.

This principle might not be immediately or intuitively clear right way. But as we wade through more examples in the coming tutorials, you’ll see it in action.

Note from Cary: It is important to note that contrary to the above, my style is to do as much as possible in VBA rather than in a worksheet. The main benefit being a simpler debugging process which is critical when you have more complex systems interacting with each-other. This tutorial is an opportunity for you to see a different style when it comes to Excel programming and you can make your own conclusions as to what feels right for you.

Principle 2: Think Encapsulation!

Encapsulation is a tenant of object-oriented programming that argues (1) associated data and procedures should be organized together; and (2) access to and manipulation of the former items should be restricted or granted in only certain circumstances. For you accountants out there who shudder at the thought of software development, think of encapsulation as the segregation of duties and restricted access internal controls…for code.

Let’s deal with item 2, first. Think back to how we would use the variable, Calc.Left, in a formula. For instance, to find out the location to the right of a character, we’d have to do something like =Calc.Left + 1. Notice that this formula can only access Calc.Left but it can’t change it. This is an example of enforcing encapsulation on the spreadsheet. If there are no form controls attached to cell corresponding to Calc.Left, then the only way to change this variable is through code (or you could just type in a new value yourself—but let’s pretend for now we’re talking about a production level item and your variables tab is hidden). And for the Model-View-Controller wonks out there, consider how this dynamic mimics MVC.

Now let’s focus on the first item of encapsulation, item 1 from above. If you’ve been coding for a while, the advice about to give might feel pretty counter-intuitive. Here it is: don’t place your sub procedures in a module. Instead, place related variables and procedures an their associated Sheet objects as I’ve done below.

image

Modules feel like the natural place for subs because that’s what Microsoft’s macro recorder uses. But there’s no reason for it.

[Cue the organ]

How many times have you accidentally performed work on the wrong sheet because you used the ActiveSheet object but were looking at the wrong sheet when you ran your macro? (Raise your hand!) How many times on the wrong cell because ActiveCell wasn’t where it was supposed to be? (You know I’m talking to you!) The Selection object referred to wrong object? (Can I get an amen?)

When you place pertinent code in its associated Sheet object, you no longer need to Activate the desired sheet or reference the sheet the long way.

Worksheets(“Sheet1”).Activate

ActiveSheet.Range(“A1”).Value = 1

Me.Range(“A1”) .Value=1

Here’s why this is great. When you run the code in Sheet1, you never have to worry about it accidentally modifying the wrong sheet. And when you use “Me,” instead of ActiveSheet, the intellisense dropdown appears! Hallelujah!

Oh, and while we’re on the subject…

Principle 3: Stop using ActiveSheet, ActiveCell, ActiveWorkbook, and the Selection object

You really don’t need these objects. Use ThisWorkbook instead of ActiveWorkbook to ensure you are always modifying workbook in which your code resides. If you want to modify a cell, address it directly. Give it a named range like I’ve done above to make it easier. For worksheets, modify their names in the VBA project explorer. (You’ll also need to change the  tab caption as it appears in your tab list to make them match.)

image

Forget Worksheet(1).Range(“A1”) or Worksheets(“Game”).Range(“A1”) or Activesheet.Range(“A1”). Each of these methods relies on something: the desired worksheet being in the first position; the name of the worksheet still being named “Game,”; the sheet being active. Set it and forget it: Just type Game.Range(“A1”), instead, to reference A1 on the worksheet called “Game”. Look at the figure above: isn’t it annoying that Sheet1 is the caption for an object named Sheet2? Change the names!

Principle 4: Break every rule.

Look, I appreciate that you’ve listened to me so far, but astute observers will point out the shorthand range syntax (remember the “[Calc.Left]” from above?) is one of the slowest ways to read and write to a worksheet. And there are some instances where selecting an object appears unavoidable to achieve a desired effect. Also, sometimes hard and fast rules can be broken; like the idea that creating a mouseover effect with over an Excel cell is impossible, turns out, that isn’t always true. Finally, some things, like variable constants, probably DO belong in modules.

The point is, few have thought about using the Excel platform for a game, but that’s exactly what Cary did. He thought outside the cell, so to speak. (I use that concept, trite as it seems, throughout my upcoming book.) All Excel applications require creativity. If you internalize anything from this list of principles it should be that. Rote memorization of rules and formulas will only get you so far, but sometimes you need to try things differently.

Here’s what I’m talking about. One morning I woke up and thought, “wouldn’t it be cool to create a maze in Excel.” I tried a few things but they didn’t really work. And then a few days later, I woke up and it came to me. See:

Maze

Like it? The illuminated pathway on the right is just formulas and conditional formatting. In fact, the pseudo-3D maze in the middle is nothing more than a stacked area chart. See, you don’t need mad GUI skills to create something like this, you just have to think about how to use your current tools differently.

Principle 5: Always use Option Explicit

Actually, this is a rule you should never break. In fact, if you break it, I will judge you. Look, there’s no reason not to use Option Explicit. I believe so strongly in it, I named my blog after it.

What’s next?

If you plan on sticking around, the maze presented above will become the main focus of the following tutorials. We’ll talk about how you can create something like it for yourself. In addition, I’ll provide a little more depth to the different pieces above. For example, the mechanism that creates the illuminated pathway can be expanded to create scrollable arenas—and you can easily create a map editor, too.

Like I said, my approach is different from Cary’s. Different enough, that it was worth pointing out before going forward. But whether you follow or ignore my approach, it’s important to have an approach before diving right in.

Jordan

Grab the download file…

Maze Example

 

The two WORST Excel Errors EVER

In my opinion, these are the two worst Excel errors ever:

  • Code execution has been interrupted, and
  • There was a problem sending the command to the program

A casual Google search will show these errors are as common as they are arcane. Below I’m going to detail my experiences with these errors and how I’ve fixed them. I won’t pretend my experiences are exhaustive, but hopefully they’ll contribute to our understanding of these errors.

Code execution has been interrupted

The problem

When you run a macro, execution is constantly interrupted with a dialog box that says “Code execution has been interrupted” and you’re given a choice to continue, end, debug, or get help.

codex

If you press continue, the dialog will keep reappearing. You’ve looked through your code but found no errors—and yet, the dialog will not stop appearing.

What I think is happening

I’ve found this error often appears in VBA code using loops. Let’s say you realized you’ve accidentally written an infinite loop, so you use CTRL + Pause Break and then tell Excel to end execution. Or, alternatively, a runtime error results in your loop and you tell Excel to end execution rather than fixing the code and continuing.

I’m not an expert on how Excel works in the background, but it appears that when you break execution, that breakpoint is written somewhere in memory. If you do multiple breaks, you may end up writing that breakpoint multiple times. For whatever reason, these breakpoint instructions aren’t cleared when you stop your code.

My guess is, when you run your code again, Excel attempt to use an address in memory that still has a breakpoint instruction in it.

How to fix it

The most immediate fix is to save and then restart your computer. This will wipe out any data stored in your RAM.

In addition, Dave left the following comment in response to this article:

When the “Code execution has been interrupted” dialogue box appears hit debug and then hit ctrl break again (sometimes it you have to hit ctrl break more than once). This seems to reset what ever setting within excel was set after the first ctrl break.

How to prevent it

Try your best not to break execution while in a loop if you don’t need to. I realize however, that’s not realistic advice. So here’s what I do: I plan my loops accordingly. Before running a loop, I include a comment  above about my loop’s terminating condition. If I must press CTRL+BREAK during the loop, I never instruct Excel to END execution. Instead, I press Debug and in the line following the place execution has stopped, I write some code to help the loop terminate safely. For do/loop and while/wend loops, often this mean setting a Boolean condition to true or false. For for/next loops, this means setting the iterator to its max value. You can also do this for runtime errors.

What NOT to do

A few forums have suggested you add the following line to your code:

Application.EnableCancelKey = xlDisabled

This code will disable execution breaks in your code. That means, if you get caught in an endless loop, you won’t be able to use CTRL+BREAK stop it. Therefore, I strongly advice against use this approach. It doesn’t really solve the problem, it ignores it. And, you lose the ability halt all code execution. It’s not a good idea.

What hasn’t really worked for me

When I first encountered this error, I though maybe there was something wrong with the file. So I copied all the code into another file. I also used the various code cleaner products out there written by Excel MVPS. It’s my belief – and I could be wrong about this – these fixes have only appeared to work when I’ve also reset my computer concurrently. I don’t advise against your trying them, but I can’t say for sure if they really solved the problem.

In my experience, properly handling breaks in loops is the best prevention.

There was a problem sending the command to the program

The problem

You open an Excel file and the first thing you see is a message box that says “There was a problem sending the command to the program.” Then the file does not open.

image

This error appeared for me when I had some code that opened another spreadsheet. The code tested for certain items in the other spreadsheet. If a division by zero error occurred, I used the ON ERROR handler to close the other file and then close the current file running the code. Afterward, when I opened the current file again, I saw this popup.

What I think is happening

Again, I’m not an expert on what happens behind the scenes in Excel. However, when you open another file with Excel, Microsoft appears to take advantage of something called the Dynamic Data Exchange, which is what Office applications use to communicate to one another. While handling my error, I never actually cleared the error out, I just told Excel to save and quit. Because of this, some error instruction was written into this file’s memory – and when I open the file again, the error persisted.

How to fix it

The most immediate fix is to do a clean start of Excel, and then to deselect Ignore other applications that use Dynamic Data Exchange (DDE) from Excel’s options menu. If you have Excel 2010 or 2013, you can find this item by clicking File > Options > Advanced tab > General section. For Excel 2007, click the Office Button > Excel Options > Advanced tab > General section.

How to prevent it

If you are handling errors, make sure to use Err.Clear in your error handler. Don’t let Excel close without handling and clearing all runtime errors. In fact, don’t do what I did: don’t use ON ERROR to handle errors you can anticipate. Test for those instead. Use ON ERROR for errors you can’t anticipate. 

What NOT to do

If you encounter this problem in your spreadsheet, make sure you understand what’s causing the error, even if it doesn’t happen very often. The fix above will allow you to once again open the file. But make sure to investigate the root cause, and try your best to make sure all potential errors are handled. I’ve seen Excel add-ins that instruct users who’ve had to perform a forced-shutdown of Excel that the method above is the “fix” to the problem. I suppose not all errors are foreseeable, but preventing forced shutdowns should be a primary goal of any product.

What hasn’t really worked for me

In this case, restarting my computer didn’t seem to provide any relief.