Useful Utilities

Last month we discussed buying commercial add-ins. I haven’t actually installed any commercial add-ins yet, but that’s not really a surprise.

A lot of people have their own personal add-in (or personal.xls) with home-grown macros. I assumed that I had only one macro in my personal.xls because I really only use one. Well, I was wrong. I have quite a few little macros in there. Some I’ve started and never finished. Some I created, but have since forgot. Here’s the list:

  • A user form where I paste a string into one textbox and it converts fancy double- and single-quotes to normal ones. Based on this
  • An unfinished userform that would allow me apply borders to cells without using the mouse.
  • Something in ThisWorkbook that looks like I thought I was pasting it into a standard module of a throw-away workbook. In other words, I don’t know what the heck it is, but I didn’t write it.
  • A macro to Paste Special > Values.
  • A macro to Autofill a data series.
  • A macro to create a new window. I remember this one – it used to be useful to me.
  • A macro to Toggle the Move After Enter option.
  • A few procedures dealing with extending the selection.
  • A macro to fix the VBE undocked window problem.
  • And the one I actually use: Uploading pictures via ftp

The one I actually use is special. Every time I use it, I marvel at how convenient it is for me. I don’t upload images everyday, but it’s often enough that I should be taking that code for granted, and yet I don’t. It’s such a huge time saver for me, that even though it takes several seconds to run, I spend those several seconds appreciating the futuristic world in which I live. A world where manually uploading images via an ftp client is a distant memory.

Do you have any macros in your personal.xls that make you feel like that?

Posted in Uncategorized

