Check which Excel version is installed

Whenever I need some enviroment information I always tries to read the wanted values from Windows registry.  In VB.NET this is as rather straightful process as we have access to the namespace “Microsoft.Win32′ which includes the class to work with the registry.

Recently I had a need to find out if Excel is installed and if so if the version is 2002 or later was available. Since I’m also trying to learn how to use regular expression I took this opportunity to use it.

The following code shows the function and how to get the wanted information from the registry:
 

The above approach works in both Windows XP and Windows Vista and it does not require that users must have administrative rights.

The code can also be downloaded as a textfile here.

Kind regards,
Dennis

VB Snippet Editor

Together with a lot of Intellisense improvements a large number of Code Snippets is also shipped with VB 2008. But what may be surprising is that there is no tool included to create and manage our own Code Snippets with.  This is where VB Snippet Editor comes in. It gives us the option to develop our own snippets in a rather easy.

The following screenshot shows the tool in action and its main screen: 

 

To download the tool: VB Snippet Editor

If I have the time for it I may consider to make my own snippets available as a download.

Kind regards,
Dennis 

VSTO Power Tools

Andrew Whitechapel announced yesterday that a new toolkit for VSTO and VSTA now is available for download. 

I will test most of the tools and review some of them here. For me these kinds of tools are very important as they can both save time during the development cycle and when troubleshooting targeting computers.

What should be noted is the following:

• The toolkit is available “as is” meaning MSFT does not give any officially support for it.
• The toolkit explicit target Visual Studio 2008 and Office 2007. I will test the VSTO Troubleshooter tool with Excel 2003 to find out if it is compatible or not.

Those of us who use VSTO should take the time to test and to give feedback in order to improve the toolkit. Does anyone know how we can communicate the feedback?

For me it’s too early to implement any of the class libraries ’Office Custom UI’ manager and ‘Office Interop API extensions’ in production solutions. Nevertheless, it looks very promising!

Kind regards,
Dennis

Export data from DataGridView to Excel

A common task is to view filtered data from databases in Windows applications where the users then can select records to be imported into Excel. In this context the main issue is usually how to achieve an acceptable performance when writing the selected data to Excel.

When it comes to automation of Excel the communication between Excel and for instance a .NET application is said to be an Out-of-Process communication as Windows allocate separated memory areas to them. In other words, the applications do not share the same memory area which has a negative impact on the overall performance.

When it comes to .NET automation of Excel there are two additional aspects we need to be aware of:

• The COM interop calls themselves create an overhead and
• The more calls to Excel Object Model the slower it gets.

The best approach is therefore to reduce the number of calls to Excel Object Model and before calling Excel prepare the data as much as possible. In VBA we have the data type ‘Variant’ which is good to use when working with cells’s data. In .NET we have a similar data type by the name of ’Object’. The final key to increase the speed is to use as much as possible 2D arrays (which are 0-based) and when necessary declare them as ’Object’.

The Case

For the case I use the Northwind database which is connected to a SQL Server 2005 where the data is presented for the users in the following Windows form:

 

The screenshot below shows all the code in use for the case. The ‘Export’ procedure shows the use of 2D arrays where they in the first step are populated with data in managed code. In the next step the data is ‘dumped’ in one go into the targeting COM worksheet:

The above code can be downloaded from here. Of course, in the above case we could have reduced the number of arrays to only use one.

When I have managed to fully port myself to VS 2008 and when SQL Server 2008 is out I will make some posts about LINQ to SQL.

Kind regards,
Dennis

Measure Data Quality

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

RibbonX: Customizing the Office 2007 Ribbon - Review

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

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

Summarize
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,
Dennis

Excel User Group

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:

VSTO/COM/XLL Add-Ins
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,
Dennis

Excel 2003 & Windows Vista bug?

Introduction
After the release of .NET Co Library I immediately received some inputs indicating that the add-in does not work with Excel 2003 on Windows Vista. For some unknown reasons, at least for me, this configuration was not tested before I released the add-in. (It was tested on Windows Vista where Office 2003 and Office 2007 coexisted but not Office 2003 alone on Windows Vista.)

Initially I thought it was related to the add-in itself and how it added the main menu item to the standard toolbar in the VB Editor. But when I made some additional debugging and tests it turned out to be related to Excel 2003 when running it on Windows Vista. 

The case 
I set up the following vmWare configuration:

