Activating a macro from another file for advanced security protection

[John Jok]

Dear experts


I've been working for the last few months on a vba project that basically generates a technical report of many pages long, distributing imported data, populating tables etc. in one click. It's quite an important project and I have already spent many months on it, trying to automate our work.
Problem is that not only I want to keep the source code completely safe from my colleagues and the management, but for now I would like to let everyone but me only use it without actually owning it or running it independently.


Therefore, the file under development should not contain any of the code I have built, but make it somehow load it upon use and unload it afterwards.
The source code should be totally absent from the file under development and saved in another, hidden location in our common file server, where all colleagues have access.


So I have thought of this: Instead of the file (technical report) under development to run directly the populating macro (which I have built already), the file would use an intermediate file which basically contains the real path to the hidden source code. The intermediate file would be encrypted in binary code, preventing anyone from reading the target's location.
If my research is correct, a safe encryption of the intermediate file can be accomplished by using Excel and the special software from vbacompiler.com.


In summary:
Let word file "A" be the file under development.
Let excel file "B" be the intermediate file for redirecting purposes.
Let word file "C" be the file containing the actual populating code.
The user opens first of all "A" and clicks a button ("A" must remain open) -> a marco in "A" triggers a specific macro in "B" -> a macro in "B" triggers a specific macro in "C" -> "C" can read and alter the contents of "A" -> when finished, "A" should remain open and "B" and "C" should close.

'corresponding Word and Excel Object libraries have been activated in all cases

"A"

Private Sub Label4_Click()
    Dim objExcel As Object
    Dim wbExcel As Excel.Workbook
    Set objExcel = CreateObject("Excel.Application")
    Set wbExcel = objExcel.Workbooks.Open("C:\testvba\B.xlsm")
    With objExcel
        ' .Visible = True
        .Application.Run ("thisworkbook.function22")
    End With
    wbExcel.Close SaveChanges:=False
    objExcel.Quit
    Set objExcel = Nothing
    Set wbExcel = Nothing
End Sub

"B"

Sub function22()
    Dim objWord As Object
    Dim wbWord As Word.Document
    Set objWord = CreateObject("Word.Application")
    Set wbWord = objWord.Documents.Open("C:\Users\C.docm")
    With objWord
        '.Visible = True
        .Application.Run ("thisdocument.task22")
    End With
    wbWord.Close SaveChanges:=False
    objWord.Quit
    Set objWord = Nothing
    Set wbWord = Nothing
End Sub

"C"

Private Sub task22()
    Documents("A.docm").Activate
    'rest of code…
end sub

With all the above being said, I currently cannot tell "C" file to make "A" the active document, because my source code contains a lot of lines staring with "ActiveDocument.".
I get a pop-up error "'run-time' 440:Method 'run' of object_Application failed." in the line of "A": .Application.Run ("thisworkbook.function22")
Perhaps this is happening because the macro is still running in "A", which begins the process in the first place?

Is the method i have thought for protection safe and feasible, or do you have anything better to suggest?

Thank you in advance.

[macropod]

There is a far simpler solution: Delete all the content and save the empty document as a macro-enabled template (.dotm format). Save the template wherever group templates are stored if you want your macros to be available to the document when your colleagues are using it, otherwise, just save the template to your local templates folder. Having done that, re-open the original document and attach your new template to it. Then save the updated document in the .docx format. Done. Your macros will be available to the document provided its template is also available to it.

[Logit]

