I can't believe that I've been racking my brains for hours trying to figure out how to execute my .NET 2.0 code in Microsoft Excel 2003.  Hopefully this tidbit of information will save somebody else on the web some pain, frustration and time.

Punchline: You need a file called EXCEL.EXE.CONFIG in the same directory as EXCEL.EXE.

I created a class, written in C# and compiled with Visual Studio .NET 2005, with the .NET 2.0 Framework.  This class has a COM Interop wrapper so that it can consumed by ActiveX clients, most notably Excel 2003.  This class was compiled with the "Register for COM interop" setting in the properties turned on.  This arrangement autogenerates the GUIDs and COM interface for you, though I prefer to define my own COM interfaces manually in production code.

Here is a sample class in C#:

// COMClass.cs
using System.Windows.Forms;

namespace AnyCOMLib {
    public class COMClass {
        public void DoSomething() {
            MessageBox.Show("I did something.");
        }
    }
}

When I compiled this C# class, Visual Studio generated the DLL and TLB files I expected.  You can invoke it from a COM client, and it seems to work.  Here is the VB code:

Dim thing As Object
Set thing = CreateObject("AnyCOMLib.COMClass")
thing.DoSomething

I typed out a simple VBScript file with this code, and it worked.  A message box popped up saying "I did something.".

I created a simple VB 6.0 program with this code, and this worked, too.  The same message box displayed.

I created a VBA macro in Excel 2003.  This did not work.  I had a message that read:

Run-time error '-2147024894 (80070002)':
File or assembly name AnyCOMLib, or one of its dependencies, was not found.

So I thought - If I can create a VB6 DLL that can be called from Excel (true) and VB6 code can call my COM-wrapped C# code (also true) then why not create a VB6 DLL that calls my C# code and call that from Excel?  Unfortunately, Excel was not easily fooled by this subversion, and I continued to get the -2147024894 error.  That was a waste of time.

After spinning my wheels for too long, I finally found something helpful.  It turns out that I need a file named Excel.exe.config to be placed in the same directory as Excel.exe (typically at C:\Program Files\Microsoft Office\Office11).  That file has the following format:

<configuration>
    <startup>
        <supportedRuntime version="v2.0.50727"/>
        <supportedRuntime version="v1.1.4322"/>
    </startup>
</configuration>

You have to make sure that you have the correct supportedRuntime elements for the .NET runtime to work.

Here are links to the pages that helped me find out about this:

newsgroup microsoft.public.dotnet.framework.sdk

topic in msnewgroups.net

These were specifically targetted for MS Word, but the same method applies to running .NET assemblies in Excel 2003 as well.

I hope this helps somebody!

This describes yet another way to do things.  Suppose you have input coming from a common source, but based on the content of that input, you want your program to do different things.  Naturally, the first thing you think of is the switch statement.  But I've stumbled across an interesting alternative.

Here is a simplified class in C# that uses a switch statement:

          public class MyGame: IGame {

    


    public string ExecuteCommand(string command, string parameter)
{

        string result;

        switch (command) {

            case "ECHO":

               
result = DoEcho(parameters);

               
break;

            case "JOIN":

               
result = DoJoin(parameters);

               
break;

            case "READY":

               
result = DoReady(parameters);

               
break;

            default:

               
result = DoError();

               
break:

        }

        return result;

    }

    


    public string DoEcho(string parameters) {

        string result;

        // code here

        return result;

    }

    


    public string DoJoin(string parameters) {

        string result;

        // code here

        return result;

    }

    


    public string DoReady(string parameters) {

        string result;

        // code here

        return result;

    }

    


    public string DoError() {

        string result;

        // code here

        return result;

    }

}
        

