Hi,

I need to create a spreadsheet that contains a set of special functions for manipulating data. Obviously this can be done with VBA. However, it is vital that users of the spreadsheet are unable to see or edit this code. What options do I have? This is a new area to me, so I can envisage a couple of possibilities:

a) Write a plug-in for Excel that adds some new menu options that run the code behind the scenes. The code spits out the results to cells as required.

If this is possible - how do I do it? How straightforward is it to write a plug-in? How is it installed be the user etc. etc?

b) Write some VBA (as normal) but somehow "protect" it so that it can't even be looked at by the user.

I don't know if such a thing is possible - is it? If so, how do I do it?


There may be other options that I don't know about. I am relatively new to Excel, so am not sure what my options are.

Please let me know what I can do - all suggestions gratefully received a.s.a.p.

Many thanks.

James

Open the Excel Visual Basic Editor, then in it's menu choose
Tools > VBAProject Properties > Protection
check the box and fill in the password fields.

hth
Phil

OK - thanks.

I can see (from some more reading) that I can write as "add-in" and password protect it.

Next question: How secure is the protection? Are there ways of hacking around the protection so that someone can see the code? It is vital that I prevent the code from ever being seen, as the code will form part of a commercial product.

Hope you can help. Thanks.

James

Its a fair question to which i don't really know the answer, but as there are people who make a living by selling password recovery software, I guess its not 100% secure.

Have you considered writing a COM object containing the code that you wish to protect? You could then distribute the compiled COM object with your product and there would be no human-readable script.

Excel can use COM (aka ActiveX) objects via the CreateObject function.

Thanks.

Yes - I suspect that an add-in may not be secure for us and that we really need to write a compiled product.

You mention ActiveX objects - please can someone advise how I go about writing one, and how it gets integrated into the spreadsheet at run-time? What development environment do I need to write an ActiveX object?

To repeat - I am pretty new to this area, so please provide the very basics in your answers, with more detail on top of that, if possible.

Many thanks.

James

Two common options for writing COMs (ActiveX):
1. use Visual C++ and the ATL COM wizard
2. use Visual Basic and create a new ActiveX DLL project

Whichever you choose, you need at least one public class in your project and any methods you need to call from Excel will need to be declared public too.

There are many tutorials out there about creating COM objects - take a look at a few and ask any questions you may have here.

As for integrating it, you will need a setup program that installs the COM object and registers it - again all standard stuff which you find plenty of examples of on the web.

Once the object is registered you can easily get at it from Excel by setting a refernce to it from within the Excel VB editor (Tools > References), then have code like this:
Dim x As YourProject.YourClass
Set x = CreateObject("YourProject.YourClass")
y = x.YourMethod(param1, param2)

etc

hth
Phil

Thanks, Phil.

I assume you mean that a user has to install the AciveX object on their PC, which means they can then call the class methods from a few lines of VBA code within the spreadsheet. Is that right? Have I understood correctly?

If I write an ActiveX object, is that the sort of thing that we can sell commercially? Is packaging and selling AciveX objects something that is commonly done these days?

Forgive my ignorance about this kind of thing. Sometimes the basics have got to be filled in before progressing down a particular route.

Hope you (or someone) can fill in the gaps in my knowledge.

All related posts on this subject gratefully received.

Thanks.

James

Hi James

Yep that's exactly what I mean.

If you take a look at, say, an ASP developer site you'll see loads of people flogging components to do all sorts of things. These components are ActiveX objects.

At the end of the day only you know the ins and outs of what you're trying to achieve and why. I'm just making suggestions ;)

hth
Phil

Thanks Phil.

That's fine.

One further queation: I am going to write the ActiveX DLL with Visual Basic. Do you know if the Visual Basic .NET Standard Edition enables me to do this? (Obviously, it could be done in VB6, but I don't know if the .NET version caters for it - I presume it does. We need to find out before we buy it, but I can't find any reference to this in the marketing info for the .NET product)...

I know these are really fundamental questions...excuse me for being a beginner...thanks for your patience in replying.

James

I don't use .NET, so I don't know.