Add-ins – Where does Windows store the information?
The purpose with this post is to give a basic understanding about where Windows registry store information about add-ins in order to load the add-ins when we start Excel through automation. It’s the first out of three posts on the subject. As for the Excel versions the 97 and 2007 are excluded.

Types of Add-ins
Since Excel can handle several different types of add-ins the best approach to try to answer the question is to use these categories.

In general add-ins can be divided into the following categories:
• XLA Add-ins (Created with Excel)
• XLL Add-ins (Created with C/C++)
• Automation Add-ins (Usually created with classic VB or with C++)
• COM Add-ins for Excel (as above)
• COM Add-ins for the VB-editor (as above)

XLA
XLAs are easy to locate in Windows registry as they only appear in two places.

If they are available and activated Windows stores the information at the following place:
HKEY_CURRENT_USERSoftwareMicrosoftOfficeversionExcelOptions

If they are available but deactivated Windows stores the information at the following place:
HKEY_CURRENT_USERSoftwareMicrosoftOfficeversionExcelAdd-in Manager

“Version” refer here to the internal number versions of Excel like “9.0”, “10.0”, “11.0” etc.

The XLAs that are shipped with Excel is not located in the Add-in Manager section when deactivated instead they can be found in the HKEY_CLASSES_ROOT like the following example:
HKEY_CLASSES_ROOTInstallerComponentsC848331AADAA4D11298D0001A58916F6

XLL
Despite the technical differences between XLAs and XLLs Windows registry store the information about XLLs in an identical way as for the XLAs.

Automation Add-ins
This type of add-ins is only available from 2002 and on forward. Compared with XLAs/XLLs the only differences are the following:

* Instead of filenames they are referred with their ProgID (Servername.Classname).
* When activated the string”/A” is added in front of the ProgID.

COM Add-ins for Excel
This category of COM Add-ins is accessed via Excel’s standard UI. Unlike the other add-ins above the subkey’s names are identical to the COM Add-ins ProgIDs and each COM Add-in has at least the following accessible information available:

– Description / Friendly Name / LoadBehavior

Unlike the other add-ins above the information for the COM Add-ins can be found in one of the following sections in the registry:
– HKEY_CURRENT_USERSoftwareMicrosoftOfficeExcelAddins
-HKEY_USERS.DEFAULT SoftwareMicrosoftOfficeExcelAddins
-HKEY_LOCAL_MACHINESoftwareMicrosoftOfficeExcelAddins

Information about third-part COM Add-ins (like SnagIt etc) tends to be stored in the HKEY_LOCAL_MACHINE, which may be the preferable location (at least from my point of view).

COM Add-ins for the VB-editor
This group of add-ins target the VB-Editor in Excel and the information about them are stored (in an identical way as for the COM Add-ins for Excel) in:
HKEY_CURRENT_USERSoftwareMicrosoftVBAVBE6.0Addins

However, in addition to this location information is also stored in, when the COM Add-ins are activated: HKEY_CURRENT_USERSoftwareMicrosoftOfficeversionExcelOptions

Not only that, the “/A”-switch is also added to the ProgID which means that they are similar to Automation Add-ins, at least on their faces. I’ve not been able to understand the logic for this behavior so if anyone can explain it to me I would be very pleased.

Additional information about XLLs, Automation Add-ins and COM Add-in is stored in the HKEY_CLASSES_ROOT section of the registry.

For additional information please see articles listed at the following URL:
A collection of useful Excel Articles

Please let me know if I’ve missed any essential as it can be rather confusing when ‘diving’ into the registry.

In the next coming post I’ll discuss an alternative approach to Windows API when working with the Windows Registry.

Kind regards,
Dennis

The deterioration of the MVP-program

For many years I considered the MVP-program to stand as a guarantee for:

• Professional high quality and a deep knowledge and skillfulness about various tools.
• A driving force for continuous development where the output became standards and widely adapted in the global virtual communities.
• Represent the cutting edge.

Individuals who was accredited the MVP-status did to high degree qualify in view of the above. This was valid during the 90’s and in the early 2000’s but for the last years it has been a dramatically change where the above seems to be no longer valid.

I’m the first one to regret that I nowadays consider the whole MVP-program as a joke and therefore no longer stand as a guarantee for quality.

It’s obvious that MSFT has switched strategy for the program as they now accredit:

