International Keyboard Shortcut Day 2018: The Ultimate Excel 2016 keyboard shortcut list

To download the list, click the button below. Please feel free to share it, but please do not remove the attribution. Read on for an explanation of how it all works.


Yeah, I know, I’m a little late. Not late for IKSD, but late for posting an Excel 2016 keyboard shortcut list. Come on, it’s 2018 already, and Excel 2019 has already been foisted upon the masses, which just again shows how marketing screws everything up–the same way retail stores are putting up their Christmas decorations at the same time they are taking down their halloween decorations (i.e., right about now), way before most of us are even thinking of Christmas, but rather just trying to get through the inevitable pile-up of work that accumulates at the end of each year.

But I digress. I think this list will still be fine for Excel 2019/Excel 365, and to commemorate International Keyboard Shortcut Day, I am sharing this on my blog. I made this list for a book I was writing on Excel (which has stalled a bit, due to my life circumstances changing), and I modified it considerably in prepation for a presentation I did for Danielle Stein Fairhurst’s Financial Modelling in Excel Meetup group in Sydney.

The ultimate Excel keyboard shortcut list

Now, let me defend my claim. Just do a search for “ultimate Excel keyboard shortcut list” and you will see that there are many claimants to that title, but I am going to be even more brash, and claim to have the ultimate, ultimate list.

Why do I say this?

  1. My list is the only list which is provided as an Excel workbook. Go figure. Imagine creating a list of Excel keyboard shortcuts, and not doing it in Excel? In fact, why do any list of keyboard shortcuts and not do it in Excel? Show some dedication to the cause! Excel is great for lists like this, because it provides us with a searchable, sortable, filterable list.
  2. My list is the most complete list I could find. Really. I challenge you to find more Excel keyboard shortcuts in one location than in my workbook.
  3. My list covers both Windows and Mac. I am not a routine Mac user, but I took a Macbook and worked through every keyboard shortcut there. I am aware that there might be some discrepancies between a desktop Mac and a Macbook, and I confess I did not dig into these, if there is one shortcoming.
  4. The extra information I provide on the keyboard shortcuts. See below.

How the list works

The worksheet has 11 columns, and it is the combination of information across these columns that I consider to be so valuable:

  • Access
  • Key
  • Keyboard shortcut Windows
  • Keyboard shortcut Mac
  • Function
  • Group
  • Subgroup
  • Scope
  • Contextual variant?
  • Toggle
  • Comment

Here is a short explanation of what the purpose of each of the columns is, in a slight re-ordering of their position on the worksheet.

Keyboard shortcut Windows & Mac

These two columns (C and D) contain the actual keyboard shortcuts. It might be worth noting that, for the Windows shortcuts, where keys on a standard keyboard layout contain two symbols that are governed by the Shift key (e.g., the number keys, or ” ‘), I have used the symbol which will be entered if that key is entered with/without the Shift key, depending on whether Shift is used in the shortcut (see Access below). This is evident even with keyboard shortcuts using letters, for example, Ctrl+a and Ctrl+Shift+A.

Access & Key

These two columns help with sorting and filtering. Essentially, most keyboard shortcuts (with the exception of function keys) consist of a normal key on the keyboard (which is listed in column B: Key), and a combination of one, two or three “access” keys (which are listed in column A: Access).
While these columns may seem to be superfluous in that this information is already in column C: Keyboard shortcut, I have elaborated a bit by, for example, listing both symbols found on a single key on a standard Windows keyboard layout. These columns are also useful in that it makes it slightly easier to see, for example, which keyboard shortcuts use a certain access key (granted, Excel’s filter allows that to be done on column C as well), or which keyboard shortcuts make use of a certain key (a little harder, but not impossible, to do with the filter on column C).

Function

This column describes the actual action performed by the keyboard shortcut, or, if it does different things in different contexts, then it elaborates those differences. Here, again, I believe my list has a lot more detail than other lists out there.

Group & Subgroup

Some keyboard shortcut lists are too rigid: Ctrl+A does this, Ctrl+B does that, Ctrl+C…. The better lists organise the keyboard shortcuts into various themes, and Group and Subgroup in columns F:G are my themes. I have 13 top level groups in column F, which are subdivided into 39 second level groups in column G (although 7 of the top level groups are not subdivided, and count as 1 each in that list of 39). To see my breakdown, go to the Lists worksheet. I have used some elementary conditional formatting (horizontal borders) to indicate the different groups (solid border) and subgroups (dotted border).

Scope

Keyboard shortcuts often work in a variety of contexts. It is useful to know this, because if you know that a keyboard shortcut works in more contexts than only the program where you learnt it, then you could be adventurous and try it in other programs, and so extend the time-saving benefit of that keyboard shortcut. I try to indicate this here using one of three different scopes:

  • Excel: This keyboard shortcut works like this only in Microsoft Excel (e.g., Ctrl+E for Flash Fill)
  • Office system: This keyboard shortcut works like this for all (or most) of the Microsoft Office programs (e.g., Ctrl+H for Replace, which is Ctrl+R in many other programs)
  • System: This keyboard shortcut works like this for most programs (e.g., Ctrl+C for Copy).

Contextual variant?

The aim of this column is to indicate whether the same shortcut does different things when used in different contexts. I have simplified things by using a formula to count whether the shortcut appears more than once in the list (which is indicative of such variations), but there are some exceptions, where I have entered the contextual variations all in one row. These are set manually, and indicated using some conditional formatting.

Toggle

Some keyboard shortcuts work like switches: Do it once, and it turns a feature on. Do it again, and the feature is turned off (e.g., Ctrl+B for Bold formatting). This is indicated in this column (column J).

Comment

As if all of the above was not enough, in this last column (column K), I provide some additional insights for a few keyboard shortcuts.

Duplicate shortcuts

There is no column for this, but some shortcuts are indicated in grey formatting across the row. Where you encounter this it will be on the second (or third) duplicate shortcut. I have sorted the list in a specific way, so that the shortcuts I commonly use for a task are listed first, and where more than one shortcut exists for a specific task, the preferred shortcut (IMHO) is listed first, and not in grey, and subsequent shortcuts that do the same thing are shown in grey. Ctrl+B vs Ctrl+2 for Bold formatting is an example of this.

Some interesting stats

As you will see from the explanations above, it becomes an interesting exercise to count keyboard shortcuts. Do we count contextual variants (the same shortcut that does different things in different contexts) once, or once for each variation? Do we count duplicate shortcuts (another shortcut that performs the same function as an existing shortcut) or exclude them? And some things are not really true shortcuts (Ctrl+Shift+Enter is an example). My list has 255 rows. If we exclude the variants and the duplicates, we can reduce that down to 207. Comparing OS versions of Excel, 205 of those work in Windows (i.e., two are unique to Mac), and only 147 work for Mac. Is the Mac OS inherently more keyboard-unfriendly, or did Microsoft neglect it’s duty in compiling keyboard shortcuts for Excel for Mac? It is noteworthy the amount of confusion Mac introduces with Ctrl vs Cmd, as some shortcuts work with both, some with only one, and some do different things for Ctrl than for Cmd (compare Ctrl+G to Cmd+G on Mac). Also, that comparison (205 vs 147) is unfair, because a lot of the Windows shortcuts relate to operating the ribbons and menus (or is it unfair?). So if I exclude them, using a pivot table with a slicer, I get this comparison of Windows and Mac keyboard shortcuts, according to my top level categories.

Group Windows Keyboard shortcuts Mac Keyboard shortcuts Mac vs Windows %
Calculation 5 1 20.0%
Editing 78 64 82.1%
File operations 6 5 83.3%
Formatting 9 9 100.0%
Help 1 1 100.0%
Macros 3 3 100.0%
Names 5 3 60.0%
Navigation 22 20 90.9%
Outlining 3 3 100.0%
Selection 33 32 97.0%
Tables & Filtering 3 3 100.0%
Windows 7 2 28.6%
Grand Total 175 146 83.4%

