Moving VBA Code and Forms Into PERSONAL.XLSB

After you’ve written some VBA, or created your own form, or you’ve found some useful code on the internet, you might want to put this into your PERSONAL.XLSB.

If you leave the code/form in the workbook it was created in, then that workbook needs to be open every time you run the code or use the form.

If you move everything to PERSONAL.XLSB, you can use the code/form in any workbook.

There are 3 ways to move code and forms into your PERSONAL.XLSB.

Form Code

Before we go any further, let's just have a quick look at forms and the code that makes them work.

A form has its own VBA code, separate to what is in code modules. If you double click a form module you see the form design. To see the code, you can right click on the module and choose View Code, or right click on the form and choose View Code.

Double clicking (with left mouse button) will also open the underlying code in a form, but the difference here is that this will create a sub for the _Click event for whatever you double clicked, if that sub does not already exist.

If it does exist, because you or someone else already wrote it, then double clicking will open the form code and show you the code for that sub.

Confused? Let’s look at an example. My form looks like this

If I double click the Delete button when I am looking at the form in the VBA editor, then the form code will be displayed, and my cursor will be in the Delete_Click sub.

Manually Create Modules, then Copy & Paste

If you want to only copy/move portions of code, then this is probably the way to do it. If you want to move/import whole modules, then use the drag and drop method below.

If you are working with forms, I’d recommend that you use the Export/Import or Drag/Drop method.

Right click in the project and choose Insert then either UserForm, Module or Class Module, and a new module is created. It will have a default name e.g. Module1. You can change this in the Properties window of the VBA editor.

Now you need to open the module where you are going to copy code to, copy it, then open the new module and paste in the code.

Because VBA is just text, you could use this method to paste in code sent to you by email or in a text document, or copied off a web page.

Export/import

VBA code is contained in a code module. Forms and their code are in code modules.

Code can be exported as .bas files, forms (and their code) as .frm files. These files can be imported to other workbooks, or sent to people and they can import them to their own workbooks.

Export

Right click the module you want to export and choose Export File. Browse to the folder you want to save the file into, change the name of the file you are saving if you wish, and then click Save.

Import

Right click in the project you want to import to, choose Import File. Browse to locate the file you want, and click Open.

Drag & Drop Modules

This is the quickest and easiest way to get modules and forms from one workbook to another.

Click on the module with your mouse and hold the mouse button down. Drag the mouse pointer to the workbook (VBA Project) you want to copy the code to, then release the mouse button.