Point and Click

I mentioned in my first post that I tend to use keyboard shortcuts as much as possible, but there are times when clicking isn't so bad. In my Excel workbook I've added two new ribbon menus, each of which link to a bunch of macros that I've written. You can see below there are a lot of custom commands that I find useful. Some of these I use daily and some just every now and then, but most of them allow me to just point-and-click to get what I want rather than point-and-click-and-scroll-and-click-and-browse-and-click-etc...

Let's go through these as a preview for some of the code I'll be posting later.

• Hex/RGB commands: These four commands will pop open a window so I can convert between Hex and RGB and Decimal colors. I find the Hex commands useful when I'm coordinating with a website and need to go from the Hex from HTML to RGB of Excel, or vice versa. And the decimal conversions are useful in coordinating with some statistics software and other programs.
• Cell Height/Width: Don't you hate trying to line up your cursor with the edge of a cell so you can drag it to a new size? Oops, then it's too big and you have to drag it back? Just me? If you want to make it perfectly align you can always double-click, but I tend to prefer nicely spaced sizes (usually all the same size or multiple widths of each other). So these buttons will rotate through 10 evenly spaced sizes. My current default uses Excel's default of 8.43 and rotates through (8.43 / 2) * i, where i = 1/10. The more useful tool for me is the row height. If you have a merged cell with word wrap, then double-clicking the cell height won't automatically resize the cell to see the rest of the words. So I do the same technique for row height sizes, where I loop through the height of 1/10 lines. I use these two functions often enough that I have linked them to Ctrl-Shift-R and Ctrl-Shift-C for row and column heights.
• Define Arrays: Array names are very nice when coding VBA. They allow you to use a name in your code and if you need to change the array, you just change the name reference and your code still works (and you don't have to search for the 37 places that you've referenced those cells). When I set up reference arrays in my workbooks (typically on a hidden or very hidden sheet so the user can't overwrite the values) I use row 1 to define the array name and place the array below. All of the arrays are next to each other (possibly in a few groups for organizational purposes). The Define Arrays command will then loop from left to write and define all of the array names and ranges automatically. If any arrays get longer or shorter and I need to re-define them, it's just a matter of clicking the button again. Much easier than using relative references in the array definition to call a cell that has the length number blah blah blah, but I do that sometimes as well.
• Top Left Visible: This is just a quick way to place the current active cell on a sheet into the top-left visible position on the sheet. It's just like using the scroll bars to move the visible portion of the sheet, but automated and exact, just the way the world should be!
• Check Column Widths and Row Heights: Okay, so I go a bit overboard sometimes with the way an Excel sheet looks. I like good color schemes, I like orderly, and for final products I try as often as I can for the workbook to look and feel like a good website. So the check column widths and row heights commands will highlight the columns and rows in a sheet that are not multiples of the default column width and height (see Cell Height/Width above).
• A1 All Sheets: Probably one of my most used buttons. Again, I like order. So this will loop through all the sheets and place the cursor and visible area to cell A1. Before sending a workbook to a client this is one of the last steps I do so they receive the document as I intended.
• Last Cell All Sheets: This is more of a QC step for me. Often you may be working in Excel off to the side in a sheet and then delete that content. But if you changed font or colors or any number of other properties that cell will now be in memory for a workbook. This will add just a little more to the filesize, but more importantly this may cause printing issues where a document will generate a lot of extra blank pages (c'mon, I know you've been there!). The Last Cell All Sheets command will find the last row/column in each sheet and will put the cursor there. That way you can use Ctrl-PageUp/Down to loop through the sheets and make sure there isn't any extra space. Once verified you can use the A1 All Sheets before saving the document.
• Cell Size All Sheets: Want to revert all cells in all sheets to the standard cell height/width? Just a quick click of this button will do it!
• Zoom All Sheets: As the name says, it'll set the zoom level on all the sheets. The user is prompted for the desired zoom level and has a default of 100%.
• Sort Sheet Names: I often work with workbooks that have more than 100 sheets, usually from a data dump somewhere, or I've written a macro to load in 100's of csv files into a single workbook (more on Stata and R later...). I find it useful for organizational purposes to sort the sheet names. This command is set up to sort by number if all sheet names are numeric, or sort by ASCII otherwise.
• Unhide All Sheets: Will unhide all sheets :)
• First/Previous/Next/Last Sheet All WB: If you're comparing documents side-by-side, particularly if it's one of those monster workbooks, this command lets you navigate the workbooks together. I even have the previous/next commands set up as keyboard shortcuts, Ctrl-Shift-P and Ctrl-Shift-N.
• Linked Workbooks: Do you ever get an error when you open someone else's workbook that says a file link can't be found? Always? Okay, then this will help. It'll look through the areas that usually have links and display a pop-up with what is linked. You'll have to then manually go into those places to remove the links, but at least you know what Excel is trying to access. I might automate the deletion of those links at some point.
• Save All Workbooks: Just another quick automation task; if you're working on multiple workbooks at a time, this will save them all. Useful if you're like me and save often enough that your hands do it without even thinking now. Shortcut is Ctrl-Shift-S to make the no-thinking-save even more automated.
• Close All Workbooks: Does just that. You're left with the Excel window open, but no workbooks (well, no visible workbooks at least -- I'll get to personal.xlsb in my next post).
• Kill All Styles: Ugh, so have you ever worked on a workbook that has touched at least 50 other computers before yours? The style list gets monstrous as everyone's custom styles get added to the workbook one at a time. Not sure where that workbook has been, but I've found most of the added styles are usually just duplicates of the default styles, or at the very least they're not currently being used anywhere. This command is a quick way to get back to the default Excel style list.
• Remove Named Ranges: Named ranges is one place where the links to external workbooks can hide. Click this button to remove all the named ranges if you don't think any of them are useful. You can go to Formulas/Name Manager to view details about the named ranges.

First

I read a lot of blogs and enjoy the wide variety of content: keeping up with family/friends, new gadgets, coding, building science, geeking out, etc. I've always thought about starting my own blog as mostly a way to write more, but a lack of content/focus and knowing myself well enough that I wouldn't be able to stick with blogging long enough if I had a short lapse of interest has kept me from embarking on the quest. But I was thinking about it more today that between my short VBA scripts, gadget testing, home automation, and even the occasional race report, I could probably put something together.

Just a quick blurb about me before I begin, I'm a data analyst working on energy efficiency in buildings, and I run. "Data analyst" isn't the most in vogue term, so data scientist, research analyst, statistical programmer, and the list can go on; use what you'd like. But "runner" is a pretty safe secondary title. I spend much of my time in Stata or VBA with a very small, but slowly growing, use of R. I've touched a number of other languages that I could probably remember if I cared enough to write them down, but at this point I'm just thinking of topics that I may write about. Python will likely appear with growing frequency over time.

A lot of my early posts will probably involve going through my personal.xlsb macro list -- a suite of tools I use frequently in Excel to make my life easy. I've even added a couple custom ribbon bar menus to add point-and-click ease to a lot of the macros, and the most used ones get a custom keyboard shortcut (I shy away from the mouse whenever possible).

So that's it for now. I'm not sure how many posts I'll do per week/month/whatever and it probably won't be a regular frequency anyway, but I'm hoping I can share a lot of code, tips/tricks, races, and whatever else might come up.