Before Visual Studio 2005 introduced XML-based registration for add-ins with an .AddIn file (which enabled X-Copy deployment), add-ins for Microsoft applications required two steps to be registered:

• To register the add-in dll as ActiveX (COM) component
• To register the add-in dll as add-in for the host application through some registry entries

This is still true for COM-based add-ins for Visual Studio (any version) and for other hosts such as Microsoft Office or its VBA editor which only support COM-based add-ins.

Some months ago I wrote how to create a COM add-in for the VBA editor of Office using .NET, which is almost the only way to create an add-in for the VBA editor of Office 64-bit, since it doesn’t support 32-bit COM add-ins.

I am working since some months ago on a .NET-based version of my MZ-Tools add-in for the VBA editor of Office 32/64-bit and I always wanted a single script to perform the two steps above. This was a nice excuse to learn Windows PowerShell, so I bought a book and after reading some chapters to get the concepts today I decided to create the scripts that call regasm.exe to register the .Net assembly for COM-Interop and create the registry entries for the add-in to be recognized by the VBA editor:

1) This is the content of a file named Functions.ps1 which contains reusable functions:

# To run .ps1 scripts you need to execute first: Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
$Regasm32 = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\regasm.exe'
$Regasm64 = 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\regasm.exe'

function Register-Assembly32([string]$Assembly, [string]$RegistryKey, [string]$FriendlyName)
{
   Execute-Command -RegAsm $Regasm32  -Arguments '/codebase' -Assembly $Assembly
   Register-AddIn -RegistryKey $RegistryKey -FriendlyName $FriendlyName
}

function Register-Assembly64([string]$Assembly, [string]$RegistryKey, [string]$FriendlyName)
{
   Execute-Command -RegAsm $Regasm64 -Arguments '/codebase' -Assembly $Assembly
   Register-AddIn -RegistryKey $RegistryKey -FriendlyName $FriendlyName
}

function Unregister-Assembly32([string]$Assembly, [string]$RegistryKey)
{
   Execute-Command -RegAsm $Regasm32 -Arguments '/unregister' -Assembly $Assembly
   Unregister-AddIn -RegistryKey $RegistryKey
}

function Unregister-Assembly64([string]$Assembly, [string]$RegistryKey)
{
   Execute-Command -RegAsm $RegAsm64 -Arguments '/unregister' -Assembly $Assembly
   Unregister-AddIn -RegistryKey $RegistryKey
}

function Register-AddIn([string]$RegistryKey, [string]$FriendlyName)
{
   New-Item         -Path $RegistryKey -Force
   New-ItemProperty -Path $RegistryKey -Name Description  -PropertyType String -Value $FriendlyName
   New-ItemProperty -Path $RegistryKey -Name FriendlyName -PropertyType String -Value $FriendlyName
   New-ItemProperty -Path $RegistryKey -Name LoadBehavior -PropertyType DWord  -Value 3
}

function Unregister-AddIn([string]$RegistryKey)
{
   if (Test-Path -Path $RegistryKey)
   {
      Remove-Item -Path $RegistryKey
   }
}

function Execute-Command([string]$RegAsm, [string]$Arguments, [string]$Assembly)
{
   $psi = New-Object System.Diagnostics.ProcessStartInfo
   $psi.CreateNoWindow = $true
   $psi.UseShellExecute = $false
   $psi.RedirectStandardOutput = $true
   $psi.RedirectStandardError = $true
   $psi.FileName = $RegAsm
   $psi.Arguments = $Arguments + ' ' + $Assembly
   $process = New-Object System.Diagnostics.Process
   $process.StartInfo = $psi
   [void]$process.Start()
   $StandardOutput = $process.StandardOutput.ReadToEnd()
   $StandardError = $process.StandardError.ReadToEnd()
   $process.WaitForExit()
   [system.windows.forms.messagebox]::show($StandardOutput + $StandardError)
}

2) Then I have other scripts that include that script:

MyAddInVBA32Registration.ps1:

$ScriptDirectory = Split-Path $MyInvocation.MyCommand.Path
. (Join-Path $ScriptDirectory Functions.ps1)

$Assembly = (get-item Env:USERPROFILE).Value + 'Documents\MyAddIn\Exe\Debug\MyAddIn.dll'

Register-Assembly32 -Assembly $Assembly -RegistryKey 'HKCU:Software\Microsoft\VBA\VBE\6.0\AddIns\MyAddIn.Connect' -FriendlyName 'My Add-In'

MyAddInVBA64Registration.ps1:

$ScriptDirectory = Split-Path $MyInvocation.MyCommand.Path
. (Join-Path $ScriptDirectory Functions.ps1)

$Assembly = (get-item Env:USERP2ROFILE).Value + 'Documents\MyAddIn\Exe\Debug\MyAddIn.dll'

Register-Assembly64 -Assembly $Assembly -RegistryKey 'HKCU:Software\Microsoft\VBA\VBE\6.0\AddIns64\MyAddIn.Connect' -FriendlyName 'My Add-In'

MyAddInVBA32Unregistration.ps1:

$ScriptDirectory = Split-Path $MyInvocation.MyCommand.Path
. (Join-Path $ScriptDirectory Functions.ps1)

$Assembly = (get-item Env:USERPROFILE).Value + 'Documents\MyAddIn\Exe\Debug\MyAddIn.dll'

Unregister-Assembly32 -Assembly $Assembly -RegistryKey 'HKCU:Software\Microsoft\VBA\VBE\6.0\AddIns\MyAddIn.Connect'

MyAddInVBA64Unregistration.ps1:

$ScriptDirectory = Split-Path $MyInvocation.MyCommand.Path
. (Join-Path $ScriptDirectory Functions.ps1)

$Assembly = (get-item Env:USERPROFILE).Value + 'Documents\MyAddIn\Exe\Debug\MyAddIn.dll'

Unregister-Assembly64 -Assembly $Assembly -RegistryKey 'HKCU:Software\Microsoft\VBA\VBE\6.0\AddIns64\MyAddIn.Connect'

To run the scripts you need to enable PowerShell execution first and they need to be run with admin rights.

I am finding PowerShell with a learning curve harder than expected and with some “by-design” issues that makes it “tricky” in my opinion, but I hope to learn it in depth.