Cookies in Use Read more: Cookie Button:Get a free "Cookies in Use" button for your website | Attacat of Edinburgh http://www.attacat.co.uk/resources/cookie-button-generator#ixzz2fW6KXm1C

MAKING EXCEL WORK FOR YOU

Heading

 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy


The Personal Excel WorkBook

What is it?

Microsoft Excel provides a special workbook called the Personal Workbook where you can put any macros and User Defined Functions that you want to be available for all of your workbooks to use. This workbook is hidden, so that you would not normally see it in the list of open workbooks when you click on the Window menu command. When you install Excel, this workbook is not created by the installation process, so this tutorial is to explain how to get Microsoft Excel to create a Personal Workbook for you, how to put your macros and User Defined Functions into it, and how to use those macros and User Defined Functions.

Create your Personal Workbook

The Personal workbook must be created in the correct directory or else it will not work. It should  also have some special attributes like being hidden. The best way to create your Personal Workbook is to get Microsoft Excel to create it for you. You do this by recording a macro and telling Excel to put it into Personal.xls(m). When you do this, Excel will automatically create a Personal Workbook if it does not already exist, put it in the correct place, and give it all of the correct attributes. You can use the following procedure to create your Personal Workbook:

  1. Start Microsoft Excel with a new empty workbook.
  2.  Click on the Tools menu, in Excel 2007 and later versions open the Developer tab
  3.  Click on the Macro command so that the submenu opens. In 2007 there is a button marked "Record macro"
  4.  In the submenu click on "Record New Macro.
  5.  In the Record Macro dialog box click on the arrow in the "Store macro in" box.
  6. Select "Personal Macro Workbook" from the list.
  7. Change the Macro Name if you like, but this is not necessary.
  8. Click on OK. You should get a small toolbar that says "Stop Recording" in the title bar, and that has a button that looks like the Stop button on a VCR.
  9. Click on any cell in the worksheet to select it.

10 - Click on the Stop Recording button in the small toolbar. The toolbar should disappear.

You should now have a Personal Workbook file. To see what has been done, do the following:

View the recorded code

  1.  Press Alt+F11 to open the Visual Basic Editor.
  2.  On the left side of the screen should be the Project Explorer that should show the workbook you are working in (probably named "Book1") and your Personal Workbook . The Personal Workbook should have a Modules collection that contains a Module1 object. Double click on the Module1 object and the macro you just recorded should be in the right window. It should looks something like this:

Sub Macro1()

'

' Macro1 Macro

' Macro recorded 2/19/2002 by your name

''

    Range("A1").Select

End Sub

 I doubt if you will use this macro so you can now select the code by clicking and dragging and then delete it.

If you put macros into your Personal Workbook, then those macros will be in the list when you go to the Tools menu, select Macro and then select Macros from the submenu. Just select the macro and click the run button. You can also assign macros in Personal Workbook to buttons on your command bars. You can  have the following macro in your Personal Workbook file assigned to a button on a tool bar to put the current date and time into the current cell and then move one cell to the right.

Option Explicit

Sub InsertTime()

    ActiveCell.Value = Time

End Sub

 

 

If you put a User Defined Function into your Personal Workbook , you can use this function in any workbook. However, when you enter the function you must also tell Excel that it is located in Personal Workbook . If you have a User Defined Function named MyFunction in your Personal Workbook , you could use the following in a cell to execute this function:

=PERSONAL.XLS!MyFunction()

See also:

The Excel Macro Recorder

Excel addins

Click to view the full sized image