Implement the Ribbon UI in Windows Forms

Some of us Excel developers like the Ribbon UI while others find it more difficult to work with it. No matter what we developers think about it exist a growing number of clients requiring that the Ribbon UI to be implemented in .NET based Excel solutions.

Microsoft offers no controls that can be used to implement any Ribbon UI solution so we are forced to decide which third-party controls package to be used.  Of cause, most large vendors, such as Component One et al, offer components that can be used but they can be rather expensive.  DotNetBar from DevComponents is an inexpensive controls package including controls for the Ribbon UI.  I have been using the package for a while in clients solutions. DotNetBar includes 47 controls whereof some controls allow us to create a Ribbon UI in Windows Forms. All controls that are shipped with DotNetBar are highly customized. The controls are also frequently updated and new versions of DotNetBar are released on a regular basis.

With the package a collection of relevant case studies are shipped, both in VB and in C#. A knowledgebase is available to the DotNetBar customers and I find it easy to use it and it also provides relevant links based on the search criterias. The knowledgebase can also be accessed via the help file. The help file describes, in a strictly technical way, the DotNetBat assembly and its namespaces.

The following screen shot shows DotNetBar Samples Explorer: 

Because I find the controls to be good, especially the Ribbon UI controls, I have decided to use them for the upcoming .NET version of my commercial tool for Excel, the SQL Tester. The following screen shots are from the alpha version of SQL Tester.NET and all controls in use are part of DotNetBar package. 

The following screen shot views the main Windows Forms for SQL Tester.NET in the VS IDE. In addition to use a Ribbon UI it also use a tabcontrol with two tabs. These tabs will be synchronized with the tabs in the Ribbon UI and therefore is hidden when the tool is being executed.

Next screen shot shows the code in use to synchronize the tabs in the tab control with the tabs in the Ribbon control:

Having access to a tab’s click event is something we miss when building native Ribbon UI solutions in Excel. With DotNetBar we have it available without the need to create any workaround. Initially it may take some time to get used to the controls and what they offer. In my experience I would like to say that the more time we put into learning the controls the more we can leverage them in our Windows Forms solutions.

The following two screen shots show when SQL Tester.NET is being executed and we switch between the tabs in the Ribbon UI solution.

Kind regards,
Dennis

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

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

Sheet Navigation in The Ribbon UI – Part I

This is the first post out of two that will be dealing with Sheet Navigation and how we create solutions for it in the Ribbon UI. In my experience sheet navigation is common for large-scale dictator applications but also for smaller application solutions.

The example provided here is based on a VSTO workbook solution which contains five worksheets and the whole solution is showed in the following screen shot:

The end user UI is based on four customized images and when the users run the solution the following UI is available:

The customized Ribbon UI solution is a basic approach that does not involve any advanced or complext coding. The following procedure is executed when users click on one of the image buttons:

As we can see we convert the ActiveSheet to an Interop worksheet which gives us access to the Previous and Next methods of the worksheet object. Other then that standard code is being use and should not create any issuse.

In the second post I will discuss more advanced sheet navigation in the Ribbon UI.

Kind regards,
Dennis

Sharing Custom Tabs in The Ribbon UI

To let add-ins (native, unmanaged/managed COM add-ins and VSTO add-ins) share tabs is a good strategy but it can be applied in two different ways. Which to apply is depended on the individual add-ins.

Sharing the Add-ins Tab
For small application it is not necessary to use tabs on their own. Instead we can share the Add-in tab with other add-ins as the first screen shot shows:

 In this case we use the following customized Ribbon XML:

It should be noted that it is not required that any classic CommandBar add-in is activated to access the built-in tab Add-ins. 

Sharing Custom Tabs
For large-scale applications it may be an advantage to split the applications in smaller parts and let the parts share a custom tab. To get there it requires that we:

1. Use a unique identical namespace and 
2. Use a unique identical idQ attribute in the add-ins’ customized Ribbon XML.

A result of this is showed in the following screen shot:

To produce this output we use the following customized Ribbon XML in the add-ins.

The first add-in’s customized Ribbon XML:

 

The second add-in’s customized Ribbon XML:

As we can see they share the same namespace and call the same idQ attribute which makes it possible to let add-ins share a custom tab.

The customized Ribbon XML for sharing the custom tab is available here.

Kind regards,
Dennis

Using Custom Images in Ribbon UI Solutions

Although the number of built-in images in Excel is large we still may want to use custom images. Because it exist a great number of inexpensive commercial images packages it is not justified to spend our time to create customized images.

So the question is which image format to use? The Ribbon drawing engine is designed to work best with full-color (24-bit) images that also have an alpha channel to control each pixel’s transparency. Since the PNG file format supports an alpha channel and produces relatively small files, it is the best alternative. The preferred sizes of custom images are either 16×16 (small size) or 32×32 (large size).

The first step is to add the selected images to a project’s resources and then create the code to use them. Adding images to the resources is not an issue and the first figure shows the added images for the case.

 

The next screen shot shows the content of the Ribbon.xml file which is also added to the resources of the project:

The following screen shot shows the class Ribbon.vb:

The key to the solution is the function GetImage which uses the System.Drawing namespace to return Bitmap objects.

The next screen shot shows ThisAddin class of the VSTO Add-in I use for the case:

 

When running the above code the customized Ribbon UI solutions looks like the following:

Looking on the solution it reveals nothing extraordinary in order to use custom images in Ribbon UI solutions.  