I decided to play around with generics to see if I could come up with an alternative to "switch".  By using a Dictionary of delegates, I can look up what the appropriate action to take would be, and then do it in a generic fashion.

          public class MyGame: IGame {

    


    
          
            private delegate string
CommandHandler(string parameters);
    
          
            protected Dictionary<string,
CommandHandler> commandHandlers = 


          
          
            new Dictionary<string, CommandHandler>();
    


    public MyGame() {

        commandHandlers["ECHO"] = new CommandHandler(DoEcho);

        commandHandlers["JOIN"] = new CommandHandler(DoJoin);

        commandHandlers["READY"] = new CommandHandler(DoReady);

    }

    


    public string ExecuteCommand(string command, string parameter)
{

        CommandHandler handler;

        if (commandHandlers.TryGetValue(command,
out handler)) {

            return handler.Invoke(parameter);

        } else {

            return DoError();

        }

    }

    


    // handler code is same above.

    


}
        

I don't know about you, but this is much more satisfying at varying levels.  I've never liked the switch statement syntax, ever, and this code is easier on the eyes.  This transforms the dispatch mechanism from a procedural approach to a table-driven approach.  The one disadvantage is that all the dispatch methods must have the same figure, but usually uniformity is a welcome attribute to code.  One cool thing is that by adding a new handler, Resharper will automagically generate the signature for the new dispatch method.

A place where this approach might be useful is by assigning abstract factory methods as delegates.  Those have to be uniform, by design contract.

One thing I would like to see is the ability to assign all of the elements in the dictionary in a single statement.  I understand this will be a new feature in C# 3.0, but I haven't experimented with that yet.  I just downloaded the SDK yesterday!

I have this habit of underestimating Microsoft, especially when it comes to the Office product line. At first blush, I was unimpressed with Office 2007.  To the average user, on first glance, it looks like Microsoft has traded one set of GUI widgets for another set of more complicated GUI widgets.  Combining this with the IT pundits declaring Office 2007 dead on arrival, I ignored it.  It's hard for me to get very excited about new widgets.

But last night I chanced upon the VSTO site (Visual Studio Tools for Office), and took a peek at this video.  I was amazed, and had to concede that Microsoft had pulled it off once again.  They have raised the bar for us programmers and expectations of users.  Briefly, your WinForms user controls can plug right into the Office environment, into either the ribbon or in the side docking panel.  Having worked on a system in C# that emulates a similar behavior, I was very interested, and a little disappointed that my craftily written code would soon be obsolete.

The thing about Office, and Word and Excel in particular, is that everyone uses it.  People get a warm fuzzy from software they use frequently and have some understanding of.  The number one feature request I get is to be able to download data into an Excel spreadsheet.  And VSTO 2005 SE (Second Edition) makes the job for programmers integrating into Office a whole lot better.  And it gives a compelling reason to upgrade to Office 2007.  This is only a minor upgrade for the end-user, but this is a sea-change upgrade for the Office developer.

VSTO 2005 SE will also allow Office programmers to achieve the type of service-oriented architecture that was difficult to achieve with our previous tools.  Since your user controls can do all those wonderful things like consume web services, chat over networks, query databases, access the file system, etc., the integration possibilities are nearly endless.  And the ability to dock your components right at the user's fingertips within the Office application makes the software convenient to summon for the end-user.

Now to work on my "reasons to upgrade" spiel for my clients...

I recently tuned into the podcast on IT Conversations, hosted by Phil Windley, and featuring David Platt, .NET programmer and author of the new book "Why Software Sucks".  In the podcast, Platt adds some new light to problems of user-facing software (as opposed to programmer-facing software).  Much of what he has to say has already been covered in Alan Cooper's unforgettable book, "About Face".

So what makes some software suck?  Software is supposed to make our lives and jobs easier to accomplish.  When software makes it difficult for a user to do things, or forces the user to remember things, or demands that the user do a complex dance with her keyboard and mouse, then that software sucks unequivocally.

But if you visit the computer lab and ask the programmers what makes software suck, they will have a totally different perspective.  Code that's hard to read, lack of unit test coverage, tight coupling of objects, redundancy: these are the problems that make software suck.

