Excel Automation AddIn with Installer
1.0.0
Excel User-Defined-Functions (UDF) Automation Add-In 1 with integrated Installer, both written in VB.Net
Full Microsoft Visual Studio 2022 solution and project source code.
Installers for both 32-Bit and 64-Bit Office / Excel included.
Click on Releases > Assets for pre-built examples.
Excel User-Defined Functions (UDFs) as developed in VB.Net have been around for many years, early examples of which include -
http://www.cpearson.com/Excel/CreatingNETFunctionLib.aspx
https://www.codeproject.com/Articles/7753/Create-an-Automation-Add-In-for-Excel-using-NET
Whilst these functions work well, the deployment of them can be more problematic, particularly where end users may not be familiar with, or are permitted to run command-line utilities such as Regasm to complete the installation.
The design goals for this project are therefore :-
A Windows PC with the following software installed is required to build the solution
A 'fresh build' of all the above components is recommended, on a dedicated development PC if possible, and with all updates applied.
Visual Studio should have the following items installed
The following utility is useful to inspect the Registration process, but is not mandatory.
The Automation Add-In is registered during the installation process.
Different values need to be written to the Registry for 32-Bit and 64-Bit versions of Office.
The installer class provides these values, Custom Action Properties is set for the version required in each installer project.
Separate 32-Bit and 64-Bit Office installer projects are provided and should be built for each version required.
Visual Studio generates two output files, setup.exe and AUTO_INSTALLER_nn.msi from each Installer project
Either of these files can be distributed to, and run by end users, to install and uninstall as required.
After running the installer, users need to configure Excel to enable the Automation Add-In.
From Excel > File > Options > Add-Ins > Manage Excel Add-Ins
Click on Automation, scroll down and select AUTOMATION.Functions
Click OK to confirm
Two sample Excel formulas are supplied
=IFX() in a Worksheet cell returns the text string AUTO FX OK
=TIMENOW() in a Worksheet cell returns the current time with milliseconds e.g. 12:34:56.789
This is a 'Volatile' function and will re-calculate when the F9 key is pressed or another cell changes.
Functions offered by the Add-In can be listed by clicking on Formulas > Insert Function and selecting AUTOMATION.Functions as a category
Users can uninstall the Add-In by right-clicking the Windows Start button and selecting Apps and Features
Scroll down to Automation FX and select Uninstall
Class module Installer.vb performs the Assembly Registration and Registry updates required when the developer or end-user runs the installer .exe or .msi program.
Tag <System.ComponentModel.RunInstaller(True)> is provided automatically by vb.net in file Installer.Designer.vb when a new Installer class module is added to a project.
This tag is used by the installer program to call Public Overrides Sub Install(stateSaver As IDictionary) via Custom Action Properties in projects AUTO_INSTALLER_32 and AUTO_INSTALLER_64.
Sub Install then calls RegisterAssembly which is functionally equivalent 2 to running RegAsm.exe manually.
RegAsm.exe itself uses methods exposed by RegistrationServices 3
The following points should always be observed to avoid performing any conflicting Registry updates during development and testing.
In project AUTO_FUNCTIONS > Properties, the options below should not be selected at any time.
Register for COM Interop in section CompileMake assembly COM-Visible in section Application > Assembly InformationTags <ComRegisterFunction> and <ComUnRegisterFunction> should also not be used in any module.
In each project > Primary Output Properties, Register should be set to vsdrpDoNotRegister
A new Production Build should be developed to ensure that all GUIDs are unique and all Visual Studio updates, references and dependencies are incorporated.
https://support.microsoft.com/en-us/topic/excel-com-add-ins-and-automation-add-ins-91f5ff06-0c9c-b98e-06e9-3657964eec72 ↩
https://learn.microsoft.com/en-us/dotnet/framework/interop/registering-assemblies-with-com ↩
https://learn.microsoft.com/en-us/dotnet/api/system.runtime.interopservices.registrationservices?view=netframework-4.8.1 ↩
