PERSONAL.XLS(B)

If you want that certain code is available in all your workbooks, then use your
PERSONAL.XLS or in Excel 2007 your PERSONAL.XLSB file.

What is it:
This is a hidden workbook that opens when you start Excel.
The code you copy in this workbook is available in all workbooks you have opened in Excel.

Where is it:
If it exists, you can find the file in the Excel startup folder.
C:\Documents and Settings\Ron\Application Data\Microsoft\Excel\XLSTART
In Vista look here
C:\Users\Ron\AppData\Roaming\Microsoft\Excel\XLSTART

Note: Ron is the username in the path above

With code you find the correct path on your machine with this line

1) Open excel
2) Hit alt-F11 to get to the VBE
3) Hit ctrl-g to see the immediate window and type this:
4) ?Application.StartupPath

You’ll see the startup path returned


How do you create the file if it does not exist:

If it does not exist then record a dummy macro and change the “Store macro in:”
drop down to Personal Macro Workbook. Excel creates the file for you then in the correct folder.


Excel 97-2003: Tools>Macro…Record New Macro.

Excel 2007: On the “Developer” tab click on Record macro.
To display the “Developer” tab go to Office Button >Excel Options...Popular.

Or faster click the button on the bottom left corner of the Excel 2007 window to start the recording.




Press OK in the dialog that you see now




Because we only want Excel to create the file for us we can stop the recording directly.
Press the Stop Recording button or use Tools>Macros…Stop recording in Excel 97-2003



In Excel 2007 you can press the Stop Recording Button on the Developers tab or click the
button on the bottom left corner of the Excel window to stop the recording



Now we use Alt F11 to go to the VBE editor(working in all Excel versions)
Or:
Excel 97-2003: Tools>Macro>Visual Basic Editor.
Excel 2007: Click on the button named “Visual Basic” on the “Developer” tab


Make the file ready

Note: I use the name PERSONAL.XLS in the text below but in Excel 2007 the name is PERSONAL.XLSB
To be sure that we see the Project Window, Code window and the Properties window we use one time.

Ctrl-r (to see the project explorer)
F7     (to view the code window)
F4     (to view the properties window)

Now we give the PERSONAL.XLS a unique project name (Default is VBAProject)
Select the PERSONAL.XLS in the project window and change the name in the properties window.

    

Now use Ctrl-s or File>Save in the VBA editor to save the PERSONAL.XLS.

We are now ready to add code (macros or/and functions) to this file so we can use it in all the workbooks.
With Insert>Module in the VBA editor you can also add more modules to the PERSONAL.XLS.
This way you can organize your code in this file.

Use only code here that you use yourself and not code that you use in files that you distribute to other users.
In this case add the code in the modules of the workbook itself. Or see the ”Add-in” part on this page.


Example 1: Macro

Public Sub Test()
    MsgBox "Insert your favorite code here"
End Sub

1) Select the PERSONAL.XLS in the project window and click on the + before it
2) Then click on the + before Modules
3) Double click on Module1 to open the code window on the right
4) We copy/paste the example macro in this module.
5) Now use Ctrl-s or File>Save in the VBA editor to save the PERSONAL.XLS.
6) We use Alt–q to go back to Excel or use "File >Close and Return to Microsoft Excel".
7) You can run the macro then with Alt-F8 or Tools>Macro…Macros / Developer tab>Macros

Note: You can delete the dummy macro that we used to create the PERSONAL.XLS.


Example 2: Function

This function gives you the ISO week number that is used in Europe and a lot of other countries.
Note: This function is not available in Excel.

Public Function IsoWeekNum(d1 As Date) As Integer
' Daniel Maher
    Dim d2 As Long
    d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
    IsoWeekNum = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function

1) Select the PERSONAL.XLS in the project window and click on the + before it
2) Then click on the + before Modules
3) Double click on Module1 to open the code window on the right
4) We copy/paste the example function in this module.
5) Now use Ctrl-s or File>Save in the VBA editor to save the PERSONAL.XLS.
6) We use Alt–q to go back to Excel or use "File >Close and Return to Microsoft Excel".

Now we can use this in a worksheet cell with the date in A1

=PERSONAL.XLS!IsoWeekNum(A1)

If you create a reference in the workbook where you want to use it to your
PERSONAL.XLS then you can use

=IsoWeekNum(A1)

Go to Tools>References in the VBA editor with the workbook selected in the project window and
add a check mark before RonPersonal. (Save the workbook then)




Problems with the PERSONAL.XLS(B) workbook


What to do if your PERSONAL.XLS(B) is visible

When you open Excel and you see PERSONAL.XLS(B) in the title bar you can use this to hide it.

Excel 97-2003: Window>Hide
Excel 2007     : On the View tab in the window group choose Hide

Then close Excel and say Yes to save the changes to your PERSONAL file.


PERSONAL.XLS(B) will not open but is in the correct location

When Excel think the file is corrupt it can disable the file.
You can re-enable it here but if it is really corrupted replace it with a backup.

Excel 2002-2003: Help>About MS Excel>Disabled items
Excel 2007         : Office button>Excel Options..Add-ins
                              In the "Manage:" dropdown (bottom of the dialog)
                              Choose "Disabled Items
                              Go




Use a Add-in

You can also create an add-in to store code that you want to use in all workbooks.
(A add-in is also a hidden workbook like PERSONAL.XLS)

This is a good way if you want to distribute code to other users. Open a new workbook and copy
the code in this workbook and then use File>Save As or Office Button>Save As to save it as
Excel Add-in, use a name like MyUtility.xla for example(xlam in Excel 2007).

Before you save it give MyUtility.xla a unique project name (Default is VBAProject)
see how I do it above with the PERSONAL.XLS(B).

You can install the add-in like this:

1) Tools>Add-ins in Excel 97-2003
    Office Button>Excel Options..Add-ins and then Manage Excel add-ins...Go in Excel 2007
    Or use the shortcut (Alt ti example for the English version)
2) Browse to the XLA(M) file
3) OK
4) Add a check mark before it in the list
5) OK

Now every time you open Excel it opens the add-in and you can use the code in all workbooks.

Note: Add-in macros not show up in Alt-F8 or in Tools>Macro…Macros in 97-2003 or in
Developer tab>Macros in 2007. You must make a menu for the macros in the add-in.


Create your own menu/toolbar to run the code

Excel 97-2003

Debra Dalgleish's (Toolbar example from Dave Peterson)
http://www.contextures.com/xlToolbar02.html

Creating Custom Menus (John Walkenbach)
http://www.j-walk.com/ss/excel/tips/tip53.htm

Ole P. Erlandsen's Web Site (Example workbooks)
http://www.erlandsendata.no/english/index.php?d=endownloadcommandbars

How to customize menus and menu bars in Excel
http://support.microsoft.com/default.aspx?scid=kb;en-us;830502&Product=xlw


Excel 2007

In Excel 2007 check out this example for your PERSONAL.XLSB
http://www.rondebruin.nl/qat.htm

If you want to change the ribbon check out this page
http://www.rondebruin.nl/ribbon.htm

Note: It is not possible to use XML to create a menu for your PERSONAL.XLSB.
Use the QAT example then