# Off Topic > Tips and Tutorials >  >  What's in Your Personal.xls?

## shg

After using =Char(Mid(A1, 15, 1)), =Char(Mid(A1, 16, 1)), =Char(Mid(A1, 17, 1)), ... a few hundred times in years past, I made a form and wrote some code to list the contents of a cell in binary. It's been handy.

I recently added support for Unicode, so it may not be fully cooked (or maybe _reheated_), and double-width Unicode characters are problematic.

In addition to adding the attached form to the workbook, this needs to go in a code module.



```
Please Login or Register  to view this content.
```

----------


## shg

I use these functions in lieu of the volatile constructs like this:

ROW(INDIRECT("1:" & A1))
COLUMN(INDIRECT(A1 & ":" & A2))

... like this:

RowArr(1, A1)
ColArr(A1, A2)

... with the added benefit of having a step variable

RowArr(1, 10, 2)

... and you can have negative numbers

RowArr(10, -5, -3)

Here 'tis.

----------


## shg

Just thought I'd share ...

----------


## shg

Ever wonder about how binary (IEEE-754) floating point numbers (the source of all those mysterious precision problems) are actually represented? The attachment has a family of functions for converting between floating point numbers and hex strings. I used a couple of these in responding to http://www.excelforum.com/excel-prog...h-queries.html.

Most work in either VBA or as worksheet functions. Here's the list:




```
Please Login or Register  to view this content.
```

----------


## StephenR

Ill play even though by comparison, I fear Im a bit lowbrow.

I have a number of macros which are specific to my job  organising, running checks on and analysing data.

I have a macro listing names of files in a folder, and one which extracts data from files in a folder. I have a couple of template-style macros which contain little bits of code which I tend to use more often than most (e.g. Find).

I also have a number of very short macros which Ive assigned to buttons on a toolbar. Theyre so simple but they save so much time. For example, vertically centring text, removing colours, turning off AutoFilter, centring across selection, filling selected cells with random numbers.

----------


## shg

My thought was that we could  use this thread as a repository of sorts, and vector people here when one of the routines might solve a problem.





> I have a macro listing names of files in a folder, and one which extracts data from files in a folder. I have a couple of template-style macros which contain little bits of code which I tend to use more often than most (e.g. Find).
> 
> I also have a number of very short macros which Ive assigned to buttons on a toolbar. Theyre so simple but they save so much time. For example, vertically centring text, removing colours, turning off AutoFilter, centring across selection, filling selected cells with random numbers.



Those sound great! How making a separate post for each?

So -- post 'em up, Stephen!

----------


## ChemistB

Mostly have a lot of little utility macros with shortcut keys if I use them often.
Here's one where I select all the cells in my workbook that I want to have a specific # of digits 


```
Please Login or Register  to view this content.
```

----------


## ChemistB

And here's one that Inserts a worksheet and lists all the formulas off the active sheet and what cells they are in


```
Please Login or Register  to view this content.
```

----------


## ChemistB

I use this one a lot before protecting my workbook.  It selects all unlocked cells.


```
Please Login or Register  to view this content.
```

----------


## NBVC

Mine are mostly specific to my workplace, like changing Csv files to a nicely Formatted BOM form.. or actually consolidating quantities in CSV files to BOM's, etc.. 

But here is a simple one that I am sure people have already access to, I use to delete blank rows...




```
Please Login or Register  to view this content.
```

----------


## DonkeyOte

