Embedded Files
Let's say you have a file called "test_lock.xlsm" that you want to protect, even after you share it with other users. You have 2 options, neither one is perfect, because both can be circumvented.
Option #1: VBA Protection With A Password
Option #1: VBA Protection With A Password
To lock, right click on the project properties (make sure to add some code in a Module and save afterwards):
This prompt will appear to all users afterwards:
To remove (might not work in Excel 2019)
To remove (might not work in Excel 2019)
Rename file as ".zip" and open it. Navigate to the "xl" folder and find "vbaProject.bin". Open with Notepad and find/replace "BPB" to "BPx".
Save the new "vbaProject.bin" and replace it in the .zip file. Restore the original extension in Windows.
Open the file again & set a new password of your choosing. Reopen and that's it, you are free to remove the password (and you avoid the error messages about an invalid key that way)!
For reference, the VBA property to debug is ?ThisWorkbook.VBProject.Protection
Note: If you get this screen, something went wrong (there are some known issues with Windows 10).
Option #2: Make VBA Project Unviewable
Option #2: Make VBA Project Unviewable
To lock, type the following code in the VBA Editor Immediate window (in this example, we will use 'test_lock.xlam' instead of 'test_lock.xlsm', though when saving don't get tricked into saving in the default add-in folder):
Workbooks("test_lock.xlam").SaveAs Filename:="NameOfAddIn", accessMode:=xlShared
We want to make the file MultiUserEditing = True, but since that property is read-only, we cannot set directly, we must absolutely save as a new file.
Anyway, the line of code above will the save the file as .xlsm (since we cannot have a shared add-in, it's no use trying to use FileFormat:=xlOpenXMLAddIn (or 55). This line isn't going to work either: Workbooks("NameOfAddIn.xlsm").SaveCopyAs Filename:="NameOfAddIn.xlam".
Then simply rename the .xlsm for .xlam in Windows. Note that you will get an error message if you try to open the .xlsm version of the file since: Workbooks("NameOfAddIn.xlsm").isAddIn = True. Use Workbooks("NameOfAddIn.xlsm").Close if the file stays open.
To remove the protection (requires LibreOffice)
To remove the protection (requires LibreOffice)
Two commands in the Immediate window is all it takes:
Workbooks("NameOfAddIn.xlam").isAddIn = False 'Makes the file "visible" outside of VBE
Workbooks("NameOfAddIn.xlam").SaveAs "AnotherName.xlsm", FileFormat:=52 '52 is xlOpenXMLWorkbookMacroEnabled
On the new file, type AccessMode:=xlExclusive (alternatively, you could use ActiveWorkbook.ExclusiveAccess method) because that's the default on SaveAs when not specified.
Now you can see the code. But that might not be enough for elaborate defenses.
Open in LibreOffice Calc, Tools>Macros>Edit Macros. Then comment out any protection or obfuscation (like making sure a certain user is the Application.UserName). Don't worry, you will get this pointless warning:
Important: Saving a .xlam as .isAddIn = False or a .xlsm as.isAddIn = True will result in a file error (you won't be able to reopen the file).
One way to try to prevent use of the .SaveAs or .SaveCopyAs technique in a protected add-in is to hide a macro in the Workbook_BeforeSave (or even Application_WorkbookBeforeSave) event (and setting Cancel = True). To be extra mean, they also can try at the same time to execute ThisWorkbook.Close SaveChanges:=False (though to circumvent those two methods, just set Application.EnableEvents = False as soon as you can).
Page updated
Google Sites
Report abuse