Building a Real Time Data server for Excel: Creating the Setup project, part 10

This is part 10 of the tutorial that describes the techniques necessary to build an Excel RTD server. If you have just come to this page, I suggest you read all parts in order, here is Building a Real Time Data for Excel, part 1.

In part 2: Avoiding VSTO, I lauded Add-in Express for having such an easy interface to create a Setup Project. I stand by what I said, but GeodesiX goes a fair bit further than just implementing a few Excel UDFs. The problem that have to address is:

Excel UDF descriptions are per-user and not for all users of the PC. Technically, they are stored the in registry in HKCU and not HKLM. This means that you have to install your UDFs for each login separately. This is a pain in the arse.

You might naively think that it is sufficient to create the appropriate entries in HKCU during the Setup. A good idea, but it won’t work because:

• If you create a per-user Setup, you’ll have to run the complete Setup for every user who needs your UDF.
• If you create an All-Users Setup, it runs with elevated privilege. This privilege is acquired by changing the user of the Setup task to “System”. If the Setup task modifies HKCU, it will modify the HKCU of the user “System” and not the HKCU of the user running the Setup. In other words, if you login as System, you’ll be able to use the UDF, as any other user you won’t.

Note. ‘Professional’ setup programs like InstallShield suffer from the same problem (and don’t offer a solution).

The only solution that I have found to this is to use the undocumented Active Setup (take a look at the nice description Active Setup explained on Helge Klein’s blog).

The swindle is performed in two steps.

1. At the end of the Setup, we create a custom action which runs the command-line program PostInstall.Postinstall.Main #31#. It calls PostInstall.SetupUDF.SetupUDF #32#. SetupUDF creates the registry entries to invoke Active Install at the next login (for any users of the machine).
2. Whenever a user logs on in future, Active Setup checks to see the currents user’s registry keys at HKEY_CURRENT_USER\SOFTWARE\Microsoft\Active Setup\Installed Components to see if they are the same as HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Active Setup\Installed Components. If they differ, Active Setup invokes the program stored in the StubPath key: Our UDFInstaller #33#. UDFInstaller creates the registry keys in HKCU that define the UDF and we’re done.

The one thing to watch out for (both in PostInstall and UDFInstaller) is that we’re running as a 32-bit task. Making naive registry changes using My.Computer.Registry will work on 32-bit operating systems, but fail on 64bit systems because when a 32bit task modifies the registry on a 64bit system, the modifications are made under the Wow6432Node key.

When a 64bit Excel starts, it accesses the real registry key and won’t find the USD entries. You can read all the gory details here.

There is a downside to this: when the Setup has completed, the UDFs won’t be installed until the user logs out and logins in again (we show him a message asking him to do this). The final step of Setup, in PostInstall #42#, is to display the help Excel file, but the UDFs aren’t installed, so the Geodesic formulas would return #NAME#. They don’t because GeodesiXReadMe.xls has calculation set to manual. Clever users will notice this if they try to add or modify the help file whilst they’re reading it. This is a despicable trick, but what the eye doesn’t see, the heart doesn’t bleed over.

A final Setup note: I build Geodesix on a 64bit machine, so the references to Addin Express point to “Program Files (x86)”:

If you’re running a 32bit machine, you’ll need to adjust this here and in the Setup Custom Actions:

Building a Real Time Data for Excel tutorial:

• Building a Real Time Data for Excel server, part 1
• Building an Excel RTD: Avoiding VSTO, part 2
• Building an Excel RTD: How RTD servers work, part 3
• Building an Excel RTD: Architecture, part 4
• Building an Excel RTD: Excel, multithreading and callbacks, part 5
• Building an Excel RTD: Providing easy-to-read function names, part 6
• Building an Excel RTD: Talking to the GoogleMaps APIs, part 7
• Building an Excel RTD: Avoiding Application Domain misery, part 8
• Building an Excel RTD: Embedding a GoogleMap page in an Excel Task Pane, part 9
• Building an Excel RTD: Creating the Setup project, part 10
• Building an Excel RTD: Changing the Excel RTD Throttle Interval, part 11