In Avoiding VSTO, I lauded AddinExpress for having such an easy interface to create a Steup Project. I stand by what I said, but GeodesiX goes a fair bit further than just implementing a few Excel UDFs. The problem that I had 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 that ‘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 like this:.
- 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).
- 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 ReadMe.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. Fixed 6 April 2011.
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: