Hi Josh!
Here's something I put together on Personal.xls
The Personal.xls file is both a normal file and a special one. It is
‘normal’ because it has the same structure and facilities as any other
workbook. It is ‘special’ because Excel can create it and it is most
often used for special purposes by users who go beyond the
intermediate stage and start to write their own subroutines and
functions.
The Personal.xls file, if it exists, is usually loaded by Excel every
time that you start Excel. If it exists, it sits in the XLSTART folder
(aka directory). In multi-user set-ups in Windows XP and Windows 2000,
each user has their own Personal.xls file. What’s all this ‘if it
exists’ about?
When you create your first Macro / Subroutine, you are given the
option of saying where you want to put it. One of those options is
“Personal Macro Workbook.” If you select that option, Excel will
create the Personal.xls file and when you save it, it will be saved in
the XLSTART folder / directory. Thereafter, every time you start Excel
the Personal.xls file will be loaded (as will any other files that are
sitting in the XLSTART folder).
You don’t usually see the Personal.xls file. It is loaded as a hidden
workbook. But if it is loaded you can establish its existence and
unhide it using:
Window > Unhide [There will then be an “Unhide” dialog and if the
personal.xls file exists, it will be listed.]
Cancel
But it is not usually a good idea to unhide the Personal.xls file. You
should only unhide it if you want to place material on the worksheets
that are in the Personal.xls file
**What is the Personal.xls Used For?
The Personal.xls file can be used for various things. You might use it
to:
1. Establish your favoured settings or ‘defaults’ so that if any
“foreign” or special workbook has altered them, you can get them back
by the simple expedient of closing and re-opening Excel.
2. Create special functions (User Defined Functions) that do special
calculations that aren’t easily done by Excel’s existing functions or
which require complex processes to achieve by normal operations in a
workbook.
3. Create special subroutines (aka ‘macros’) that do things that
cannot be achieved by formulas or functions such as alter the format
of a cell or send an e-mail or set up standard print options like
headers and footers or …the list is almost limitless.
4. Create or modify special commandbars (aka ‘toolbars’ and ‘menubars
’) with or without your own special buttons, which insert formulas or
functions or which trigger a particular subroutine.
5. Create new keyboard shortcuts that trigger a subroutine or to
assign a new context sensitive mouse right click menu option.
6. Create powerful event handling subroutines that get triggered
whenever a particular event occurs such as opening a new workbook.
7. Test functions and subroutines before turning them into or
incorporating them into an existing or new workbook or Add-In.
8. Personal base data may be stored on one of the worksheets within
the Personal.xls but I haven’t seen that mentioned anywhere.
9. John Walkenbach uses a special procedure for building up
hierarchical menus from worksheet information and if you follow that
approach, the data may be kept on a worksheet in the Personal.xls.
For many Excel advanced users it can a varied box of tricks. Here’s
what Jon Peltier MVP said in the microsoft.public.excel.programming
newsgroup in response to a question “What's in your personal.xls
file?” on 9-Jan-2002:
Start Quote from Jon Peltier>>
“In addition to many half written bits and pieces, I have several
crucial things in my personal.xls workbook. First is the code that
constructs my custom command bars. My various paste special buttons
(paste values, paste values transposed, paste formulas, paste as
picture plus copy as picture for charts). The button to center across
the selected cells (like Excel 95; I don't like merging cells if I can
avoid it). The button with the special border styles I like, the code
to fix up any chart the way I like and some other charting things, one
to lock or unlock all sheets in the workbook, the ones that increase
and decrease zoom by 5 percentage points. The screen saver (an API
deal). The custom print setup routine, the text change case routine.
The very important one that lists all defined names in a workbook.
Another that breaks the hyperlinks (from a web page loaded into
Excel). Some things that perform rounding or truncate to integers, or
fill linearly or logarithmically the range between two cells, or fill
blank cells from the previous value above. Some statistics, some
rudimentary cryptography, some file renaming stuff, a lot of little
things called "test()" or "testme()" etc.
The sucker's around 600k, and between it and the addins I use, it
takes 20 or so seconds to load Excel. I'm thinking of breaking it
down into a few addins (charting, formatting, whatever) so I only need
to load what I need at that time.”
<<End Quote from Jon Peltier
And as I’ve done a ‘show and tell’ with Jon Peltier it is only fair to
admit to what I have in mine!
Sub ApostroRemove()
Sub Beeper()
Sub CentreAlign()
Sub CharMap()
Sub DateTimeStamp()
Sub EquationEditor()
Sub FormatComments()
Sub InsertDate()
Sub InsertTime()
Sub LeftAlign()
Sub LISTENVIRON()
Sub makelastcell()
Sub MergeLeftTopWrap()
Sub PrintBy()
Sub ProtectAllSheets()
Sub RightAlign()
Sub SaveAllFiles()
Sub ShowCommandBarNames()
Sub UnhideAllSheets()
Sub UnprotectAllSheets()
But most of my UDFs are put in a special Functions Add-In which is
updated as soon as it is reasonably established that the function
works as it is supposed to. I also have a tendency to run major new
tests in separate files so that I don’t get too much accumulated stuff
in my Personal.xls.
***Creating a Personal.xls
It’s handy to have the Personal.xls in existence ready for use
immediately you want it.
First establish that you don’t already have one (under the current
user log-in if appropriate):
Window > Unhide [check there isn’t a PERSONAL.XLS entry]
Cancel
Open a new workbook [Just so you don’t damage anything you have]
Tools > Macro > Record New Macro [Up pops the Record Macro dialog]
Click dropdown arrow for “Store Macro In:”
Select “Personal Macro Workbook”
OK
Tools > Macro > Stop Recording
If you now open the Visual Basic Editor you will find that you now
have a Personal.xls file:
Alt + F11 [Opens the Visual Basic Editor]
In top left box you’ll see, possibly among other listings, an entry:
Personal (PERSONAL.XLS)
Expand the tree and you’ll find under Modules that you have a Module1
that contains whatever you recorded between pressing OK in the above
procedure and stopping the recording process.
Select the Personal(PERSONAL.XLS) entry
File > Save
You now have a saved Personal.xls file and you can check that it now
exists using the Window > Unhide > Cancel process.
***Finding the Personal.xls File
**Windows 95, 98 and ME
The Personal.xls file is usually pretty easy to locate:
C:\Program Files\Microsoft Office\Office\XLStart
But note at this point that there isn’t always a Personal.xls file and
it can be a hidden file.
There are minor variations such as different Drive or non-standard
installation or Office10 being the folder under Microsoft Office but
these aren’t really too much of a problem. So this isn’t always the
case and is only to be regarded as the “usual” case.
**Windows 2000 and Windows XP
There is a facility for all users to have their own Personal.xls and
that this indeed is the unwritten rule. That makes finding the
Personal.xls a little more difficult. It's perhaps useful to note two
easy ways to finding the "current" Personal.xls file are:
**Excel Approach
Open up Excel (if you can!)
Windows > Unhide
*Note* here that you might not have a Personal.xls file and if it's
not listed in the Unhide dialog you've establish this fact and can now
stop all further processes of trying to find it. Otherwise continue:
Select Personal.xls
The Personal.xls should be active workbook now and appear in the Title
Bar.
Then in A1 use:
=INFO("directory")
That should give the path to the Personal.xls for this session.
Copy and paste to Word or even write it down (remember pen and paper?)
Delete A1
Windows > Hide
Close Excel
If you get asked whether you want to save changes to the Personal.xls
just say, "No!"
That should give the path to the Personal.xls for this session.
Off to Explorer and follow the path you've found and move Personal.xls
out of the XLSTART folder pending establishing whether you need to
delete it. If you don't see it there, it's because it is a hidden file
and you will need to use the option of displaying hidden files. Tools
Folder Options (View Tab)
**Explorer Approach
Cutting out the use of Excel is another option that is really the best
(especially if Excel won't boot up <vbg>)
Start > Shut Down [I mean, how else would you stop but by
pressing the Start button?]
Click the drop down box of options
Note and remember who you can Log Off
Click Cancel
Right click the Start button to active Windows Explorer
Now you'll find the current user's Personal.xls in:
C:\Documents and Settings\*Who was logged on*\Application
Data\Microsoft\Excel\XLSTART
But note that the Personal.xls may be a hidden file and that it might
not exist if the current user has never created it. You might find the
drive letter is different from C: if you have a non-standard
installation.
I think that's pretty definitive as a guide but there may be other
ideas and suggestions.
***Opening Without the Personal.xls
The Personal.xls file can be the source of many problems. A quick way
to check this out is to open without that file. First we can
investigate whether problems *may* relate to the Personal.xls file.
Use:
Start > Run "C:\Program Files\Microsoft Office\Office\Excel.exe"
/Automation
[Quotes mandatory, But you may have to change the path depending on
where Excel is installed. You must have the space before the /]
That starts Excel without the Personal.xls or any Addins. So if
problems have gone away, then there *may* be a problem with the
Personal.xls file, other files opened on normal opening of Excel or
with an Add-In that is loading when Excel is started. If Excel still
has problems then all we can say is that it *may* relate to the
Personal.xls file and we need to check out that file.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
pen and