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

Why I would not do my thesis with Word 2013 (and why I might)

I have been working with Word 2013 since the public beta was made available in October 2012—more than two years now.

While I have always had my pet peeves about the Office products, I have, in many ways, also been a bit of a fan boy for products like Word and Excel. Even with Word 2007, which many people deplored, and about which I had my own complaints, I was still quite able to see improvements which made the upgrade worthwhile. However, even after two years of use, I find myself particularly unimpressed with Word 2013. In fact, most of Office 2013 has left me rather unimpressed, if not downright disappointed. A prime example is the dumbed-down Presenter view in PowerPoint 2013—which Microsoft touted as a big improvement, but which is, in comparison to what PowerPoint 2010 offers, so pathetic that I refuse to do live presentation with PowerPoint 2013 if I can help it. Excel 2013 is the only program where, along with the regressions (like the dumbed-down sheet tab controls that I detest, and the dumbed-down charting tools that I find actually hinder the quick creation of charts), there are also noticeable improvements that matter to me. I suppose that’s an important point to make, because, of course, Microsoft has made many “improvements” to all their products in the 2013 iteration, it’s just that most of them are the kind that I could happily live without, and the things that are important to me, I find frustration.

In fact, if I stop to think about what it is I find so distasteful about Office 2013, it is that I feel it is a definite dumbing-down of the product. Some things are superficial and cosmetic (even after two years, I prefer the Office 2010 ribbon—which is, itself, a toned-down version of the too-loud Office 2007 ribbon—to the bland, dead Office 2013 ribbon). Or the new UI messages which are just too informal, even for me (actually, it’s not the informal that bothers me, but that fact that they have, at the same time, become uninformative). Other things are more serious, like the removal of tools that I actually use (see autocorrect below).

Positives

But before I make this one long gripefest, let me highlight some of the positives in Word 2013. You can see this article to see what’s new in Word 2013, most of my comments here relate to the new features.

Microsoft has made some significant changes to reviewing. While you will see below that some of them are negative, one positive is that reviewers can now comment on each other’s comments. The process is quite smooth, and, if a student and multiple promoters are all into electronic reviewing, a real time saver. Promoters can see what other promoters have said, and can weigh in themselves. Of course, we will all debate like adults, won’t we! J

Word 2013 now also offers the option to embed videos into a Word document. While PowerPoint has been able to do that for a very long time, it has always seemed a bit pointless in Word, but that has changed with the rise of eBooks and the rising popularity of multimedia content in eBooks. So while you still can’t print out a video on paper, put it into an eBook, and it could work. So there, Word is keeping with the times, and this an important addition.

Furthermore, Word 2013 now also has the image guides which were added in PowerPoint 2010, but not the rest of the 2010 Office suite. This also makes working with images much easier, and is a welcome addition, although it should have been available in Word 2010 already—I sometimes get the impression that the Office team doesn’t get time to complete all the planned work before the scheduled release date, and so we have one feature being progressively made available throughout the suite across several releases.

The integration of OneDrive giving you cloud storage of your documents is also a boon, but I must add that one does need to use the cloud “carefully.” If your account gets hacked, and your files get deleted, and those are the only copies around, you might be in for some trouble.

The layout of the Office 2013 applications are supposedly to also make them easier to use on small devices like tablets, but I find that a bit of a non-argument, as I really can’t see why I would want to do my thesis on a small device like that in the first place. Maybe for some extra work at the airport or on the road, yes, but for the data-to-day work needed to complete something big like that? No way!

Negatives

The Resume reading feature is a waste, since Shift+F5 has worked in every version of Word except Word 2007, and in any case the little message often disappears before you can click on it—what good is it then?

While I like the ability to comment on comments, the change to “simple “reviewing is a downright pain. Again, Microsoft dumbs things down, which may make them more unobtrusive, but also make them less efficient.

Microsoft removed AutoCorrect. I’m not alone in missing it. They say people never used it, which just means that they never survey people like me, who do use it. Fortunately, Greg Maxey shows how to fix this.

