VSTO & .NET & Excel

January 28, 2008

Measure Data Quality

Filed under: .NET & Excel, COM Add-ins, VSTO & Excel — Dennis M Wallentin @ 9:33 pm

Nowadays it’s a common task to retrieve data from databases and put the data into MS Excel. The acquired data is usually part of the information in decision processes. 

The data that is stored in databases are either collected automatically or manually. The process of collecting data manually can either be dictating or free. With ‘dictating’ I mean that no fields are allowed to be left empty upon registration while with ‘free’ I refer to the opposite, i e fields can be left empty.

One of the more critical data for all kind of corporate is the volume of quotations which is a key performance indicator (KPI). This type of data is usually manually entered into the business system.

Measuring the quality of the data is to try to answer the question to which degree the data is accurate and reliable. To do it we can use different approaches where we can create rather sophisticated models but here I prefer to keep it simple. The reason for it is that we need to make sure that it can be communicated in an easy way within the corporate in order to improve the quality. In my experience we should therefore keep it straight and simple.

A dictating system may seems to be attractive to use as it makes sure that users enter all required data by filling in all fields. But in view of the quality of the data we get a situation where it is very difficult to actually measure it. A free system can be a better approach as it allows to leave data fields empty which makes it possible to, at least,  measure which fields are filled in and not.

The following is an output from a free system where the data is retrieved in order to present the volume of quotations for a given time period:


As we can see in the above table some fields are left empty. To measure we need to decide which fields are important as indicators of the data quality. Here in the example the fields Expiring Month (EM), Sales Type (ST) and Sales Value (SV) have been selected.

Here we only measure the number of records that has one or more of these fields empty or not. The following table shows the output for the case:


In some corporate the presentation paradigm is based on charts instead of tables so here is the output presented in a chart as well:

(Based on the input from Dermot and Jon the chart is revised to the better)

The output of this simple measurement of quality indicates how accurate and reliable the information for the volume of quotations actually is.

In my experience, when some indicators for the quality of the data are available the decision makers gets a better understanding for the presented information. The indicators also provide input to improve the quality of data.

Kind regards,


January 20, 2008

RibbonX: Customizing the Office 2007 Ribbon – Review

Filed under: .NET Books, VSTO Books — Dennis M Wallentin @ 10:52 pm

RibbonX: Customizing the Office 2007 Ribbon
Robert Martin, Ken Puls, Teresa Henning (Oliver Stohr)
Target group:
According to me: Power user to professional VBA-developers
Targeting softwares in the Office suite:
Excel 2007, Word 2007 and Access 2007
7.5 (out of 10)

This book is the first of its kind as it explicit only target the RibbonX and how to manipulate and customize it. It covers how to work with RibbonX in Access, Excel and Word.

According to the book itself it contains two parts but for me it contains three parts where the first part serves as an introduction to RibbonX, XML and VBA. The second part is a walkthrough of the RibbonX’s object model and the final part leverage the preceding parts as well as cover some advanced topics.

Part I
The authors present the Ribbon UI and discuss its pros and cons. As we all know, new things solve some issues but also at the same time create new issues and the Ribbon UI is no exception from that rule. Two important tools are also presented, the Custom UI Editor and XML Notepad which is probably not so known among VBA-developers. Together with the presentation of the tools the tools’ pros and cons are also being discussed. ‘XML for RibbonX’ is also presented here together with a chapter for beginners of VBA.  

I find it to be good that the authors actually also discuss the shortcomings of new technologies and tools. The introduction is well written but does not give a ‘clear picture’ for the remaining parts of the book as it tries to target all groups from ‘novice to professional’.

Part II
This part is the backbone of the book as it describe the RibbonX’s objects model in detail and provides us with a great number of examples for Excel, Word and Access. Whenever there is a discrepancy between the softwares, on how they handle the RibbonX’s object model or how the XML files needs to be written, we are given examples that cover it. The presentations of the objects are strictly and a lot of data about the objects are given.

All the chapters are well written and it covers all aspects of the RibbonX’s objects model. It can be a little bit confusing when focus is switched from one software to another one but the authors have managed to keep it together.

Make sure You got the book available when developing RibbonX’s solutions as You need to go back to this part on a regular basis.

Part III
In this part the authors put things together and provide us with practical cases as well with some advanced topics on both VBA and on how to customize the RibbonX.

Here You will find some interesting aspects such as creating UI for Web Service and to work with contextual controls, keytips & keyboard shortcuts as well as how to share and deploy ribbon customizations. By the way, the chapter ‘Sharing and Deploying Ribbon Customizations’ is excellent.

For me this part of the book is the best part. In these chapters the authors push the customization of the RibbonX to the limit but also at the same point out some ‘traps’ to avoid.

Missing entries
The following aspects would have been nice to take part of:

I consider the RibbonX UI design to be very important. Therefore I was surprised that the book didn’t cover or at least discussed best practice for Ribbon UI design.

The book discusses reading and writing settings to Windows registry which is good. The way security is implemented in Windows Vista that approach may not always be the best alternative. On the .NET platform we use XML files for various tasks and since RibbonX rely on XML it would have been natural to also cover XML files to store settings in.

A book that explicit focus on how to customize the RibbonX should at least have some part that introduce RibbonX developing on the .NET platform. It becomes more important when it exist some shortcomings via VBA which the book also discuss.

Special kudos to the authors for:

  • Emphasizing on functionality and by doing so they don’t get ‘carried away’. 
  • Recommend to use a table-driven approach when customizing the RibbonX.
  • RibbonX Naming Convention which is based on RVAB naming conventions. This is a good start in order to create a standard on how we name the RibbonX’s object model in code. We may not necessary agree but it allow us to discuss the subject.
  • The tool, imageMSO Reference, which allows us to lookup imageMSO and gets the XML code for the selected imageMSO. Of course, I would be very pleased if it had been created as a managed COM add-in but that’s another story. 

This book provides us with a depth on how to control the RibbonX and it does it well. 

If You are a serious VBA developer who target Access 2007 /  Excel 2007 / Word 2007 then this book is a must have.

Kind regards,

January 10, 2008

Excel User Group

Filed under: .NET & Excel, VSTO & Excel — Dennis M Wallentin @ 1:14 pm

Nick Hodge has recently set up a new peer-to-peer forum with Blogs, Forums and File areas for Excel users.

For more information and member registration please see: Excel User Group

What makes it attractive is that it has an Add-ins subforum:

This subforum will discuss and also be a Q&A forum for:

  • COM Add-ins created with VSTO (and also about workbook level solutions + templates.)
  • COM Add-ins created with C#/VB
  • COM Add-ins created with classic VB/C++
  • Automation Add-ins created with classic VB/C++
  • Automation Add-ins created with C#/VB
  • XLLs (Add-ins) created with C++ and C/C++

The subforum also covers deployment of all the above add-ins types.

Kind regards,

Create a free website or blog at WordPress.com.