The ultimate list of keyboard shortcuts

Ok. That’s enough talk. Here is the list.

Please feel free to share it, but please do not remove the attribution.

Share

Giving feedback in an Excel worksheet UI

I am creating a tool as part of my work at the University of Sydney to extract data from Google Trends via die Google Extended Trends API for Health. Really an exciting project, and a tip of the hat to Google for making this awesome tool available.

Part of the project is getting multiple samples of the data from the Google servers (I am hoping to get a publication out soon about the why and how of this kind of sampling), and then aggregating them (just calculating data-point averages, really), and charting them. Since most of the output will end up in an Excel workbook, it just made sense to put the whole thing in Excel.

The first part of this is creating a UI with which the user can specify the extraction that needs to be done.

I have built a worksheet-UI, in similar fashion to that recommended in the Excel developer’s “bible”, Professional Excel Development. Part of the UI involves giving feedback when input is still required or inappropriate. These feedback messages can, of course, be given in different ways. Using VBA, a message box can tell the user what is needed, but the problem with that approach is that message boxes can be cancelled, and the input still not corrected. So it makes sense to display on-sheet messages which persist for as long as the issue exists, and these can easily be done using in-cells formulas. The question then becomes, where to put these error messages.

An early version (slightly condensed) of the UI looked like this:

Figure 1    Early version of UI error messages

The two salient points from this figure are firstly that there are error messages in several places (below the API_Key, although no error is currently displayed, below the Target, and in the Messages block). This can clutter up the UI very quickly, so perhaps it is best to put all error messages in one location, like the Messages block. But that brings me to the second point, which is that each cell in the messages block contains one error-checking formula. So in Figure 1, the first four error messages are not displayed (because there are no errors), and the fifth is. This just looks weird. Of course, I could write my error checking formulas to display some kind of “All’s well” message for each formula, but my general aim is to reduce clutter, so I prefer to have the blank cells than a succession of “All’s well” messages.

Figure 2 gives an idea of the structure of the worksheet, showing some of the error checking formulas. It can be seen that I have made extensive use of named ranges to simplify things, and that most of the formulas use a simple IF function with a blank string (“”) displayed if there is no error.

Figure 2    Error checking formulas in UI

So, I was a little dissatisfied with the look. If some issues are addressed and others not, then unsightly gaps are created between the various messages.

What I wanted was a list of messages, where the various issues are displayed top-down. That means that each cell in my list (range) of error messages must be able to display any of the error messages, or the list of error messages must “magically” rearrange themselves so that the displayed messages are at the top of the list.

This is how I went about doing it.

  1. The first thing to do was to create an off-area range where each message is displayed—off-area meaning a range of cells that will be hidden when the finalised version of the workbook is released. So I cut the original list of error messages and pasted them in cells $N$16:$N$23 (columns K to XFD will be hidden in the final version), as shown in Figure 3 (which actually shows the end result, but that is useful for understanding the process).

Figure 3    Moving the error checking formulas

  1. I added a single “All is well” message. One is fine, so long as it appears right at the top. In cell $N$24 I used this formula:
    {=IF(SUM(LEN(N16:N23))>0,””,”All inputs are completed. Data extraction can be commenced.”)}
    For the uninitiated, the braces {} indicate that this formula is an array-entered (or CSE) formula. You do not type the braces, but enter the formula and then press Ctrl+Shift+Enter (hence the CSE alias) to “transform” it from a regular formula to an array-entered formula. It essentially looks for zero-length strings in all the error message cells, and if so, returns the “All’s well” message shown.
  2. To figure out which of the error messages to show in the original message area, I added some formulas in columns L (with the heading “Index” in Figure 3) and M (with the heading “Display position” in Figure 3). The text descriptions in the “Refers to” column (Column O) are not used, and are just for my own reference, so that I can debug or modify the formulas later on.
    Column L has the formula =COUNTA($N$16:$N16) in cell L16, and so on down—i.e., L24’s formula is =COUNTA($N$16:$N24).
    Note that the first part is entirely absolute ($ signs before the column and row references), but that the second has a relative row reference. Thus, as it is copied down, the row changes. Essentially, it is counting the number of rows from the fixed starting point if $N$16, and using that as a means of “numbering” (or indexing) my error messages. Simple, really.
  3. Next is the magic bit. The formula in column M that tells Excel whether to display the error message or not, and if so, in what position. It again uses an absolute starting point, with a relative row end: =SUMPRODUCT(IF(LEN($N$16:$N16)>0,1,0)*(LEN($N16)>0))
    Essentially, what this formula does, is count the non-blank error messages. The first part, IF(LEN($N$16:$N16)>0,1,0), basically says, “If the cell length is longer than zero (i.e., not a zero-length string), then return a 1, but if its length is zero, return a zero). The second part, (LEN($N16)>0) returns True or False to the statement “length is greater than zero.” But True, in Excel, equals 1, and False, in Excel, equals 0. So when the position is multiplied by zero (i.e., when there is no error message), it returns zero. When the position is multiplied by 1 (there is an error message), then the position is returned. Perhaps not that simple, but it works. Figure 3 shows that the 1st, 3rd, 5th, 7th, and 8th error messages will be displayed in the 1st through 5th positions.
    the formulas (partly) are shown in Figure 4.

Figure 4    Error message index and display position formulas

  1. On the off chance that I will be adding more error messages, it makes sense to create a dynamic range name that encapsulates my list of error messages, with their indexes and positions. So I added a range name MessageBaseList with this formula:
    =’Query specification’!$L$16:INDEX(‘Query specification’!$O$16:$O$30,
    COUNTA(‘Query specification’!$N$16:$N$30))
    I somewhat arbitrarily used row 30 as the end point, as that is near the end of my visible interface, and I judged it unlikely that I would be adding more error messages than that!
  2. All that remains is to use a formula in my original message area to look up the error messages that have display positions (i.e., those values in column M greater than 0), and return them.
    Again, I use the absolute anchor with a relative end to allow the formula to adjust as it goes down:
    =IFERROR(INDEX(MessagesBaseList,MATCH(COUNTA($E$15:$E15),INDEX(MessagesBaseList,,2),0),3),””)
    This formula essentially self-determines its position in the message display area using the COUNTA function, and then uses the INDEX function to check if there is an error message with the corresponding position, and if so, displays that. Again, perhaps not quite that simple, but it works!
    Note that the message area is essentially E16:E30, although I merged cells to get enough space to show the messages. Generally, I would advise against merging cells like this, but the layout and the fact that I had to make provision for up to 30 search terms (the limit allowed by the Google API) meant that I really had to make the best use of the space I had at my disposal. The solution works, so I will settle for that, but I do confess that it would be simpler if I had not merged cells.

The basic concept is that the error messages that are active will be displayed, from the top down, in the display area. I can finally add my single “All’s well” message to indicate when there are no more errors, which does help with feedback but still minimises clutter (Figure 5).

Figure 5    “All’s good” message

I will be publishing the complete tool when I am done, and I will update this blog post with a link when I do, so that you, dear reader, can see the effect in action!

Share

Not all keyboard shortcuts are created equal

I thought I would do something different today for IKSD. Like so many things in life, one may find a whole spectrum of keyboard shortcut users. Let me elaborate:

First, there are those who don’t (but, I would say, who should). These are the people who use the keyboard to type letters and numbers. For everything else, there’s a mouse. Want to move the insertion point one character to the left? Use the mouse—there’s no need to bother with tapping that pesky little left arrow on the keyboard when you can reach over, drag the mouse to where you want it, click, and then move the mouse pointer out of the way!

Then there are those who discover one or two keyboard shortcuts that they find really useful, but never really venture into the realm of learning more. These are very often the Alt-set (not the alt-right!)—those who use Alt+xxx to enter special characters not available on the keyboard. You will often find a petite list of Alt codes pasted onto their PC box or the bottom of their monitors. They may even get someone more knowledgeable to help them create some custom shortcuts for certain special uses.