Microsoft also made important changes to the way their citation tools work which render even advice Microsoft posted about how to customise the tools invalid. I discuss some of these changes here. What really irks me is that they did not announce these changes in any way that I could find—they just made them. That is not the way to win or keep the confidence of your users.

Did I mention how much I detest the flat, uninspiring interface of Office 2013?

One of the things I really hate is the vanishing scroll bars. Type in Word, and the scroll bars disappear. Move the mouse and they momentarily reappear. I suspect this also has to do with getting Word ready for smaller screens, but for goodness sake, I have a big screen, and I am proficient enough to type for an extended period of time without have to resort to the mouse. I have grown accustomed to glancing at the scroll bar, as it provides important visual feedback as to where I am in a document (I read a lot of other people’s documents, so I need this information very frequently), and I still get irritated when I look in its direction to get that information, and it’s not there!

I have also found Word 2013 to be significantly slower than Word 2010. This is in all areas, from running macros (I have documented tests for running the same macro in both versions, showing Word 2013 to be up to 40% slower), to loading documents, saving documents, and even just the time it takes to open. I have Word 2010 and 2013 installed on my current system, so it has nothing to do with the system. The degraded performance is noticeable.

With Office 2013, Microsoft have gone all out for online help. That already irritates me, because getting online help is necessarily slower than getting installed help. When you click on Help in Office 2013, you don’t get a help browser that open—no, you get your web browser opening to go to Microsoft’s site. That takes a few seconds at best, and each time a web page loads, more time is lost. Thankfully, you can still set the Application help to Offline help, but the help from the VB Editor goes straight to the browser. And that’s the help I use the most. But it’s not just that that frustrates me. Microsoft has changed the very nature of their help content. The impression that I get is that, I would assume to save costs, they seem to have fired all their help-writing staff, and now all you get in your web browser is a search of various Microsoft sites and the rest of the Internet on your help topic. If I had wanted to go to a search engine to see what the rest of the world has to say on a topic, I would have done that. When I click on help, I expect to find the documentation provided by the people who made the program on how their program works. It seems harder to get that now than ever before.

One last thing (I think). I simply deplore the new dumbed-down spell checking pane which has replaced the old spelling and grammar dialog. Not only is it not now possible to ignore grammar rules, you can now also not edit the misspelled item in the dialog (e.g., if none of the suggested items matches what you actually intended to type–yes, I do type that badly!), but have to jump to the text to change it, and then resume spell checking. These are small changes, but I find that spelling & grammar checking now take much longer than they used to (i.e., a decrease in productivity). Oh, and to make matters worse, despite the buttons in the pane being marked with accelerators, they do not respond to the keyboard, so you have to manage the process with the mouse now, which is even slower! And it’s not just in the pane that the dumbed-down spell checking annoys me. I have already mentioned the missing auto-correct, but the long history of red, and green underlining for spelling and grammar errors, respectively, with blue added for contextual errors since Word 2007, has been changed. Now, in Word 2013, we have red for spelling errors, and blue for grammar and contextual errors. I realise that the choice of red, blue, and green may have been unfortunate for those who are colour blind, but why not include options in the interface to change those colours? Now, however, Word 2013 is giving me less information than Word 2007 and Word 2010 in terms of inline spell checking. And I find that annoying.

Summary

In short, if you asked me for advice, then unless you were a hardcore going-paperless-all-the-way student (who has promoters who can actually play along) or a cutting-edge creative arts student, where your thesis will incorporate video, I would tell you to stick with Word 2010 for your thesis.

I am still rooting for Microsoft, and I am hoping that Office 2016 will be to Office 2013 what Office 2010 was to Office 2007—the way it was meant to be.

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

Table styles and multiple-row table headers

This post might present you with something small, to which you say: “I can’t believe he didn’t know that,” or it may just show you something so deceptively simple that you end up saying “I can’t believe I’ve missed that all these years—it’s so obvious, but simultaneously so counter-intuitive.” So this post won’t be “up there” on the Wow-factor scale, but it may just simplify one little aspect of working with tables for you, I hope.