26 thoughts on “Useful Utilities

  1. I had/have one (it’s not there now but it soon could be). It’s Colos Custom Transposition, I used to get people to send me there SS and i would run the code on it send it back to them and have a bit of a feeling of smugness – needless to say it didn’t last too long!

    heady days indeed!

    http://puremis.net/excel/code/043.shtml

  2. Interesting Ross. I have a one like that I called Transposer. It allows me to do column-to-array and array-to-column transpositions (also works for rows). It lets me split the column or row based on count, value, or pattern matching; apply headings to the rows and columns; and optionally omit or insert blank cells.

    In my personal.xls, I also have…
    – a simple trim function that trims all the cells in the selection
    – a concatenate function that prompts for a delimiter (e.g. space or comma) and concatenates all the columns into a single column with each column value separated by the delimiter.
    – a subtotal extractor that takes a sheet with subtotals applied and extracts just the subtotal values to a new sheet.

  3. I have a number of work-specific (budget/forecast and the like) macros in my personal.xls, but also other code which allows me to:

    – unhide all hidden worksheets at once
    – hide multiple sheets
    – hack passwords on protected worksheets for those who can guarantee me that they created the workbook, but can’t remember their own password
    – making it easy to select from multiple printers with one click
    – export a chart to a GIF
    – one click font size increase or decrease
    – one click toggle gridlines
    – one click to format any pivot table numbers to comma no decimals style
    – one click to replace a specific code module with an updated version exported by someone else

    There’s a slew of others in there too. Generally these are just things that make working with Excel easier for me.

    One day I’ll make an add-in out of it all for myself.

  4. Strange…

    I have plenty of subs and functions in my personal add-in, not 2 or 3… I thought everybody here would too…
    let’s see…
    a function to display timer results (speed testing) in a friendly “1mn12s” format
    a macro to put bold outer border and normal inner borders on a selection
    a whole module for my code cleaner (to make other people’s code compliant with my company’s coding standards, things like name syntax, declarations, error handlers and so forth)
    a macro to pull data from closed sheets (using ADO objects)
    unhideallsheets (name says it all)
    crackandremoveall (cracks all passwords and removes all protections on a target workbook, in part using shareware utilities, still in testing)
    a function to extract dates from strings
    a macro to batch-rename files
    wrapper functions to get the name, fullname and path of a workbook (useful for sequential workbooks)
    a macro to find unique instances in a selection
    a macro to clear 0-values and/or error-values from a selection (optionally deleting the whole row or column as well), actually a whole module with associated user forms.
    a macro to round all values in a selection
    a macro to reduce file size by reducing the used range
    a macro to delete calling sheet name from multi-sheet formulas
    a RMB shortcut to make the selection into values
    a button that sends my boss a copy of the file I am working on
    an argument sub that extends formulas to neighbouring cells (makes the code easier to read to avoid endless copy-paste lines)
    some personalized statistics functions
    a class module for accessing and storing calculation and screen updating in one statement.

    I use most of them at least once a week, some of them several times a day…

    K

  5. There’s a lot of junk in my personal macro workbook.

    Good junk (*some can be found on my web site):
    *A macro that exports all selected charts on the active sheet onto the active slide in PowerPoint (I used to use this a dozen times a day in my old job).
    A macro that applies a nice grid, whether to a selected range or to the active chart.
    Paste Special – Formulas and Paste Special – Transpose – Values, which go with toolbar buttons (which 2007 won’t let me use).
    More buttons: increase and decrease active window zoom by 5% at a time.
    *A simple temperature conversion userform which I haven’t used since I left the engineering profession.
    *Code that builds a custom drawing toolbar (this is pretty helpful).
    A utility that exports a code module to Word and formats it (this one is very helpful, and I use it a lot).
    *A face IDs utility (very helpful: doesn’t everyone have their own?).
    *The utility that does find and replace in the chart series formula, plus *the one that switches X and Y in a chart.
    Various fill functions (linear and log fills, fill empty cells from above, clear cells if the same as above, etc.).
    A routine that lists named ranges and information about them (very useful).
    A routine I use to export and format ranges for web pages (very useful).
    A set of fullname to filename, path, and extension parsers.
    A hyperlink breaker.
    A quickie that resets screen updating, calculation, and enable events to default.
    A routine that imports a text or html file line by line without interpreting any tags.

    Junk I never use any more:
    Start the screen saver.
    Some crypto routines.
    An abortive add-in manager which only lists the installed addins.
    Numeral to text conversion.
    An interpolated VLookup function.
    A function that changes firstname-lastname to lastname-comma-firstname.
    A broken calendar/date picker.
    A print setup header and footer routine for what I used at a company I left six years ago.
    A routine that cycles text case like Word does.
    A macro that sorts sheets alphabetically.
    Leo Heuser’s routine that removes unused custom number formats.

  6. Now I feel bad – I dont even use a personal.xls.
    My code (and clever formulas etc) is scattered all over my laptop hard drive, assorted pen drives, my MP3 player, client pcs, my old pc…
    I generally start a new code workbook for each project and add to it as needed. If I need to re-use code I just need to remember which project. If I ever run out of things to do I might sort it all out into a code library.(yeah right!)
    All my projects end up with my environment settings code (calc, screen updating etc) and my general functions code (finding cells, extending ranges).
    For FTP I just copy from windows explorer to IE.
    I would estimate I have written the unhide all sheets routine 20-30 times now. Sadly that is faster than finding a previous version.
    I have a few COM add-ins loaded all the time, my favourite prevents that reviewing toolbar from poping up uninvited.
    cheers
    Simon

  7. A couple of my favorites
    – reset crashed settings – restores application settings screwed up when VBA modules crash
    – a “column resizer” form that I wrote years ago and still use from time to time
    – “save with backup” that I use in both Word and Excel that has saved my butt more than once
    – “turn formulas into comments” tool (and vice-versa) that uses the N function
    One thing I like about all of these is that I’m the only one who has to understand them, so it’s okay if they only work about 85% and need a little nudge to work sometimes. Getting them to work that last 15% of the time for somebody else would probably take me 5 to 10 times as long as I’ve spent on them.

  8. Jon “A face IDs utility (very helpful: doesn’t everyone have their own?)”

    I wrote one that resides on this very web site, and which at one point Rob Van Gelder called “the best FaceID tool I’ve seen so far.” I know he’s since written one that he likes a lot, but his praise really made my day. :)

  9. I don’t actually have access to personal.xls on my work laptop, so that one is blank (I have some junk on my home PC, this needs to be cleared down as there is nothing complete / really useful)…

    However I do have a folder full of useful codes snippets, example files, tips and tricks (doesn’t everyone?).
    I am slowly pulling the best ones together to make an add-in so this list will probably be refered to when I eventually get started!

  10. I’ve converted my personal macro workbook into an add-in, so I don’t get that read-only warning when I open a second instance of Excel. I’ve also renamed it, because a former employer’s IT police used to have a script that deleted personal.xls when the computer booted. I finally got them to change that policy, but just in case, I never changed back to the default file name.

    I used to use my personal macro workbook a lot, before I began writing programs for other people. I haven’t put anything new into it in a couple years, and I only occasionally make tweaks to it. But it contains my UI modifications (toolbars and all).

    Now I’m more likely to develop tools as part of a client project, so I keep it separate. This makes it easier to keep it modular, to make adjustments, and to find it and drop it in elsewhere without much fuss. In addition to a detailed directory structure of client work, I have a detailed directory structure containing utilities (both mine and downloaded), tips, examples from book CDs and web sites, etc.

    Like Simon, I use Windows Explorer and IE for http://FTP. It works so well natively, I can’t imagine writing my own code to do this. No offense, Dick. If that pesky Reviewing toolbar pops up, I click the X to close it, no sense writing code to handle that. No offense, Simon.

  11. Doug –

    Your face ID viewer is pretty nice. I like mine, because clicking on an icon will place a labeled copy into a worksheet, so I can compare several at my leisure. But I like your Favorites menu, the Copy From Toolbar option, and the scanner.

    The problem with mine is that it seems to take forever to update the floating toolbar with the icon images. I have to look into that.

  12. My favorite: I often have a bunch of monthly data in a column but I need to see a column of quarterly totals. So I make a formula for the first quarter’s total and then my macro copies it down as many quarters as I want. It’s a lot better than copying the formula every third row (or is it fourth row?) and then having to move them all together.

    I also have an unpivot macro that I used to use a lot but seem to rarely use it now. Something must have changed in the way I get data or put data out, but I can’t quite pinpoint it.

    Most of my macros are very old and are probably doable directly in my current Excel version, but I don’t bother changing them.

  13. Other than work specific macros I have a few that I don’t think I could live without:
    PasteSpecial Values

    Circular Formula

    Reverse 1D Array

    Set Sheet View (Sets Zoom to 100% and moves focus to A1 in every worksheet, Deselects and zooms to fit every Chart sheet)

    Pivot Table Refresher adds any extra data adjacent to the pivot table data to the source data and refreshes all pivot tables in the workbook. This is only meant for pivot tables built on data in their workbook.

    I wrote most of these as I was just starting to learn VBA and they desperately need cleaned up but they work for what I need them to.

  14. I have the following in personal.xls:

    a macro to Paste Special>Formulas
    a macro to display the next/previous open workbook
    macros to show the conditional formatting, data validation and define names dialogues
    a macro to show all sheets (including Very Hidden)
    a macro to hide the active sheet
    a macro to hide the selected sheets
    a macro to password protect/unprotect all sheets
    a macro to add sheets, using an input box for the user to type how many sheets they want
    a macro to sort sheets alphabetically
    a macro to toggle calculation between automatic/manual
    a macro to calculate the active sheet
    a macro to display Tools>Options>View (saves time when turning off row/column headers, sheet tabs, etc)
    a function to concatenate a range
    functions for VLOOKUPNTH and HLOOKUPNTH
    a function to list all formulae in a workbook

  15. Just like Jon I do have some code that used to be housed in personal.xls, which now in fact is in utilties.xls. This has my two custom toolbars toolbars attached to it, to make migrating from one PC to the other simple.

    All tools I use are in folders devided by clients and by tools/downloads. The ones I use frequently have a button on my custom toolbar that calls them up automatically. This way I only load what I need. So for example Charles’ and my Name Manager has its button there rather than having it installed.

    Quite frequently I know I should have a tool somewhere for what I want to do, but it simply takes me less time to write the VBA directly rather than finding the file that holds the code. Maybe I’m game for a code library thing. OTOH, I’d probably forget to add stuff to it anyway.

  16. There are a few macros that that I use all the time, but most are too specific to be useful outside the enivronment in which I’m working. However, one is pretty useful that I use all the time. It’s based on Peltier’s technique of copying chart pictures to powerpoint. In fact, it’s so useful, I included it as an example in “Excel and Access Integration” (http://www.datapigtechnologies.com/Books_eai2007.html)

    This code copies each worksheet in a workbook as a picture into a new powerpoint presentation. The idea here is that I can build a workbook in such a way that it mimics a PowerPoint presentation; the workbook is the presentation itself and each worksheet becomes a slide in the presentation. Once I do that, you can easily convert that workbook into an actual PowerPoint presentation using a bit of automation. This allows me to create presentations that I can update by simply refreshing the data in my workbook.

    Sub WorkbooktoPowerPoint()

    ‘Step 1: Declare your variables
    Dim pp As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide
    Dim xlwksht As Excel.Worksheet
    Dim MyRange As String
    Dim MyTitle As String

    ‘Step 2: Open PowerPoint, add a new presentation and make visible
    Set pp = New PowerPoint.Application
    Set PPPres = pp.Presentations.Add
    pp.Visible = True

    ‘Step 3: Set the range that includes your data and charts.
    ‘Ideally, each worksheet’s content is encapsulated in the same range.
    ‘You could also dynamically determine the used range, but I’m too lazy for that.
    MyRange = “A1:I27?

    ‘Step 4: Start the loop through each worksheet
    For Each xlwksht In ActiveWorkbook.Worksheets
    MyTitle = xlwksht.Range(“C19?).Value

    ‘Step 5: Copy the range as picture
    xlwksht.Range(MyRange).CopyPicture _
    Appearance:=xlScreen, Format:=xlPicture

    ‘Step 6: Count slides and add new slide as next available slide number
    SlideCount = PPPres.Slides.Count
    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly)
    PPSlide.Select

    ‘Step 7: Paste the picture and adjust its position
    PPSlide.Shapes.Paste.Select
    pp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    pp.ActiveWindow.Selection.ShapeRange.Top = 100

    ‘Step 8: Add the title to the slide then move to next worksheet
    PPSlide.Shapes.Title.TextFrame.TextRange.Text = MyTitle
    Next xlwksht

    ‘Step 9: Memory Cleanup
    pp.Activate
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set pp = Nothing

    End Sub

  17. Mike –

    I have a guy who wants his workbook dumped into PowerPoint, with a twist or two. He wants the sheets inserted as Excel worksheet objects. He also wants multipage worksheets to be split among multiple slides which means a lot of deleting rows above and below the rows which should be visible on the slide. He already has a function that exports the worksheets into an archival workbook in which all formulas and links have been converted to values. Some of the sheets are just not amenable to a PowerPoint view, but I haven’t been able to convince him otherwise. Even mentioning the hours it will take to code a sheet splitting algorithm have not deterred him.

  18. Falling in love with an ftp macro? There were ftp scripts back in DARPA Net times. Does the macro upload image files embedded in workbooks?

    I haven’t fallen in love with my macros, but I have 3 I use a lot: swap formulas and notes in each cell in the selected range, replace initial = with |=, replace initial |= with =. Another I use less often concatenates the contents (.Text) of all cells in the selected range into the active cell. Last one worth mentioning prints name definitions, cell formulas (R1C1), formatting strings and all VBA modules for entire active workbook, basically a text file dump of an XLS file. Now if I’d only get around to writing a macro that could create XLS files from the text dumps…

  19. I’ve got lots of goodies as well, many of which have been already mentioned above.
    My favories (that have not already been mentioned) are:
    – Buttons to toggle full commandbars off and on – like Audit, VBA and a couple personal compilations
    – Toggle Gridlines, Column & Row Headers and Outlines with one button
    – Set Outline levels to 1, 2, or 3 (etc) with one button
    – a version of Find Name In Range from Rob Bovey’s Excel Utilities which I adapted to allow selection of the found range
    – a Unique Filter macro which uses Advanced Filter to produce just the unique names in a list to an adjacent column
    – a macro to invoke the character map

  20. Oops – forgot to mention a modified version of Rob van Gelder’s Conditional Format Maager (posted on DDOE) which I adapted to do Data Validation Management as well. (See Dick, we DO use the posted code! :-))

  21. I have lots of tools in various xla files (no Personal.xls). Most often used, descending order:

    – Reset Excel (calculation automatic, mousepointer default, enable selection, statusbar false, …)
    – Fill selection with random text or letters or numbers or dates (userform ui)
    – Lock/unlock/hide/show selected sheets (userform ui)
    – Clean cell content in selection (remove spaces, replace formulas with values, remove non-numbers, upper/lower/proper, … userform ui)
    – Filter assistant; switch autofiler criteria based on active cell content (also/instead/all) with save and recall for filter settings. (userform ui)
    – Copy column widht / row height
    – Selection to html
    – Close and kill all unsaved workbooks (“Book14? not saved as anything)
    – Close activeworkbook and send to recycle bin if saved as file

  22. I have a set of growing utilities called MyTools…lots of small stuff collected/built over the years… a few very useful shortcut keys as well…

    Some of the buttons are
    – Format filter (filter on cell format)
    – Copy unique/Paste Unique
    – Higligth / Delete Duplicates
    – Text Tools – Toggle case/Proper Case/Text 2 Numbers/Numbers to Text/Trim All
    – Comment Tools – Send cell data to comments/Comment Data to Cell/Aling Comment
    – Graph Paper – Convert cells in to Square – like on a graph paper – say 0.5 cms
    – Extend to last row/column
    – Paste Excat formula
    – Dynami Range Tools – Dynamic Rows/Columns/Table + Dynamic Rows with Blanks/Dynamic Columns with Blanks
    – Toggle calulation mode
    – Merge to Cell
    – Swap Cells
    – Delete Blank rows/Colums
    – Insert/Delete Alternate Rows/Columsn
    – Batch Printing utilit
    – Delete Hyperlinks

    Some of the custom shortcut keys are
    – Ctrl+l – Toggle filter
    – Ctrl+j – Show all
    – Ctrl+q – List sheets
    – Ctrl+m – Make formulas in to values
    – Ctrl+t – Go to Top sheet in a file
    – Ctrl+e – Go to End sheet in a file
    – Ctrl+shift+l – Paste Exact Formula

  23. I got two macros in my add-in, and they both are hooked up to toolbar buttons and hotkeys:

    One applies word wrap. (I still can’t believe this is not a canned selection when customizing toolbars or keyboard shortcuts)
    The other brings up a calendar form so that you can select a date (ever since I saw this kind of user interface on a travel website I NEEDED to have it everywhere. I think I process dates with the creative/image side of my brain.

  24. I often need to copy a cell formula and paste it into another cell exactly as it appears in the initial cell without absolute referencing. I have two small pieces of vba that copy the cell contents and them paste it into another cell – very handy.

  25. Oh, I have piles of macros, too. Most of the useful ones are invoked from a modeless userform that opens from a toolbar button with a shrunk down photo of my face as the icon (personal.xls, get it?)

    The one I use the most shows a checked listbox of the sheets in the active workbook. I can hide/unhide or protect/unprotect all or some of the sheets in one fell swoop. I absolutely HATE that you can only unhide one sheet at a time in the absurdly-located Format/sheet/unhide… dialog.

    Other stuff I didn’t see mentioned:

    Eliminate #DIV/0! by changing simple formulas in the selection of the form “=a/b” to “=if(b,a/b,0)”
    Also do similar to above for complex formulas using ISERROR().
    Reformat all comments in selected cells to Ariel 10 point normal.
    A find and replace for changing names of shapes on the current sheet.
    Reset the cell pointed to by ActiveCell.SpecialCells(xlLastCell) by using ActiveSheet.UsedRange

    I also have toolbar buttons to:

    Toggle between A1 and R1C1 styles (I use this all the time).
    Invoke the Insert/Name/Define dialog.

    Personal.XLS also builds and supports the right-click context menu to which I’ve added:

    Copy Reference – if the selected cell(s) is a named range like “MyTotal”, put the string “Range(“MyTotal”)” on the clipboard so I can paste it into my code. I love this! It works for sheet-local names as well, and (to my great shame) unnamed ranges.
    I also have a couple that copy cells without the absolute referencing like Stuart above, but it works with an arbitrarily large selection of cells.

    Dave


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *