Protection Techniques

[tscbill5]

I've been searching and reading as much as possible, but I know little to nothing about workbook, worksheet and vba security. I've seen some posts talking about "very hidden sheets" and code hides all sheets unless macros are enabled.

My question is what are some of your favorite/most comprehensive security techniques? My understanding is that I'll never be able to fully prevent my workbook/code from being accessed but I'd like to come as close as possible.

Details on how to execute the security procedures would be great, but even guidance on what I should be searching would be extremely helpful. A simple search on here or on google returns the basics, but I would love to know what I should be searching for.

Thanks,

Bill

 

[tscbill5]

Anyone ever go the VB.NET route? I don't know much (anything) about it, but it seems like it could (potentially) improve speed, and provide a secure environment. I seriously have no idea what I'm talking about (yet), but if I can code in VB.NET and execute (call) it from Excel/VBA then this may be exactly what I'm looking for.

 

[Colin Legg]

VB.NET isn't secure and it will be slower at automating Excel than VBA. It's also more complicated to automate Excel from VB.NET than from VBA. C++ will offer greater security and performance, but will be even more difficult and time consuming.

My advice is not to worry about code protection too much: just protect the VBA project which will stop day to day users messing around with it.

If you want to learn VB.NET then that's another matter altogether; just don't start to learn it with the expectation that the code will be more secure!

 

[Cbrine]

This site provided me with a cool little technique that uses the ontime method, and excel's single threaded nature to create a cool little work around that will allow you to force a user to enable macro's or close the sheet. This combined with some xlveryhidden sheets can give you a pretty good degree of control to force users to do what you "Allow". I haven't implemented this code technique yet, but I will in the future.
HTH
Cal

 

[tscbill5]

What if I compiled the .NET into a DLL and it took an array input from VBA, and fed an array output back to VBA? Securitizing a specific region of code I do not want users to be able modify/see?

 

[Juan Pablo González]

I've done that with VB6. Create a DLL that then VBA uses, only exposing public methods. Of course, I'm sure someone could decompile the DLL as well, but then, you're getting to the point where maybe using a computer isn't the best thing.

Another alternative is to use a server, execute a web query, like using a webservice or something, and then all the calculations are done in the server, and the client only knows about the function being called, and the response that it returns.

 

[tscbill5]

I've done that with VB6. Create a DLL that then VBA uses, only exposing public methods. Of course, I'm sure someone could decompile the DLL as well, but then, you're getting to the point where maybe using a computer isn't the best thing.

Another alternative is to use a server, execute a web query, like using a webservice or something, and then all the calculations are done in the server, and the client only knows about the function being called, and the response that it returns.

Do you have an example of how you called the DLL from VBA? I think that will be the missing link once I get all of the VB.Net work done. I guess I would need to see how the DLL received the input in VBA, how the DLL is executed, and how VBA receives the output.

 

[Juan Pablo González]

No. You can add the reference to the DLL using Tools | References. From then on, it's just an object that you can create, and that object exposes methods (Subs and/or functions). The rest is the same as if it was just VBA, in terms of variables types. As far as VB.Net, I don't know... never tried it.

 

[yytsunamiyy]

This site provided me with a cool little technique that uses the ontime method, and excel's single threaded nature to create a cool little work around that will allow you to force a user to enable macro's or close the sheet. This combined with some xlveryhidden sheets can give you a pretty good degree of control to force users to do what you "Allow". I haven't implemented this code technique yet, but I will in the future.
HTH
Cal

You got me curious. Would you have the link handy?

 

[Cbrine]

AGGGH...Sorry about that. I thought I had placed the link in the post.

http://www.thijsschreijer.nl/blog/?p=263#respond