As readers of my book will know, I am a (cautious) fan of Word’s table styles. Cautious, because while they can drastically ease and speed up the process of creating multiple uniformly formatted tables, they take some getting used to, and you can just as easily do something wrong and “blow up” your tables if you don’t know what you’re doing.

One of the nice things about table styles is that you can set formatting for the table as a whole, and then for each specific constituent part of the table (Figure 1). You can specify individual (and thus differing) formatting for:

  • Whole table
  • Header row
  • Total row
  • First column
  • Last column
  • Odd banded rows
  • Even banded rows
  • Odd banded columns
  • Even banded columns
  • Top left cell
  • Top right cell
  • Bottom left cell
  • Bottom right cell

Figure 1    Choosing which portion of the table formatting is specified for

This feature makes table styles very flexible: Note the group of Table Style Options on the far left of the Table Tools: Design ribbon (Figure 2). These setting, as it were, turn the differentiations you specified in the process of defining the various elements of your table style, on and off. Thus, if you specified a bold header row, then the text in the header row will only be bold if the Header Row check box in this group is selected. If not, then the text in the header row will appear as defined for the whole table.


Figure 2    Table style options

Probably the most-used of these various constituent parts is the Header Row (and probably then First Column after that). It stands to reason that the Header row formatting applies to the first (i.e., top) row of the table. However, here we have a dilemma. What if you have specified a certain header row formatting, but some of your tables have headers that span two (or three) rows?

Once you see it, it is obvious, but it’s one of those things that is so obvious that we (myself included) often overlook them. You should know that the Repeat Header Rows function (Figure 3) can be used for multiple rows. This setting is used if you have long tables that span more than a single page, as it allows the header row(s) of the table to be repeated on each successive page.


Figure 3    Repeat Header Rows

The interesting thing (and this is not necessarily intuitive, and has not, to my knowledge, been documented by Microsoft), is that turning this setting on with multiple rows selected, even when your table does not span multiple pages, will instruct Word to treat all selected rows as header rows, and Word will then apply the header row formatting to all of those rows. Et violà!

Caveat:

Yes, there’s a catch. This works fine for settings like font settings, shading, even repeating header rows. But it doesn’t work for borders. So if you want, for example, only a bottom border and have that set on your header row formatting, then when you do this with multiple rows, each row will have the bottom border, not only the lowest one as you might have hoped. That will require a manual fix, unfortunately.

Share

Learning Keyboard Shortcuts

This week (Week 26 of 2014, for the Excel aficionados), we have had a very interesting discussion on the Word-PC e-mail list about keyboard shortcuts. This led to me sending a mail to the list about how to teach oneself keyboard shortcuts (or, more precisely, the system I use to teach myself—and I am still continuously learning—keyboard shortcuts). Since I realise that it might prove useful to others, I have modified that mail slightly for this blog post. If you find what I write below useful, this topic is discussed in greater detail in my book on Word, together with many others topics that you might benefit from.

First up, how not to learn keyboard shortcuts: Don’t search for a list and try to commit it to memory. This fails, because firstly we are overloading our brains with too much information in too short a space of time (any student can verify this three weeks after an exam). Secondly, we are dealing with the list as an abstract thing, and not relating it to our use.

My own experience has been that a very simple discipline (which I will get to shortly) has worked very well for learning keyboard shortcuts.

