What's in Your Personal.xls?

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.

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.

Just thought I'd share ...

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.

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!

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.

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.

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

Please Login or Register  to view this content.

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.

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.

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

Maybe it's just because we're both "Essex Boys" ...
(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...)

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.

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

Please Login or Register  to view this content.

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.

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

Originally Posted by martindwilson

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?

Thanks Ron
another keeper

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

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

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.

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

- 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'

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

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

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 )

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

Please Login or Register  to view this content.

Dom

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!

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

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

Dom

Here's another alternative

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).

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'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.

Originally Posted by 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?

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.

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

Yup .

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

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.

Will you share the code with us, Tony?

It's from this thread...

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

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.

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.