Protecting intellectual property

[inarbeth]

I am developing spreadsheets for about 30 users to prepare completion statements. They will assist them in doing the math and laying out information in a logical fashion. Whatever I say to the little darlings it is guaranteed they will send out the whole spreadsheet (and not just a pdf copy of the statement) by e-mail to other people. They are impervious to the threat of sacking, torture or death if they disobey

So, I want to find a way to stop people outside the company from using the formulas and macros I build in. Sheet level password protection is as strong as a wet paper bag.

I can't be the first person to want to do this and I wonder if anyone has any great ideas and their experience of password protecting macros and workbooks.

 

[Jay Petrulis]

Put the formulae and macros in an add-in and build the working spreadsheets on the fly. Other ways to do it, of course. The idea is to separate the end result from the "engine."

If the add-in is not installed on the external computer, there will be reference errors, etc. If the formulae are complex ones using only native Excel function calls, convert them to UDFs unless they will slow the recalc time too much.

Of course, it is still not foolproof if the users send the .xla outside, but it is a start. .xlls, .dlls, and .com add-ins would offer a better level of protection but are more involved to create.

 

[Jay Petrulis]

Or...

Develop the application so that the formulae are replaced by values after completion.

 

[inarbeth]

Hi Jay
Thanks for your help. How do I put the formulae and macros into an add-in?

 

[Jay Petrulis]

You would just have to develop your application with a different active workbook / active sheet(s) in mind. That requires correct referencing and the like. Since the critical formulae/calculations are contained int he add-in, distributing the workbooks would require the add-in, or else the report would be filled with errors.

I do not know the details of your project, but I would create an interface for the user to enter the critical parameters and then create the report on the fly -- with no formulae remaining after the processing.

If you have to retrieve data, you can have the user load it at run time as well. Multiple scenarios can be handled easily, too.

Just some ideas. The whole idea is to separate the data from the calculations from the final report.