I just wanted to say I don't use a Personal.xls... adding no value to the thread per se just thought I should point out how odd I am... and wondered if anyone else out there like me... I do have a libary of exported modules (.bas) which I import to files as and when but that's about it (along with some old .xla's)...

Interesting thread... cheers shg.

----------


## shg

You're in good company -- Andy Pope doesn't use one.

----------


## DonkeyOte

Maybe it's just because we're both "Essex Boys" ...  :Roll Eyes (Sarcastic): 
(I might live in Suffolk in the UK but I'm a born & bred Essex Boy... note to non-Brits - that's _not_ a good thing in terms of sterotypes...)

----------


## StephenR

I had a friend in Essex who was continually at pains to point out that he lived in the posh bit (which was virtually Suffolk).

I don't claim authorship of any of these except the really short ones, some of which could probably be done with keyboard shortcuts but originate from when I first discovered VBA and wanted to do everything with macros.



```
Please Login or Register  to view this content.
```





```
Please Login or Register  to view this content.
```





```
Please Login or Register  to view this content.
```





```
Please Login or Register  to view this content.
```

----------


## DonkeyOte

> I had a friend in Essex who was continually at pains to point out that he lived in the posh bit (which was virtually Suffolk).



Are we friends but don't know we're friends ???  Me too... 
(If he was indeed a he, and he continuously referred to his hometown as the oldest recorded town & former Roman Capital of Britain (Camulodunum) and former home to Damon Albarn of Blur then there's a good chance we know each other...)

Promise I won't darken the thread further unless I have something worthy to add.  Sorry.

----------


## sweep

A couple of functions that I find useful:

Range 



```
Please Login or Register  to view this content.
```


Multi cell concatenate



```
Please Login or Register  to view this content.
```


interrogate cell colour



```
Please Login or Register  to view this content.
```


interrogate cell formula



```
Please Login or Register  to view this content.
```


Upper and Lower control limits:



```
Please Login or Register  to view this content.
```


Note that these are just to "get by" and don't error trap (and probably aren't particularly well written)

Dave

----------


## StephenR

DonkeyOte: oh my God I can't believe it, you're ... well it is uncanny except that my friend would have had difficulty spelling Excel never mind penetrating the mysteries of VBA. So, Damon Albarn left Essex did he once he made some money...who'd have thought it.

Apologies to shg for derailing his thread.

Thanks sweep - what do you use UCL and LCL for?

----------


## sweep

> Thanks sweep - what do you use UCL and LCL for?



I used to use it all the time for control limits on XBar & R charts.  However, no I have Minitab, it's not used so often...

----------


## Jbentley

As I regulary use workbooks with many sheets, and often hide a number of sheets from time to time I find the below useful. 





```
Please Login or Register  to view this content.
```

----------


## Cheeky Charlie

I store the macros I've pinned to keyboard shortcuts in one module. I start them all ok (OnKey) so they're easy to discern/ignore in larger lists:
Assigning the macros to various application.onkey strings is done in my personal.xls workbook open event.




```
Please Login or Register  to view this content.
```


I find the last of these very useful.  Whilst working on a sheet, I can quickly colour cells needing special attention for any reason (I have pinned it to ctrl+g as I use Go To very rarely).  The cycle is very powerful, once for green, twice for purple, etc. is super quick.  Any "mixed" group is blanked first, then coloured (which can be quite helpful depending on which colour-happy mentalist sent you the sheet you're working on).

CC

----------


## pike

Always at hand for working with PowerPoint, Word and large Data



```
Please Login or Register  to view this content.
```

----------


## Ron Coderre

I utilize Named Ranges in many of my Excel apps. Occasionally, some bozo..um...I mean: an esteemed colleague will completely destroy key named ranges by deleting critical cells.

I've gotten into the habit of using Insert.Name.Paste...Paste List
to archive the definitions.

My PERSONAL.XLS has this macro to convert that listing back into Named Ranges:



```
Please Login or Register  to view this content.
```

----------


## martindwilson

ron i thought i could use that code to insert multiple tables but i keep getting error
any ideas?

----------


## Ron Coderre

> ron i thought i could use that code to insert multiple tables but i keep getting error
> any ideas?



Using your posted workbook I didn't have any trouble.
On Sheet2, where Col_A contains the "Names" and Col_B contains the "Refers To" expression.
Here's what I did:

1) Select A1:B101 
2) Tools.Macro.Macros....CreateRangesFromList....Click: Run

