Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 22. February 2010 10:23

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:

RibbonFolder

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

EmptyRibbon

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...

by Mathias 21. February 2010 13:36

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.

by Mathias 16. February 2010 19:27

Now that we have created the VSTO add-in project, it’s time to add some functionality to it. We want to provide a user interface to select what sheets we want to compare, and navigate between the differences that the add-in has found. In order to do this, we will create a custom task pane.

You can think of a custom task pane as a placeholder for controls. The best way to illustrate the concept is to simply do it. In our project, we will add a folder “TaskPane”, and add a new User Control by right-clicking on the TaskPane folder, which we will name “TaskPaneView”.

SolutionWithTaskPaneFolder

If you double-click on TaskPaneView, visual studio will display a gray empty area. This is the “canvas” on which we will add controls later, to allow the user to call the operations our add-in will expose. For now, we’ll leave it at that, and just focus on displaying the task pane.

Now go to the ThisAddIn class, and add the following code in the startup method:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    var taskPaneView = new TaskPaneView();
    var myTaskPane = this.CustomTaskPanes.Add(taskPaneView, "Anakin");
    myTaskPane.Visible = true;
}

Hit F5 to debut, and you should see the following:

DockedTaskPane

More...

by Mathias 12. February 2010 13:33

In the recent past, I have been developing solutions for clients using VSTO – Visual Studio Tools for Office – and I believe that VSTO is a technology which has a lot to offer. I used to write a lot of VBA code to automate Excel, but after spending the past 5 years or so writing C# code using .Net, I find VBA limiting, and the development environment extremely frustrating. So when I saw that Jon Peltier had begun a tutorial series on how to build a classic Excel add-in, I thought, why not do the same with VSTO?

My baseline objective will be to write an add-in which tracks the differences between two open worksheets, and allows the user to merge some of the contents, if appropriate.

As a secondary objective, I will try to showcase some of the benefits of using VSTO over VBA; for that reason, the add-in will be built for Excel 2007, and use the Ribbon, and .Net 3.5, so that we can use some WPF controls. I will do my best to highlight some of the differences between VBA and C# along the path, as well as highlight some important aspects of the Excel object model. As a result, some places will probably seem slow to the experienced .Net developer, and some others to the VBA veteran.

Unlike Jon, I have not written my add-in yet; I will share the successes and struggles as I progress along. The main tasks/areas we will have to cover are

  • hooking the add-in to the Excel user interface,
  • accessing the Excel objects through the add-in,
  • creating custom controls to select sheets,
  • identifying differences between worksheets,
  • listening to Excel events,
  • deploying the add-in.

I will likely dig into Excel 2010, .Net 4.0 and Visual Studio 2010 along the path, but given that Office 2007 hasn’t been adopted by all yet, this will not be my initial focus.

More...

by Mathias 6. February 2010 13:26

I am currently working on a project which extends an Excel VSTO add-in model I had developed a few months back. This is a joint project, and my add-in has to interact with a classic Excel worksheet model, which got me worried. The original model read data from Excel into a C# object, which handles the heavy-duty computation, and writes back results to a spreadsheet once it is done. The modified model has to proceed in 2 steps: perform a partial read of the inputs, compute some outputs, feed them into the worksheet model, read some results from that worksheet, resume computations and write out the final outputs.

The reason I was worried is that the spreadsheet I have to interact with is a bit slow, and I was concerned about a race condition type of problem. What if the add-in attempted to read data from the worksheet, before Excel had time to update the values in the worksheet?

In order to check whether there was a problem, I created a small test case. I first wrote a VBA function which was on purpose very slow:

Public Function SlowFunction(arg As String) As String

    WaitFor 10
    SlowFunction = arg

End Function

Public Function WaitFor(seconds As Integer)

    Dim startTime As Double
    startTime = timer
    
    Do While timer < startTime + seconds
    Loop

End Function
The SlowFunction simply takes a string as input, calls the WaitFor function, which stays busy for a few seconds, and returns the input string after 10 seconds have elapsed. 

This allowed me to artificially create an extremely inefficient worksheet: when the input cell A1 is modified, the output cell A2 is updated only 10 seconds later.

SlowWorkbook

More...

Comments

Comment RSS