• Kids
• Young and nice individuals but with no experience or with a limited experience.
• Site owners & Authors

What do these groups have in common except for the lack of quality, experience, knowledge and skillfulness and what can they actually contribute with?

Personally I see the individuals in these groups as victims and I can also understand why they accept the accreditation. After all, MSFT are today the most powerful software company worldwide and therefore it would require a lot from individuals to turn them down.

However, I can only conclude that it exist a gap between what the MVP-program once was and what it has become today. What is also notable is that highly respected members have left the program or consider leaving it (keep in mind that Excel is a small group in the total MVP-program).

Where are the new Excel MVPs?
It looks like many of the recently MVPs disappear from the global community which indicate that something is wrong. Has the accreditation become a burden and therefore feel that they cannot stand up for it?

As a consequence I conclude that the recent Excel MVPs have contributed with little to the knowledge base for the last years. For the last 4-5 years I can only single out the chart-area. Sure, we’ve see some nice solutions that use API and other libraries but these solutions have been applied in the classic VB for a longer time (read 90’s).

The rapid development in many areas where Excel is involved strongly requires that the Excel MVPs take the responsibility for the development and take the lead. Excel cannot no longer be viewed as a standalone tools.

There are some emergency areas like Excel – Sharepoint, Excel – Visual Studio.NET & VSTO that need to get the attention. However, in order to do it MSFT need to make sure that they have well qualified and active MVPs.

Restore the MVP-program
I would like to see that MSFT reconsider the development and restore the trust in that the MVP-program still stand for all the good aspects. Here is a list what I would like to see:

• Clean up – Individuals that doesn’t measure up to the level, or are no longer active or are not interested to be part of the global community should gracefully be helped to leave the program.
• Accept that during some years there are no good candidates and therefore no accreditation will be executed.
• Encourage and support the MVPs to be the driving force for Excel in all areas.

People who know me well also know that I’m not a badge/title person and therefore do not mix my own person with the above discussion.

It’s my concern to maintain the level of quality associated with the MVP-program. Perhaps I’m old fashion but that’s the way it is.

Kind regards,
Dennis

Different approaches to retrieve data from underlying workbooks

One of the more frequent questions I see on different public forums is “How can I summarize data from several workbooks into a master worksheet?” In general the original posters have made a basic structure, which include fixed folder(s) and the same layout for all the underlying workbooks.

Open – Read – Close workbooks
This is the basic approach to just simple open each workbook and pick up the wanted information as the following example shows:

Read workbooks using Application.ExecuteExcel4Macro
This approach can be both flexible and fast. The following example shows how it can be applied:

Read workbooks using ADO & SQL
In order to use this approach we need to involve an external library, Microsoft ActiveX Data Object Library, where the version is 2.5 and later.

VSTO 2005 solution
Just for fun I include a solution based on ExcelExecute4Macro:

To sum up
Personally I prefer the ExcecuteExcel4Macro approach as it is fast, create no overhead and don’t require opening workbooks. However there are situation where we need to manipulate properties of worksheets and therefore need to open workbooks.

The negative with ExcecuteExcel4Macro is that it can be little bit messy like the following snippet code shows:

When to use ADO/SQL instead of ExecuteExcel4Macro? One possible situation is when we have larger amount of data to be “pumped” to a master sheet.

Anyway, what do You use and what are the scenarios where You apply them?

Edit note:
Ron de Bruin has some good and a more flexible ADO-approach then the above at the following place:
Copy a range from closed workbooks(ADO)

Kind regards,
Dennis

General development
The picture below represents the present development when it comes to what is set in focus with and for Excel (at least from my point of view).

In the late 80’s to the mid 90’s the focus was basically set to Excel itself and everyone was pleased to cut & paste as a process for data exchange between Excel and other softwares in a combination with DDE.

From late 90’s the interest and the needs to exchange data through automation and also to control different softwares have rapidly increased. When adding the rapid development of databases, different kind of servers and development tools the picture itself become more complex and reflect a major diversity.

ExcelKB’s Forum
For a longer time I had been thinking of setting up a new international discussion forum. In the end of last year I finally decided to launch a forum that to some extend reflect the above roadmap. In addition, it reflects to a high degree what I nowadays find to be of interest.

What ExcelKB’s Forum is about:

