VBA Reference Libraries

Question

I am new to VBA and am building a small macro application for Office. We have about 80 users on almost identical PC settings, and all but a few users will be available to them.

I played with some automation of access to web pages using links to web services, and also uploaded Microsoft Scripting Runtime links to the project in the project. I tried to run it on a test PC, and he complained about the lack of links.

I don’t really want to get around 80 PCs and manually download links.

My question, basically, is how I should manage the distribution of this macro application for 80 odd users, to ensure that the links will be loaded every time for each user.

Thanks!

Answer 1

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

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 links on which your application depends, you know that you are not going to target PCs, I highly recommend that you study some of the installer technologies.

Using the installer, you can install your macro, as well as install and register all relevant links / libraries.

Windows usually has two options: Windows Installer and Script-based technology.

We use InstallShield for our entire deployment, although there are several use cases (there are several discussions about stack overflows).

Using Windows installation technology, you can create MSI installation files, which can then be automatically deployed using Group Policy.

Answer 3

Instead of documents showing functionality, make it an add-on for Office (a set or individual applications of your choice). This way you do not need to deal with links.

Then simply distribute the installation package with an add-in that registers components and registers add-ins with the appropriate Office applications.

VB6 might be a good idea here, given its similarities to VBA.

Answer 4

In addition to this answer , which is a bulletproof solution to solve these kinds of problems, but which is rather difficult to implement, you can also write some code that should be run when your VBA application starts, checking the collection of "links" of the "application" object. Then you can check (1) if the required files (dll, ocx, tlb) are available on the computer, and (2) if the link can be created (application.references.addFromFile ...).

Be careful: declarations of objects that may be link dependent, for example:

 Dim cat as ADOX.catalog 

compilation will fail if the link is not active when the corresponding module is “compiled”. Then I advise you to isolate your "link checking procedure" in the launcher (equivalent to "autoexec"), which deals only with VBA and the application's underlying objects. Check it with help files (example: in Access, the default links that can be used without external links are VBA, Access, and DAO).

EDIT:

in case external links depend on another software package and (1) cannot be distributed with the MSI file or (2) may have several versions, I think that "reference.addFromFile" is the only solution that can be applied. Example:

• You have a run-time client for a VBA / Access application that should reference Word (file msword.olb).
• For licensing issues, you cannot freely distribute this file using your msi package
• olb file can be either XP version or newer

Our solution is to have 2 tables in the client access file. The list lists all the links that you need to check or add at startup (Word will be one of them), and the other lists all possible locations of the file (depending on whether the user has a version of "office11" or a newer one), with one to many relationships between two tables.

So, a combination between msi packages and code-based management might be the best strategy:

• msi is great for distributing independent dlls or other files completely “embedded” in your application, such as ActiveX controls (for example, scanner controls, report or file viewers, etc.).
• code is the best solution in which your application will have to communicate with other applications (word, excel, outlook, etc.) that may exist in different versions on your user machines.