20.6 Protecting and Sharing your UDFs

Password Protecting Your VBA

Before we leave this code, let me show you one other helpful tip. Sometimes you have code you want to share with others, but you don’t want them snooping around looking at the VBA code itself. Excel has a way to protect that. Now, word to the wise, this isn’t super great encryption or protection, but it is convenient for the layperson.

In your Project window, you can select which project you want to protect, right-click, and select VBA properties. Under the VBAProject, you can see some attributes under the General tab, but if you go to the Protection tab on the right, you can lock the project for viewing.

To lock the project for viewing, select the checkbox, add the password, confirm the password, and press the OK button. Save your file, close it out, and relaunch it. When you try to look at the code, you will see a dialog box that requires a password to view it. Enter the password, and you should now be able to see the code.

You can remove the password by looking at the VBA properties and unchecking the “Lock project for viewing” checkbox. So, that’s pretty handy.

Saving VBA as an Add-In

After I’ve written several functions in VBA, I like to package those up into an add-in I can share with coworkers. Doing this is surprisingly easy. All you have to do is save your file as an Excel add-in rather than just a macro-enabled workbook. Switch over to your Excel workbook view. Go to the File menu and select Save As. In the filetype drop-down, select Excel Add-in. You can find that down here toward the bottom.

When you open the .xlam file, it will be added to your add-ins such that you can enable it, like you have Solver or other Excel add-ins. The functions found in the add-ins are machine specific, so you need to be careful. If you are using a function packaged up in an add-in, other people using your function must also have that add-in. So, depending on the situation, you might decide that a given function should be associated with a workbook rather than an add-in.

All right, I hope you’ll get in and use these add-ins and enjoy having the power to create new functions that work for you in your situation. Now, you can start letting your coworkers use all these useful functions you have created!