CODEWITHSUNDEEP
excelvba
user9654842
user9654842

Reputation:

how to lock down excel macros(VB code)

I have an excel which has lot of macros written, now our security team has come back and reported the vulnerability since a tech savvy can modify or delete macros. So they suggested to lock down macros so that none of the user can even see it. Actually, I changed macros security by checking “Lock Project for viewing” in protection Tab which will lock down the macros, but the problem of this approach is that I need to maintain a password. Is there any other approach where I can lock down the macros in order to restrict the user to view . help will be really appreciated.

Upvotes: 0

Views: 1263

Answers (2)

DecimalTurn
DecimalTurn

Reputation: 4127

In terms of protecting your macros from being edited by other users, the simplest method is certainly the Password Method you've been suggested (Lock Project from viewing).

There is another way that I know to stop people from viewing your macros. It's a tool called Unviewable+. This tool works well when you are a developer that want to distribute an add-in or a workbook with code to other people and you don't want them to have access or change the code.

Password

Pros:

  • Simple to implement

Cons:

  • Need to remember the password.
  • Less secure as there are ways to bypass the password using some techniques you can find online.

Unviewable+

Pros:

  • This is a much more secure method. [Updated: It was only a matter of time, but there are now publicly available methods to bypass Unviewable+ (see followhiterabit's answer for example).]

Cons:

  • This is not free

  • You need to have a master version where you keep your code in a separate file. It will not be possible to reverse the process after making the code unviewable, so you want to apply this protection only to the file accessible to your end users.

Upvotes: 1

followhiterabit
followhiterabit

Reputation: 21

Unviewable+ Pros: This is a much more secure method

I am not so optimistic about Unviewable+ protection.

Any “unviewable VBA code” protection works only in Excel VBA editor, and that VBA code is reachable outside of Excel.

Here is a 10-step guide on how to view VBA code protected with Unviewable+.

Step 1. Detect the file format of the workbook or Excel add-in protected with Unviewable+. To do that, open the file in Notepad and look at its first 2 symbols. If first symbols are "PK" – the file is in XLSM format, otherwise it is in XLSB format (compound OLE storage). If your file is in XLSM format, then go to Step 4.

Step 2. If you have unviewable Excel add-in in XLSB format, then you need to save it as a workbook. To do that open the add-in file in Excel, open VBA Project (Alt+F11) and open "Immediate window" (Ctrl+G). Type in the following command into the immediate window and press Enter (instead of "TheNameOfAddin.xlam" use your Unvewable+ protected add-in file name):

Workbooks("TheNameOfAddin.xlam").IsAddin = False

Then type the next command and press Enter:

Workbooks("TheNameOfAddin.xlam").SaveAs "ProtectedWbk.xlsb", 50

Then close Excel. As a result of the actions above the "TheNameOfAddin.xlam" Excel add-in is saved as a "ProtectedWbk.xlsb" workbook in the default saving location of your Excel, which is most likely your Documents folder.

Step 3. Convert the workbook in .XLSB format into a workbook in .XLSM format. To do that open the .XLSB file in Excel and use File > SaveAs to save it in a XLSM format (Excel Macro-Enabled Workbook) like “ProtectedWbk.xlsm”. Close Excel.

Step 4. Create a new, empty workbook in Excel and save it in XLSM format (for example with the name "NewWbk.xlsm"). Open VBA Project (Alt+F11) in "NewWbk.xlsm" and add a new module into the project (by right-clicking on the "Project – VBAProject" explorer window, then left-clicking the Insert->Module buttons). Save the workbook. Close Excel.

Step 5. Rename the new workbook into a file with a zip extension, as such: "NewWbk.zip" Rename the workbook with the unviewable VBA code from "ProtectedWbk.xlsm" into a zip file like "ProtectedWbk.zip"

Step 6. Open ProtectedWbk.zip in Windows Explorer and go to the folder called "xl" If you see one "bin" file called "vbaProject.bin" then unzip it onto the disk. If you see 2 "bin" files called "vbaProject.bin" and "printerSettings.bin" then unzip the "printerSettings.bin" file on disk and rename the unzipped file from "printerSettings.bin" to "vbaProject.bin".

Step 7. Open NewWbk.zip in Windows Explorer and go to the "xl" folder and overwrite "vbaProject.bin" with "vbaProject.bin" you have unzipped on Step 6.

Step 8. If you do not have LibreOffice installed, then download it from their official website (linked below) and install it on your computer.

https://www.libreoffice.org/download/download/

Step 9. Open LibreOffice Calc. Open "NewWbk.zip" in LibreOffice Calc.

Step 10. Follow the path -- LibreOffice Calc main menu > Tools > Macros > Edit Macros. Open "NewWbk.zip" catalog and observe all the VBA code from the "protected" file.

Upvotes: 2

Related Questions