Thursday, August 09, 2018

Show Column in Outlook With Email Purge Date

If you're running Outlook in an Exchange environment, and the administrator has defined a Retention Policy, you can use the following kludge to configure Outlook to display a column of dates that the emails will be deleted on.  It's a kludge because the configuration doesn't actually retrieve the Retention Policy from Exchange -- it's up to you to find out what it is and then add it to the configuration.

Select your Inbox and then right click on the message list header and select Field Chooser.

Click the down arrow next to Frequently-used fields and scroll down to User-defined fields in folder.

Click New... and then enter PurgeOn in the Name box, and scroll down the Type box and select Formula.

Enter in the Formula box the DateAdd function that will return a date that's X number of days or months after the message Received date.  For example, if the Retention Policy is 18 months, enter this into the box: DateAdd("m",18,[Received]).  (The first parameter "m" specifies "Month" as the interval.  The number in the second parameter defines the number of intervals, or months in this case.  The third parameter represents the each message's received date.) Then click OK.

Drag the new PurgeOn field that you just created to the header in the position that you'd like the column to appear.

Close Field Chooser and resize the columns so that the dates in the new PurgeOn field display properly.

Note that the dates in the PURGEON column are 18 months later than the dates in the received column.

Unfortunately it seems that each folder (Inbox, Sent Items, etc.) requires its own User Defined Field. The [Received] field is appropriate for use in the Inbox. You might use DateAdd("m",18,[Sent]) in the Sent Items folder.

Again, this is a kludge. If an administrator changes the Retention Policy on the Exchange Server, your new field will ignore the change and blithely continue to show dates based on the old policy.  It may be possible to write a VBA procedure that would run on startup, retrieve the Retention Policy from Exchange and define the PurgeOn field programmatically.  Let me know if you get that working!

A subset of this solution appeared on Microsoft TechNet on 2018-08-09.

Friday, April 13, 2018

Too Much Soap – Too Much Trouble

The effect of using too much laundry detergent makes for a funny, suds-filled scene in the movies.  But the real problem occurs the next time someone does laundry.  All those suds fill the drain line causing turbulence during the rinse cycle, which leads to an overflowing filler tube and a flooded laundry room.

I’d been trying to get my wife to measure the detergent with the cap according to the label.  But she always felt the need to do it her way.  And her way has been to pour the detergent directly into the washer tub, as if adding creamer into coffee.  We’d usually get some overflow in the drain line, and suds would emerge in the adjacent shower.

It’s for this reason I would always buy the cheapest (most watered-down) detergent.  Unfortunately, one day she decided to buy some ultra-concentrated product.  The flood from repeated washings was significant.

I cleaned it up and then set up an electric heater to dry out what I couldn’t remove.  In order to plug in the heater, I unplugged the washer.  So my wife was prevented from running the washer for a few weeks.  That gave me time to snake the drain line (just in case) and run several wash loads with the correct amount of soap.  After a couple of loads, the machine drained better than it ever had before.  I left it this way for a couple of weeks.  In fact, she got mad about that – she wanted to do laundry herself.  Imagine, someone offers to do the laundry for you and you get mad because you can’t do it yourself!

Eventually she settled down enough that she was willing to show her the proper way to:
1. Read the label.
2. Pour the correct amount into the measuring cup.
3. Pour the measured amount from the cup into the tub.
4. Not rinse the cap under the running water.
5. Load the tub right away with clothes.

The last two points are also important.  If you place the cap under the gushing water, suds will form.  Also, if you don’t put the clothes in right away suds will form.  I actually add the detergent after loading all the clothes, making sure to pour it into the water farthest from the water source.

It was amazing that my wife watched and listened to me patiently while I demonstrated the correct way to do laundry.  I didn’t think it was possible.  But it happened, and it felt like my birthday, Christmas and Father’s Day all lumped into one!  Even more amazing is that she’s been doing a lot of laundry over the past couple of weeks, and we’ve had no overflowing water or subs in the shower.  Still, I’m keeping the ultra-concentrated detergent out of sight indefinitely.

Saturday, November 18, 2017

How to Use Excel Pivot Tables to Summarize Continuous Data

Suppose you have a set of data points that represent some quantity in a volume.  It could be temperature in a solid, for example.  How can you use a Pivot Table find the average temperature in a region on the solid’s surface?

Well, if you’re lucky, the region is rectangular, bounded by some Xmin and Xmax and Ymin and Ymax.  Then you can use the built-in Label Filters.

I created a set of data for this article with -20<=X<=20, -20<=Y<=20, -20<=Z<=20.  The temperature is a function of X,Y,Z using the (arbitrary) formula =2.5*X+0.25*Y^2+2.5*SIN(Z).

First, set up the Pivot Table and chose the option to Allow multiple filters per field.

In the screenshots below, we define a Region with Label Filters in the PivotTable Rows within X=[-2,8] and Y=[5,7].  The function is not dependent on the Z coordinate – we’ll use a Page Filter to constrain Z to 0.  As can be seen in the status line of the fourth screenshot below, the average temperature in that region is 16.67 degrees C.

But suppose the region is more complicated than a rectangle, or you want to summarize multiple regions?

You can define a function in VBA to “discretize” the data, as shown in the code sample, below.
Option Explicit

