A couple of quirky bugs…

Found a couple of funny bugs today that I thought I’d share.

First, this one:
 
Italics
 

Weird: Partially italicizing right-aligned text screws with the display of trailing spaces. But it doesn’t do the same to left-aligned text with leading spaces:
 
Italics2
 

And then there’s this one, where you start with a file that you’ve suppressed gridlines on:
 
Nada
 
…and then you add a New Window…which allows you to look at a different parts of the same file on dual monitors – or even the same monitor if it’s suitably wide, by clicking on this:
 
New Window2
 

(Aside: I never rated this feature as being particularly useful until I got a second monitor and then recently rediscovered it. Now I’m starting to think is indispensable, as I no longer have to scroll around half as much as I do when getting to grips with how spreadsheets are laid out, or when putting in new formulas that point from one region of a workbook to a completely different region of the same workbook. I’m sure Dick has been using it for years to avoid rodenting.)
 

But when you compare the new window (which Excel temporarily renamed SomeFileName:2) to the old (which Excel temporarily renamed SomeFileName:1), there’s a couple of subtle differences:
&nbps;
SideBySide

 

So it respects my wishes to not show the Formula bar, but ignores my wish to not display gridlines and headings. And if I close the original window – the one called SomeFileName:1 – then those settings I didn’t want from SomeFileName:2 are now in the file SomeFileName. Bummer.

So make sure that’s the one you close when you’re done. Otherwise your file now has gridlines and/or headings when you didn’t want it to.

More AutoHotkey Sugar

Ken Puls, the Excel Guru, sent me some nice AHK stuff and I’m trying to put a post together to share it with you. But it’s not happening so far. In the mean time, I didn’t want you to have to wait for these gems that I’ve been enjoying.

Definition and Last Position in the VBE

If you right click on a procedure call in the VBE and choose Definition, it takes you to that procedure. If you then right click and choose Last Position, it takes you back. If you’re like me, the thought of right clicking is abhorrent to you. Instead, I use the context menu key, but alas there’s a problem. It takes a few microseconds for that menu to show up. So while I press menu+D, all I’m really doing is inserting the letter ‘d’ in my procedure call.

As any good keyboard hound knows, you can use Shift+F2 and Ctrl+Shift+F2 to accomplish the task without the context menu delay. That’s great except that my sausage fingers don’t bend that way. Shift+F2 isn’t bad, but Ctrl+Shift+F2 is too much work for something I use as much as this. Without further ado

^d::
    Send +{F2}
    Return
^l::
    Send ^+{F2}
    Return

Now Ctrl+D gives me the definition and Ctrl+L takes me back. Sometimes hotkeys take a little getting used to. It’s easy to forget they’re there. This one, however, took about five seconds to become a natural part of my VBE workflow.

Switching tabs in Access

There’s so much about Access that isn’t keyboard friendly that it sickens me. But the worst is switching tabs. Ctrl+F6 cycles forward through the tabs and Ctrl+Shift+F6 cycles backward. Never you mind that Ctrl+Tab does that job in every other Windows program every released. Ever.

#IfWinActive ahk_class OMain
^Tab::
    Send ^{F6}
    Return
^+Tab::
    Send ^+{F6}
    Return

AHK’d!

Changing Field Size in Access

Speaking of crappy Access keyboarding, how about changing the field properties as you create a table? Sucks, huh? Here’s the scenario: I’m entering a field and changing the Data Type to Number. If I want a Long Integer, I’m good because that’s the default. If I want a Double, however, I have to press F6 to get to the Field Properties and change the field size to Double. Then, instead of Shift+F6 to back, I have to F6 five times to cycle through all of the windows/panes/screen areas. That’s just stupid. So

+F6::
    Send {F6 5}
    Return

That works for me because I don’t vary my Access window. If you hide your Navigation pane, or have some other configuration than me, you might have to change that 5 to something else.

Windows APIs

Ken has a nifty API generator that I’ll be posting about later. While mine is a little more pedestrian, I think it offers some advantages. I have a separate hotstring for every API that I care about. Here’s one