• Windows Vista Business
• Office 2003 Professional Edition & Service Pack 3
• Visual Studio 2008 

I made sure that the security settings in Excel 2003 were correct as the following image shows:

 

It does not exist any activated add-ins at all. Next I created a small Windows Form project with VB and made sure that all references to Excel existed as the following image shows:

 

In the final step I wrote the following simple code in a Button Click event:

I also created a similar small project in C# as the following image shows: 

Programmatic access to Visual Basic Project is not trusted
When the above code was executed it showed the first message correctly while it generated the following error message when trying to access the VBE’s toolbar:

A bug?
When running Excel 2003 & SP-3 on Windows XP Professional it works as expected. (It also works as expected when Office 2003 and Office 2007 coexist on Windows Vista. I’m aware of that this configuration is not recommended and is not supported.)

The only scenario I can raise this error is when using Excel 2003 on Windows Vista. I have also tested to turn off the UAC but with any success, i e the error still appears. The same error is raised when running a Shared add-in with the above code. It is also applicable to Add-in Express .NET solutions as well. As for VSTO I have not tested it.

For me it is important to know if this is related to some setting I have overlooked or if it is a bug.

If anyone can shed some light over it please let me know.

Kind regards,
Dennis

.NET Co Library is released!

Introduction

I have finished the work with .NET Co Library so now it’s officially released. It has been an interesting project in many ways. To work with the VBE’s object model has been a challenge and I’m looking forward to the day when it has been replaced.

I hope that this tool can be a valuable tool in the daily work for developing VBA solutions in Excel. To my knowledge it’s a unique tool in that it does not exist any similar add-in.

Acknowledgement

I would like to thank Ken (Puls) and Ross (Mclean) for their help to test it and also for their contributions to improve the UI. I’m part of a mixed developer group which is named the “Group”. The “Group” major contribution was simple to raise the question “Why”.

The following is a summary about .NET Co Library:

.NET Co Library can be used with

• Microsoft Excel 2000 and later
• Microsoft Windows 2000 and later
• .NET Framework 2.0 and later.

What .NET Co Library is

.NET Co Library is a free managed COM add-in for Microsoft Excel. The acronym Co in its name refers to both Connection and Code. 

With the tool you can: 

• Store created VBA code, code snippets / procedures / modules, and SQL Queries in a well organized way enabling you to easily reuse the code in all kind of Excel VBA solutions.

• Create connection strings to a various number of databases with two wizards, the .NET Wizard and the Data Link Wizard. Store the created connections strings in a structural way enabling you to easily reuse the connection strings in all kind of Excel VBA solutions. 

.NET Co Library has been designed so it also can be shared within a group of VBA developers over a network.

Requirements, known issues and installation

Before installing .NET Co Library it is important that you explicit read the following sections in the help file:

• Requirements

• Known issues

• Installation

The help file can be downloaded as a separate file (see below - NET Co Library.chm) 

The NET Co Library.zip file contains two files, the setup.exe file and the NET Co Library.msi file. Installing .NET Co Library on any Windows Vista version should be done with the setup.exe file.

The tool is available “as is” but XL-Dennis providing a free support for .NET Co Library and welcome any input to improve it.

To download the help file and installation package please see: .NET Co Library

Note: There exist some improvements in the final version compared what I have been blogposting about so therefore please read the help file.
 
Kind regards,
Dennis

Build Office-Based Solutions Using WPF, WCF, And LINQ

Andrew Whitechapel has written an article in the December 2007 number of MSDN Magazine (Build Office-Based Solutions Using WPF, WCF, And LINQ) 

The article is interesting in many ways:

• The case in the article use Visual Studio 2008 and .NET Framework 3.5, i e the latest version of the .NET platform.  
• It shows how to implement the latest new technologies (WPF, WCF and LINQ) in VSTO solutions for Office.
• It also shows the complexity involved to create such solutions as mentioned in the article.
• I find the article to be well written and I hope Andrew will be able to write more articles. 

If You don’t know what WPF, WCF and LINQ stands for then the following sources can hopefully shed some light:

• Windows Presentation Foundation (WPF)
• Windows Communication Foundation (WCF)
• Language Integrated Query (LINQ)

However, I cannot disregard from the gap between what VS 2008 offer and what Excel as a development platform offer. For me it’s quite obvious that MSFT must upgrade Excel’s development environment in the next coming version. After all, Excel as a development platform is still a too important platform to be neglected. 

Kind regards,
Dennis