MAKING EXCEL WORK FOR YOU
© Roy Cox 2005 to 2013
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:
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
' Macro1 Macro
' Macro recorded 2/19/2002 by your name
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.
ActiveCell.Value = Time
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:
|The Personal WorkBook|
|Excel Sheets in VBA|
|Excel UserInterfaceOnly <New>|
|Excel Macro Recorder|
|Excel Camera Tool|
|Excel Summarise Data|
|Excel CHOOSE Function|