The 2nd Edition of Professional Excel Development (PED) is available!

General
The book “Professional Excel Development” (aka “PED”) is now available in the second edition. The major news is that .NET including VSTO has been added to the book. Because I’m responsible for the .NET section in the book the list of authors now also includes my name too.

For some weeks ago Ross McLean had the kindness to make an interview with me. Instead of repeating what I said in the interview you can read the full story here. However, I would like to thank Rob Bovey  for being an excellent teamleader for this edition. Another person I would also like to thank is Gabhan Barry (a Program Manager in the Excel group at Microsoft) who made the technical review on the .NET chapters.

New Chapters
The second editions includes five new chapters:

Chapter 10 – The Office 2007 Ribbon User Interface:
This chapter cover the Ribbon UI paradigm and discuss some advanced problem solving with the new UI.

Chapter 11 – Creating Cross-Version Applications:
In view of the fact that the Ribbon UI was introduced with Excel 2007 and that Windows Vista differ from Windows XP we decided to add this chapter. It covers how to create applications that target both Excel 2003 and previously versions and Excel 2007. We also discuss the major differences between the two operating systems.

Chapter 24 – Excel and VB.NET:
In the first part we introduce the IDE in detail and cover some basic VB.NET programming. In the second part of the chapter we discuss how to automate Excel with VB.NET.
The chapter present a practical case, PETRAS Report Tool.NET which is a standalone utility to retrieve data from PETRAS SQL Server database and populate some Excel reports templates.

Chapter 25 – Writing Managed COM Add-ins with VB.NET:
This chapter is the “flag ship” of the .NET section and goes in detail on how to create managed COM Add-ins and it also discuss in detail Automation Add-ins.
Here we also port the practical standalone utility, PETRAS Report Tool.NET. to a managed COM Add-in.

Chapter 26 – Developing Excel Solutions with Visual Studio Tools for Office System (VSTO):
In the first part of the chapter we set focus on two questions: What is VSTO and When to use VSTO. We also discuss VSTO add-ins, VSTO workbooks and how to deploy a VSTO workbook via the Web with the ClickOnce technology. To leverage the content in this chapter it requires that you have access to Excel 2007.

To buy the book or not?
If you have an interest in Excel and .NET including VSTO then you should consider buying the second edition. However, if your concern is native Excel and VBA then it is better that you save the money and wait until the next edition of the book is available.

If you want to buy or rank the book then please click here. 

PED’s site
With this edition we decided to build a completely new site for the book with Q&A forums. The intention with the site is to allow you to interact with the authors and leave suggestions for future editions of the book. The Q&A forums are built around the book’s content and they have a similar structure as the book’s TOC. In addition, it is here you will find information about updates and more downloads. 
We strongly encourage you to become a member and discuss the book in detail. Rob and I will try to answer all questions to the best of our knowledge. At the same time we feel it is important to state that the site is not a general Q&A forum about the involved technologies.

Together with the publisher we have made two chapters available for free download. To find out more please visit the PED’s site.

Let me know if you want any further information.

Kind regards,
Dennis

Advertisement

Sheet Navigation in The Ribbon UI – Part II

In my first post about Sheet Navigation I showed a basic approach. Here I will show a more practical and realistic solution to navigate between sheets. The example is based on a VSTO workbook however it can be applied to both native Excel solutions as well as managed COM/VSTO add-ins.

When it comes to customize the Ribbon UI with VBA and with VB.NET the major difference is the callbacks signatures. Because I constantly forget which signatures to be used depending on which language I use I check with the following article: Customizing the 2007 Office Fluent Ribbon for Developers (Part 3 of 3).

Anyway, in this example I use the dropDown control but the drawback with it is that it does not explicitly give us the name of the selected item, only its ID and index number. Therefore we need to resolve it in the code. 

Although we should strive to create fully dynamic applications, they come with the price of having to write code for all possible scenarios. In real-world applications we therefore tend to create semifixed applications where some parts of the solutions are fixed and some parts are dynamic. In the example here we have a fixed list of sheets whose names will not change during runtime and we therefore do not need to invalidate the dropDown control during runtime. On the other hand, we will not hard-code the sheets names in order to ease the maintenance.

The following screen shot shows the whole VSTO project: 

As we can see we have four worksheets and one chart. One of the worksheets, “Hidden”, is hidden which we also need to resolve in the code.

The following screen shot lists the Ribbon UI XML solution:

The next screen shot shows the relevant code in the ThisWorkbook module:

We grab the collection of sheets in the workbook in a sheet collection variable which we use in the callbacks signatures as the following screen shot shows:

It should be noted that I use Option Strict for the example solution, i.e. it forces us to cast.  When running it we can navigate between the sheets rather smooth as the following screen shot lists:

The example shows that we can rather easily create robust solutions to navigate between the sheets in a workbook. Compared with the basic approach the above solution gives the end users a better option as it shows the names of the sheets.

In the coming blogpost I will demonstrate how we can implement the Ribbon UI in Windows Forms.

Kind regards,
Dennis