How to distribute your macro?

[Hans Hubers]

What is the best way to send a macro to colleagues that don't know how to
program in VBA? Until now I only made macro's for myself and just put them in
a custom menu, but that doesn't work for colleagues of course...

 

[Norman Jones]

Hi Hans,

See Jan Karel Pieterse's extensive tutorial at:

Creating An Add-in
http://www.jkp-ads.com/Articles/DistributeMacro00.htm


See also:

Updating An add-in Through the Internet
http://www.jkp-ads.com/Articles/UpdateAnAddin.asp

and

Installing An Excel Add-in Using Setup Factory
http://www.jkp-ads.com/Articles/AddinsAndSetupFactory.asp

 

[Alan]

Personally, I like to put my macros in a workbook, using an Auto_Open event
to install a toolbar referencing the workbook macros. The end user can store
the workbook anywhere they want. They open the workbook, install the toolbar,
and access my macros via the toolbar. If I enhance the macro, I send the new
workbook to the user, they replace their copy with the new workbook, and the
toolbar continues to function. If I add a macro during the enhancement, I ask
them to re-open the workbook and install the toobar, which replaces the old
one, and give them access to the new macro.

In one case, I have a workbook, sitting on a intranet server, that 30+
people access at least once a week. Easier than installing add-ins for 25+ of
them, since there is only 5 in the group that know that Excel is more than a
spreadsheet viewer.

Alan

 

[Hans Hubers]

Yes, indeed, well done. But what about an ActiveX control and the macro in
the workbook of the file. Would that work?

 

[Norman Jones]

Hi Hans,

I am tempted to say yes on both counts
but I have a suspicion that your question
may not be what it seems.

Perhaps you could give an explicit example.

 

[Hans Hubers]

Well, I want to send a spreadsheet to colleagues with a macro so they can use
it easily without explaining them how to customize the menu etc. So I guess
that with an ActiveX button and the code in the sheet part of the project it
should work.

 

[Hans Hubers]

Thanks for your remarks Alan. Didn't think of updating yet... Will try with
an AtiveX button. If something has to be changed in the code, I'll turn to
your method.

 

[Norman Jones]

Hi Hans,

Try downloading Jan Karel Pieterse's
excellent Name Manager add-in for a
highly useful pracical example.

You will see, with your own eyes, that
what you want is eminently feasible!

T|he Name Manager add-in may be
downloaded at:

http://www.jkp-ads.com/Download.asp