Replacing UDFs with their value

[Dave Moran]

I've developed an Excel Addin with several UDFs. Everything's fine until my
users want to pass their spreadsheets to other people that don't have my
addin installed. They get #Name in the cells that reference my UDFs. How do
my customers replace the references to my UDFs with their results? These UDFs
could be part of another formula.

e.g = MyUDF(10 * Sum(A1:A10) * MyUDF(B2))

The only solution that I can think of at the moment is to a do a global copy
& paste value, but these loses all formulae.

I know that Excel can replace parts of a formula with its value whilst
editing it. Can this be done programmatically?

Thanks

Dave

 

[Johnny]

Dave,

I had this EXACT same problem with an accounting system add-in. Some
people had the add-in installed on their machine (i.e. - they had
access to the accounting system) and some did not (usually managers who
reviewed the work but did not prepare it and thus had no need for the
accounting system access.)

So, I wrote and add-in called Formula Freeze where the user can
maintain a list of formulas that they could later convert to values
(freeze) with a click of a button before they distributed their
workpapers to others. I don't mind sending it to you, but there are a
number of caveats:

In the current version of Formula Freeze, you can't freeze "nested
formulas". For example:

Formula to Freeze: FOO

This will freeze: =FOO(...stuff....), -FOO(...stuff...),
+FOO(...stuff...)
=FOO(...stuff...) + FOO(...other stuff...)

This won't: =SUM(FOO(...stuff...),FOO(...other stuff...))

I working on adding this functionality as we speak, but this is proving
more challenging than one might expect! If you're interest, I'll send
you the add-in.

Thanks,
Johnny

 

[Dave Moran]

Hi Jonny

I'd certainly be interested in taking a look. I'll send you an email, so you
can reply with the code.

Thanks

Dave

 

[PBezucha]

Hello,
As this seems to be a more general issue, I’d like to provoke discussion
about the topic. In my opinion add-in is not always the best solution how to
apply distant UDFs; maybe your case is a piece of evidence. Working with a
pseudo-add-in workbook needs some provisions i.e. links, opening (automatic
in XLStart), and another type of security. You gain instead, for example, a
non hidden bid of included subs, and, regarding your problem, you may
constuct a simpler freezing macro that doesn't need any list of add-in
functions but recognizes the workbook name alone.
Petr

 

[Dave Moran]

Hi Petr

Thanks for the suggestion, but I don't really want to rework my addin to be
a pseudo addin workbook. It would also screw up my existing users. My addin
only has three UDFs so recognizing the workbook name only wouldn't give me a
huge performance benefit.

I'm working on some VBA code and hopefully the whole process shouldn't be
too difficult. Other suggestions are still welcome, just in case I'm missing
something.

Thanks away

Dave