# Who uses Personal.xls?



## Colin Legg (Jul 22, 2010)

I'd thought it would be interesting to get an idea about the proportion of MrExcel members who use Personal.xls. 

I've never used Personal.xls.... perhaps I'm missing out?


----------



## Richard Schollar (Jul 22, 2010)

Yes, although I don't actually use it to store code I use everyday, I use it to store code that I think looks cool and want to keep a record of.


----------



## RoryA (Jul 22, 2010)

I use it as a sort of code library for the kinds of stuff I post in forums (i.e. rubbish).


----------



## Richard Schollar (Jul 22, 2010)

Colin, since we've taken the effort to reply to your poll, how about you buy me & Rory a couple of rounds of beers?  Seems only fair...


----------



## Colin Legg (Jul 22, 2010)

I would say yes, but my concern is that my acceptance would extend to everyone else (hopefully many) who votes!


----------



## Jon von der Heyden (Jul 22, 2010)

I voted so I think I should get a beer too 

I don't use it, only because I have gone to the effort of building an addin as a library of my favourite routines / functions...  I used to use it before that.


----------



## Colin Legg (Jul 22, 2010)

lol... I'm really struggling today... I meant to post this in the lounge!

Unless there are any objections, please can a moderator do the honours (and delete this post)?


----------



## Peter_SSs (Jul 22, 2010)

Yes, I have used it a fair bit with excel 2003 and earlier with a number of commonly used nacros attached to custom toolbar buttons. Have not done so as much (yet, at least) with Excel 2007, mainly because the toolbar button customisation is not so simple and I get confused by all those identical green buttons.  



Colin Legg said:


> I would say yes, but my concern is that my acceptance would extend to everyone else (hopefully many) who votes!


I hope you are delivering and that I don't have to collect!


----------



## Peter_SSs (Jul 22, 2010)

Colin Legg said:


> lol... I'm really struggling today... I meant to post this in the lounge!
> 
> Unless there are any objections, please can a moderator do the honours (and delete this post)?


What about I just move the whole thread to The Lounge?

Edit: Hmmm, it is is The Lounge! Was it already there when I asked?


----------



## Jon von der Heyden (Jul 22, 2010)

I have already 



> Edit: Hmmm, it is is the Lounge! was it already there when I asked?


Yep


----------



## VoG (Jul 22, 2010)

I don't - I store my code online (at MrExcel.com) 

A G&T would be nice, Colin.


----------



## SuperFerret (Jul 22, 2010)

I use it every day (for the ones I have to press at least 10 times a day) 

I've also set some up for colleagues who don't know the first thing about VBA but like what it can do. 

Seeing as I posted do I get a drink too? I think I need one after the week I've had so far!



> toolbar button customisation is not so simple and I get confused by all those identical green buttons.


 
I've changed mine so they look different, even thought I haven't got round to making more sensible icons. 

I currently have a Cloud (Reports Macro), a Piggie bank (Save to Import Macro) and a Mouse (Save for creations Macro) on my toolbar.


----------



## RoryA (Jul 22, 2010)

Guinness for me. If *everyone* is coming, then I'll get the 2,000th round in.


----------



## Joe4 (Jul 22, 2010)

I use it a lot, and have set-up many of the users around the office with it also, as it helps with a lot of repetitive formatting tasks.  Some of things I have it are procedures to:

- Format Social Security Numbers (remove dashes, add leading zeroes if necessary)
- Format Zip Codes
- Format data copied and pasted from Access
- Remove unwanted "special" characters by ASCII code
- Combine a long list of entries in mutliple cells into one string with a delimiter and text qualifier specified at run-time (useful for creating an email list, or a criteria list to use in SQL programming)

As you can tell, we work with a lot of data.  It saves me a ton of time and our users love it and can't live without it!


----------



## Colin Legg (Jul 22, 2010)

That's really interesting, Joe. I use add-ins. 

If you wanted to update one of the procedures, what's the best way to get that update into each user's personal.xls?


----------



## RoryA (Jul 22, 2010)

I'd do that as an add-in, myself. To my mind Personal.xls is, well, _personal_. Anything for distribution gets made into an add-in.


----------



## TinaP (Jul 22, 2010)

I use it all the time, mostly as a code library.


----------



## Joe4 (Jul 22, 2010)

Colin and Rory,

Both very good points.  The truth of the matter is I created these procedures years ago, and very rarely do we ever update them (we might add a new one once a year for a specific group of people).  It is kind of a nuisnace when someone gets a new computer and I need to add it though.