• Discussions about software based projects and documentation, both in general terms and more specific projects terms – Best Practical Practice.
• Controlling Excel through automation with developing tools like classic VB and VB.NET/VSTO et al.
• Data exchange between databases (MySQL, SQL Server, Oracle, PostgreSQL et al), and Excel.
• Discussions about commercial and free components that improve application’s usability and performance, like ComponenOne, Iocomp and Robocx.
• Books and online sources that is relevant for the above subjects.

There are some aspects that are important to me and therefore will be valid at ExcelKB’s Forum:
• No banners or ads will exist.
• No ranking system will exist and no titles/badges will be associated with any particular group of members.
• No Lounge will be available.
• Only real full names can be used for member’s accounts, i e no alias can be used.

The ExcelKB’s Forum will not “compete” with the established online public core Excel forums or with Excel related Microsoft’s Newsgroup. ExcelKB’s Forum does not target to become one of the many general Q&A forums about Excel as it target only specific areas.

If You find the target areas of interest then I recommend You to become a member. If You believe that You have no particular interest in them then I strongly suggest that You bookmark the ExcelKB’s Forum and visit it as a guest and perhaps later become a member.

Link to it: ExcelKB’s Forum
Kind regards,
Dennis

Acknowledgement
An old and indeed a close customer to me agreed to allow me to publish some data from a project we conducted last year. The agreement allows me to present some details but at the same time keep it in general terms and therefore no aspects on the effects on the organization etc. are discussed. I take this opportunity to thank them for their kindness.

The Company
The company is a subcontractor to a worldwide company and has around 120 employees. At the time for the project the company could be described as a core “Excel-company” since the staff used Excel extensively although the company had never actively made any strategically decision to use Excel as part of their business platform.

The Project
After we had implemented a minor MIS-solution (A frontloader in classic VB with Excel reports) we had a follow up meeting with the general manager and the IT-manager. During the meeting we were told that many co-workers had raised demands for a longer time on more powerful pc, more RAM and wider screens and even to have two screens on their desks. The main reason for these requests was that they experienced poor performance and not seeing enough on the screen when working with their spreadsheets. What it finally turned out to be about was large workbooks and after additional meetings it was decided that the project “Large workbooks” should be started.

The focus with the project was:
• Explore and document the present standard for storage and handle information in Excel, including the data
quality aspect
• Analyze the present internally information workflow and to identify the real information needs
• Offer a solution that would solve the information needs and the storage and the value added processes to generate reports with the data from the central sources

The following picture shows how the company viewed their IT-situation at the time of the project:

The following picture shows how it really was:

(The pictures are simplified and also adjusted in order to keep it in more general terms.)

The Large Workbooks
Data from the Business system was exported to Excel’s workbooks and then manually transferred to the user’s workbooks. The MPS system generates textfiles that was manually imported and then manually handled by the users. Data from the SQL Server was retrieved via DTS and then manually imported to the user’s workbooks.

We randomly picked 42 workbooks to analyze and interviewed 18 co-workers from different departments about their way of working with the information.

• The average file size was around 8,7 MB (37 workbooks was included as the remaining files had file sizes greater then 20 MB)
• 100 % of the workbooks contained outdated data (The question “Why” was commonly answered with “just in case” and “No time to clean up”)
• About 88 % of the workbooks contained some kind of calculation errors
• About 76 % of the workbooks were poorly designed and was difficult to interpretate without asking the users about the contents and the purposes of the files
• About 95 % where “designed” individually and had names that poorly referred to their contents

The Suggested Solution
The following picture (again simplified) shows the suggested solution which was also implemented by the company:

The fixed reports, which are accessible through the Intranet, are all created with Crystal Report Enterprise. The fixed Excel reports are DTS-generated and then completed with a COM add-in tool. The ad hoc reports are generated with a special COM add-in tool (directly connected to the SQL Server) that is available only to a limit group of users.

In addition some standalone desktop’s databases (aka “Access databases”) was developed and implemented for a smaller group of users and only connected to a group of special Excel reports. All generated Excel files are named like the following Reportname & date & Time and these files are automatically deleted after 2-4 weeks. All files are stored in network folders. Different groups of users have different access rights.

Some Conclusions
• The most important conclusion is that the company with the solution now takes full responsibility of the internally information workflow. The users were previous on their own to a) decide which information was relevant for their work b) and where to store and how to add value to the data
• A higher information and storage security has been achieved and the company is now less key person depended
• The numbers of distributed reports have decreased from over 100 to only 35 and all the standalone reports are now unified (design and names), including a full documentation
• The average quality of the workbooks has heavily increased and the file sizes are on the average less then 1 MB
• Less time consuming processes to generate the fix reports due to nearly full automation
• No upgrades of any hardware was necessary to accomplish

