Protecting Word Macro Code? 1

[paperworker]

Is there a way to make Word macro code available for others to use but at the same time hide the code or deny access to the code to protect it from tampering?

 

[Ed2020]

You can place command buttons, userforms, or any number of other controls within your document and call the code from the controls' events.

You can also add a VBA password from the VB Editor window (from within Word go to Tools>Macro>Visual Basic Editor):

Go to Tools>Project Properties>Protection. Select "Lock project for viewing" and add as password of your choice.

Ed Metcalfe.

Please do not feed the trolls.....

 

[fumei]

You can do that and more...

You can change the actual Word command to even open the VBE. Have it so that only you can open the VBE. Have it check first to see if it IS open, if it is, close it, then offer a password box. No password, they can not even GET to the VBE. They will never even have a chance to see any code at all. You can go farther and don't even offer a password entry to the VBE. Make it so that only your login password allows access.

You can do other stuff as well...but you know what? There IS NO FULL SECURITY. You can certainly block access to the average user, but you absolutely can NOT make any Word document fully secure to a determined and knowledgeable hacker.

Gerry
My paintings and sculpture

 

[paperworker]

I've tried "Lock project for viewing" with a password. The problem is, the macro has to reside in a template on a server so that many people can access it. The lock project feature prevents the macro from being copied through the Organizer into the document where the macro will be run.

I'm intrigued with the idea of preventing end-users from opening VBE for my macro. How would I go about setting this up?

I found the method Application.ShowVisualBasicEditor = True (change this to False?) but I'm not sure where to put that in my macro. Sorry, I'm still new at this stuff.

 

[fumei]

The problem is, the macro has to reside in a template on a server so that many people can access it. The lock project feature prevents the macro from being copied through the Organizer into the document where the macro will be run.

Sorry, but this does not make sense.


The first sentence is reasonable, but why would the macro be copied into a document? If it is in a template, when a template is properly used, the clone of the template has access to the code.

Why would you be copying the code with the Organizer? Is this copy being done by code, or manually? I am not getting this, and as stated, it does not make any sense to me. Could you clarify?

With the renewed caveat that there IS NO FULL SECURITY...

Sub Document_Open()
If Application.ShowVisualBasicEditor = True Then
   Application.ShowVisualBasicEditor = False
End if
End Sub


Sub ViewVBCode()
Dim response
response = InputBox("Enter your password.")
If response = "zippity do-dah" Then
   Application.ShowVisualBasicEditor = True
Else
   Msgbox "Visual Basic Editor is not available."
End If
End Sub

Placed in a code module in the document, this:

1. on Document Open, checks to see if the VBE is already open
2. if open, closes it
3. on trying to open the VBE, asks for password
4. if password correct, opens the VBE. If not, displays message the VBE not available.

Note that the password is hard-coded. There are other possibilities.

An alternative is:

Sub Document_Open()
If Application.ShowVisualBasicEditor = True Then
   Application.ShowVisualBasicEditor = False
End if
End Sub

Sub ViewVBCode()
If ENVIRONS("username") = "[i]your_loginname[/i]" Then
   Application.ShowVisualBasicEditor = True
Else
   Msgbox "Visual Basic Editor is not available."
End If
End Sub

This disallows anyone, other than someone logged on with your login-name, from accessing the VBE.

In other words, trying to access the VBE on any computer that is not logged on with your login_name will fail.

You MUST use Document_Open to close the VBE, if open. Doing it with the ViewVBCode command would also work...BUT

if there was another document open, and IT was used to open the VBE, and THEN your document is opened...the VBE is available. Yes, trying to open the VBE could be made to close it and deny access, but if already open, then it is pretty darn easy to get around this.

If the document in question explicitly closes the VBE (if open) as soon as the document itself is opened, then access to the VBE can be restricted.

However, I must stress again - this is NOT even remotely bullet proof. For most general level users, sure. But no Word document, or any code in one, can be made fully secure. It simply is not possible.

As a further note: you can override ANY Word command by code. This is just overriding the command to open the VBE with other code.

Final note: ViewVBCode is the command for both the menu Tools > Macro > Visual Basic Editor and the Alt-F11 shortcut.

Gerry
My paintings and sculpture

 

[Ed2020]

Gerry,

Isn't there one very easy workaround to these suggestions? Opening Word and then disabling macros when prompted will turn off all of your security, unless you combine it with a VBA project password as well...

Ed Metcalfe.

Please do not feed the trolls.....

 

[fumei]

Well of course you would still password the project. Sorry, I was never intending to suggest this as a replacement. As I tried to point out there is no security - even, BTW, password protecting the project; and that you can build layers of trying to secure it.

I would like to point out though, even if you set Security to HIGH, and therefore macros are disabled, having the override code for ViewVBCode, because it is a built-in Word command will, in itself, prevent opening the VBE.

ViewVBCode is now a macro, and will be disabled..ie. you can not open the VBE.