Kind regards,
Dennis

(If we only have access to images in the GIF or BMP or JPEG file format then we can apply the same approach as with the PNG images.)

Load Ribbon UI XML files

For some years I have used one approach to load Ribbon XML files in VB.NET’s solutions. Recently I discovered that it exist a better approach. Perhaps not better but definitely a better looking “one line” solution. I find it interesting that we can always solve code related issues in one of three ways; bad, OK and excellent. In the most cases I solve them OK.

Anyway, the key to the better solution is to add the Ribbon XML file to the project’s Resource list. Next, in the GetCustomUI function the file is loaded in one go as we also can see in the picture below (line 28).

I guess I need to explore My.Resources more!

Kind regards,
Dennis

SharePoint – The Start

Last year I decided to learn more about Microsoft Office SharePoint Server (MOSS) and in particular Excel Services. Instead of setting up a VMWare configuration I decided to buy a physical server to which my developing machine will be connected to as the only client.  Because of my security approach none of these two machines have access to Internet. Yes, I’m the first to admit that I’m paranoid but that is the way it is.

Hardware
I got a nice price for the server Fujitsu Siemens FSC PRIMERGY ECONEL 100 S2 and bought more memory so it now has 2 GB. The server has two hard drives and therefore I decided to use a RAID 0  solution so one drive mirror the other drive. Previously I only had a 2 port KVM Switch but now I needed a 4 port Switch so I can switch between my laptop, my development machine and the server. The model I selected was Athen CS1734B which is one of a very few KVM Switches that can work with Windows Server 2008.

Softwares
I decided to use Windows Server 2008 x64 standard edition which also gives access to the Hyper V technology. Unless we have all the knowledge required to configure and running a server and installing all the softwares we need some helps. In my case I prefer printed books and the help I got is from the book “Windows Server 2008 A Beginner’s Guide“. It covers the most basic to install and configure a Windows 2008 server and is written in a understandable way.

Next, a SQL Server 2008 Express Edition was installed and it did not cause any problems at all.

Installing a MOSS 2007 server requires one important preparation, to set up 11 accounts with names and passwords. They are required for both the Windows SharePoint Services (WSS) and for the MOSS server. Andreas Glaser has published a nice installation guide; Installing MOSS 2007 on Windows Server 2008 and SQL Server 2008 . However, to understand more a book is required and I was recommended to use “Beginning SharePoint 2007 Administration” written by my fellow countryman Göras Husman. At present I have only started to read the book.

Finally, the only thing that remains to do is to learn more about MOSS and what we can do with its Excel Services!

Kind regards,
Dennis

VSTO Office FAQ Entry List

The VSTO support team has released the first FAQ for VSTO and Office. It is a start and it single out the most frequent questions together with the answers:

VSTO Office FAQ Entry List

This is good for all developers new to VSTO. What are annoying, at least to me, is that all the answers points to solution where C# is used. We know that this is the preferable language inside Microsoft but why do they continue to ignore all the VB developers? How many average VBA-developers will jump on the C# bandwagon when starting out with .NET? OK, most VBA-developers will probably never touch .NET but those that really do it are forced to either:

a) learn C# or
b) convert all C# code with tools like ConvertCSharp2VB.

I don’t understand why VB does not have the same status as C# at Microsoft, especially when many corporate developers use it on a daily basis.

Kind regards,
Dennis

VSTO news

In a recent blog by Soma Segar, Office client developer enhancements with VS 2010, indicates that the next version of VSTO, version 4.0, comes with some great news, especially the PIA-less interoperability. In addition, C# will have an improved support for interoperability coding against Office.

From a general point of view I hope that the upcoming VSTO version includes new extension for Pivot Tables as well. If so then we can use ADO.NET/LINQ to populate Pivot Tables. VSTO 3.0 includes extensions for Chart, ListObject, NamedRange and XMLMappedRange and the Pivot Table, at least for me, is a natural object of this collection.

For Excel developers perhaps the most important question is about UDFs. Using VSTO 3.0 for UDFs, both add-ins and with workbooks, requires that we write VBA wrappers for every UDF. Compared with the other options we have; using VBA, using managed Automation add-ins and using XLLs, using VSTO for UDFs is simple the less attractive approach to use. Will VSTO 4.0 make a change or not?

Hopefully some of the news, like the PIA-less interoperability and customize the deployment package, also target the Shared Add-in template. After all, using managed COM add-ins is still an important area.

The high pace of releasing new VSTO versions may create an unwanted situation; today’s issues are not solved with the present version. In other words, instead of releasing major SPs new versions are released. The question is to which degree we should follow the pace that Microsoft put up. In my own opinion I see a need to accept to upgrade until VS and VSTO have reached the maturity stage. When this happen we will get a similar situation with the development tools as with Excel, i.e. new versions only compete against older versions.

Speaking about VBA, I have spent considerable time with the Ribbon UI and VBA. In VSTO we have fully support for the Ribbon XML, we have access to a Ribbon Visual Designer where callbacks can automatically be generated. In VBA we do not have the same support and in fact have no support at all. It’s vital for us VBA developer to get access to an object model to manipulate the Ribbon UI,  get a standard GetCustomUI event added to the Workbook object and a CreateCustomUI method added to the Commandbars Objects. In view of the present situation I can only conclude that VBA is classified by Microsoft as a second class citizen.

Kind regards,
Dennis