Did we manage to fix all the issues to 100%? Of course not, my estimation is that we managed to take care of about 70 – 75 % of the problems and that it’s quite normal for projects like this one, at least in my experience.

Kind regards,
Dennis

Ps:
In the early 90’s Robert S Kaplan and Thomas Johnson wrote the book “Relevance Lost – The Raise and Fall of Management Accounting” which became the starting point for the global era for Activity Based Costing (ABC). The only relation between their excellent work and this post is that I have “borrowed” the title from them and the fact that I like case studies.

Today it looks more promising then ever.

Microsoft will soon release their SQL Server 2005 in a free version which will replace the present MSDE Server 2000. MySQL has recently launched their new major upgraded version, 5.0, which can handle stored procedures, triggers and views. Oracle recently announced that they will make a free version of their RDBMS version 10G available – Express Edition.

Why is this of interest at all?

As a small Excel-developer with limit resources it may be impossible to learn how to interact with and develop Excel-solutions for larger RDBMS (relation database management systems).

Since the major RDBMS has their own dialects of SQL and other individual properties it also mean that we can directly create and test solutions with correct syntax etc, for instance for Oracle, then first develop with MySQL and then convert it to an Oracle production database.

In view of this I welcome that the major vendors provide us with free RDBMS-version so that we are not limited due to the lack of resources.

However, in this context I find it questionable why Microsoft limit the new DB-functions in the coming Excel 12 to SQL Server 200/2005 only. After all, many organisations use other RBDMS and Excel is widely spread.

Kind regards,
Dennis

The Structure & the Content of Help files

For me help files can be divided into three main sections:

ï Introduction, which includes a general presentation of the application, install / uninstall the application and minimal requirements to use it. It may also include any specific set up and configuration guide.

Some of the information should also be available in the printable Start guide and in the printable Installation guide as the help file is usually installed together with the application itself.

ï The How To section(s), which is the core of the help file and should provide the users will necessary information in order to accomplish specific tasks and activities. This is the most importation section.

ï Contacts & Support, which gives the users the necessary information to get in touch with us and take part of information how to get updates etc.

General guidelines
Keep the language simple and straightforward. The UI of the application is what the users usually see and therefore there is no need to add technical details that the users never will take part of, i e avoid information overload.

(No, most users are not interested to learn how amazing UDFs or procedures weíve been creating to solve specific issues withÖ)

If the application explicit target beginners then itís highly recommended to either avoid the use of abbreviations or explain them. The same is valid for all the buzzwords that tend to be in use but never explained (Iím no exceptionÖ).

However, it does not necessarily mean that all the explanations should always be on a basic level. A good judgment should guide us to decide the level of complexity together with information about the target group of the application.

Use screenshots as they give the users a quicker and better understanding where to do the activities in the application.

Avoid using too many hyperlinks and too many popup. If You believe You need many hyperlinks and/or popup then You probably will need to completely re-design the structure.

Check the spelling in the help file. Personally I can get irritated if common words are misspelled and beside that the impression of a professional applications increase if misspelling is avoided.

In my experience itís a good situation, if possible, to let a member of the target group help out with the creation of the help file. An even better case is if a professional documenter does do the work but so far I have not yet have had that possibility. For smaller projects itís not reasonable from a strictly financial point of view.

A helpfile should include the following tabs:

ï Content
ï Index
ï Search

Both the Index- and Seach-tool may seem to be overkilling but some users actually use them instead of drilling down via the Content-tab. Except for that I would say that the existence of the tools reflect the present standard for help files.

The following screenshot shows an example of the basic structure and the content of a help file:

(Yes, the names of the tabs etc in the above image are in Swedish).

It may seem to be a simple task to create help files but in my experience it takes a while and lots of practice before we can create help files of good quality.

The process of creating helpfiles gives us valuable input on how the applicationís UI is structured and it can also help us to detect any major flaws in the UI.

Over the years a practical standard seems to be developed for how help files should be set up but as far as I know little has been written about it. Hopefully some of the above will give You some ideas about it.

In the next post I will discuss how to create help files with a help authoring tool (no, itís not with Excel ;) ).

Kind regards,
Dennis

