Migration of Excel Add-in

[Phanidhar]

Hi,
My customers use the excel add-in provided by us to build their models.
That is they use the functions exposed by the add-in to build their own
macros, formulae, etc.
Now we're bringing out the newer version of excel add-in with substantial
changes. This new add-in would have to replace the old add-in in a seamless
fashion without customers making any change to their macros and formulas.

To offer this, we're thinking of a migration tool that would allow us to
convert each of the customers macros/formula to be compatible with new
add-in. For e.g. if the old add-in exposed a function called Add() and the
new add-in deprecates the Add() but introduces AddX(), the migration tool has
to identify each call to the deprecated method in the customer macro/formula
and replace the same with AddX().

I'm trying to see whether anyone has experience is doing something like
that and what are the best practices to be followed for the same. FYI, the
customer macros/formulas are expected to be complex.

Any suggestions on this regard are highly appreciated.

Thanks,
Phani

 

[Nigel]

I presume that the reason you have renamed the function Add() to AddX() is
because it is functionally different with more / different parameters? Which
means more than just changing Add to AddX.

Why cannot you keep Add() and enhance the functionality by responding to the
extra parameters where they exist. The customer then has no changes to
consider, or keep Add() and AddX() as two separate functions?

If I were your customer I would expect that!

 

[Jon Peltier]

You want to go through the client's worksheets and modules and replace all
instances of Add() with AddX()? My first thought is, are you out of your
mind? You have to make sure you get them all, even in protected sheets,
workbooks, and VB projects, without breaking any existing functionality. I'd
say you have less than 1% probability of pulling it off.

Unless AddX introduces radically different behavior (as opposed to enhanced
functionality) you should simply keep using Add() with additional optional
arguments to enable the newer features.

At the very least, leave Add() in place, and introduce AddX() as an enhanced
version. Let the users replace Add() with AddX() if desired and if
necessary, or provide a feature that will do it selectively on a given
worksheet or workbook, with the ability to switch backwards as well.

- Jon

 

[Phanidhar]

Thank you, Jon for your comments. I do agree that its not a trivial job to
pull-off the conversion. In fact, I've proposed other solutions which are
transparent to the customer and the conversion/re-direction is handled at a
different layer.

Having said that, I would still look for solution that would replace my all
reference to the old add-in calls to new reference calls. I can propose this
solution if the customer models are not too complicated.

So what I'm asking at a technical level is if there is a way I can:
1. Get the list of all references of a excel add-in function used in a
sheet( lets ignore VB projects)
2. Replace those calls with the ones new call
3. Sanity check for unresolved references

Thanks in advance,
Phani

 

[Jon Peltier]

On the rare occasions when I've done this. my approach has included

(a) backing up the original workbooks
(b) executing a Find-Replace to change "Add(" to "AddX("
(c) looking for cells which were okay and now contain errors
(d) running through these workbooks to make sure everything seems okay.

- Jon