::declaregetopenfilename::
    SendInput !to!i{Enter}
(
+3If VBA7 Then
    Public Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
            "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
     
    Public Type OPENFILENAME
        lStructSize As Long
        hwndOwner As LongPtr
        hInstance As LongPtr
        lpstrFilter As String
        lpstrCustomFilter As String
        nMaxCustFilter As Long
        nFilterIndex As Long
        lpstrFile As String
        nMaxFile As Long
        lpstrFileTitle As String
        nMaxFileTitle As Long
        lpstrInitialDir As String
        lpstrTitle As String
        flags As Long
        nFileOffset As Integer
        nFileExtension As Integer
        lpstrDefExt As String
        lCustData As Long
        lpfnHook As LongPtr
        lpTemplateName As String
    End Type
 
+3Else

    Public Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
            "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
     
    Public Type OPENFILENAME
        lStructSize As Long
        hwndOwner As Long
        hInstance As Long
        lpstrFilter As String
        lpstrCustomFilter As String
        nMaxCustFilter As Long
        nFilterIndex As Long
        lpstrFile As String
        nMaxFile As Long
        lpstrFileTitle As String
        nMaxFileTitle As Long
        lpstrInitialDir As String
        lpstrTitle As String
        flags As Long
        nFileOffset As Integer
        nFileExtension As Integer
        lpstrDefExt As String
        lCustData As Long
        lpfnHook As Long
        lpTemplateName As String
    End Type
+3End If
)
SendInput !to!i{Enter}

That’s a big one. I type declaregetopenfilename in a module and this little honey appears. One of the advantages of this method, verbose though it is, is that the API is mostly intact in the AHK file. That makes it really easy to edit if the need were to arise. Making a new API hotstring is as easy as setting up declareapiname, pasting Jan Karel’s code, and changing a few things.

The first thing you have to change is the # signs have to be change to +3 or they won’t come across. The other thing is a little more subtle. Did you notice that I send Alt+TO, Alt+I, Enter before and after the API stuff? That turns off, then back on, the Auto Indent feature in the VBE. AHK sees all these tabs in my string and it dutifully returns them. That means that every line is indented one too many times from the line above it. Cumulatively. By the time you get to the last line, there’s 30 tabs in there.

By turning Auto Indent off and back on, I can keep my API looking clean in the AHK file, which I like.

One of these things is not like the other…

Sometimes when I copy code from the web and paste it into VBA, I get something like this:
 
Error
 

…and I’m damned if I know why. It’s not the usual culprit of incorrect quote marks. It’ something else, and I don’t know what.

Usually I just retype the offending line:
 
No error
 
…and after looking in vain for a difference, simply delete the bad one, and put it down to inexperience.

But not today. Because I’m tired of being compiled around. So today, I’m gonna find out why. And I’m going to use Excel to keep VBE honest. Conquer and #DIV/0, I say.

If I paste the two formulas into separate cells in Excel, then I can clearly see that something is amiss. I just can’t clearly see the actual something:
 
Excel 1
 

Okay, let’s get nasty, and atomise these suckers so I can compare their DNA:

=MID(A,ROW(A1:INDEX(A:A,LEN(A))),1)
=MID(B,ROW(B1:INDEX(B:B,LEN(A))),1)

Array
 

Well take a look at that…there’s the culprit:
 
False
 
No wonder I couldn’t see it:
 
Different

In this particular case, the culprit looks like Ken Pul’s Blog platform…I stole the code from a comment Jan Karel left there, and note that the code has no similar issue if I lift it directly from Jan Karel’s site.

There you go. Busted.

Makes me feel like singing:

Singing

Formulas for Sale


101 Ready to Use Excel Formulas

It’s for sale. If you pre-ordered, it’s probably on the way. If you didn’t, well, you know what to do.

I got my copy last night. My wife, who is not an Excel geek by any stretch of the imagination, is excited to read it. At 240 pages, it’s not the usual phone book sized tech book, and it seems more accessible to her. What she doesn’t realize is that it’s packed with awesome formulas and she’s actually going to learn something. That’ll show her.

