Create a DLL for VBA Class Module or better alternative?

Question

Here's my issue: I wrote a VBA class module that consists of commonly used methods and functions. I am continually refining the module and making changes to it.

The problem is that I include this module in almost all of my VBA projects and I really don't want to keep importing the new module into each project everytime a change is made.

My question(s): Would it make sense to create a DLL that all of my projects would link to? Or is there an alternative solution that would make more sense?

Thanks

Just create an Add-in msdn.microsoft.com/en-us/library/office/…? — A. Webb
Creating a DLL would make sense, but then you would need to deploy that DLL to all the computers running your Excel projects. You may be echanging one problem for another, but it depends on your environment what makes most sense. — GTG
Thanks A. Webb for the add-in suggestion. That would work but I also use this library in Access. I could maybe break out the functions and methods into separate classes for Excel and Access but I am trying to avoid that. — UberNubIsTrue
One problem with creating a DLL - you may run into 32-bit vs 64-bit Windows and/or Office issues down the road. Another approach is to leave the common code in a separate add-in that gets demand-loaded when your other add-ins load (iow, the add-in loads, iterates through the add-ins collection looking for your "code library" addin, locates and loads it if it's not present. — Steve Rindsberg
@UberNubIsTrue - did you ever arrive at what you felt was an answer to your question? If so, would you post it here so everyone can see where you landed on this? — Lokerim

StoriKnow StoriKnow · Accepted Answer · 2013-02-19T19:15:12

If you're going to create a DLL, which I think makes perfect sense, you might want to do so in a language built on the .NET framework, such as VB.NET or C#. This provides the ability to combat the 32bit and 64bit issue by building both 32bit and 64bit versions.

It's worth noting that a 32bit DLL running on Windows 64bit with Office 32bit will function just fine. It's when the user is running Office 64bit that you need to worry.

With regards to deployment, you could spend a little extra time to create an installer (using NSIS for example) to make deployment much simpler. This installer would serve the purpose of registering the DLL on the target machine. You could also make the installer install both 32bit and 64bit versions of the DLL to guarantee the DLL will be compatible, regardless of the users configuration. I like the idea of registering both bit versions to avoid the scenario where a user has a Windows 64bit/Office 32bit environment and decides to upgrade their Office version to 64bit. If you have the 64bit version already installed, the reference to the DLL will resolve correctly.

Create a DLL for VBA Class Module or better alternative?

1 Answers