Before that, though, it is useful to examine how we discover keyboard shortcuts. Yes, lists are useful for that—every time I see an article on keyboard shortcuts, I make certain that I read it (I just picked up a new Excel keyboard shortcut this week in that manner). Second, we all (or at least those who type as inaccurately as I do) have had the experience of pressing something on the keyboard, and seeing something go haywire (e.g. we wanted Shift+R for an uppercase R, but hit Ctrl+R and right-aligned our paragraph). Normally we just undo (Ctrl+Z), but I normally take some time out, try to see if I can figure out what I just hit (I will sometimes undo and try to recreate the keyboard shortcut with what I thought I was typing). I will also evaluate whether this keyboard shortcut is useful for me—if not (e.g., the keyboard shortcuts in Word for Danish characters are some that, no offense to the Danes, I hope I never have to use), then I just forget about it and carry on with my life. If it does appear to be useful, then I will set about learning it. Also, it pays to be observant. It amazes me that people look at menus, and never see (i.e., observe) the keyboard shortcuts listed in those menus (of course, since Office 2007, the Office keyboard shortcuts are displayed in better-hidden tooltips—yet another way Microsoft seems intent on de-cluttering by dumbing down).

One other note before I get to the learning bit. Some keyboard shortcuts are system-wide—meaning they will either work on the OS level, or will work for almost all programs known to man (if you will excuse the exaggeration). Examples are Ctrl+C for Copy, Ctrl+P for Print, Ctrl+S for Save, etc.). Some are, for want of a better term, platform-wide (e.g., Ctrl+H for Replace in all of the Microsoft Office programs, while Ctrl+R does the same in many other programs). And some are program-specific (e.g., Ctrl+Alt+M for a comment in Word, which is Shift+F2 in Excel). If I am working in a new program, I will (after having duly saved my work), freely experiment in that program with keyboard shortcuts that I believe would work there (i.e., those keyboard shortcuts that are in the first two categories I mentioned). I’ve never had a computer meltdown as a result of this (yet!), and it gives me, so to speak, more bang for my buck for the effort of learning keyboard shortcuts.

So, how do we learn keyboard shortcuts?

Still not there yet…. Some keyboard shortcuts are mnemonic (Ctrl+B for Bold, Ctrl+I for italics, Ctrl+C for Copy—and, on that point, notice the position of the X,C,V keys on a qwerty keyboard, and remember Cut, Copy, Paste—and, in Word, Ctrl+L for Left alignment, Ctrl+R for Right alignment, and Ctrl+J for Justified alignment, etc.). Unfortunately, not all keyboard shortcuts can be mnemonic (e.g., Ctrl+C is already taken, so Centre alignment in Word becomes Ctrl+E). Obviously, mnemonic keyboard shortcuts are going to be easier to remember, but we need to go further.

So, how do we learn keyboard shortcuts?