After being on sale for a few hours it’s the 643,122nd ranked book in the world. Let’s see if we can get that up to 643,121.

Slicers and SlicerCaches

Behind the scenes, Excel does quite a bit of smart rationalisation in terms of SlicerCaches whenever you connect Slicers to mulitple PivotFields, and this can be a bit confusing if you’re not familiar with what’s going on. So let’s take a look-see.

First, let’s create three PivotTables all based on the same data source – meaning they all share the same PivotCache and therefore can all be connected – or ‘daisy-chained’ – together with Slicers later on if we so desire:
 
Three Pivots One Cache

Next, let’s add a separate Slicer for each of them, with each Slicer pointed at the “Item” field of it’s related Pivot:
 
Slicers and Pivots only
 
 
So that’s what we see. How does Excel see this?

Seperate SlicerCaches

There’s a couple of points to note about this diagram. Firstly, the boxes across the top are screenshots from the Report Connections dialog box:
 
ReportConnections

…which you get by right-clicking on a Slicer and selecting this:
 
ReportConnections<

And secondly, the reason I've drawn circles around those Item fields:
 
PivotField level

…is that I really want to underscore that slicers operate at the PivotField level, not on PivotTable level.
 
So where where we. Ah yes, three Pivots based on the same PivotCache, with three Slicers all pointing at the Item field of their respective PivotTable:
 
Seperate SlicerCaches

Let’s now change Slicer Two so that it also points at the Item field of PivotTable Three:
 
ReportConnections - both
 
How did that change the conceptual lay of the land?
Shared SlicerCaches2

 
Well, that looks different. Excel rationalised the SlicerCaches by ditching SlicerCache three, and now both Slicer Two and Slicer Three are connected to SlicerCache Two. Meaning that conceptually, they both point at the Item field in both PivotTable2 and PivotTable3. In fact, if you were to right click Slicer Three and look at the ReportConnecitons, you’d see it looks exactly the same as for Slicer 2, even though we didn’t touch it. And if we change the selection in one of these Slicers, we see it replicated in the other as well as in each Pivot. It’s as if those Slicers are one and the same:
One and the same
 
Interestingly, if we remove PivotTable3 from that SlicerConnections dialog:
 
ReportConnections - remove 3
 
…things don’t go back to the way before: Slicers Two and Three are still synced together, but control PivotTable2 only. PivotTable3 is completely slicer-less:
 
Remove PT3  from Slicer Two

There’s no way you can get that Slicer Three to operate independently on its own again. You’ll just have to delete it and add another, I’m afraid.

Adding Slicers Programatically

 
If you record a macro while adding a slicer, you get code like this:

ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables("PivotTable1"), "SomePivotField"). _
        Slicers.Add ActiveSheet, , "SomePivotField", "SomePivotField", 146.25, 309.75, 144, 187.5

All those arguments of the Slicers.Add command are optional except the first. And all those numbers just tell Excel where you want the Slicer, and how big you want it to be. So you could just go ahead and use this for the same result:

ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables("PivotTable1"), "SomePivotField"). _
        Slicers.Add ActiveSheet

You can actually add a SlicerCache that controls a pivot without adding a Slicer:

ActiveWorkbook.SlicerCaches.Add ActiveSheet.PivotTables("PivotTable1"), "SomePivotField"

…and then you can connect another PivotTable to it:

ActiveWorkbook.SlicerCaches("Slicer_SomePivotField").PivotTables.AddPivotTable (ActiveSheet _
        .PivotTables("PivotTable2"))

…meaning you now have an invisible slicer that keeps the pivots in sync, based on user selections from the pivot filters themselves. Spooky! Note that –

  • If you add another slicer to that same PivotField on either of those PivotTables, Excel simply uses the slicer cache you just set up, meaning the new slicer controls BOTH pivots, even though you just added it to one.
  • If you delete that slicer, Excel performs a Slicer Exorcism: it deletes the underlying SlicerCache, meaning your two pivots are no longer synced. Unspooky!