The need of documentation – Part I

No matter if we are professional Excel-consultants, official / unofficial developers within companies there is one thing we both love and hate. Love when someone else has already created it and hate when we need to do it ourselves.

What I mean is the documentation of the applications we have created.

I’m the first one to admit that this part of the work is not the main reason for working with applications development. But over the years I have come to the insight that documentation is an essential part of the solutions where the applications themselves are the core of and therefore it deserve some attention and thoughts on the subject. I’m by no means a specialist when it comes to documentation but I have learned a lot about it during the years, especially when working with worldwide large Swedish companies.

Sometime when I discuss this subject with customers there seems to be a misunderstanding what documentation really is about. A quite common comment is like the following “the code has been comment and there are several comments inside the worksheets of the workbook and all the formulas are available – isn’t that enough?” Another common comment is “no one will ever read it so why document it?”

My answer is:
No, there is a need to provide documentation in plain English (or in any local language in use) that gives information about the application without having it available.

Before moving on here are some reasons for creating the documentation:

• Customer can evaluate and review that the business logic in the application agrees with the requirements
and the documentation will make the acceptance test smoother.
• The applications are not strongly associated with the creator(s), i e not key person or company related.
• The customers have documentation over the applications that they can trust and use and therefore lower
the risk both in using it and in case something negative happen.
• Customers IT-policy may demand the documentation.
• The documentation support maintains and makes it easier to port it to a new platform (if necessary.)

It’s also important to note that whenever any critical changes have been done in the applications all the related documentation must immediately be updated.

Personally I use a couple of MS Word templates to document applications and projects. It’s a good investment to spend some time to set up the templates and then convert them to PDF-documents before making them available to the customers.

The following image tries to give a general schema about the documentation:

The Presentation layer
This is what the end users see, i e the user interface (UI). There are three kind of documentation that should be considered to be included:
• Printable simple Starguide enabling users to understand the basic of the application from a core user
perspective.
• Printable installation guide should be available if the users will handle the installation process.
• A compiled Windows standard helpfile, which should be accessible through the UI.

The Business layer & The Data layer
The business layer (also known as the business logic) is the bridge between the presentation layer and the data layer as it describe how the data is to be handled. The documentation should include:

• Different kind of flow/process schemas for general as well as specific handling of the data.
• The logic for how the data is handled in plain English.
• Printable installation guide

Example of a description:
Suppose we have a dynamic chart that is populated with data from an underlying table which in return is based on data from a larger data list in a (hidden) worksheet. The values in the table are retrieved by using the SUMPRODUCT-function which includes several conditions as the following sample shows:

=SUMPRODUCT(- -(Target=”Forecasted”),- -(Sales_Stage =”Negotiation”),- -(Orders<>”Not booked “),- -(Values))

First of all, the formula itself should not be included!

Here is my way to describe it:

Sum all the values in the column Values for each Sales Stage that meets the following criterias:
• Have the value “Forecasted” in the column Target
• Have the value “Sales Stage name” in the column Sales_Stage
• Do not have the value “Not Booked” in the column Orders

Use the “cluster”-technique to document all the central functions/formulas, i e avoid the situation to document each individual function. The same technique should also be applied to all the code that, in one or another way, handles the data.

By the way, I usually document all the names that are in use (as an attachment and as a more “technical” documentation) as well as anything that are not considered to be “standard”, i e:

• Use of external libraries
• Use of third part Active-X controllers
• Reading and especially writing to the Windows registry

The documentation of the Data Access layer should include the following:
• How the data is retrieved and updated
• Where the data is stored, both actually data and in case of historical data (no longer in use)
• Any clean up process before handle the data
• Any special remarks about the data or the access process itself

Practice is the best approach to get used to document applications and Excel-applications are one of the more challenging applications as the presentation, the business logic and the data may be mixed due to the use of cells and worksheets.

Actually, I’ve find it to be a very good learning process over the years as it put a demand to describe the structure of the application in a logical way and to some extend it also reduce redundancies. In the long run applications will be better structured and therefore will be benefiting from the documentation process too.

The above reflect how I try to work with documentation and does not imply, explicit or implicit, to be the best practice. However, it gives a structural approach to meet the need of documentation.

In the next part I will make a wider approach and discuss providing help, both directly in the UI and through helpfiles.

Meanwhile how and when do You document Your applications?

Kind regards,
Dennis