Category Archives: VBA add-ins

Microsoft Office Language Packs

In the same way that Visual Studio offers language packs to have multiple languages, which is really handy for developers of VS add-ins, Microsoft Office 2010 and higher also provides language packs, although not for free:

Microsoft Office 2010 Language Packs
http://www.microsoftstore.com/store/msusa/en_US/pdp/Language-Pack-for-Office-2010/productID.253665800

Microsoft Office 2013 Language Packs
http://www.microsoftstore.com/store/msusa/en_US/pdp/Office-Language-Pack-2013/productID.259321800

which are also handy for developers of add-ins for the VBA editor, to discover, for example, that the “Standard” commandbar name is localized (“Estándar” in Spanish), when it shouldn’t.

To change the language of Office you have to go to Start, All Programs, Microsoft Office, Microsoft Office 2010 Tools, Microsoft Office 2010 Language Preferences. Programmatically, you can get the language of Office through the registry key HKEY_CURRENT_USER\Software\Microsoft\Office\<version>\Common\LanguageResources, UILanguage name.

MSADDNDR.DLL 12.0.20617.1 installed by VS 2013 Preview breaks setups of VBA add-ins

In the last couple of days two users of my add-in MZ-Tools 3.0 for VBA have reported me that the (latest) setup failed with error “Unable to register DLL/OCX:RegSvr32 failed with exit code 0x5”.

Again?

After discovering some months ago that the problem happened because Office 2013 no longer ships the MSADDNDR.DLL file, officially confirmed by Microsoft, I modified the setup to install that file if missing. Then I had to modify the setup again because apparently an older version of that file didn’t work either, so the setup installs it now “if missing” or “if present file older”.

And then, these two bug reports with the same problem? One of the users provided me the version installed on his system: 12.0.20617.1, and a copy, so I could reproduce the problem. Where does that version “12.0.20617.1” come from, if older versions were “6.x.y.z”?. At first I thought about Office 2010 (internally version 12.0), but once I verified with Orca.exe that it ships version 6.x, I searched the term 12.0.20617.1 on the web, and you know what? It is installed by VS 2013 Preview (internally version 12.0)! (I have verified and reproduced the problem). And why does it break existing setups that ship version 6.x? Because it is not backwards compatible! I explain the details in this bug report that I have opened at Microsoft Connect:

MSADDNDR.DLL 12.0.20617.1 installed by VS 2013 Preview breaks setups of VBA add-ins
http://connect.microsoft.com/VisualStudio/feedback/details/793386/msaddndr-dll-12-0-20617-1-installed-by-vs-2013-preview-breaks-setups-of-vba-add-ins

The workaround is easy: force the setup to install its version, not matter which version is installed, but I want to know from Microsoft if I will break VS 2013 doing that.

Msaddndr.dll file officially not installed by Microsoft Office 2013

As I posted back in November, the setup of an add-in for the VBA editor of Office 2013 written with VB6 could fail with the following error:

“Unable to register DLL/OCX:RegSvr32 failed with exit code 0x5”

I mentioned that the cause was that the file Msaddndr.dll is no longer installed by Office 2013 and today I have found that Microsoft wrote an official Knowledge Base (KB) article stating it a month later:

A custom add-in that uses interfaces in the Msaddndr.dll file does not work in Office 2013
http://support.microsoft.com/kb/2792179

The workaround is, of course, that your setup installs that file. BTW, I got yesterday a bug report from a user of my MZ-Tools 3.0 for VBA with that same error but using a MZTools3VBASetup.exe that already (supposedly) installed the file. It happened that the system already had that file installed, but an old version, and the setup only installed it if not present, so it was not replaced by the newest version. So, when applying this workaround, ensure that your setup installs the file if not present, or if it is an older version, because it seems that there are at least two versions of Msaddndr.dll out there.

Windows PowerShell scripts to register a .NET-based add-in for a COM-based host application

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.

MZ-Tools Articles Series: BUG: CommandBar.Name property localized in VBA editor of Office.

Visual Studio causes problems to developers of add-ins in localized versions (as I blogged four years ago), but it is not the only one. The automation model of the VBA editor of Microsoft Office has also problems in localized versions. The CommandBar class has the Name and NameLocal properties for the English name and the localized name, but there are commandbars with its Name property localized (non-English) and others with its NameLocal property not localized (English). This poses a challenge to add-ins for the VBA editor when trying to get a commandbar by its (English) name:

BUG: CommandBar.Name property localized in VBA editor of Office.
http://www.mztools.com/articles/2012/MZ2012020.aspx

MZ-Tools Articles Series: HOWTO: Create a setup for an add-in for the VBA editor of Microsoft Office for the current user (not requiring admin rights) using Inno Setup.

This new article shows a sample of a setup for an add-in for the VBA editor of Microsoft Office using InnoSetup. In this sample I show two interesting techniques:

HOWTO: Create a setup for an add-in for the VBA editor of Microsoft Office for the current user (not requiring admin rights) using Inno Setup
http://www.mztools.com/articles/2012/MZ2012019.aspx

MZ-Tools Articles Series: INFO: Registry entries to register an add-in for the VBA editor of Office for the current user without admin rights.

Continuing with the tutorials that I am writing about creating add-ins for the VBA editor of  Microsoft Office with Visual Studio and the .NET Framework, a new interim article between the last ones with sample code and the next ones that will come with sample setups:

INFO: Registry entries to register an add-in for the VBA editor of Office for the current user without admin rights.
http://www.mztools.com/articles/2012/MZ2012018.aspx

This article provides the solution to two common issues with add-ins of the VBA editor: “How can I install an add-in without admin rights?”, and “After installing the add-in, I don’t see it”.

MZ-Tools Articles Series: HOWTO: Create a toolwindow for the VBA editor of Office from an add-in with Visual Studio .NET.

The next article about creating an add-in for the VBA editor of Office with the .NET Framework, and before writing articles related to the setup, is about creating toolwindows. If you thought it was going to be easy…it’s tricky as hell, with several issues. But fortunately I have found workarounds for all of them:

HOWTO: Create a toolwindow for the VBA editor of Office from an add-in with Visual Studio .NET.
http://www.mztools.com/articles/2012/MZ2012017.aspx

MZ-Tools Articles Series: HOWTO: Create a button with a custom picture for the VBA editor of Office from an add-in with Visual Studio .NET.

After explaining how to create buttons with a built-in Office picture using the CommandBarButton.FaceId property, the next step is to use custom pictures. Tricky as always for the conversion from .NET bitmap to OLE IPictureDisp, but at least fortunately the use of the clipboard (CommandBarButton.PasteFace) is no longer necessary as it was in initial versions of Microsoft Office and in VB5/VB6 add-ins.

HOWTO: Create a button with a custom picture for the VBA editor of Office from an add-in with Visual Studio .NET.
http://www.mztools.com/articles/2012/MZ2012016.aspx

MZ-Tools Articles Series: HOWTO: Adding buttons, commandbars and toolbars to the VBA editor of Office from an add-in with Visual Studio .NET.

Now that I already explained how to create a basic add-in for the VBA editor of Office using Visual Studio and the .NET Framework, and how to debug it, it’s time to add a more complex user interface:

HOWTO: Adding buttons, commandbars and toolbars to the VBA editor of Office from an add-in with Visual Studio .NET.
http://www.mztools.com/articles/2012/MZ2012015.aspx