Firstly, one or two at a time. This overcomes the first mistake I noted at the start. Sometimes I will do a handful at a time (e.g., when I discovered various commonly used number formats in Excel with Ctrl+Shift+1 (Ctrl+!), Ctrl+Shift+2 (Ctrl+@), Ctrl+Shift+3 (Ctrl+#), Ctrl+Shift+4 (Ctrl+$), Ctrl+Shift+5 (Ctrl+%), Ctrl+Shift+6 (Ctrl+^)—although I never use the last one in my line of work). But when I do teach myself a whole bunch of keyboard shortcuts, there will be some relation between them, as in the list I just showed here.

Secondly, we want to relate it to usage (thus overcoming the second mistake mentioned above). Allow me to explain, because here I am finally coming to that little discipline that I hinted at. For example, this week I learned that Ctrl+6 hides all objects in an Excel worksheet. If I didn’t get that from a list (I did, in this instance: http://www.databison.com/so-how-many-of-these-excel-shortcuts-do-you-know-punk) then I will make a note of it. Now I don’t work with objects in most of my sheets, so it may be two or three weeks before I get an opportunity to use that one. By then, I will have forgotten it. So three weeks from now, most people think to themselves:
“What was that keyboard shortcut for hiding objects again? Arrghh. Can’t remember it. Ah well, that just proves that this keyboard shortcut stuff is a load of junk. I’ll take my mouse, click Home | Find and Select | Selection Pane, and then click on Hide All.”

What I do, in contrast, is use this little discipline: I stop, go back to my list or note, look the keyboard shortcut up, and then use it. This process of using it activates a second memory channel (muscle engrams—go look it up), over and above the cognitive memory process. We all use this, we just don’t think about it. For example, I’m standing in front of the ATM to draw cash, and it’s one of those days where, for the life of me, I can’t remember my PIN number. So what do I do? Hopefully, not pull out the little slip of paper—that everyone can see!—where I scribbled it down. I know people who have had their accounts emptied in this way! No, I make as if I’m going to type the PIN, and suddenly it comes back to me. I am using my muscle engrams (the same things tennis or cricket or any other sport players use to perform those shots so masterfully) to bring back the memory of what my PIN is. In other words, the part of my brain that moves my hand to type my PIN also remembers that action—it is a second memory channel. So when I use that with keyboard shortcuts, I am actually memorising the keyboard shortcut in two different ways (cognitively and “physically”). In fact, even now, I sometimes struggle to recall what a certain keyboard shortcut is, but if I get behind the keyboard and make as if I’m going to do it, I can figure it out again.

Now sure, that process of stopping, looking up the keyboard shortcut, and using it, does take longer than just doing it with the mouse. But it is a short term loss for a long term gain. Once that keyboard shortcut is mastered, I will save a few seconds every time I use it, regaining and overtaking what I have lost in looking it up once or twice.

And one last thing. I know keyboard shortcuts are not for everyone. I must also confess that I am not musophbic (go look it up, although I am twisting the word beyond its original intention), and the mouse has its place—some things really cannot be done without the mouse (some programs, for example, are really not keyboard shortcut friendly) and some things (not many, but they are there) can be done faster with the mouse than the keyboard. Some things, of course, can only be done with the keyboard and mouse in conjunction (macros aside): Did you know that you can select one sentence in Word by holding the Ctrl key while clicking anywhere in that sentence? (This does sometimes trip over abbreviations, though.)

But keyboard shortcuts definitely do help you work faster, and keyboard shortcuts can help out in the most unlikely situations—those that you have never thought of (have you ever tried working with the touchpad of your laptop while seated in a plane going through some turbulence?). So much so that, in addition to keyboard shortcuts, I have memorised quite a few ribbon manipulations on the keyboard (for one example, Alt | JL | F | C to AutoFit a table in Word—I defy you to do it quicker with the mouse than I do with the keyboard. Sure, I could create a keyboard shortcut for that and do it seven split seconds faster, but typing that string is so quick, I hardly see the need.

The question then becomes twofold: So how many keyboard shortcuts do you know, punk? And, more importantly, how many keyboard shortcuts will you know a year from now?

Share

Harvard SA update for Word 2013

As you can imagine, it’s been a while since I’ve used the Word referencing system (having switched totally to Mendeley, as my book on the topic attests).

So it was a bit of a surprise when a student came to me and said that my Harvard SA style for Word is not working in Word 2013. It took a bit of head scratching, and then some internet searching, to get a solution. As usual, it was Yves Dhondt of BibWord who had already come to the rescue.

http://bibword.codeplex.com/discussions/404418#post956736

I have made the change he recommends, and it now works in Word 2013.

It still took a little more trying, after making his recommended modification, to find that I needed now to add the HarvardSA.XSL file to

c:\Users\Jacques\AppData\Roaming\Microsoft\Bibliography\Style\

and not (on my system):

c:\Program Files (x86)\Microsoft Office\Office 2013\Office15\Bibliography\Style\

In short, I recommend doing a complete file search for Chicago.xsl, and copying your extra style files to each place where you find that file from Microsoft.

The updated file can be downloaded from my example files page:
http://insight.trueinsight.za.com/word/dissertation/examples (or directly from here). Unzip it, and copy it to the aforementioned folders.

The new file works on Word 2007, Word 2010, and Word 2013 (tested on my system), so there’s no need for separate versions.

I must say that I am a bit disappointed (understatement!) in all this. Think about it. Microsoft creates a new tool (in Word 2007). They even encourage us to customise it for our own use:

http://blogs.msdn.com/microsoft_office_word/archive/2007/12/14/bibliography-citations-1011.aspx

http://blogs.office.com/2009/04/29/bibliography-citations-102-building-custom-styles/

Then they go and change the way the tool works, so that the customisations they encouraged us to make are rendered inactive. But they don’t post any notice about these changes, not in their online documentation (such as this page or this page or this page), not in their blogs, nowhere. This is not the kind of thing I expect form a company that charges you serious money for their product (I confess that I still feel it’s worth the money, but then they need to give the support the product’s price demands).

Ah well, ranting like that doesn’t help. But at least you can still do your thesis referencing, even if you are using Word 2013! Thanks Yves, you’re a hero!

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

Separate table (or figure) numbering for appendices—Take II

This post will only make sense after you have read the first part. If you haven’t read it yet, go and read it first, and then follow the link at its end to return here.

If you’ve used my uTIlities, then you know that I do a little programming on the side. As such, although I am by no means an expert, or even a professional, programmer, I do have sympathy for the challenges that programmers face, like, for example, the team at Microsoft responsible for Word or Excel (despite my occasional griping about things I don’t like). One of the biggest challenges I find in programming is that I cannot anticipate all the contexts and ways in which some tools might be used. For example, how will the Fit Hyperlink tool work when the hyperlink is in a table? At first, I didn’t program for that eventuality, and, of course, the first time I tried it in that context, it didn’t work.

Of course, that doesn’t necessarily only happen when we do programming. About a year ago, I wrote a piece about how to get caption numbering set up in appendices. Of course, it worked, and the post has been reasonably popular, and, I am glad, seems to have been helpful to quite a few people. And then someone contacts me and lets me know that my solution is not working. Why not? They have tables and figures in their appendices, and I only set it up for tables. My solution would work for tables, or figures, or any other caption, but only when only one of those is present in the appendix. When tables and figures appear in the appendices, then the List of Tables and List of Figures (actually, in terms of Word, the TOC for tables and TOC for figures) contains all the tables as well as all the figures.

So how to fix this?

Well, the approach I suggested previously for the numbering is sound (the problem is with the TOCs), so for the tables, use SEQ fields with the customised identifier AppendixTable and use the customised identifier AppendixFigure for the figures. But for the List of Tables (and the List of Figures), we now need to split up the process. First, add this field: { TOC \H \C “Table” }. Then, right next to it, this one: { TOC \H \C “AppendixTable” }. What we are doing, is essentially creating two TOCs for the List of Tables: The first one finds all the tables in the main document (i.e., our chapters), then the second one finds all the tables (marked with the SEQ identifier AppendixTable) in the Appendices.

There is one trade-off. When the fields are built, an extra paragraph is inserted. It doesn’t actually look that bad, though, and the break between the main tables and the appendix tables seems natural.

There are some alternatives to this, if you really don’t want the break. Firstly, you could manually mark all the tables (and figures) with TC fields (I explain how to do this in my book). This will allow you to build a single TOC for the tables and a single TOC for the figures. But this is a lot of manual work, and I want an approach that is relatively automatic.

If you want to see this in action, this sample document shows the effect.

Share

It’s here! The first book published on Mendeley

After a year’s work, I can now proudly announce that it’s here:

Mendeley: Crowd-sourced reference and citation management in the information era (ISBN: 9780620594424).

book front

This is not the first book that has ever mentioned Mendeley, but to the best of my knowledge, it is the first book in the world devoted entirely to Mendeley.

It covers everything you need to know to get on the go and make a success of your research with Mendeley.

If you are in SA, it can be ordered here.

I will update this post when it is available in South African bookstores.

Alternatively, go here:
France: http://www.amazon.fr/dp/062059442X
Germany: http://www.amazon.de/dp/062059442X
Italy: http://www.amazon.it/dp/062059442X
Spain: http://www.amazon.es/dp/062059442X
UK: http://www.amazon.co.uk/dp/062059442X
US and Rest of the world: http://www.amazon.com/dp/062059442X

Enjoy!

Share