Here’s something else slightly spooky. Or rather, kooky. Normally if you delete a pivot that is the ONLY pivot that uses a particular PivotCache, Excel gets rid of the PivotCache automatically. Excel basically thinks “Well, we won’t need that crap lying around anymore”. But strangely, if you have a slicer set up for that pivot, then deleting the pivot leaves both the slicer AND the pivot cache alone. The PivotCache only gets deleted once you delete that orphaned slicer.

Well, that’s enough for today. But *HORROR* there’s some Slicer-related sequels coming to a screen near you soon:
 
Nightmare

So stay tuned. And awake.

Formulas? Pah!

Welcome back to Twice Daily Dose of Excel. Heck, these days we’re more regular than Julian Assange’s visits to the Ecuadorian Embassy!

Audrey has a table that looks something like this (if you’re American):

Table US

…or like this (if you live somewhere sensible, like in New Zealand and a good deal many other places besides):

Table English

She uses this to track when she requested a piece of information from someone, and the date she received a response. I’m picking she’s NSA, and is asking Julian – who is actually a quadruple agent – whether he needs more sun lamps. Stranger things have happened, recently.

Anyways, she’d like a formula to find the oldest/latest request date out of all responses received in a particular month.

She could array enter something like this, which will do the job without any need for helper columns:

=MAX($A$2:$A$20*(DATE(YEAR($B$2:$B$20),MONTH($B$2:$B$20),1)=DATE(YEAR($B2),MONTH($B2),1)))

…which to an Excel Pro with a lifetime of formulas under their belt would look like this:

=NOT(RocketScience)

…but to anyone else:

="Αυτό θα μπορούσε κάλλιστα να είναι γραμμένο στην ελληνική γλώσσα"

You could debate whether there’s a right formula to use in a situation like this. In fact there’s some great debate on that original blogpost as to whether one proposed solution is awesome, potentially obfuscating, or incomprehensibly mutant. So with this in mind, is there a right formula to use in this case? Depends on who’s trying to comprehend what’s happening here in 6 months time. Perhaps yourself, with six more months of grey-matter dieback under your belt. Or hat, rather. Is there a right non-formula approach to Audrey’s problem? You betcha:

Just create a PivotTable out of that sucker:

Blank PivotTable

…drag the ‘Date Requested’ field to the Rows pane and the ‘Date Received’ to the Values pane:

PivotTable

…launch the Value Field Settings dialog for the Date Requested field:

Launch Value Field Settings

…change the name of the Date Received field to something meaningful, and change Sum to Max:

Value Field Settings Dialog

…plus click on that Number Format button while you’re there so you can change the format to Date:

Number Format

…then select any cell in the Date Received column and click Group Selection from the PivotTable Tools > Analyze contextual tab, and group by Months and Years:

Group Selection

…and exhale:

Pivot

No, waithold that breath…we forgot to change the name of that ‘Years’ column that just appeared – as well as the ‘Date Received’ column that now holds Months only – to something more suitable:

Pivot finished

Now exhale.

Here’s the genius…it even works in American:

Pivot finished american

and it works on trickier problems, like the original one at that post:

Original Problem

 
¿Fórmulas? No nos hacen falta fórmulas apestosas!

Centennial Weekend Birthdays

On this episode of the BBC’s More or Less podcast, they discussed big, round birthdays that fall on a weekend. A listener said that she had to wait until her 60th birthday for it to fall on a weekend. The guy who figured out how unlucky she was tested every birthday from January 1, 1900. Since he picked that date, I assume he used Excel, but he never said.

They did include the caveat “as an adult” so that leaves off the 10th birthday. Here’s how I did the math.

I started with 1/1/1900 is cell A2 and used the formula

=A2+1

copied down to today. Then in B1:J1, I entered the values 20-100. The formula in B2 is