Why don't I use Add-Ins?
This truly started out as a personal macro workbook just for me.  Then others saw the benefit of it, so I gave it to a few people.  Before I knew it, I was giving it to everyone.

And I am embarrassed to say, I never really took the time to learn how to convert it to an Add-In (actually, I have never created an Add-In).  

I suppose I should learn how to do that and how to distribute them some day.  If anyone knows of a good write-up on the web on Add-Ins, please pass it along, and I will check it out in my spare time!


----------



## RoryA (Jul 22, 2010)

Sure do - JKP has a great article here.


----------



## Jon von der Heyden (Jul 22, 2010)

Colin Legg said:


> ...what's the best way to get that update into each user's personal.xls?



Not that I've ever done this but I suppose the workbook could site on a shared directory..  But that makes it not so _personal_ anymore.

One thing I'm working on at the moment is an addin that needs to go on quite a wide distribution.  The _plan_ is to include a routine that looks at the addin version # and see if it is the same version # as the master on a specific directory.  If a newer version exists (on a per module basis actually), then it removes the old one and imports the new one.  I've not done this before but I know Mikey B has had some success with a similar process before...

I suppose similar can be done on Personal.xls...

Personally I agree with Rory.  If it needs distributing then it should be ackaged as an addin...


----------



## TinaP (Jul 22, 2010)

Jon von der Heyden said:


> One thing I'm working on at the moment is an addin that needs to go on quite a wide distribution. The _plan_ is to include a routine that looks at the addin version # and see if it is the same version # as the master on a specific directory. If a newer version exists (on a per module basis actually), then it removes the old one and imports the new one. I've not done this before but I know Mikey B has had some success with a similar process before...


 
I'd be very interested in that.  I hope you plan to post your code here.


----------



## SuperFerret (Jul 22, 2010)

Joe4 said:


> If anyone knows of a good write-up on the web on Add-Ins, please pass it along, and I will check it out in my spare time!


 
I should probably learn as well, my story is much the same, but probably with more sub standard macro's than any of you guys! 

I just know when I'm relieved of everyone elses jobs (when people come back from maternity) I'll be asked to set them up the big 3. 
Hopefully by that time I'll have enhanced my VBA skills and have learnt how to make them into an add-in!


----------



## RoryA (Jul 22, 2010)

Jon,
Have you read this?


----------



## Colin Legg (Jul 22, 2010)

Hi Joe,

Add-ins (I mean .xla / .xlam in this context) are not without their own set of problems.

When an add-in is shared amongst users there are two important tricks in relation to updating it:

(1) Making sure all users are using the correct, shared version (when installed it is not copied to their local drive)
(2) Making sure all users have read-only access


Ken Puls wrote a really good article here:
http://www.excelguru.ca/node/45


----------



## Colin Legg (Jul 22, 2010)

rorya said:


> Jon,
> Have you read this?


 
Oh man... same link as mine! Now it looks like I copied you.


----------



## RoryA (Jul 22, 2010)

Welcome to Richard's club.


----------



## Domski (Jul 22, 2010)

I use personal.xls for functions and code that pretty much only I will or should use.

I use add-ins for distributing things to other people generally although I have got a couple I've thought worth creating for my own use.

Pint of G&T, a nice steak and a bottle of Chateauneuf du Pape for this stellar contribution please 

Dom


----------



## Richard Schollar (Jul 22, 2010)

Welcome to the Plagiarist Crew!  Colin you are the third member after me and Andrew Poulsom!  You now have to buy me a further round of beer!


----------



## Joe4 (Jul 22, 2010)

Thanks for the links on those.  Learning how to create Add-Ins is one of those things that has been on my mental "to do" list for about five years now.  No excuses for me anymore!  

