Protecting intellectual property

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912
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 :devilish:

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

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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
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

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
Or...

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

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912
Hi Jay
Thanks for your help. How do I put the formulae and macros into an add-in?
 

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top