=WEEKDAY(DATE(YEAR($A2)+B$1,MONTH($A2),DAY($A2)),2)>=6

I added the value in row 1 to the year to make the centennial birthday and fed that into the WEEKDAY function. WEEKDAY returns 1 through 7 representing the day of the week. I used ‘2’ for the second argument so that Monday is 1 and Saturday is 6. Then I return TRUE or FALSE depending on whether the weekday is greater than or equal to 6.

Column K finds the minimum age that has a TRUE under it

=MIN(IF(B2:J2,$B$1:$J$1,""))

That’s an array formula, so I entered it with Ctrl+Shift+Enter.

Next, I repeated 20-100 in column N. These formulas complete the table

    O3        =COUNTIF($K$2:$K$41832,N3)
    P3        =O3/SUM($O$3:$O$11)
    Q3        =Q2+P3

As if that wasn’t enough, I wanted to make a single formula that could accept a date and return the earliest major birthday that was on a weekend.

=MIN(IF(WEEKDAY(DATE(YEAR(O16)+{20,30,40,50,60},MONTH(O16),DAY(O16)),2)<6,"",{20,30,40,50,60}))

That’s also an array formula, so you know what to do. I celebrated my 30th birthday on a weekend.

Dynamic Vertical Line on a ScatterPlot

GMF comments:

I use XY charts for schedules and I like to have a vertical line showing today’s date. I do this so frequently I have a named range for each of the X and Y values. To get the current date to show up as 2 similar X values the best I could come up with is:

=(ROW(INDIRECT(“1:2″))/ROW(INDIRECT(“1:2″)))*TODAY()

There must be a better way, but I can’t just put ={TODAY(),TODAY()} as a value and I’m curious why.

Until somebody smart like Charles Williams jumps in with a better explanation, the reason you can’t put individual functions into an array is that you can’t. I know, it sucks. I’d like to be able to do that too.

Edit: Colin Legg beat Charles to it:

When you use { } within a formula they are delimiters for an array constant. The formula parser won’t let you embed functions or references within an array constant because, by definition, the elements wouldn’t be constant anymore.

That said, you can get there indirectly:
=TODAY()*{1,1}
={41854,41854}

Sure Jeff, but I want to be able to put different functions in each array position.

Not a problem…with a bit of cleverness, you can do the equivalent of this:
={MIN(SomeRange),Max(SomeRange)}

…like this:
=MIN(SomeRange)*{1,0}+MAX(SomeRange)*{0,1}

…which says to Excel:

  • Populate a 2-element array with the minimum of SomeRange, but multilpy the first element by 1 and the second element by 0, in order to clear the minimum from that second element.
  • Populate a 2-element array with the maximum of SomeRange, but multilpy the first element by 0 and the second element by 1, in order to clear the maximum from that first element.
  • Add them together, leaving just the minimum in the first, and the maximum in the second.

 
 

So how does this tie in with what GMF wants to do, i.e. put a vertical line in an XY chart to show the current date?
 
Chart

Well, because this is an XY chart, you only need two coordinates to draw that date line line: the point at the bottom, and the point at the top.

So to get the X values we want – today’s date, we define a name called Today:
=TODAY()*{1,1}
And for the Y values – the Min and Max values across both ‘Values’ series – we define a name called MinMax:
=MIN(SomeRange)*{1,0}+MAX(SomeRange)*{0,1}

And then we can add a new series called Today to our chart:
 
SelectDataSource

…with the X and Y coordinates of that series pointing at the appropriate name:
 
EditSeries

Of course, you don’t actually need a Named Range to do this… you can simply have some helper cells in the actual worksheet that calculate the Min and Max values of the entire block of data, and point your Today series at that:
 
Chart - non array2

Here’s a sample file with both approaches: Todays Date on ScatterPlot

Maybe this stuff is all included in Dick and Mike’s book 101 Ready-To-Use Excel Formulas. I don’t know…I’m still waiting for my free advance copy in the post.

Well, that’s all folks. Hopefully this post moved those lines on the chart closer together rather than pushing them apart.