VBA Reference Libraries

Question

I'm new to VBA and have been throwing together a small macro application for the Office. We've got about 80 users on essentially identical PC setups, and it will be accessed by all but a few users.

I've been playing around with some automation of accessing web pages using the Web Services references, and I've also loaded the Microsoft Scripting Runtime references into the project. I attempted to run it on a test PC and it complained that there were missing references.

I don't particularly want to go around 80 PCs and manually load the references.

My question, basically, is how should I manage the distribution of this macro-app to 80 odd users so as to ensure that the references will load every time for every user.

Thanks!

Answer 1

For the most part, late binding will solve problems with references in VBA, unless you have some unusual references. Most problems are caused by differences in library versions that can be overcome with late binding. With VBA, it is often recommended that you develop with early binding but release with late binding. The main disadvantage of late binding is changing built-in constants to values (speed is no longer the issue it used to be.)

So:

Dim fs As Object 'Instead of FileSystemObject '
Dim xl As Object 'Instead of Excel.Application '

Set fs=CreateObject("Scripting.FileSystemObject")
Set xl=CreateObject("Excel.Application")

'Value instead of built-in constant '
ForReading=2
Set f = fs.OpenTextFile("c:\testfile.txt", ForReading)

Answer 2

If you have references that your application depends on, that you know are not going to be on the target PCs, then I would strongly recommend you investigate some installer technology.

Using the installer you should be able to install your macro, and install and register all appropriate references / libraries.

There are generally two flavours on windows, Windows Installer based technology and Script based technology.

We use InstallShield for all of our deployment, although there are several options for you to use (there are several discussion on Stack Overflow).

Using windows installer technology, you can build MSI install files, which you are then able to deploy automatically using Group Policy.

Answer 3

Instead of having the documents expose the functionality, make it an add-in for Office (the suite, or the individual apps, your choice). This way, you don't have to deal with references.

Then, just distribute an install package with the add-in which registers the components and registers the add-ins with the appropriate Office apps.

VB6 might be a good idea here, given it's similarity to VBA.