How to distribute VBA-Code

[Herbert Becker]

Hello!

My question is how do I distribute VBA projects to our
users' computers, so that I can call the subs/functions
that come with that project?

The background is we have close to 100 different
templates, most of them use VBA code. Now quite a few of
these templates use identical sub routines (i.e. a sub
that reads from a certain text file). I thought it would
be helpful to have a pool of common subs/functions instead
of copying one and the same code to all templates.

I tried using add-ins or putting a template into the
startup folder, but that doesn't work unless you set a
reference to the template containing the code. But the
reference only works for the current project.

Any ideas, links, hints, suggestions are appreciated

Herbert

 

[Jonathan West]

Hi Herbert,

You can call common subroutines that are in a template in the Startup folder
by means of the Application.Run method. This only works for subroutines, not
for functions. However, you can return a value in a parameter of a
subroutine.

 

[Jezebel]

Persevere with the add-in concept: it does work, and for most purposes it is
a good solution for what you're trying to do. An alternative is to put your
code into a VB DLL; you can instantiate the reference from your template
code using GetObject.

 

[Herbert Becker]

First of all thanks a lot for your reply!

I'd like to solve this with add-ins as you suggested, only
I can't see where I made a mistake trying.(and I obviously
made one 'cause you're saying it does work) For testing
reasons I manually added a template (via tools =>
templates and add-ins .. ) but I can't reference the subs.
Any ideas what I might be missing here?

Herbert

 

[Herbert Becker]

First of all thanks a lot for your reply!

I'd like to solve this with add-ins as you suggested, only
I can't see where I made a mistake trying.(and I obviously
made one 'cause you're saying it does work) For testing
reasons I manually added a template (via tools =>
templates and add-ins .. ) but I can't reference the subs.
Any ideas what I might be missing here?

Herbert

 

[Herbert Becker]

First of all thanks a lot for your reply!

I'd like to solve this with add-ins as you suggested, only
I can't see where I made a mistake trying.(and I obviously
made one 'cause you're saying it does work) For testing
reasons I manually added a template (via tools =>
templates and add-ins .. ) but I can't reference the subs.
Any ideas what I might be missing here?

Herbert

 

[Herbert Becker]

Thanks a lot for your reply!

Good to know that there is a way even if it means recoding
almost all of my templates. But noone said life is fair
I will consider your suggestion.

Greetings,
Herbert

 

[Jonathan West]

Thanks a lot for your reply!

Good to know that there is a way even if it means recoding
almost all of my templates. But noone said life is fair
I will consider your suggestion.

An alternative approach might be to accept that the code will have to be
duplicated, and write a macro that copies the modules from a common source
template into all the other templates. You can do this using the
OrganizerCopy method. This way, you can ensure that all the templates are
using the same version of the common modules.