My opinion is that you can have the most highly refactored code, in all of its object-oriented goodness, and the software can still suck.  My fear is that there are many devotees of Martin Fowler and Uncle Bob, who in their own minds believe they are writing great software, but realize only after it has been foisted on the end user that it actually sucks.

When I tried to de-suckify a feature in the web application we recently released, I was often met with fierce resistance from programmers, whereas the business analysts were very open to new ideas on how to make the software more user-friendly.  One example was when we were required to build an interface whereby a loan representative could transfer the responsibility for a loan application to another loan rep.  In our lingo, this is called "pushing a loan".  The original specification required that the end user would enter the name of the receiving loan rep or their office then run a search against the database.  I believed that this method would be error-prone and that there ought to be any easier way.  I sat down with the business analyst in charge of this new feature and asked her "What do people use this for 90% of the time?"  She stated that most often, people would push the loan to someone else within their own loan office.  So I asked how she would like it if, when the user first selects the feature, he is presented immediately with a list of the loan reps from his own office, and that this list would precede the search function on the page.  The business analyst thought this was an excellent alternative, and she went to draw up the new plans.  Back in the development lab, things ran afoul because "everyone knows" that the search function "has" to be the first thing on the page.  This was going to "break the convention" of the look and feel of the rest of the web application, and this self-imposed directive should take priority over giving the user what they truly want.  Also, this alternative was going to require extra programming that we had not originally planned.  I stood my ground on the customer's behalf, and ultimately we all did agree on this alternative presentation of the UI.  But this incident illustrates that sometimes even software developers who are in good communication with the end user will fail to have the end-user's best interest as their top priority.

In our development shop, we developers would often lapse into the flawed mentality of "the customer's always right", and I have been just as guilty as others on this.  The customer would dictate the requirements and the developers' task was to merely implement whatever they came up with.  Occasionally, the customer would ask for something so goofy that we developers would have to put our foot down and everyone would go back to the drawing board.  Such are the risks of assigning the responsibility of software design to the customer, in this case the business analysts.  The business indeed knows best what the business needs, but is typically not the best choice in deciding how to get there.  The developer has the knowledge to create whatever a specification calls for, but his knowledge of the business need is often very shallow.  This leaves a disconnect between the customer and the developer.  This disconnect was partially alleviated by the agile process we used, during the breakout sessions.  These sessions are great at defining the business need at a high level that could be understood by both business analysts and developers.  These session are not so good at defining the user interface.  And user acceptance is all about the interface.

In Cooper's book, "The Inmates are Running the Asylum", he introduces the concept of the "interaction designer", a person who's job is to define the user interface and overall orchestration for a software project.  In software development circles, the "architect" is more like the lead programmer who has view of the entire system as a whole, including database structures, development frameworks, and the code base.  Cooper's interaction designer is more like an architect in the real world, and they are not so concerned about the mechanics of making it happen as they are with getting it absolutely right with the customer.  Programmers don't necessarily make good interaction designers, because of their intense focus on the "how" of creating software, they lose sight of the "what".  The interaction designer is not so concerned that the chosen framework doesn't directly support a certain feature, they are focused on the end product, and have the artistic license to devise the product so that it works intuitively and flawlessly for the end user.

In Platt's interview, he does not acknowledge many of his observations that have already been articulated at length by Cooper.  He does offer a few helpful tips for both end users and developers.  His tips for developers include:

1) Make sure the project includes a "virgin" - that is, someone who is knowledgeable in software development but comes to the table with a clean slate, unaware of the software currently under development.  This fresh perspective will cause new ideas to bloom outside of the box of groupthink that evolves within a development team.

2) Be willing to break with convention. Doing things the way they've always been done is no longer good enough.  If you can't find the right GUI widget from your class library, get a better class library or build it yourself.