Function Region(rngX As Range, rngY As Range, rngZ As Range) As String
Const regA_Xmin = -2#: Const regA_Xmax = 8#
Const regA_Ymin = 5#: Const regA_Ymax = 7#
Const regB_Xmin = -6#: Const regB_Xmax = -3#
Const regB_Ymin = 5#: Const regB_Ymax = 7#
Const regC_Xmin = -2#: Const regC_Xmax = 8#
Const regC_Ymin = -5#: Const regC_Ymax = -3#
Const regD_Xmin = 9#: Const regD_Xmax = 12#
Const regD_Ymin = 5#: Const regD_Ymax = 7#
Const regE_Xmin = 16#: Const regE_Xmax = 18#
Const regE_Ymin = -15#: Const regE_Ymax = -7#
Dim x As Double, y As Double, z As Double

x = rngX.Value: y = rngY.Value: z = rngZ.Value

If x >= regA_Xmin And x <= regA_Xmax And _
   y >= regA_Ymin And y <= regA_Ymax Then
        Region = "Region A"
ElseIf x >= regB_Xmin And x <= regB_Xmax And _
       y >= regB_Ymin And y <= regB_Ymax Then
        Region = "Region B"
ElseIf x >= regC_Xmin And x <= regC_Xmax And _
       y >= regC_Ymin And y <= regC_Ymax Then
        Region = "Region C"
ElseIf x >= regD_Xmin And x <= regD_Xmax And _
       y >= regD_Ymin And y <= regD_Ymax Then
        Region = "Region D"
    Region = "NA"
End If

End Function

The function accepts three Range type parameters, each of which are intended to be the address of a single cell.  It returns one of five strings: Region A, Region B, Region C, Region D, NA.

The first four constants that are defined in the first two lines are minimum and maximum X and Y coordinates for the region that were defined in the previous example with Label Filters.  So the string “Region A” is returned if X and Y are within those X-Y intervals.

When we type in cell E2 =Region(A2, B2, C2) and then fill down, Excel calls the function for each of the cells that the formula occupies and then puts the result in that cell.  It can take several seconds for all the cells to be populated, even with an i5 processor.  And the calculation can increase the drain on the battery if you’re doing this unplugged / on the road.

But when it’s complete, you can set up your Pivot Table with the regions in the row.  The average of the temperature values for Region A is shown in cell B5 below as 16.67.

Wednesday, November 01, 2017

Introduction to CLCL Tools

CLCL is the clipboard manager that I prefer.  But it’s lacking in extensive documentation, at least for those who don’t know Japanese.  I figured out how to configure the Tools that it comes with, so I thought I’d share that with you.

This procedure assumes that you already know how to download, install and run the main program.

Download the Tools from .  There are two ZIP files ( and, as of this writing) that provide DLLs with the functions that serve as tools.  Copy the DLLs from those ZIP files into the directory in which the CLCL executable resides.  While you’re at the Nakka website, you might as well download the other ZIP files that provide ability to manage as wider range of file formats.  Copy their DLLs, too.  As of this writing, there are two:; .

Invoke Tools from the Viewer menu, then choose Tool Setup…

Click the Add… button.

In the DLL textbox, enter the path and filename of one of the tool DLLs, for example, C:\Program Files (x86)\CLCL\tool_text.dll .  If you click the Browse button, you can use a File Dialog box to navigate to and select the DLL.

Now choose one of the Tools to add.  I find &Edit to be useful, so choose that.  OK your way back out.

Now when you press your hot key to call up the clipboard list, you can right-click on a text entry, then click on Edit to open a simple editor to modify the text.  When you’re done, you can click OK to put the edited content onto the clipboard, replacing the original entry.

Use the same procedure to try out the other tools.

Tuesday, October 31, 2017

Where Should You Stick Your Function?

Let's say you need to create a VBA function to use in a spreadsheet. Where do you put it? Quick answer, put it in a module.

In Excel, press Alt-F11, Ctrl-R (to set focus to the Project Explorer). Right click on VBAProject (xls_name), where xls_name in the file name of your workbook. Select Insert Module.

A blank code window should appear and the cursor should be positioned in it.  This is "Module1" where you can enter your code, such as...

Option Explicit
Function FortyTwo(r As Range)
If r.Value = 42 Then
    FortyTwo = "Forty-Two"
    FortyTwo = r.Value
End If
End Function

If you enter 1, apple, and =6*7 in cells A1, A2 A3, here's what you get as output if you enter =FortyTwo(A1) in cell B1 and drag down to B3:

Of course, if A3 contained =6*9 you should also get Forty-Two, but that's a programming challenge for another day.

Sunday, July 02, 2017

The Dreaded Non-Disclosure / Non-Compete Agreement

If you work in the USA tech industry, you've probably been asked to sign a non-disclosure / non-compete agreement with a new employer.  It may be that even your current employer asked you to sign a new one.

If you're not familiar with it, let me describe it for you in a nutshell: Anything you witness or produce on company time becomes the property of your employer.  Even if you think of something, if you thought of it on company time, that idea is your employer's property.  This explains a lot of what I've observed during my career -- many coworkers are so poignantly aware of this that the second they clock in, they empty their minds completely of thoughts and go about their duties thoughtlessly.

If asked to sign a non-disclosure / non-compete agreement, what should you do?  This ebook from IEEE should help:

Good luck!