Leading the way in Microsoft Office Development
 Home|

Excel

|Word|PowerPoint|VBA|SharePoint|Consultancy|Newsletter|Contact 
 Excel > Visual Basic > Personal xls< Previous | Next > 

 

What is the Personal xls ?

 
 

The Personal Macro Workbook is often a good place to keep your common macros as this will always be accessible whenever Excel is open.

 
 

This workbook will not exist until you record your first macro but then it will always be open when Excel is open.

 
 

This workbook can be a good place to store your macros to start with, before you get more advanced.

 
 

This workbook can usually be found in your Excel start-up directory - possible locations for different versions.

 
 

If the Unhide command is unavailable or if it does not display a Personal.xls file then it means that this file has not yet been created. To create this file you must record a macro and store it in the Personal Macro Workbook.

 

 

After this workbook has been created it will load every time as a hidden workbook whenever Excel is open.

 
 

This workbook can be made visible using (Window > UnHide).

 
 

If you choosse to store your recorded macros in the Personal Macro Workbook, the macro is added to a special file called Personal.xls.

 
 

This file is hidden and is saved in yoru Excel startup folder when you close Excel.

 
 

If Personal.xls does not exist, the macro recorder will create one for you.

 
 

You can use Window > Unhide to see the workbook in the Excel window but this is not really necessary as the VBA project is displayed in the Visual Basic Editor window.

 
 

An excpetion where you might want to make the Personal.xls file visible is if you need to store data on its worksheets.

 
 

You can hide the workbook easily afterwards.

 
 

If you are creating a general purpose utility macro which you want to be able to use with any workbook you can store this in your Personal.xls workbook.

 
 

If the macro relates to just the application in the current workbook you can store the macro in that specific workbook.

 

 

Where is the Personal.xls

 
 

This is stored in your start-up folder

 
 

If you have a Personal.xls file this will be stored in your Personal xlStart folder:

 
 

For more details about the exact location of this folder, please refer to the Templates > xlStart page.

 

 

Advantages of the Personal.xls

 
 

Excel opens the Personal Macro Workbook as a hidden file every time that you open Excel.

 
 

If you want to add a custom button to you standard toolbar then you should put the code to be executed when the button is pressed in a module in your Personal.xls.

 
 

Any macros that are stored there are available to run with any workbook.

 
 

To make changes to any menus or toolbars the changes must be saved in your Personal.xls Don’t woryy if you remove buttons and realise you need them later it is very easy just to reset the menu or toolbar.

 

 

Creating a Personal.xls

 
 

The Personal.xls file will not exist until you record your first macro.

 
 

What to do if there isn’t one ??. Try to record a macro into the Personal.xls. Goto (Tools > Macros > Record) – and select “Personal Macro Workbook” before recording a very basic macro.

 
 

If the Personal.xls file does not exist then the Macros recorder will automatically create one for you.

 
 

If you Personal.xls doesn’t appear when you go into you VBE just record a macro making sure that you record it in your Personal Macro Workbook. You should then be able to edit the macro.

 

 

Displaying the Personal.xls

 
 

Personal.xls is a special hidden workbook used to store your macros that is saved in your Excel startup directory.

 
 

This workbook is normally hidden however you can unhide it by pressing (Window> UnHide) and selecting "Personal.xls" from the dialog box ??

 
 

You can view the Personal.xls workbook by selecting (Window > Unhide). It is sometimes useful to store data on the worksheets that is needed for a macro.

 

 

If you have a Personal.xls created it is open every time Excel is open meaning your macros are always available. You can only see the workbook from the VB Editor as it is always hidden.

 

 

Storing your Macros

 
 

You can store your macros in a couple of places. In individual workbooks, in your Personal.xls or in an add-in. If you have recorder or written a macro which you would like available every time you open Excel, the easiest place to store it is in your Personal.xls.

 

 

The project explorer lists any workbooks you have currently got open and also includes any workbooks in the xlStart folder or alternate startup folder. Even workbooks that are hidden (ie the Personal.xls). You will also see some but necessarily all add-ins.

 

 

This can be thought of as the place to store your so called "global" macros, i.e. macros that you want available all the time.

 

 

Things to Remember

 
 
  • This file does not exist unless you have recorded a macro to it.

     
     
  • Your Personal.xls workbook is a common and easy target for macro viruses so if you have made a lot of customisations to the Excel environment it is worth saving a copy of this file.

     
     
  • Knowledge Base Article (822107)

     

     Copyright © 2011 Better Solutions Limited. All Rights Reserved.< Previous | Top | Next >