Just have to find the time to do it now (still trying to catch-up at work after last week's vacaction!).


----------



## Domski (Jul 22, 2010)

A recent post along these lines: http://www.mrexcel.com/forum/showthread.php?t=482674

Not sure about the library.xls idea.

Dom


----------



## barry houdini (Jul 22, 2010)

I probably shouldn't admit this......

I use personal.xls a little like Joe describes. All users in my office have a copy. Each macro therein simply calls a routine which is centrally stored, so if I need to change the code I can do it once only. Of course if I want to add new procedures I need to change each users personal.xls.....but that doesn't happen very often.

It didn't work that way before last year but that was the only way I could make it happen with 2007. Never created an add-in......



SuperFerret said:


> .....a Piggie bank........on my toolbar......


 
Piggy Bank is my favorite, everbody has one.....


----------



## Jon von der Heyden (Jul 22, 2010)

rorya said:


> Jon,
> Have you read this?



That's handy, pretty much what I had in mind!  Thanks Rory, Colin and Ken!


----------



## Domski (Jul 22, 2010)

barry houdini said:


> Piggy Bank is my favorite, everbody has one.....



Smiley face and 8 Ball are my buttons of choice


----------



## SuperFerret (Jul 22, 2010)

Domski said:


> Smiley face and 8 Ball are my buttons of choice


 
My boss wanted her Reports Macro to be a Smiley Face and another colleague wanted hers to be the Cloud because it's her boyfriends nickname... the things I do to keep people happy 

I'm waiting for a really good Macro to use the 8 ball...


----------



## Rekd (Jul 22, 2010)

Jon von der Heyden said:


> I voted so I think I should get a beer too
> 
> I don't use it, only because I have gone to the effort of building an addin as a library of my favourite routines / functions... I used to use it before that.


 
I'm a bit behind you. I still use it, but have been considering ways of moving away from it. Add-ins seem to be the best bet. I've got custom toolbars for my dozen most used macros, all with custom buttons. (I really really don't underdstand the logic in the stock ones... :twak: )

Oh, and I'll pass on the beer, thanks. Real men don't drink beer. 

They drink Vodka!


----------



## SuperFerret (Jul 22, 2010)

Rekd said:


> Oh, and I'll pass on the beer, thanks. Real men don't drink beer.
> 
> They drink Vodka!


 
Mmmmm vodka... Shame I have to wait hours til I can have some


----------



## RoryA (Jul 22, 2010)

My favourite icons are Mr. Potatohead, Jessica Alba (in a bikini), and a couple I can't mention.


----------



## SuperFerret (Jul 22, 2010)

rorya said:


> ...and a couple I can't mention.


 
Why not? 

Jessica Alba sounds like a good one, getting to push her buttons all day


----------



## WaterGypsy (Jul 22, 2010)

It was me ..... 

.... I'm the one who doesn't know what Personal.xls is ....

...... can anyone enlighten me please?


----------



## VoG (Jul 22, 2010)

WaterGypsy said:


> It was me .....
> 
> .... I'm the one who doesn't know what Personal.xls is ....
> 
> ...... can anyone enlighten me please?




This should help http://www.rondebruin.nl/personal.htm


----------



## RoryA (Jul 22, 2010)

SuperFerret said:


> Why not?



I think the board software would censor me.


----------



## SuperFerret (Jul 22, 2010)

rorya said:


> I think the board software would censor me.


 
Now I'm even more intrigued!


----------



## VoG (Jul 22, 2010)

Actually I do now since I had to test for this thread http://www.mrexcel.com/forum/showthread.php?t=483113

How do I change my vote  and does this qualify for another G&T


----------



## Cindy Ellis (Jul 22, 2010)

I use personal.xls (.xlsb now) to store macros that don't belong to a single workbook...pretty much a repository.  (Used to be for custom toolbars...oh well).
Also, a couple of times a year I need to summarize data from 1500 to 5000 machine data files as a set, with a fair amount of data in each (85 columns by ~2000 rows).  It's easiest for me to have my tried and true macros handy, rather than lost somewhere in a workbook that I've moved to some other folder, renamed, etc.  It also ensures that when I find something that needs to change, it's only in one place.  I don't have to go digging to figure out which version of which file had the right code in it.
I've created one or two add-ins for those macros that turned out to be useful for other folks.  I haven't had to update a distributed add-in yet...that's for next month.  I'll have to check out the links in the earlier posts.


----------



## xenou (Jul 22, 2010)

I created a Personal.xls for myself long ago, but I renamed it.  So I guess technically I do not use Personal.xls ... 

Keyboard shortcuts are in there, some menu items, various routines for dealing with imported data - stuff I do daily/weekly - sorting, stripping, cleaning.  Otherwise, I tend to create macro workbooks for specific tasks that may come up, but less frequently.  One button on my toolbar just for getting rid of wrapped text when exporting from Access (Joe4, saw you have one for Access too).  Coffee Mug.  I don't remember the piggy bank?  How did I miss it.  Can't bring myself to put a smiley face up there ...


----------



## WaterGypsy (Jul 23, 2010)

"This should help http://www.rondebruin.nl/personal.htm "

Brilliant Peter thanks


----------



## Fazza (Jul 23, 2010)

I've got some personal add-ins and use personal.xls too. A few handy basic routines - a standard re-format of data from a database (format the headers, left align text, whatever), set up footers & headers how I like them. There must be over a dozen. All instantly available via familiar CTRL-SHIFT-shortcut keys. A good boost to productivity - not that I need that. Things happen quickly!


----------



## Domski (Jul 23, 2010)

A few of my most used ones are for formatting and summarising standard SAP reports how I like them. I save myself hours every week using these little beasties.

I get all lost when I'm at someone else's PC frantically pressing Ctrl+Shift+? and nothing happens!!!

Dom


----------



## SydneyGeek (Jul 24, 2010)

I never really got in the habit of using Personal.xls -- probably because I move around so much it'd drive me crazy. 
I tend to push useful stuff into a workbook that I use as a code library, and use that instead. Also, if I need to process / update multiple workbooks I will write code in one master and update all workbooks from there. The code still gets to live in one place, it's just more portable. 

Denis


----------



## Smitty (Jul 25, 2010)

I use it, but more along the lines of why Dom does.  I have a bunch of formatting code to make report generation easier.  I also keep a few code snippets handy for more common board questions (like boilerplate change event code).  The rest I keep categorized in a file library of sorts (which I need to consolidate one of these years...


----------



## HalfAce (Jul 25, 2010)

Yep, pretty much like Smitty & Dom too.
Basically things like routines to enable events or reset command bars and such that may be left in an undesirable state when some code bugs out.

But other than that I don't use it much.


----------



## Darren Bartrup (Jul 29, 2010)

I did use it to store a list of postcodes in my county along with the districts and area's those postcodes are in working on addresses.

I thought it would be easier to direct people on how to update this list rather than a list in an add-in.

Turns out I couldn't trust any of them to do anything so had to write code to do it for them.


----------



## Rekd (Jul 29, 2010)

Delmar D'Percy said:


> I thought it would be easier to direct people on how to update this list rather than a list in an add-in.
> 
> Turns out I couldn't trust any of them to do anything so had to write code to do it for them.


 
This!


----------



## bertie (Aug 2, 2010)

I have never used it. In all honesty up until about six months ago I never knew personal.xls existed. It was only through browsing posts on this forum I became aware of it. Even then I thought it was new functionality with Excel 2007.

For code which can be categorised I organise in local html files for easy access. This allows me greater scope to comment how the code was built up. Other codes, which are based on workbook contents, are scattered all over the place. 

But in an organised kind of mess.


----------



## baitmaster (Aug 23, 2010)

Use personal.xls for all sorts of stuff, its built up over about 10 years now

Auditing tools, sheet protection / formatting, incrementing ranges of numbers, deleting ranges that meet specific criteria, applying conditional formatting, applying version control and saving, appending text to equations / ranges of data etc etc

Its easy to update an interface in Excel, by writing new tools into a drop down menu bar, rather than creating icons. This still works in XL07, but not as well as in 03 (its hidden away in the "add-ins" tab)


----------



## Johnny C (Sep 2, 2010)

Couldn't live without my personal.xlsb file! All sorts of stuff in there. Some out of the ark, like a macro to go down a sheet and change the colour of every other row so it look like the old stripy pyjama paper (for those of you who can remember that!). Some I use many times every day; custom number formats peculiar to accountants that MS don't seem to have considered (gettinbrackets on negative numbers so the positive numbers line up the digits).

Some are to launch userforms with code, for example a link manipulation userform that's the entire width of the screen so you can actually see what a link is. 

Some are just bits of amusing junk - the magnifying glass. One to open/close the CD (there are spreadsheets still being used in past copmpanies that open & close the CD based on key presses (worksheet_change events) with a random number generator so it's once per 1,000 cell changes on average)

Functions I put in an add-in personal.xla. Maybe I should merge the two files now into a single add-in, make life easier. Especially now your PC gets locked down so tightly in companies.


----------



## TheAverageBear (Sep 3, 2010)

I never used to, but at this job we cannot download outside software without it going through an entire testing and approval process.  So I've coded my own version of several of the functions that are supplied by ASAP Utilities that live in my Personal.xlsm file.  They are mostly simple things that work on the selected range and: convert to upper or lower case, trim leading & trailing spaces, delete all objects on a page, convert hyperlinks to text... and things like that.


----------



## gardnertoo (Sep 5, 2010)

I'm right there with you Dom: 





> I get all lost when I'm at someone else's PC frantically pressing Ctrl+Shift+? and nothing happens!!!



The one keyboard shortcut I use all the time, the first thing for which I decided to build a personal.xls, is dead-simple:

```
Sub Rubberstamp()
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
End Sub
```

I've also done a couple to take .csv outputs from specific websites and reformat them into proper workbooks with charts.


----------

