I have dozens of Excel spreadsheets that contain VBA code and buttons,
is there an easy way to update all of them to the latest version of
the VBA code?

I want to a button and a new function to all of them. Is there any way
to automate this?

Phil Hibbs.

Phil,

I put all my UDFs and routines into one spreadsheet with a custom toolbar,
save it as an add-in an then distribute this to all my users. That way I can
modify and add to a master spreadsheet and then re-save it as an add-in with
version control to be redistributed.

Might be worth considering rather than adding the same code to several
different places.

"Phil Hibbs" wrote:

I have dozens of Excel spreadsheets that contain VBA code and buttons,
is there an easy way to update all of them to the latest version of
the VBA code?

I want to a button and a new function to all of them. Is there any way
to automate this?

Phil Hibbs.

Sam Wilson wrote:
I put all my UDFs and routines into one spreadsheet with a custom toolbar,
save it as an add-in an then distribute this to all my users. That way I can
modify and add to a master spreadsheet and then re-save it as an add-in with
version control to be redistributed.

I've considered making it an add-in but I suspect there are version
compatibility issues here - my client uses an older version of Excel,
and last time I tried to install an add-in that I had created on a
client PC, it crashed Excel. I now have another PC with the client's
build on it so I will try creating the add-in on that PC.

Making it an add-in is a barrier, though, there's a big difference
between "Open this spreadsheet and press the button on the sheet" and
"Make sure you un-install the old add-in, then install the new
version, then open this spreadsheet and press Button X on the
Toolbar".

How do your users know whether they need to update the add-in?

Phil Hibbs.

They get sent an e-mail... It would be easy enough to add a sub to check a
shared location and warn the user if they were out of date, then all you'd
have to do is remember to update that location.


"Phil Hibbs" wrote:

Sam Wilson wrote:
I put all my UDFs and routines into one spreadsheet with a custom toolbar,
save it as an add-in an then distribute this to all my users. That way I can
modify and add to a master spreadsheet and then re-save it as an add-in with
version control to be redistributed.

I've considered making it an add-in but I suspect there are version
compatibility issues here - my client uses an older version of Excel,
and last time I tried to install an add-in that I had created on a
client PC, it crashed Excel. I now have another PC with the client's
build on it so I will try creating the add-in on that PC.

Making it an add-in is a barrier, though, there's a big difference
between "Open this spreadsheet and press the button on the sheet" and
"Make sure you un-install the old add-in, then install the new
version, then open this spreadsheet and press Button X on the
Toolbar".

How do your users know whether they need to update the add-in?

Phil Hibbs.

I have investigated the Excel4MacroSheets property and the Modules
property of the Application but these appear to be empty lists. I was
hoping one or other would contain a list of the macro modules and the
text of the macro code so I could update that.

Phil Hibbs.