How to Copy and Load VBA for Excel

I love VBA for Excel. VBA can automate tasks that you use often, making the process faster, easier and more accurate to execute. I like writing VBA for myself to solve my own problems, I like sharing what I write with others, and I like taking the VBA that others write and use it myself.

Now that I am teaching a lot of students how to use Power Pivot, I am coming across a lot of burgeoning Power Pivot users that could benefit from VBA but have exactly zero experience in how to copy and load VBA code that is made available by others for you to use. As Chip Pearson pointed out to me, you have a responsibility to ensure you have the rights to use the code. The purpose of this post therefore is to provide step by step instructions on how to take someone else’s VBA code (that you have permission to use) and get it working on your PC – zero VBA experience assumed.

The steps I will cover in this post are:

1. Turn on the Developer Menu
2. Set up your personal.xlsb workbook so that you have somewhere to save your VBA code
3. Copy your first VBA code into your personal workbook
4. Add the code to the quick launch menu

I am writing this post using Excel 2013 as the tool, but the process is essentially the same for other versions too (although the user interface may be slightly different).

Turn on the Developer Menu

The Developer Menu in Excel is turned off by default. To turn it on, you need to:

1. Right Click on the Menu Bar/Ribbon
2. Select Customise the Ribbon
3. In the dialog box (shown below), turn on the Developer tab by placing a check mark in the box provided.

After you click OK, you will see the new Developer Ribbon appear.

Set up Personal.xlsb

The personal.xlsb file is a special workbook that is used to store VBA code that you want to use with any other workbook. You can also save VBA code directly in other workbooks if you want (your workbook must be in the format .xlsb or .xlsm to save VBA code). After the personal.xlsb workbook is created, it is opened automatically each time you start Excel (it is hidden from view by default). You therefore can’t see it but it is there, and the VBA code will be available for you to use with any other workbook you have open.

Now given this tutorial is for beginners, the chances are that you will not have a personal.xlsb workbook set up as yet. The easiest way to create the personal.xlsb workbook is to turn on the VBA recorder and record a couple of keystrokes. Do this as follows:

Click on the Developer Ribbon (1) and then click Record Macro (2)

When the record macro dialog pops up, change the default location to store the macro from “This Workbook” to “Personal Macro Workbook”. Then Click OK to start the recorder.

The next thing is to record some keystrokes. Just type anything into cell A1 and press Enter (I typed “test”). Once you have done this, click on “Stop Recording”

Now it is time to open the VBA Editor. To do this, press Alt-F11 on the keyboard. This will open the VBA window like the one shown below.

The code you recorded should be shown in the code window (1) (if you can’t see it, then find the personal.xlsb workbook (2), navigate to Module1 (4) and then double click on Module1). You don’t need this code in the VBA code window (1), and you can just delete it. The only reason you created this code was to force the creation of the personal.xlsb workbook.

Note in the navigator window on the left there are a number of VBAProject Trees. The one shown (2) is the personal.xlsb workbook that you just created. You should be able to see Module1 underneath (shown as (4) above). You will also see a VBAProject for the active workbook you have open (Book2 in my case indicated as (3) above).

You can always add a new VBA Module (blank page to store VBA code) by first selecting the VBAProject you want (either 2 or 3 in the image above). Once it is selected, click on the drop down button (5) and select “Module” to insert a new blank module workbook.

Now that you have your personal.xlsb workbook set up, the best thing to do now is to shut down Excel. When you do this, you will be prompted to save the personal.xlsb workbook. Make sure you save changes when prompted.

Copy code into your Personal.xlsb

Now it is time to copy some VBA code from the public domain and paste it into your new personal.xlsb workbook. For all you Power Pivot users, I suggest starting with the “make pivots more readable” code that is posted here at PowerPivotPro.com.

Open Excel again and open the VBA Project Window (Alt F11). Then follow the link above, copy the code from the web page starting from “SUB” and finishing with “END FUNCTION” (you will need both the SUB and the FUNCTION for this to work), then paste the code in the code window (1 in the above image). Now it is important to make sure you have the right project in the right workbook selected before you paste the code. The easiest way to do this is just double click on the correct Module Name in the explorer window on the left (Module1 – item 4 in the image above). Double clicking on the module will make sure that the module code pane on the right is the correct one.

Add your new VBA Code to the Quick Launch Menu

You can run any of your VBA macros from the Developer menu. Just click on Developer/Macros and find the macro, then click “run”. However you can make your VBA Code easier to access and use by adding the code to the quick launch menu. To do this, go back to Excel, click on the drop down menu next to the quick launch menu (1 below), then click (2) more commands

You need to change the default “choose commands from” to be “Macros”, then select the VBA code from the list below (2) and click (3) Add > > to add it do the menu.

Click OK, and you are done.

Time to load up some useful VBA Code

OK, now you know how to set it up, here are some of my favourite VBA macros for Power Pivot users. Some of them are mine and some were written by others. You should validate your right to use these yourself – you have my permission to use mine as you see fit.