The next level is those who start learning keyboard shortcuts, and use the most basic set for certain tasks. These are the ones who learn about Ctrl+C/V/X (but somehow never seem to grasp the Cut/Copy/Paste—XCV keyboard layout connection) and some others (Ctrl+P, Ctrl+O, etc.). They generally only use system-wide shortcuts that work in all their programs for the most common tasks. And perhaps some program-specific exceptions like learning about Shift+F3 in Word (but don’t ask them what it does in Excel [it’s Insert Function, by the way—one I never use). These all, are in reality (or is that metaphorically), like the laity in the world of keyboard shortcuts.

After that, we move to the functionaries. Even here, we find different levels of progress, just like one progresses through degrees at university or coloured belts in karate. But all of these are the people who have embarked on the journey. The journey of self-improvement, or (before I start sounding like a movie-martial-arts-mystic), the journey of actively discovering and using more shortcuts. However, I recently came across shortcutfoo.com, and while I didn’t try it, I like the idea. Here, you can envelop yourself in the misty mountain, just you and your keyboard, no mouse to be heard lowing in the fields outside, and become one with the keyboard. Ok, I’ll stop now.

The point I want to make is that, while I readily admit that I am not the supreme grandmaster of keyboard shortcut-dom, there is a sure sign that someone has progressed beyond the level of just being an initiate, but is well on the way to becoming a master. And that is when they start understanding, not only that different programs have different shortcuts (that’s really for those who have just moved past initiation), or that certain shortcuts may do different things in different programs (getting into the coloured belts now…), but that even the same shortcut might work differently in different contexts within the same program (surely this must be black belt knowledge by now?).

Blackbelt keyboard shortcuts to master

I want to illustrate just a few of those, and the tool I will use for this is Excel, since it is probably the program I use that does this the most. I suppose I could list a number of others, but this is more of an effort to illustrate the point, than to provide a comprehensive list. Here is a simple workbook that you can use to practise these shortcuts and see the differences. It contains a small data range, duplicated across three worksheets (Normal sheet, Filtered list, and Table). Also, outside of that are list of numbers for rows ($J$1:$J$12) and columns ($A$17:$E$17) so that you can see when a worksheet row/column or only table row/column are being inserted deleted. Also, note that the discussion below is as for Excel 2016.

Consider the following keyboard shortcuts:

Shortcut: Ctrl + D

Normal data range

Fills cell from row(s) above. This includes all cell attributes (formatting (direct and conditional), validation, etc.) and contents.

Can fill down multiple rows and across multiple columns (although direction is always to fill down, not right).

Normal data range (filter applied)

When used on a single cell, fills cell from row directly above, even if that row is hidden by the filter.

When used down several rows, fills the top row down to all cells displayed by the filter, but leaves cells hidden by the filter unchanged.

Table (unfiltered)

As for normal data range.

Table (filter applied)

As for normal data range with filter.

Normal data range (and table) Normal data range and
table (filter applied)

Figure 1    Ctrl + D

Shortcut: Ctrl + `

Normal data range

Copies down a cell’s contents from the cell directly above. Only contents are copied, no other attributes.

Can only copy down from one row and one column (i.e., the cell above the active cell).

Normal data range (filter applied)

Copies down the value from the first row above not hidden by the filter (i.e., no longer the row directly above)

Table (unfiltered)

As for normal data range.

Table (filter applied)

As for normal data range with filter.

Normal data range (and table) Normal data range and table (filter applied)

Figure 2    Ctrl + ‘

Shortcut: Ctrl + +

Normal data range

Displays the Insert cells dialog, dynamically choosing between shifting cells down or right to make place for inserted cells.

Normal data range (filter applied)

Does nothing.

Table (unfiltered)

When one cell or several cells across one row are selected, automatically inserts a table row (not a worksheet row).

When and several cells across one column are selected, automatically inserts a table column (not a worksheet column).

When several cells across rows and columns are selected, inserts multiple rows or columns, based on which have more selected. In other words, if a symmetric range is selected (x rows by x columns), then x rows are inserted. If an asymmetric range is selected (x rows by x columns), then x rows will be inserted if x > y, and y columns will be inserted if x < y).

Table (filter applied)

Does nothing.

Normal data range (and table) Table (unfiltered)

Figure 3    Ctrl + +

Shortcut: Ctrl + –

Normal data range

Displays the Delete cells dialog, dynamically choosing between shifting cells up or left to fill up the deleted cells.

Normal data range (filter applied)

Deletes the entire row (but asks for confirmation).

Table (unfiltered)

When one cell or more than one cell within a single row are selected, automatically deletes a table (but not worksheet) row.

When cells across more than one row are selected, automatically deletes the table column (not the worksheet column).

When several cells across rows and columns are selected, deletes all selected rows or columns based on which have more selected (as with Ctrl + +).

Table (filter applied)

When one cell or more than one cell within a single row are selected, deletes the entire worksheet row (but asks for confirmation).

When cells across more than one row are selected, automatically deletes the table column (not the worksheet column). If the deleted column was filtered, then the filtering in the table for that column is removed (i.e., all rows hidden based on the filtering criteria specified for that column are shown).

When several cells across rows and columns are selected, deletes all selected columns (note the discrepancy with Ctrl++). If any of the deleted columns were filtered, then the filtering in the table for those columns is removed (i.e., all rows hidden based on the filtering criteria specified for those columns are shown).

Normal data range (and table)
Table (unfiltered) Table (filter applied)

Figure 4    Ctrl + +

I did not discuss Ctrl+R, which fills to the right, just as Ctrl+D fills down, because its functionality is not affected by context.

Another set which I could get into, but which I will for another day, is how copying and pasting to- and from filtered lists/tables differs from copying to- and from normal ranges. I think for IKSD 2018, I may do a post just on filtered list/table shortcuts, and their idiosyncrasies. If you don’t want to wait that long, you can embark on your own journey of discovery—the truth is, however, not within, but the truth is out there!

These are the first ones I could think of as I sat down to write this post. Are there others that you can add? Let me know in the comments of others that you would add to my list.

Share

Custom number formatting: Getting Excel to show date and day in one cell

If there is one skill you have to learn that will really make a different to the quality of your Excel work, it is learning to use custom number formatting. Here is one example.

It often happens that I have a list of dates against which data are going to be entered, but I would like to see the weekday as well (this will help me with the data entry).

An example is shown in Figure 1. My traditional approach, which is evident there, was to add a column and use the formula =TEXT(B3,”DDDD”) or =TEXT(B3,”DDD”) to show the day.

Date and day in two columns

Figure 1    Column to show day of week

About a year ago, I thought to myself that this extra column was really unnecessary, and that I should try to remove it. My immediate thought was to try and combine the date and day in the Date column, and I realised immediately that the only way for me to do that would be with custom number formatting. I knew that dates are stored in Excel as a serial number counting from 1 January 1900 (albeit with one intentional error—see here as well for some more info, and note Microsoft’s “diplomatic” choice of words!). So, for example, the date in cell B3 (2015-01-01) in Figure 1 is stored as the serial 42005. The display of the date is added with cell number formatting. So then the thought came to me that I could try using two different date-style custom number formats in one cell together, and it worked.

So the date format already added by Excel is yyyy/mm/dd, and I decided to try something like ddd yyyy/mm/dd, as seen in Figure 2, which also shows the result in column B (and I have already deleted the “Weekday” column which was in column C). the ddd format tells Excel to show the cell value in the short day format—I could also use dddd for the full day.

Figure 2    Adding a custom number format to show both day and date

That’s a simple change, but it is very effective. Now I only need the date column, and don’t have to copy down additional values in the next column to keep up to date with it, if you will excuse the pun.

Share

Calculating the mean from a frequency table: Array formula example

Array formulas are difficult to get your head around, but once you start understanding the way in which they work, you will find that there is a lot to love about array formulas (sometimes called CSEs because you have to type the formula and then press Ctrl+Shift+Enter to have it entered as an array formula). Of course, the converse sometimes also happens—once someone “gets” array formulas, they start using them for all kinds of tasks, even where non-array equivalents exist. That is generally not a good thing, as array formulas are computationally cumbersome, and also have other drawbacks, chief of which is that they can be edited and entered with only the Enter button instead of Ctrl+Shift+Enter. Sometimes, this results in an error value, which immediately shows the problem, but other times, the non-array formula returns a result, although that result is different (and therefore incorrect) from the same formula array-entered.

Here, however, is one nice little application of an array formula which shows their power.

It is not uncommon for me, as a statistician, to receive a table like this:

Figure 1    Frequency table

Granted, I tend to work with raw data, not processed data as in the table, but the simple fact is that tables like this do come across my path from time to time.

How, then, can I calculate something like the mean age of the respondents listed in this table? I cannot simply calculate the mean of the age column, nor the mean of the counts column, as the following figure shows (I am using one of Excel 2013’s great new functions, FORMULATEXT, to display the formulas in A16:C16):

Figure 2    AVERAGE function gives totally wrong answer when applied to wrong values

But with a very simple array formula, I can “expand” the data in the table, and then calculate the mean of that (here, FORMULATEXT shows the formulas in C1:C2):

Figure 3    Simple array formula “expands” the frequency table and allows me to calculate the average from the expanded results

I should add, as a side note, that {=AVERAGE(A4:A14*B4:B14)} does not give me the desired results, in case you were wondering why my contrived formula for calculating the average was necessary.

Simple, but very effective. And that is why I love array formulas!

Share

Conditionally stopping code

One of the VBA programmer’s tool of debugging tricks is using the Stop statement in code to stop code execution at a certain point where they want to examine what is happening.

Of course, this is similar to setting a breakpoint (F9), but it is more permanent (especially useful if you are not finished today, and want to shut down so that you can continue again on a later date).

However, generally you will find that programming “tutorials” will discourage you from using this, and with good reason. The biggest problem is scattering your code with Stop statements that don’t come out in your debugging, but do interrupt your code when your users are running it.

Now I am not going to suddenly start recommending that you start prolifically using Stop statements, but I have found that in certain instances, I really do find it useful. But at the same time, I am really fearful of leaving a Stop statement into my code, and not deleting it before I release the AddIn to the public.

So today, I thought of a simple solution for myself that will not work in all contexts, but may help someone out there too. The reason that it works for me is that I generally have two versions of my AddIns. One is named xxx unprotected.xxxm (e.g., Word uTIlities unprotected.dotm). When I feel that the AddIn is ready to release to the public (it’s been more than a year since I last released an update to the Word uTIlities, but I have some big improvements coming), I make a copy without the unprotected suffix (e.g., Word uTIlities.dotm). Then I add a password to that, package it in an install file, and it’s good to go. That fact—that the version I am working on, and debugging, does not have a password, while the one my users are running does, provides me with a clue to setting up my Stop statements in my code that will work for me, but not interrupt my users.

So all I started doing was changing the Stop statements in my code as follows:

For Word:

If Not ThisDocument.HasPassword Then Stop

For Excel:

If Not ThisWorkbook.HasPassword Then Stop

This, at least, means that so long as the document has a password, the Stop statement will not interrupt the code. Yes, I will still do my best to delete my Stop statements when I am done with them, and if I should not delete those statements and I forget to give my AddIn a password, then it will still bomb out.

What if you don’t have a password? Well, you could create a global variable, but then you would have to remember to “deactivate” that variable before distributing.

So, for example, you would include this dim statement:

Dim bAllowStopping as Boolean

Then the code would just be:

If bAllowStopping then Stop

You can only activate stopping by setting:

bAllowStopping = True

But if you ship the AddIn with that line of code still there, then it will stop, and remember that, because the variable is global, it will stop anywhere the Stop statement is found.

Note that I chose something like bAllowStopping, which, when declared, has the value of False; and not something like bOverrideStopping, which would logically, want to be set to True to not stop—I would want to have to explicitly set it to True before it starts stopping my code. So, as long as I don’t actively set it to True, it won’t stop my code.

One last option, of course, is to tie the Stop statement to the username, e.g.:

If LCase(Application.UserName) = "joe bloggs" Then Stop

However, I consider this to be quite risky, as firstly, I don’t like shipping my application username with my code, necessarily, and secondly, I might, for who knows what reason, might choose to change my username, and thirdly, the odds of there being another user out there with the same username as my own (either through coincidence or being purposefully set) are not that remote.

For my part, I will stick to my first option of checking for the password, and still try to use Stop statements as little as possible, and also follow that up with deleting them when I am done with them.

Share

Manipulating text with Excel: Take II

Quite some time ago, I did a post in which I demonstrated some techniques for manipulating repetitive text blocks. However, I will confess, as my Excel-L friend Brian Canes also pointed out, that the example is somewhat contrived, as it would probably have taken less time to use the CONCATENATE function and just to click on all the cells. What I don’t like about doing something like that is the possibility of error—of clicking on one cell twice, or skipping a cell.

I want to go just a bit further with the idea of using Excel to manipulate text (also using a less contrived example), and show how I use Excel to create repetitive (or even partially repetitive) text blocks. Granted, people tend to think of Excel as a tool for working with numerical data (which, of course, it is), and granted, Excel really has some big problems working with rich text, but people tend to miss the fact that the tabular layout of a spreadsheet can still be a useful palette for working with text. For example (and before I get to my post), if I have to create something like an attendance list (a table with space for names, signatures, and perhaps some other information to be completed, but nothing else), then I prefer to do it in Excel. The page layout is a bit trickier, but I find I can do it quicker in Excel than, for example, by creating a table in Word.

Here, then, are four somewhat less contrived (or not!) examples of using Excel to manipulate text. They all relate to using excel to create programming code. The first one I used to crea VBA code, and the last three SAS code.

Example 1

Here is an example where I used Excel to write VBA code for the Word uTIlities. I wanted to be able to determine exactly what is selected, especially if an object is selected. I copied a table from the Microsoft Word help file (and the example below is from the Word 2010 help file), but the same list can be found for Word 2013 at https://msdn.microsoft.com/en-us/library/office/ff860759.aspx. The table consists of three columns, the Name, the value, and a description. I wanted to take that and use it as the base for my programming. So all I did was copy the table and paste it into Excel, and then added some extra columns to do all my “writing” for me. The process is explained below.

Figure 1    Word Help file text pasted into Excel and modified for further programming

If you look at this, the process was simple. My three original columns from the table were pasted into the worksheet columns B:D. I then added two columns between B:C. Then I typed “elseif selection.shaperange(1).type=” into A2 and filled it down. I typed “Then” into C2 and filled that down. Then I added one double quote to D2 and filled that down, and then merged those values with the aforementioned uTIlity of mine (note that the actual values are now, in Figure 2, in Column D and no longer in E). I then typed “WhatIsSelected = WhatIsSelected & ” in an” in F2 and filled that down, and again one double quote to H2 and filled that down. The double quote in D2 deserves an explanation. Because the single quote is Excel’s cue to treat a number as text, if I added that, and copied it across to the VBE, only the number would be pasted, not the single quote (which, as you will see in the code snippet below, I wanted to use for commenting out the number). But a simple search and replace could change that double quote to a single quote once it had been copied and pasted. One more search and replace would also allow me to break the Excel rows into two code rows each. And I just had to delete the very first else to make it the starting if. So, in a matter of a minute or two, I was able, from the help file, to produce the code below. Counting only the text from Excel, that’s 367 words, 2603 characters in fifty lines of code—you would have to be a very fast typist to beat that (and I am not a very fast typist, so it helped me all the more).

intTestForError = .ShapeRange(1).Type
If Err.Number = 0 Then
  '1
  If .ShapeRange(1).Type = msoAutoShape Then
      WhatIsSelected = WhatIsSelected  _
       & " in an AutoShape"
  '2
  ElseIf .ShapeRange(1).Type = msoCallout Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Callout"
  '20
  ElseIf .ShapeRange(1).Type = msoCanvas Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Canvas"
  '3
  ElseIf .ShapeRange(1).Type = msoChart Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Chart"
  '4
  ElseIf .ShapeRange(1).Type = msoComment Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Comment"
	'21
  ElseIf .ShapeRange(1).Type = msoDiagram Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Diagram"
	'7
  ElseIf .ShapeRange(1).Type = msoEmbeddedOLEObject Then
      WhatIsSelected = WhatIsSelected  _
       & " in an Embedded OLE object"
	'8
  ElseIf .ShapeRange(1).Type = msoFormControl Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Form control"
	'5
  ElseIf .ShapeRange(1).Type = msoFreeform Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Freeform"
	'6
  ElseIf .ShapeRange(1).Type = msoGroup Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Group"
	'24
  ElseIf .ShapeRange(1).Type = msoSmartArt Then
      WhatIsSelected = WhatIsSelected  _
       & " in a SmartArt graphic"
	'22
  ElseIf .ShapeRange(1).Type = msoInk Then
      WhatIsSelected = WhatIsSelected  _
       & " in an Ink"
	'23
  ElseIf .ShapeRange(1).Type = msoInkComment Then
      WhatIsSelected = WhatIsSelected  _
       & " in an Ink comment"
	'9
  ElseIf .ShapeRange(1).Type = msoLine Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Line"
	'10
  ElseIf .ShapeRange(1).Type = msoLinkedOLEObject Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Linked OLE object"
	'11
  ElseIf .ShapeRange(1).Type = msoLinkedPicture Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Linked picture"
	'16
  ElseIf .ShapeRange(1).Type = msoMedia Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Media"
	'12
  ElseIf .ShapeRange(1).Type = msoOLEControlObject Then
      WhatIsSelected = WhatIsSelected  _
       & " in an OLE control object"
	'13
  ElseIf .ShapeRange(1).Type = msoPicture Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Picture"
	'14
  ElseIf .ShapeRange(1).Type = msoPlaceholder Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Placeholder"
	'18
  ElseIf .ShapeRange(1).Type = msoScriptAnchor Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Script anchor"
	'-2
  ElseIf .ShapeRange(1).Type = msoShapeTypeMixed Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Mixed shape type"
	'19
  ElseIf .ShapeRange(1).Type = msoTable Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Table"
	'17
  ElseIf .ShapeRange(1).Type = msoTextBox Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Text box"
	'15
  ElseIf .ShapeRange(1).Type = msoTextEffect Then
      WhatIsSelected = WhatIsSelected  _
       & " in a Text effect"
  End If
End If

 Example 2

The first SAS example concerns the scaling of values in a questionnaire for a paper we were writing. Essentially, we have thirty three items, each of which is divided into four sub-questions. The respondents had to indicate to what level they felt their mothers were involved in various aspects of their sport (A), to what level they wanted their mothers to be involved (B), and then again to what level they felt their fathers were involved in various aspects of their sport (C), to what level they wanted their fathers to be involved (D). For each parent, we had to calculate a discrepancy score—the degree to which the perceived behaviour differed from the desired behaviour (i.e., A-B for mothers, and C-D for fathers.

This could also, admittedly, be done in a different way. I must confess that my SAS macro skills are not what my Excel skills are, and even in Excel I’m probably still not at the true level of guru-like enlightenment. But presumably, this could be done with a SAS macro. However, the simple macro below doesn’t work because my incrementer variable I is used next to other characters, and so SAS doesn’t recognise it. Contrary to my own advice, I did not go and teach myself how to conflate the incrementer with the succeeding text into a new variable name, but rather thought to myself: “I’m under a tight time deadline, and I can do this in a minute or two in Excel.” Which is what I did.

So the first thing I did was enter this in Row 1:

Figure 2    Starter line of code in Excel

Next, copy all of that down to row 2 and change the value of B2, E2 and I2 to 2:

Figure 3    Second line of code, with 1 changed to 2

Now select it all, and use autofill to drag and fill it down to row 33.

Now copy all 33 rows, paste below, and in that, use search & replace to replace ‘Mom’ with ‘Dad.’

The last step can be done in one of two ways. If I copy all of that, and paste it into UltraEdit which I use for my SAS programming, each column’s values are added as tab-separated. So a simple search and replace in UltraEdit to replace tabs with nothing, gives me the final result I want:

B_1_Mom_Discrep=B_1A-B_1B;

However, I used one of my own Excel uTIlities (which I have still not released to the public!) to merge all that information into Column A (see the before and after views below). From there, I could just copy that into UltraEdit and I was good to go. Sixty six lines of code written in about two minutes from when I started typing in Excel, until I had the finished product in UltraEdit!

Before

After

Figure 4    Excel text before and after merging cell contents

Turns out, though, that a period is what joins the counter in a SAS macro to the text which comes after it, so I could just have written the macro like this (had I known this at the time):

%MACRO Discrep;
    %do i=1 %to 33;
        B_&i._Mom_Discrep=B_&i.A-B_&i.B;
    %end;
%MEND;

 Example 3

The third example, I will confess, is again a bit more contrived, but this is something I actually did, so it is worth mentioning. It also employs a technique I learned on Excel-G from the Excel guru, Bob Umlas, which, I think, is featured in his book This isn’t Excel, its Magic!

The scenario is this:

I was writing a SAS program to analyse a student’s data. The student found that some of the data points were incorrect (his error in the coding), and sent me an Excel file with the corrections. However, instead of taking the data file, and correcting it, he sent me a file containing only the corrections, and blanks for all the rest. The (de-identified) corrections look like this:

Figure 5    Data which has to be merged into SAS program

The file indicates the respondent number, as well as the items in which errors were found, and the correct values. Unfortunately, I could not simply read the data file into SAS and merge it with the existing data, because the layout was not ideal (SAS would read all the blank cells as missing values). I could certainly try to manipulate the layout and create a useable data set, and either import that from Excel, or read it into SAS as a data card. But that may have been about as much effort as the solution I chose:

The first thing I did was to delete all the empty columns, i.e., those columns to which no corrections applied. This could easily be done for a large dataset by adding a new top row, and using a COUNTA function in each column—those columns with a count of zero are empty and can be deleted (and even that process can be expedited by sorting horizontally). The result now looked like this:

Figure 6    Data with empty columns removed

Next, I needed labels for each row (this was optional, I could have written a more complex formula that pulled everything out of the top row, but I chose the simpler option for its speed). So here I used Bob Umlas’ trick. I added another column (optionally called “Order”). As before, I added 1 and 2 in the first two rows of that column (L2:L3):

Figure 7    Creating the order column

With autofill, that was quickly copied down all 33 rows. That was then copied again and pasted directly below itself, so that I had two number sequences across the rows, from 1 to 33, right below each other. Then I copied the labels from row 1 and pasted them below the data (i.e., next to the second 1 in my sequence, which was in row 35):

Figure 8    New labels added

Autofill copied that down all the remaining rows (up to row 67) and I quickly sorted on the order and No columns, to give me this:

Figure 5    Labels positioned above each row

Note that, in terms of time, this is probably about one minute’s worth of work.

Next, I added a new column A. Now I was ready to create my lines of SAS code.

The interesting thing is that I even wrote my cell formulas with the method I am describing in this post. I know it will seem contrived, but essentially, I wrote these formulas (note the cells in which I wrote them) in a blank worksheet (some cells contain text, other contain formulas, which I have highlighted, but note that B2 starts with an unseen single quote, so it is not counted as a formula). Note also that this is much less typing that it seems, as C2 & F3 contain exactly the same formula, which is only modified slightly for both B3 & D3 respectively:

Figure 9    Formulas to write an Excel formula

The result is shown below:

Figure 10    Results from formulas to write an Excel formula

Then I again use autofill to copy them down to row 12 (L is the 12th letter) and just add one snippet in B13:

Figure 11    Final text for Excel formula

Now comes the fun bit. I copy that (B2:G13), paste it into my handy text editor UltraEdit, copy it again, and paste it (F2, Ctrl+V) into cell A1 of my worksheet containing the data. The result is this:

Figure 12    Formula in first cell

Now I select A1:A2, and fill down (A2 so that the formula is filled down to every second row):

Figure 13    Final code

I can copy that, and paste it directly into my SAS program, and it will make all the corrections I want.

if no eq 8 then do;B1=3;B2=2;end;
if no eq 10 then do;B1=3;B2=3;B3=3;B6=3;B7=3;B16=3;B17=1;B18=2;B22=3;end;
if no eq 11 then do;B1=3;B2=3;B6=3;B7=3;B16=3;B18=2;B22=3;end;
if no eq 12 then do;B1=3;B2=3;B3=2;B6=3;B7=2;B16=3;B17=1;B18=2;B22=3;end;
if no eq 13 then do;B1=3;B2=3;B3=2;B6=3;B16=3;B17=2;B18=2;B22=3;end;
if no eq 14 then do;B1=3;B2=3;B3=2;B6=3;B16=3;B17=1;B18=3;B22=2;end;
if no eq 15 then do;B1=3;B2=3;B3=3;B6=3;B7=1;B16=3;B17=1;B18=2;B22=3;end;
if no eq 16 then do;B1=1;B2=3;B3=3;B6=3;B7=1;B16=3;B17=3;B18=3;end;
if no eq 17 then do;B1=3;B2=2;B3=2;B6=3;B7=3;B16=3;B17=3;B18=3;B22=3;end;
if no eq 18 then do;B1=3;B2=3;B3=3;B6=3;B16=3;B17=3;B18=2;B22=3;end;
if no eq 19 then do;B1=3;B2=3;B3=3;B6=3;B7=2;B16=3;B17=1;end;
if no eq 20 then do;B1=3;B2=3;B3=3;B6=2;B16=3;B17=1;B18=2;B22=3;end;
if no eq 21 then do;B1=3;B2=3;B3=2;B6=3;B7=3;B16=3;B17=3;B18=3;B22=3;end;
if no eq 22 then do;B1=3;B2=3;B3=3;B6=3;B7=2;end;
if no eq 26 then do;B16=3;end;
if no eq 29 then do;B1=3;B14=1;B16=3;B17=3;end;
if no eq 33 then do;B1=3;end;
if no eq 36 then do;B16=3;end;
if no eq 38 then do;B22=3;end;
if no eq 43 then do;B16=3;end;
if no eq 45 then do;B16=3;end;
if no eq 48 then do;B22=2;end;
if no eq 57 then do;B16=3;end;
if no eq 59 then do;B16=3;end;
if no eq 63 then do;B17=1;end;
if no eq 69 then do;B6=3;end;
if no eq 72 then do;B6=3;B16=3;B18=3;end;
if no eq 73 then do;B6=3;end;
if no eq 83 then do;B16=3;end;
if no eq 84 then do;B6=3;B18=1;end;
if no eq 89 then do;B3=2;end;
if no eq 99 then do;B17=1;end;
if no eq 111 then do;B18=2;end;

The process may be crude, but is surprisingly effective and efficient: This kind of programming takes just a minute or two to complete.

Example 4

This example is much the same as before: Data corrections (yes, it seems as if I spend more time correcting data than analysing data, but let’s just say that proper preparation leads to efficient execution).

The basic layout is as shown below–almost 70 rows of corrections that need to be made. The trick here is that it is not always the same variable that is to be corrected. The variables names are listed in B1:J1, and if a column has a value, that variable must be set to that value for that respondent (as listed in Column A).

Excel Text example 4b

Figure 14    Listed corrections

So I use this formula, and the results are shown in Column K in the final figure.

="if Number eq " & A2 & " and " & $B$1 & " eq " & B2 & " and " &OFFSET($B$1,0,MATCH(MAX(C2:H2),C2:H2,0),1,1) & " eq " & MAX(C2:H2)& " then do; " & $B$1 & "="&I2&";" &OFFSET($B$1,0,MATCH(MAX(C2:H2),C2:H2,0),1,1) & "="&J2&"; end;"

Excel Text example 4a

Figure 15    Formula result showing code which can be copied to SAS

Essentially, my formula finds which cells have values, and then pulls the column headings in for those cells, setting them equal to the value found in the cell. Using this method, I could get Excel to create 6557 characters of code for me by typing 228 characters (the length of the formula) and doing a little bit of layout.

Conclusion

In closing, this is definitely not a conventional usage of Excel, but in terms of getting “repetitive” (actually, “patterned” is a better term) text created, it is hard to beat for speed if you know Excel well. I can create hundreds of lines of code, if need be, in a fraction of the time it would take to type it.

Share

Using superscripts in Excel’s custom number formats

One of the most under-appreciated features of Excel, I think, is Excel’s custom number formatting.

I will be alluding custom number formatting in a future post series as well, but here is a quick custom number formatting tip.

Let’s say I want to work with area or volume, and need to indicate that using custom number formatting (e.g., m2, or cm3). How do we add those in Excel?

Note, here, that I am talking specifically about custom number formatting. I can type m2 very (well, relatively!) easily in Excel—all I need to do is edit the cell, type “m2” and then select the 2, and use the Format Cells dialog to make it superscript. But that is not what we are looking at here. Say I have a formula, and whatever value the format provides, must be shown together with the m2 suffix. Or I have a blank cell, and when I enter a value (e.g., 3), then it is displayed with the custom format suffix (i.e., 3m2).

The problem, of course, is that we use the Format Cells dialog to create the custom format, and we also use the Format Cells dialog to add the superscript, and the dialog cannot be invoked on itself (i.e., we cannot format the 2 to be superscripted while entering it into the custom number format box in the Format Cells dialog).

Your first thought might be: Type the m2 and then format it to be superscripted as described above, copy it, and then paste it into the custom format section of the Format Cells dialog. But a long-standing gripe I have with Excel is its inability to really work with rich text, and when you copy text while editing a cell, all formatting (such as bold, italics, or superscript) is lost.

So we need a different approach.

To do this, we start, instead, with the Insert Symbol dialog. Because the Format Cells dialog is modal, we cannot invoke the Insert Symbol dialog while adding the custom number format (so we cannot use Insert Symbol to add the superscripted two to the Format Cells dialog). But what we can do is learn the character code of the 2. So I open the Insert Symbol dialog. Figure 1 shows this dialog, with the Superscript Two symbol selected. Note that, if I wanted something like cm3, the Superscript Three symbol is there as well.

Figure 1    Insert Symbol dialog

The next step is to find out what the ASCII code of this symbol is. Of course, we could look this up in a table, but I am using Excel here to find it. Nonetheless, in the From list box, I choose ASCII (Figure 2). That shows (in the Character code box) the value 178. We could, of course, insert the value into a cell, and then use the =CODE function to get the same information, but this is slightly quicker.

Figure 2    Insert Symbol dialog showing ASCII codes

Once I have this information, I can use standard custom number formatting procedures to create my format. So, for example, showing a number with one decimal, I would use the format #.0. I would then just add “m2” to that (including the quotation marks). I add the 2 by holding down the Alt key while typing 0178 on the number keypad. Figure 3 shows the creation of this custom number format in the Format Cells dialog.

Figure 3    Adding the custom number format

And, to wrap it up, Figure 4 shows the custom number format in use. Note that $B$2:$C$10 use a custom format #.0″m” and $D$2:$D$10, $E$2 uses to format I just demonstrated, #.0″m2“.

Figure 4    Custom number format in use

Share

Filtering for only the first instance of all duplicate items

If you’ve been using Excel for a long time (i.e., at least since Excel 2003), then you will know that the Filter tool has come a long way. Already in the earlier versions of Excel (i.e., ?Excel 2003) it was a very useful tool, but it underwent a big makeover in Excel 2007, which was not without its problems, and Microsoft ironed those out in Excel 2010 to give us a really nice tool. But even now, it still has its shortcomings. I quite enjoyed Dick Kusleika’s posts on Daily Dose of Excel about “An Even better AutoFilter” (although I don’t quite see it as something that I will be adding to my armoury). See:
http://dailydoseofexcel.com/archives/2014/10/16/a-better-autofilter/
and
http://dailydoseofexcel.com/archives/2014/10/21/an-even-more-better-autofilter/

In this post, I will present another interesting use case that extends the capabilities of the filter.

Consider the following list (Figure 1). It presents the exported data from a great time management tool I use (ManicTime). The export is of the application-level data recorded by the program, exported as csv and imported into Excel (I also modified the column arrangement). There are a lot of duplicates (quite understandably), such as those in E5, E7, E10, E12. But what if I only wanted to see one instance of each item, regardless of whether it is duplicated or not?

Figure 1    Sample data list to be filtered

One method could be to make use of a helper column, but I thought it might be better to do without that.

Filtering for only duplicates or only singles is easy—just apply conditional formatting to highlight duplicate items, and then filter to show only those items with or without formatting, respectively. But filtering for only one of each item requires showing all the singles, as well as the first of all duplicates.

Working with ranges in dialogs

Illustrating this also allows me to highlight some tips of using the Range text box (I am actually not certain whether this is its “official” name, but you will see what I am referring to)—which appears in numerous places, amongst others the New Formatting Rule dialog. The range text box is identified by the Range Select tool (ditto about the “official” name) which appears to its right—clicking on this will shrink the current dialog (and another click will expand it), and while the I-beam is in this text box, you can select a range on the sheet, the range address of the selected range will then be entered into the text box. Essentially, clicking in the Range text box is akin to editing a cell’s contents (you can enter a formula, select ranges, etc.).

New Formatting Rule dialog

Figure 2    New Formatting Rule dialog, showing Range text box

Range text box tip #1:
Sometimes, when you are typing a formula in the range text box, you make a mistake and want to go back and correct it. If you’re like me, that does not entail abandoning the keyboard, grabbing the mouse, clicking where you want to edit (sometimes a few clicks to get it in the exact right place) and then returning to the keyboard to edit, but rather just a few quick left arrows with or without Ctrl) to get to where you want to be and edit. But there is a problem. Because you are in the Range text box, Excel assumes you want to select a range (starting from the Active cell), so when you press the left arrow (as an example), Excel dutifully selects the cell to the left of the Active cell and enters its range in the middle of your formula. But remember that working in this text box is akin to editing a cell formula—in a cell, you switch (toggle) between moving within the cell contents, and moving across worksheet ranges, by pressing F2. Same here. So press F2, and then you can happily move around within the formula you are typing.

Range text box tip #2:
Whenever you do select a range while in the Range text box (whether with the keyboard or the mouse), Excel enters the range address as an absolute address, and this is definitely by design, as most of the time, this is exactly what you need. But there are times that you want relative addresses. Again, you can edit the formula just like in a cell: Move to the range address, and press F4 until you get the reference type—Relative, Mixed (x2), or Absolute—that you want. But here is the real tip: When using relative references (or, for the relative parts of mixed references), the formula is defined for each cell in the selected range in the same relative way that the formula applies to the Active cell. So always look to see what the Active cell is, and write your formula accordingly.

Now that may be confusing (I am finding it hard to explain), so let me illustrate.

The data shown in Figure 1 actually covers this range: $A$1:$E$607 (it was a quiet day on my laptop, and I did most of my work for the day on my office PC). If I select $E$2:$E$607 by starting at $E$2 and pressing Ctrl+Shift+Down, Excel then extends the selection all the way down to row 607, showing me, then, those bottom rows; but the Active cell remains $e$2 (Figure 3). So when I enter my formula into the Conditional formatting dialog, I must keep in mind that it is relative to E2 for E2, and that it is adjusted accordingly for every cell from E3:E607 in the range.

Figure 3    Active cell in a selected range

This is important, because even though E2 is the Active cell, I can’t see it. And if, for example, I had select E607 and pressed Ctrl+Shift+Up, I would then see E2, but E607 would be my Active cell.

Filtering for only the first instance of all duplicate items

Now after all that, we can turn our attention to the filter problem. This, of course, actually very simple. I add a new conditional format, using a formula, to mark all duplicates from the second on (note that the formula is relative to E2):

=AND(COUNTIF($E$2:$E$607,E2)>1,MATCH(E2,$E$2:$E$607,0)<>ROW(E2)-(ROW($E$2)-1))

Let me unpack that.

The AND function ensures that both criteria must be met: 1) It must be a duplicate, and 2) it must be a subsequent occurrence of the duplicate, not the first occurrence.

COUNTIF($E$2:$E$607,E2)>1 uses $E$2:$E$607 as the comparison range (we keep that absolute, because we don’t want it adjusting down for all the cells below E2), and counts all the occurrences of the current cell (because E2 as the criterion is relative). For each cell, if it occurs in the range more than once (i.e., >1), it is one of a set of duplicates. All singles will return FALSE for this, and all duplicates will return TRUE.

The second AND argument is perhaps slightly more complex:
MATCH(E2,$E$2:$E$607,0)<>ROW(E2)-(ROW($E$2)-1)
MATCH returns the first instance of the criterion (E2—relative, so it is always adjusted for the current cell) in the lookup range ($E$2:$E$607, again, absolute)—Note that I am using exact matching. So it will always return the position of the first occurrence of the set of duplicates to which the current cell belongs, if the current cell is one of a set of duplicates, and it will always return the position of the current cell if the current cell is a single. However, MATCH returns the position in the range, not the row number (i.e., position 1 in $E$2:$E$607 is 1, not 2). But I need to check whether the row number of the first instance of the duplicate set (not its position) corresponds to the row number of the current cell. The relative ROW(E2) gives me the row of the current cell (whichever one of the 606 cells in this example that may be). I then just need to compare that to the row corresponding to the position returned by the MATCH function. Now I could have simplified things and said: MATCH(E2,$E$2:$E$607,0)<>ROW(E2)-1, but that would mean that my target range must always start in Row 2, as I have essentially hard-coded the row into the formula. Instead (perhaps somewhat pedantically), I avoid the hard-coding by supplying the first row of my range ($E$2) and returning its row— thus ROW($E$2)—and then subtracting 1 to find the row just before it). I suppose I could also have used ROW(OFFSET($E$2,-1,0)) to eliminate any confusion about what this part of the formula does. Nonetheless, this gives me the more complex, but more flexible, MATCH(E2,$E$2:$E$607,0)<>ROW(E2)-(ROW($E$2)-1).

Now that we have the formula, we just select $E$2:$E$607 and click on: Home | Styles | Conditional Formatting | New Rule…. That opens the New Formatting Rule dialog (Figure 4) and we add our formula (note in Figure 4 that E2 is my Active cell).

Figure 4    Creating the Conditional formatting

Once this is added, all subsequent duplicates are highlighted, but not the first instance of the duplicate set, and not any singles (see Figure 5).

Figure 5    Conditional formatting successfully applied

Once the conditional formatting has been set, it is a simple matter to filter for those cells showing no formatting (Figure 6), which gives us all singles, and all first instances from all duplicate sets.

Filtering for no formatting

Figure 6    Filtering on formatting

Share

Using the Date picker

I was working on a new tool for the Word uTIlities, when I discovered that there really doesn’t seem to be particularly much available by the way of good information on how to use the date picker control. Not even the two “standard” works I have on my bookshelf (John Walkenbach’s Excel 2010 Power Programming with VBA and Bovey, Wallentin, Bullen and Green’s Professional Excel Development) make much mention of it.

So, while this is not intended to become the be-all and end-all of your search for information on the date picker, I do want to add here what I have learned so far.

Why use the date picker?

In all honesty, getting date and time information from users can be done with standard tools like text boxes or combo boxes. But the date picker offers a large number of advantages, and the main reason for using it is the huge convenience it offers. Firstly, it is more convenient for your users, as it offers them date/time specific ways of providing information (e.g., a “digital clock” for time, and a calendar for dates), while still allowing keyboard folks like me to type in the information they want to add. And convenience for users is not a frill, but a necessity.

Having said that, the date picker also offers huge advantages for the programmer. First and foremost is that it delimits the information users can add, so that you can by definition not get invalid dates or times from your users, obviating a large amount of code that would otherwise be required to test and validate what they have entered. Furthermore, it can be customised to let users enter only the information you want (more on that below). And finally, its values are already in the date format (which in VBA is date/time), so it both accepts and provides date/time information without the need for conversion.

Getting the date picker

Finding the date picker is not obvious. In the VBE form editor, right-click on the toolbox and choose Additional Controls… (Figure 1).

Figure 1    Additional controls for the Toolbox

Then, in the Additional Controls dialog, find the Microsoft Date and Time Picker Control and make sure that the box next to it is marked (just clicking on the control doesn’t mark the box, it just selects the control—you have to click the box itself), as shown in Figure 2.

Figure 2    Additional Controls dialog (Showing Microsoft Date and Time Picker Control)

Click OK, and the control will appear on the Toolbox (Figure 3).

Figure 3    Date Picker added to Toolbox

Now you can click on the tool, and add date pickers to your user forms as you would any other control. Figure 4 shows a new tool I am working on for the Word uTIlities (this will be version 1.9), although I am not certain that all the options shown here will appear in the final version. This is suddenly quite a heavy usage of date pickers—eight at the moment (the three on the right might not make it, though…). Note the three different applications of the date pickers. Some are used to give dates, some for times, and one (bottom right) for only hours.

Figure 4    User form showing various date picker controls

Customising the date picker

I will limit this section to things specific to the date picker. Since it is a form control, it shares many of the normal form control formatting settings, which I will not discuss.

To set how the control works, it is best to work with the Properties pane. Figure 5 shows the Properties pane for a date picker control, with the relevant items highlighted.

Figure 5    Date picker properties

Note that I haven’t highlighted the Calendar color settings, but these are only for window dressing, and should be easy to figure out if you should choose to modify them.

Here’s a quick rundown of what you can do with these properties:

Use MaxDate and MinDate to set (respectively) the latest and earliest dates from which the users can choose (the default range of 1601 to 9999 might be a bit broad…).

The Value property allows you to choose what value the date picker displays when the form is loaded. See below for more details on changing this at run time.

The most important settings are the Format and CustomFormat settings.

For Format, Figure 6 shows that you have four choices: Long date (0); Short date (1); Time (2); and Custom (3).

Figure 6    Format property settings

Figure 7 shows what each of the first three look like (Short date on the left, Long date in the middle, and time on the right).

Figure 7    Date picker formats

Note that time defaults to a 12 hour clock with hours, minutes, and seconds.

So what if you don’t want one of those formats (for example, you would want date and time, or you don’t want seconds, or you want a 24 hour clock, etc.)? In these instances, choose the last format—Custom (3). Then, in the CustomFormat property, define the format you want. I am not going to go into an extended discussion of the formats now—if you are familiar with number formatting (e.g., in Excel, or in Word Merge fields), you will understand what is required. So, for example, to leave out the seconds, use hh:mm. To leave out the seconds, and switch to a 24 hour clock, use HH:mm.

But there are problems. Figure 8 shows a date picker set to the Time (2) format on the left, and one using the Custom (hh:mm) format on the right. The problem, as is patently obvious, is that when the custom format is set, the spin buttons are removed (they are quite important for a time picker) and the drop-down again gives the calendar (essentially rendering it useless for setting a time).

Figure 8    Standard time format vs. custom time format in date picker

So what to do? Well, if you’ve been observant, there’s one highlighted property in Figure 5 that I haven’t discussed—UpDown. That’s what turns on the spin button (and removes the calendar) for the time picker.

Furthermore, the Value, MinDate, and MaxDate properties remain set to dates, even though the custom format specifies only time, although that is not really a problem—just type a time in, and the VBE will accept it.

Also take note of the (Custom) “property”—note that it is in parentheses in the properties pane, indicating that this is not actually a property. Sure enough, click in the property value field, and the dialog launcher appears (Figure 9).

Figure 9    (Custom) date picker “property”

Click the dialog launcher, and the Property Pages dialog appears. I will not discuss the Font, Color, or Picture tabs. But let’s take note of some of the settings on the General tab. Many of these are in the Properties pane ad well, but you can set them here if you wish.

Figure 10    Property Pages dialog

How the date picker works for users

The beauty of the date picker tool is evident when the form is loaded and the user has to set a value. Clicking on the date picker reveals a calendar (Figure 11) that users can navigate (note the previous and next month selectors at the top) until they get to the right value. There, they can click on the date they want, and it is set as the value in the control. And, of course, the picker only presents users with valid dates to choose from. And if users type in invalid dates (e.g., 2014/02/29), the control won’t accept them.

Figure 11    Using the date picker

Times are also easy to set (Figure 12). The user clicks on the time part they want to set (e.g., in Figure 12, the minutes section is clicked). Then the user uses the spin button to move the value up or down. If it reaches the maximum allowed for that time unit (e.g., 59 for minutes or seconds), it just moves on to the minimum again, and vice versa. Users can also type in times if they want.

Figure 12    Using the time picker

Setting the date picker values

One of the nice things about the date picker is the ease with which you can set its values when you load your form (or at any other event, such as when another control on the form is set—the form in Figure 4 does exactly that). For example, for some unknown reason, the value property of the date picker is set to the date you add it to the form. But this means that, for example, a year hence, the date displayed initially by your form will be a year old, when you would probably want it to display the same date on which the user has launched the form.

So what do you do if you want to load specific values that will be determined at run time? The date picker makes this incredibly easy, since it accepts date values. So, for example, some of the underlying code for the form shown in Figure 4 is:

Dim DateTimeFirst As Date
[…]
DateTimeFirst = rev.Date
[…]
If Left(Me.CBxDateSelector1.Value, 6) = "Before" Then
Me.DTPicker1 = DateTimeLast
Me.TMPicker1 = DateTimeLast

Note that I set the date-formatted date picker and the time-formatted date picker to the same variable (the Date data type in VBA stores both date and time), but each date picker uses only the portion relevant to its formatting—the date for the former, the time for the latter. Of course, you could make doubly sure, or convert variables of other types (like string variables) to date or time values like this:

Me.DTPicker1 = DateValue(DateTimeFirst)
Me.TMPicker1 = TimeValue(DateTimeFirst)

Which of course also means that you could specify something like:

Me.DTPicker1 = DateValue("2014/04/01")
Me.TMPicker1 = TimeValue("14:05")

Although why you would want to hard-code values like that and not add them manually as defaults is beyond me.

One last thing: to set the date or time to the current (date or time), simply use

Me.DTPicker1 = Now ()
Me.TMPicker1 = Now ()

Getting date picker values

Opposite to the above, once the user has entered a value in the date picker, you would obviously want to get that value and use it in your programming (typically when the OK button has been clicked).

The code is essentially the reverse of what we have seen above. Note that since VBA only has the Date data type (and not a separate Time data type), you would declare your variable which is to hold the date picker’s user-set value as a Date Variable. Then simply set it to the date picker’s value:

Dim Date1 As Date
Dim Time1 As Date
[…]
Date1 = DTPicker1.Value
'Of course, the '.Value' is optional, since value is the default property.

Time1 = TMPicker1.Value
'Of course, the '.Value' is optional, since value is the default property.

Again, you can make more certain with

Date1 = DateValue(DTPicker1)
Time1 = TimeValue(TMPicker1)

But that does beg the philosophical question of how more certain you can be when you are already certain….

[And I think that’s my cue that this blog post has now gone on long enough!]

Share