Sub RunMacro_NoArgs()     'Macro purpose:  Use the application.run method to execute
     'a macro without arguments from another workbook
     
    Dim PathToFile As String, _
    NameOfFile As String, _
    wbTarget As Workbook, _
    CloseIt As Boolean
     
     'Set file name and location. You will need to update this info!
    NameOfFile = "OtherWorkBook.xlsm"
    PathToFile = "C:\Users\logit\OneDrive\Desktop"
     
     'Attempt to set the target workbook to a variable.  If an error is
     'generated, then the workbook is not open, so open it
    On Error Resume Next
    Set wbTarget = Workbooks("OtherWorkBook.xlsm")
     
    If Err.Number <> 0 Then
         'Open the workbook
        Err.Clear
        Set wbTarget = Workbooks.Open(PathToFile & "\" & "OtherWorkBook.xlsm")
        CloseIt = True
    End If
     
     'Check and make sure workbook was opened
    If Err.Number = 1004 Then
        MsgBox "Sorry, but the file you specified does not exist!" _
        & vbNewLine & PathToFile & "\" & "OtherWorkBook.xlsm"
        Exit Sub
    End If
    On Error GoTo 0
     
     'Run the macro!  (You will need to update "MacroName" to the
     'name of the macro you wish to run)
    Application.Run (wbTarget.Name & "!helow")
     
    If CloseIt = True Then
         'If the target workbook was opened by the macro, close it
        wbTarget.Close savechanges:=False
    Else
         'If the target workbook was already open, reactivate this workbook
        ThisWorkbook.Activate
    End If
     
End Sub

[Chas Kenyon]

Sub RunMacro_NoArgs()     'Macro purpose:  Use the application.run method to execute
     'a macro without arguments from another workbook
     
    Dim PathToFile As String, _
    NameOfFile As String, _
    wbTarget As Workbook, _
    CloseIt As Boolean
' ****
     
End Sub

Uhm,.. That is Excel code.

[John Jok]

Hello again and thank you for your replies.


Answering to macropod:


by following the template method, I cannot achieve the level of security I am trying to.


Anyone can follow the path to the source code (the .dotm file) by opening the "Document Template" menu from Developer tab or throught File -> Options -> Add-ins -> Templates -> Go.
Furthermore, the source code is accessible to anyone from the Visual Basic Editor if they open it.


It's well known that the password protection system of office documents is not quite robust, therefore I came up with the suggested solution as described in post #1.
If you think about it, it's unbreakable (thanks to converting the Excel code to binary form).


Any other thoughts?




Answering to logit:


correct me if I am wrong but the code you are suggesting is not solving the problem.
I don't really want the intermediate Excel file to become active, but rather the original document ("A") since many Lines in my code start with the "ActiveDocument." prefix.
I am willing to change that however (making "A" not active while the macro is running) if a solution is to be found.




Is what I am trying to achieve feasible afterall?


Thank you all.

[macropod]

by following the template method, I cannot achieve the level of security I am trying to.


Anyone can follow the path to the source code (the .dotm file) by opening the "Document Template" menu from Developer tab or throught File -> Options -> Add-ins -> Templates -> Go.
Furthermore, the source code is accessible to anyone from the Visual Basic Editor if they open it.


It's well known that the password protection system of office documents is not quite robust, therefore I came up with the suggested solution as described in post #1.
If you think about it, it's unbreakable (thanks to converting the Excel code to binary form).

As I said, the macro code in the template is only available to the document if the template is available. That should not be an issue if you save the template in your local templates folder since, even within workgroups, other users don't ordinarily have such access.

But if you don't want to do that, simply save the blank as an ordinary document in your own folder and make sure both documents are open if you want to run the macro and that the document you want to work on is the active one. The only inconvenience is that you'll need to run the code from the VBE instead of from within the active document.

As for VBA project security, contrary to what you say it is fairly robust. Your deprecation of it, combined with your secretiveness about your code, suggests there is another motive. As an employee or contractor, any code you might develop is the intellectual property of the organization you're working for unless there is an employment/contract clause explicitly saying otherwise.

[Logit]

Ok ... my mistake.

In your first Word .docm ... in the "ThisDocument" paste "

Option Explicit

Private Sub CommandButton1_Click()
    RunMacroInSecondDocument
End Sub

Then in a regular module paste :

Sub RunMacroInSecondDocument()
    Dim wdApp As Object
    Dim wdDoc As Object
    
    ' Open the second Word document
    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Open("C:\Users\logit\OneDrive\Desktop\Doc2.docm")
    
    ' Run the macro in the second Word document
    wdApp.Run "hellow"
    
    ' Close the second Word document
    wdDoc.Close False
    wdApp.Quit
    Set wdDoc = Nothing
    Set wdApp = Nothing
End Sub

[/CODE]

Note ... for testing purposes my second Word document is named Doc2.docm . You'll adjust the name in the macro for your own purposes.

[John Jok]

As I said, the macro code in the template is only available to the document if the template is available. That should not be an issue if you save the template in your local templates folder since, even within workgroups, other users don't ordinarily have such access.

But if you don't want to do that, simply save the blank as an ordinary document in your own folder and make sure both documents are open if you want to run the macro and that the document you want to work on is the active one. The only inconvenience is that you'll need to run the code from the VBE instead of from within the active document.

As for VBA project security, contrary to what you say it is fairly robust. Your deprecation of it, combined with your secretiveness about your code, suggests there is another motive. As an employee or contractor, any code you might develop is the intellectual property of the organization you're working for unless there is an employment/contract clause explicitly saying otherwise.

Thanks again and correct me if I am wrong but I don't quite see how my colleagues will be able to run the code if I'll have to run it from VBE.


Now, the VBA project security I was referring to has to do with the passwords.
I've done some research and they say it's quite easy to crack a password-protected document (including the access to VBE) with a brute force method.


Last but not least, allow me to defend my secretiveness and tell you that my position in the company is a Service engineer and not a programmer, even though we have plenty of them.
I've requested in the past from the company's top management to build a program like this, but the priorities are always different.
I am doing this for my myself and my colleagues you know. Eventually, if the management appreciate the easiness of automating trivial things and the great effort I've put into it, I have no objection of giving it to them. Chances are however that probably they will not. In that case, I'd rather keep at least the code for myself.


Oh, and one more thing. I am doing this in my spare time you know. Officially it doesn't exist, and it cannot exist since they will blame me that I leave my normal job behind.
Until the project is up, running and recognized by the company as it should, I'd rather keep it that way.


Thanks.

[John Jok]

Thanks Logit for your post but the code fails the security criteria I am aiming for.
Doc2.docm and therefore the source code would be still accessible.

My goal is to completely hide the source code as explained in post #1.

I'll test a few things I've come up with and post back.

[macropod]

Thanks again and correct me if I am wrong but I don't quite see how my colleagues will be able to run the code if I'll have to run it from VBE.

Do you want them to be able to run the code, or don't you? If you do, put in in a workgroup template and apply password protection to the VBA project. Simple, safe, and effective.


You originally said:

Problem is that not only I want to keep the source code completely safe from my colleagues and the management, but for now I would like to let everyone but me only use it without actually owning it or running it independently.


Therefore, the file under development should not contain any of the code I have built, but make it somehow load it upon use and unload it afterwards.
The source code should be totally absent from the file under development and saved in another, hidden location in our common file server, where all colleagues have access.

That is precidely what a workgroup template with a protected VBA project achieves.

Now, the VBA project security I was referring to has to do with the passwords.
I've done some research and they say it's quite easy to crack a password-protected document (including the access to VBE) with a brute force method.

Password protection in the pre-Office 2007 environment was weak, being more suitable to keeping incompetent coders out of the VBA project than for security against determined hackers. The Office 2007 and later environment has much stronger security.


There's also a lot you can do to obfuscate your code, too, if that's what you want, such as:
• using the enumerated values of named constants instead of those constants' names;
• giving your variables meaningless or misleading names; and
• adding clutter by inserting modules, subs, functions, & code blocks that never get called.


Any password-based security is susceptible to brute-force attacks but that takes significant effort. Do you really think your (unappreciative) management & colleagues are going to invest that amount of effort just so they can mess with your code?

You being paranoid isn't proof your (unappreciative) management & colleagues are out to do harm and, more importantly, you have no right to keep code you're using at/for work from your organization's management. They have a right to review the code you're using to ensure it won't cause harm and/or give bogus results.

[Logit]

As you are most likely aware, VBA code in Word and Excel is vulnerable to those who desire to view it. The protection schemes are very weak.

There is one method I use that has proven successful so far. It does cost a little money ($100) but for me at least, it provides a level of satisfaction concerning code protection.

You can view the software here : https://www.spreadsheet1.com/unviewa...for-excel.html