How to distribute your macro?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hans Hubers - 27 May 2008 16:49 GMT

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...

Reply to this Message

Norman Jones - 27 May 2008 16:57 GMT

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

---
Regards.
Norman

> 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...

Reply to this Message

Hans Hubers - 27 May 2008 18:55 GMT

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

Reply to this Message

Norman Jones - 27 May 2008 19:09 GMT

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.

---
Regards.
Norman

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

Reply to this Message

Hans Hubers - 27 May 2008 21:30 GMT

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.

Reply to this Message

Norman Jones - 27 May 2008 21:57 GMT

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

---
Regards.
Norman

> Well, I want to send a spreadsheet to colleagues with a macro so they can
> use
[quoted text clipped - 3 lines]
> it
> should work.

Reply to this Message

Alan - 27 May 2008 18:15 GMT

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

> 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...

Reply to this Message

Hans Hubers - 27 May 2008 21:36 GMT

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.

> 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
[quoted text clipped - 15 lines]
> > 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...

Reply to this Message