All of the range names were created.

Did you select the 2-col list before running the macro?

----------


## Cheeky Charlie

You should make the two column list a named range so you wouldn't need to bother with that first bit... oh wait...

----------


## pike

Thanks Ron 
     another keeper

----------


## martindwilson

cheers ron, guess who selected whole range a:b instead of a1:b101, oops
 :EEK!:  :Mad:

----------


## Cheeky Charlie

I wrote this recently, trying to decipher a really mixed up worksheet, with multiple different formulae at different points in columns & rows.
The comments show exactly how it works, but in summary, it returns true if the formulae of the two specified cells are equivalent (based on relative referencing, of course).




```
Please Login or Register  to view this content.
```

----------


## Domski

I love my Personal.xls, have all sorts of useful stuff in it that helps me with my job.

From the top...

- a bunch of subs that summarise/format SAP reports in different ways depending on what I want to do with them
- a batch pdf creator
- functions for counting/summing by font and colour. These aren't really for me but many people I work with have a habit of colouring cells/text and then realise they can't add the colours up.
- a toggle between manual and automatic calculation
- one that summarises consecutively dated records that SAP throws out
- one that compares worksheets and reports the differences
- a password breaker (don't tell)
- delete all named ranges
- file lister
- Chip Pearson's ImportBigTextFiles

I never realised there was quite so much in here...loads of other little functions and things that I've written or stolen over time really.

Dom

----------


## royUK

Just had time to read through this one. I don't have a Personal.xls, I prefer the mobility of an addin.

I also keep a large database of code snippets.

----------


## Domski

I agree add-ins are probably better and I use those when I've written something to share with others but if it's just for me I use my personal.xls and keep a copy in my Google Docs so I can download it wherever I want.

Dom

----------


## Cheeky Charlie

> - a toggle between manual and automatic calculation



I have this, it's called 'dumbass mode button', when I click it my statusbar shows 'warning, dumbas mode enabled'

----------


## Cheeky Charlie

Dom, I'd really appreciate a look at your compare spreadsheets code, if you don't mind?

----------


## ChemistB

Charlie, I liked your Compare function.  It will definitely come in handy when validating huge spreadsheets with complex formulas.  Thanks!

----------


## Domski

You're welcome to it although it's probably not quite what you're thinking of from my post. It's just some code that I use to compare the output of SAP reports that we've run pre and post applying patches to see if they've had any effect on the output of the reports.

It highlights the differences and sticks hyperlinks to the first 255 in the first couple of rows of the second worksheet (I work on the fact that if there's more than 255 differences we've got real problems  :Smilie:  )

I wrote this quite a long time ago so it could be greatly improved.




```
Please Login or Register  to view this content.
```



Dom

----------


## Cheeky Charlie

Normally I can't be bothered with tidying up functional code - even it could benefit from it, but this:




> ```
> Please Login or Register  to view this content.
> ```



Made me write this, general tool for picking either any worksheet in the active workbook, or any open workbook - something I've written individual versions of too many times... not any more!




```
Please Login or Register  to view this content.
```


Supported (obviously?) by a very simple userform with one label, one combobox and two buttons, one (cancel) clears the combobox entry before hiding, (ok) just hides the userform.

If Picker returns "" then consider it a cancel in either case...

Not sure it was worth the endeavour, but I needed a break, work's killing me today!

----------


## Domski

FWIW I put together an example of this working for this post...

http://www.excelforum.com/excel-prog...o-compare.html

Dom

----------


## snb

I couldn't resist...




```
Please Login or Register  to view this content.
```

----------


## royUK

Here's another alternative

----------


## Cheeky Charlie

> I couldn't resist...



:D Know the feeling

I like the use of choose (all of them), but I'm not a fan of iif. (spit).

Why add the elements to a string then split the string into the box rather than add the items straight into the box?  (I don't mean to make any judgement either way, just curious).

----------


## snb

> Why add the elements to a string then split the string into the box rather than add the items straight into the box? (I don't mean to make any judgement either way, just curious).



with .List you don't need 'clear'
.additem is noticebly slow
.list is designed for assigning multiple items to a listbox/combobox at a time

you could also use an array, but I like the simplicity of split (no need to dim, redim, redimpreserve etc.)

----------


## alansidman

Here's one that I have passed on to many people.  




```
Please Login or Register  to view this content.
```

----------


## ChemistB

I went back to this thread because I've lost some personal macro books along the way but it appears the first part of this thread is beyond viewing.  Is there something in setup where I can view older threads?

----------


## Tony Valko

I'm a mouse person so I have a bunch of macros that I attached to buttons to do things so that I don't have to use the keyboard. A few of them are:

Escape (kills the marching ants!) Yeah, I know, all I have to do is press the Escape key but like I said, I'd rather use the mouse!

Goto cell A1

Insert random dates in the selected cells

Insert random numbers in the selected cells

Remove conditional formats, data validation, convert formulas to constants

Charles Williams' code for doing calculation timings

David McRitchie's TrimAll

One of my favorites is code from Dave Peterson to show the full file path in the title bar. This one goes in the ThisWorkbook module.

----------


## shg

> I went back to this thread because I've lost some personal macro books along the way but it appears the first part of this thread is beyond viewing.  Is there something in setup where I can view older threads?



I had no problem, CB. Does http://www.excelforum.com/tips-and-t...nal-xls-2.html work for you?

If not, try increasing the number of posts per page in your preferences.

----------


## ChemistB

Is the first one "List Cell Text in Binary"?

----------


## shg

Yup             .

----------


## FDibbins

This thread title reminds me of the Capital One commercials...whats in your wallet?  lol

----------


## Tony Valko

How can I forget to give more props to Marcelo Branco for his great work in coming up with the "make a table" macro? I love being able to do this with just 4 clicks:

Data Range

*A*
*B*

*1*
Date
Value

*2*
2/26/2001
49

*3*
4/26/2001
97

*4*
8/19/2004
57

*5*
3/13/2006
83

*6*
10/11/2008
57

*7*
1/1/2010
80

*8*
3/25/2012
31

*9*
4/24/2014
39

*10*
4/29/2014
97

*11*
7/30/2014
17




2 clicks to fill the data and 2 clicks to generate the table.  :Cool:

----------


## ChemistB

Will you share the code with us, Tony?

----------


## Tony Valko

It's from this thread...

http://www.excelforum.com/suggestion...-and-data.html

----------


## Gregor y

For when copy'n'paste from Access gives you cells that have numbers with a cell format of general yet still come across as text. (You can see what I mean by: 1. format empty cell as Text, 2. enter value 1 into it, 3. format it back as General. Now the 1 stays 'Text' until you edit the contents of the cell... and 'touching' a bunch of these cells manually is no fun.)




```
Please Login or Register  to view this content.
```


to convert copy/paste values from old style pivot table into an actual data table



```
Please Login or Register  to view this content.
```


a sub to clean up pasted text and a function to check it via =A1<>ScrubText(A1)



```
Please Login or Register  to view this content.
```


and one to see what text got Scrubbed



```
Please Login or Register  to view this content.
```

----------


## Gregor y

I think this one at least in part is not mine, but I can't remember where it came from. So if I'm stepping on your code toes, I'm sorry but thank you because its been very helpful while checking macro run times.




```
Please Login or Register  to view this content.
```

----------


## MrShorty

It seems that I almost never used any of the "built in" number formats in the format cells dialog. At one point, I just got tired of navigating to the format cells dialog and finding the "custom" entry field (and since 2007 hitting the "add custom" button). I created a very short, simple routine attached to a toolbar button that pulls up a simple text input box where I can input my number format code and it will apply that code to the selection.

----------

