While working on my VSTO Excel add-in tutorial, I came across the following issue: I need to know whether a worksheet has been deleted. The reason I care is that when it happens, I need to update the display of the worksheets that are currently open, and remove it from there.

I was very surprised to find out that there seems to be no event for this. The Application object, which represents the Excel application, has a WorkbookBeforeClose event; the Workbook object has an event BeforeClose, triggered when the Workbook is being closed. So naturally, I expected to find something equivalent for the Worksheet object, at either the Application, Workbook, Sheets, Worksheets, or Worksheet level – no such luck.

I looked around on the web, and from what I can tell, there is no native event for this, and I came across multiple posts advocating to handle this through Worksheet.Activate and/or Worksheet.Deactivate. I see how this catches the obvious use case, namely, the user selects the sheet and deletes it – which causes the sheet to be activated, and then another worksheet to be activated once the deletion is performed. Unfortunately, this doesn’t catch all the cases: as far as I can tell, it is perfectly possible to delete a worksheet without ever changing which sheet is active. To prove the point, create a workbook, and add the following macro:

Public Sub DeleteSheet3()
    Application.DisplayAlerts = False
    Sheets("Sheet3").Delete
    Application.DisplayAlerts = True
End Sub

More...

Previous episodes

1. Getting Started
2. Using the Custom Task Pane
3. Using the Ribbon
4. Adding a WPF control

The shell of our control is ready – today we will fill the TreeView with all the open workbooks and worksheets. We will use a common design pattern in WPF: we will create objects that act as intermediary between the user interface and the domain objects. This approach is know as MVVM (Model-View-ViewModel) in WPF, and is a variation on the classic Model-View-Presenter pattern – the main difference being that MVVM relies heavily on the data binding capabilities of WPF.

As usual, Josh Smith has some great material on how to use the WPF TreeView, which is highly recommended reading – and was a life-saver in figuring out how things work.

In a first step, we will fill in the TreeView with fake data, and once the UI “works”, we will hook up the objects to retrieve real data from Excel.

To quote Josh Smith, “the WinForms TreeView control is not really providing a “view” of a tree: it is the tree”, whereas “the TreeView in our WPF programs to literally provide a view of a tree”, to which we want to bind. In our case, the tree we want to represent is that Excel has a collection of Workbook objects, which each has a collection of Worksheet objects. Let’s build that structure.

More...

Now that our Custom Task Pane is in place, and that we can drive its visibility with the Ribbon, it’s time to begin adding some real functionality to the add-in. In our next two installments, we will create a tree view in the task pane, which will display all the workbooks that are currently open, and the worksheets within each workbook. Later on, we will use that tree view to select the worksheet we want to compare the current active worksheet to.

I will use WPF to create our tree view, instead of a Windows Form user control. While WinForms is probably more familiar to most developers, I really wanted to use WPF in this example, because I love the flexibility it provides in user interface design, and because this is where the future of UI design is at. I can’t do a full tutorial on WPF here; I’ll try my best to explain what is going on and provide pointers, but if you haven’t seen xaml before, you will probably find some parts confusing – I hope the result will be interesting enough to motivate further exploration!

For the Windows Forms fans, Dennis Wallentin has an excellent tutorial on how to populate a WinForms tree view, for a very similar problem; I encourage you to check it out.

More...

In our previous installment, we went through adding a Custom Task Pane to Excel to host the user interface of our VSTO add-in. However, we left off with one problem to solve. The task pane is shown when the add-in starts up, but if the user closes it, there is no mechanism to show it again. We will resolve that problem by using the ribbon, adding a button that restores the task pane visibility.

First, we will create a new folder in our project, called “Ribbon”. Right-click the folder, select “Add > New Item”, and pick “Ribbon (Visual Designer)” from the available templates. We will call our ribbon “AnakinRibbon”.

By now, your solution should look like this:

Visual Studio displays a visual interface, representing the ribbon we will use for Anakin:

By default, the ribbon comes pre-populated with a tab called “TabAddIns”, labeled Built-In. This reflects the fact that, by default, your add-in ribbon will show up in the standard Add-Ins tab of the ribbon.

While this would be perfectly acceptable, we actually want to add our add-in to an existing Ribbon tab, the “Review” tab. It seems like a natural place to find functionality related to comparing different versions of a spreadsheet, and this way, we can avoid crowding the Ribbon with new tabs, and integrate seamlessly with Office, without minimal disturbance to the user experience.

More...

Who should get this book

This book is definitely for programmers. It’s a series of 15 lively interviews with legendary figures in software development, covering question ranging from what makes code beautiful to how to recognize a good programmer. If you are interested in the history of the young field of software, and want to get some perspective from highly respected people in the area, this book is for you!

3 things I enjoyed about it

Hear what the Masters have to say: every developer has his/her opinion on what good code is, whether comments are good or bad, or how to debug a program. This book gives you the opportunity to hear what people who really know what they are talking about think about this. Chances are, you won’t be able to talk about this with Donald Knuth around the water cooler and hear his take – you’ll get that with this book. Furthermore, having 15 different takes on similar questions provides an interesting way to compare views, and see where everyone agrees, and where there is disagreement.

A lively discussion: full credit goes to the author, Peter Seibel. He is a great interviewer, and has solid credentials as a developer, and it shows. He asks great questions, and the excitement of the discussion shows in the book. It’s also full of anecdotes and stories from the trenches, and fantastic quotes, making it a really entertaining read.

Literate programming: I was at least familiar with most topic discussed in the book, but I had never heard of literate programming before. Most of the interviews discussed this approach to programming, and generated interesting discussions - and made me curious about it.

3 ways I would have liked it better

Old school: I often feel that I started computing in the Dark Ages. I mean, my first computer had no mouse and hard drive, and its floppy drive made it cutting edge. Most of the guys in the book have worked with punch cards, and talk about devices which I can’t even begin to imagine the purpose of (rotating cylinders?). On one hand, it makes for great anecdotes, and gives a broader perspective on software development. On the other hand, it felt a few times like what they are describing is a bit disconnected from my own experience.

Length: I really enjoyed the book, but I had to read it by small installments. As much as I am a geek, there is just that much I can read about this in one single day!

… and I couldn’t find a third criticism.

Final thoughts

I really enjoyed that book, and would recommend it to anyone who is interested in software development, and its history. There are two points that I found intriguing in the book. First, no one seems to like C++ – which came a bit as a surprise to me. I don’t use C++ myself, but I naively thought that as a widely used OO language, it would have supporters. It doesn’t seem to be the case. The other point I found interesting is that the idea of design patterns was shot down by quite a few people, with arguments along the lines of the architecture astronaut criticism dear to Joel Spolsky. I definitely respect that opinion, but I wonder if this is generational, and has to do in part with developers used to work with low-level languages, in a time when low-level concerns mattered more than today.