3) Don't let edge cases complicate the mainstream.  Programmers tend to write software that is mathematically correct, when they should be focused on making the actions and decisions most often taken by the end-user the top priority. Platt says that it should be easy for the user to do things that are "good, smart and safe" and difficult for users to do things that are "bad, stupid and dangerous".  Platt also demonstrates that it's faster and easier to track a UPS delivery by searching the UPS tracking number in Google than it is in UPS' own web site.  In Cooper's "About Face", he makes the absurd analogy of putting the eject button right next to the radio switch in the pilot's cockpit.

4) Instrument the user experience.  Today's software allows you to be able to report on the user's experience, so you should monitor how the user uses the software and then use that information to improve the software for the user's actual needs instead of their stated needs.  If your software is a web application, the simplest thing you can do is monitor the web server logs, but you can even do more than that.

One of the problems I see with judging software usability is that it relies on subjective experience.  There is no one good metric to determine how much a piece of software sucks or doesn't suck.  Aesthetic appeal is the first thing that hits the eye, but the lasting impression is the ease of interaction.  For instance, my T-Mobile service lets me pay my bill over the phone, and I don't even have to push a single button; its voice commands are intuitive, succinct and accurate, and I have even paid the bill while driving.

I'll have more to say about these issues on future blogs.

The tool that no .NET programmer should suffer without has gotten better!  New features are enumerated here.

NAnt is great.  I have a script that compiles all my source files, builds the standalone installation packages, fires off Virtual PC for testing, and it can even FTP the installation package to my client's site.  It seems like there's nothing NAnt can't do.  But I needed to peel off the version number from the executable, and I couldn't find any easy way to do this in NAnt.  Fortunately, I discovered that it's really not that hard to create a custom task in NAnt in C#, then use it from the target.

Basically, you have to create a <script> block (as shown below) and create a class that inherits from Task.  The TaskName attribute is what you will use in the main NAnt script to call it.  Any property with a TaskAttribute attribute can be used as an XML attribute in the script, and you can set Required=true to require it.  The Task object has the Project property, which can be used to access anything at all to do with the build process.  I use it here to set a NAnt property and log to StdOut.  NAnt will compile the C# code on the fly (if you did everything correctly) and voila, anything you can think of is now possible.

Here is a sample from the build script I created. 

<project name="SomeNAntProject" default="DefaultTarget">

    <property name="version.number" value="0.0.0.0" />

    <target name="DefaultTarget">
        <getversioninfofilename="C:\Folder\MyExecutable.exe"
/> <echo message="${version.number}" /> </target> <script language="C#">
<code> <![CDATA[ [TaskName("getversioninfo")] public class
GetVersionInfoTask: Task { private string _fileName; [TaskAttribute("filename", Required=true)]
public string FileName { get {return _fileName; } set {_fileName = value; } } protected
override void ExecuteTask() { System.Diagnostics.FileVersionInfo fvi = System.Diagnostics.FileVersionInfo.GetVersionInfo(_fileName);
Project.Properties["version.number"] = fvi.ProductVersion; Project.Log(Level.Info,
" [script] Got version info for " + _fileName); } } ]]> </code> </script>
</project> 

I found an interesting alternative data access layer (DAL) to NHibernate called SubSonic.  It was formerly called "ActionPack", but I guess there must be enough confusion with Microsoft's "Action Pack" that they changed the name.  SubSonic is open-source (MPL)

SubSonic has been hailed as "Ruby on Rails" for .NET developers.  There is a screencast of it here, prepare to be amazed.  It works kind of like this:

1) Add a reference to the SubSonic DLL
2) Modify your web.config with connection string info and SubSonic build provider info
3) Build the project
4) ...
Copyright (C) Sidney Harris.

5) Amazing!  Domain objects and data access layer have been automagically created!

I foresee this as a great way to quickly get a data-driven site up with a minimum of development effort.  Realizing this is not the end-all and be-all of data access, you are still free to use "traditional" ADO.NET techniques.  A recent benefit is that CodePlex has come out with an XmlProvider, so that if you would rather persist to XML instead of a database, it can handle that data access layer, too.  This thing keeps getting better and better.