Best VBA code for security purpose

[neoseo1300]

Hi everyone,

Having a very limited knowledge in VBA, I'm looking for pre-written VBA code that could handle the most common security features a workbook would want to have, including:

• Forcing activation of macros at opening
• Preventing "Save As" another format than binary
• Verify that sheets & workbook structure are protected during file-open
• Etc.

I've found numerous codes for the first one but, as a beginner, it's hard to chose the one that appears to be the most efficient or secure (for exemple, regarding the forced activation of macros, I see that sometime, the VBA includes some code to deactivate the esc touch, is it important?). So do you guys have a library / website / resource that list some clean VBA code for security purposes that handle the above-mentioned features (and more)?

Thanks a lot

 

[macropod]

You cannot force the activation of macros at opening. If that were possible, macro-virus writers would have a field day. And, since you can't force the activation of macros at opening, it's impossible to enforce your other requirements. About the only way you can 'force' the user to run your macro(s) is to code them so that key features of your workbook are only enabled by an auto macro. There are plenty examples of such code about.

 

[neoseo1300]

Hi Macropod, thank you for your answer.
I understand that we cannot force the activation of macros per say, but there are, as you mention, ways to hide sheets and make them appear after activation of macros for exemple. And that's exactly because there are plenty of examples that I would like to have some clarity on which example(s) should be followed.
For example, am online research provides the following results:
https://www.dummies.com/software/microsoft-office/excel/forcing-your-clients-to-enable-excel-macros/
which seems pretty simple
And then you have more complexe ones like:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=379
https://stackoverflow.com/questions/31793588/force-excel-user-to-enable-macros
Etc.
My first question is then, which one to pick? Does the more complexe one provide more benefits?

Also, my initial question is a bit broader than just forcing activation of macros. It's also about what piece of code can be used to improve the overall security of the spreadsheet. I'm not expecting someone to list all of security measures that can be coded via VBA, but maybe someone has some good resources / links of online guides that discuss the subject in more detail.

Thanks

 

[Logit]

neoseo1300

My 2 cents ...

Any of the suggested methods you have listed (URL links) would suffice as a means of notifying the user. All three links provide basically the same approach. So ... the choice is yours which one you select.

Regarding security ... if you mean how to protect your code from others so it may not be seen or copied ?? .... this is not generally possible with Excel because Excel was designed from the beginning to not be very secure. For every method you can devise someone has most likely created a means
to thwart those efforts.

There are two methods which will slow down determined programmers (and for the casual Excel user ... most likely prevent them from getting to your code) :

#1 Using an add-on Third Party software that obfuscates your code or secures the code in the VBE in a way that it cannot be seen. There are numerous companies on the 'Net that advertise such software .... your choice. Some are better than others in doing their job.

#2 Create an .xlam file from your project. This then becomes an Excel Add-In and functions in every way as the original workbook except the user is unable to view your code or to even access it. To the casual user it will appear as if your workbook doesn't have any code at all yet it works. You can search for VBA .XLAM File for websites and code examples / how-to instructions.

 

[neoseo1300]

Thank you Logit. You mention that multiple software exist to help obfuscate / secure the code, do you have any recommandation in mind? I’ve heard about unviewable+ but I’m, in no way, experienced enough to assess if it is the best for that.

if this can’t be discussed publicly, I would really appreciate your views on the software in pm

Thanks again

 

[Logit]

I've researched this as much as I was able some time back. I too have wanted to "secure" my workbooks. Here is what I've found ... your findings may vary ?

Most all of the Third Party software that claims to obfuscate caused more issues than they solved. The basic issue was not being able to obfuscate all or most all of the code. I found, in order for my projects to function as desired (which means: to function at all), most of the code had to be left as is which meant anyone with a little knowledge was able to 'unlock' the VBE and view the code.

There is one product I purchased which does what it is designed to do ... obfuscate all the code and the workbook functions. That product is called VBash. http://www.ayedeal.com/vbash/

You mentioned "Unviewable+ ". I have seen numerous projects posted on this and other Forums that utilized this product. It seems to work as desired (the VBE code is truly locked down and the user cannot view the code). Plus, although I am by no means all knowledgeable on bypassing their security measures, I was not able to circumvent their security.

The only thing I didn't attempt was using a HEX Editor. I understand if you are knowledgeable on the use of a HEX Editor you can just about go anywhere you want with someone else's software. But then ... that individual would not be your 'casual Excel user'. All software is vulnerable to any half decent hacker.

Hope this helps.

ps: Discussing how to secure your code is permissible. Conversations how to by-pass security measures is not allowed.

 

[neoseo1300]

Thanks Logit, really appreciate the feedback.

 

[Logit]

You are welcome. Best wishes in your search.

Merry Christmas.