EXCEPT, if the VBE is already open from another document. That is because the Document_Open macro is also disabled, which closed it.

The short answer is...yes, you are correct, the project should also be protected.

It does go back to the OP mentioning copying the macro, which is not going to happen if the project is protected. But then, I still do not understand the macro copying part...

"turn off all your security", hmmmm, except, I never said it WAS security.

It isn't. There is none. Just layers of trying. One of which is, yup, password protecting the project.

Perhaps if we knew the precise circumstances. For example, is the template being used properly? That is, are documents being cloned using File > New. If the template is set as Read-Only (by file attribute) at the OS level (not with Word) on the server, AND file/folder permissions are also carefully set at the OS level, then certainly there is a fair amount of security for the original template file.

As for the code that would be accessible from the template, perhaps it may help to understand what the issue is. What tampering exactly is the concern?

Gerry
My paintings and sculpture

 

[paperworker]

I was asked to come up with a way to automate a clean-up project of existing documents...to remove unwanted styles, update some paragraph styles, reset the page margins, etc.

The macro resides in a separate template from the document template used to generate new documents. (The document template was authored elsewhere, and though we are expected to use this template, we have very little say as to how it is set up.) The macro is copied into an existing document and is deleted after the macro has finished the clean-up.

Someone complained to my manager that my macro didn't work. I discovered that a piece of the macro code had been "accidentally" deleted. I replaced the "tampered" template with a backup copy and the macro ran like it was supposed to. This made me think about whether there was a way to hide the code but still make it available for people to use.

 

[fumei]

The cleanup macro does not need to be copied, then deleted. There is no requirement that the code has to be in the document that it is performing actions on.

Dim ToBeCleaned As Document
Set ToBeCleaned = [i]document_name[/i]
With ToBeCleaned
  .yadda yadda
  .yadda yadda
End With

will do the job just fine. As long as the document to be cleaned is explicitly identified, then you can do whatever you want to it from another template.

It seems to me that copying and then deleting macro code is a messy, and potentially failing, way of doing things.

Heck you can have the cleanup code in the separate template and run it as a looping cleanup through a whole folder, never copying the code at all.

Sub CleanUp()
With ActiveDocument
  ' do this
  ' do that
  ' do aome other thing
End With
End Sub

Sub GetEachDoc()
Dim sPath As String
Dim CleanFile
sPath = "c:\test\"
CleanFile = Dir(sPath & "*.doc")
Do While CleanFile <> ""
    Documents.Open FileName:=sPath & CleanFile
        Call CleanUp
    ActiveDocument.Close wdSaveChanges
    CleanFile = Dir
Loop
End Sub

The above code can be in the separate template. It has the cleanup code as a separate procedure. The getting procedure uses Dir to process through all .doc files in a folder (in this case C:\test), passing each one to the cleanup procedure, then saving the changes.

This also can demonstrate something relevant to your last sentence.

there was a way to hide the code but still make it available for people to use.

Global templates. Global templates are never viewable - with the ONE exception of normal.dot.

Say you have your code in Corporate.Dot. It lives on a network folder. It is read-only...OS level read-only.

Two things you can do.

1. Have code in the user's normal.dot, OR code in documents that load corporate.dot as a global template. All code in corporate.dot is available, but the the project is unavailable to view. Projects are only available to view (except for normal.dot) when the file containing the project is open.

However, CODE in a global template is completely executable even though the file is NOT open. This is what globals are for!

2. Using whatever means you like, you can copy the global corporate.dot to the user machine, and have it load on startup. Again, the CODE will be completely accessible, but unless the file is actually opened, the code is unviewable - whether it is VBE protected, or not.

To have it be a startup global by user, put it in the Documents and Settings/username/ApplicationData/Microsoft/Word/Startup folder. This will load the gloabl on startup for that user.

To have it be a startup global for any user of that machine, put it in Program Files/MSOffice/Startup...or whatever the startup folder is for your version.

Again, CODE in a loaded global is completely available, including any shortcut keys, custom toolbars etc, etc. Unless the actual file is also opened, the code is not viewable.

With careful use of user permission levels, OS level read-only attributes, you can load that global and make code execution available, but code access not.

Again, though...this is not bullet proof. Nothing is bullet proof.

This is the route I take. The only drawback across a network is either pushing down the global to each machine, so it is local (and therefore potentially accessible); or using other code to load it temporarily and keeping the actual file locked up on a server.

The interesting thing about globals is if you do NOT want it loaded on startup, the global file can be...anywhere. It can be...anywhere. Including a folder that is normally not visible to the user. If the user has READ rights to the folder, it can be loaded. If they do not have MODIFY rights, then they can modify, and the global is mostly safe.

You certainly do NOT need to copy macro code to the documents in order to execute it. There may be other reasons why you are doing this, but being able to execute code is not one of them.

Gerry
My paintings and sculpture