Conditional Formatting Icons with Relative References

This stack overflow question is intriguing. The way icon sets works is that you select a range and each cell within that range is evaluated against the other cells in that range (or a hardcoded number). The percent or value you set can be a cell reference, but not a relative cell reference. Let’s look at an example. Here are 24 numbers over two years. I want an icon in all the 2015 cells that shows how it compares to the prior year.

I set up a CF for B14 that looks at B2, but I can’t make B2 relative. It has to be absolute. Look at 7/31/2015. It’s less than 7/31/2014, but still shows an up arrow because it’s being compared to B2.

If I copy this down to the other months, the B2 remains – that’s how absolute works. If I copy B14’s formatting down to all the cells at once, I get two CF rules: one for B14 and one for B15:B25. If I copy the CF down one cell at a time, I get 12 CF rules, but they still all point to B2.

No problem. I’ll use a little OFFSET trickery. I select B14:B25 and make a rule that says

The relevant formula is =OFFSET($B$2,ROW()-14,0,1,1). You wants absolute references? I gots absolute references. No dice (I put some edge cases in there and copied the 2014 numbers down so I could see what was happening).

That should work, but it doesn’t. Instead of doing it to the whole range at once, I did that same CF to B14 only, then copied it down one cell at a time.

Et voilà! What a pain.

Editing SQL Statements in External Data Queries

Surprisingly, I’ve been using the SendKeys macro from this post quite a bit. SendKeys is dangerous, as I’ve said, but I like to live on the edge. Jan Karel commented that I should use Alt-DDE, which gives me the Command Text box to edit the SQL query, but doesn’t give me the opportunity to change the name of the Connection. As I thought about it more, changing the Connection name happens one time and isn’t really the major source of my frustration. In fact, if I were a little more disciplined I could change the name when I setup the Connection in the Friendly Name box.

Then it’s settled. I’ll use Alt-DDE to edit the SQL and I’ll force myself to set the name when I set it up. But wait. One of the things I was really looking forward to in building my own Command Text box was making it bigger by default so I could see the whole SQL string (or at least most of it). The Alt-DDE textbox is only slightly better than the Connection properties Command Text textbox. See for yourself.

That’s a crappy UI. And that’s from someone who spends a lot of time in the Visual Basic Editor.

Then it’s settled. I’ll build my own form for changing the properties I want to change. It’s what I really wanted to do anyway, so why stop lying to myself. What kind of features should I build into this UI? A big textbox is a must. Also, I’d like to be able to add white space and line breaks. Oh, and if I could have SQL parsing, autoformatting, and autocomplete… So basically what I want is SQL Server Management Studio. I already have that. It’s called SQL Server Management Studio. That lead me to my next bit of genius. If I want to edit the SQL, even only a little, I should do it in SSMS. I added a couple of buttons to the Ribbon.

The Copy button copies the SQL to the clipboard, ready for me to paste into SSMS.

Public Sub CopySql()
    Dim doClip As MSForms.DataObject
    Dim qt As QueryTable
    On Error Resume Next
        Set qt = ActiveCell.ListObject.QueryTable
    On Error GoTo 0
    If Not qt Is Nothing Then
        Set doClip = New DataObject
        doClip.SetText qt.CommandText
    End If
End Sub

I leave the button enabled and check to make sure a QueryTable exists in the procedure. If I wanted to enable/disable the button, I would need to run a SelectionChange event constantly. I didn’t test it, but it seems like too much overhead. The Paste button looks like this

Public Sub PasteSql()
    Dim doClip As MSForms.DataObject
    Dim qt As QueryTable
    Dim sOld As String
    On Error Resume Next
        Set qt = ActiveCell.ListObject.QueryTable
    On Error GoTo 0
    If Not qt Is Nothing Then
        sOld = qt.CommandText
        Set doClip = New DataObject
        qt.CommandText = doClip.GetText
        doClip.SetText sOld
    End If
End Sub

I added one little safety step in here because I know how I am. I take what’s in the clipboard and insert it into the CommandText property. But I put the previous CommandText in the Clipboard when I’m done. That way, when I get distracted and accidentally put something else in the Clipboard before I paste, I can (relatively) easily revert back to what it was.

I’ll give this a try and see how it goes.

One unsolicited plug: I use Red Gate’s SQL Prompt in SSMS. I can’t imaging having to work in SSMS without it. It’s pricey, but if you’re spending any time in SSMS, you should give it a try.

Happy Gerbitz Day

This is the first year you can sing “Happy Birthday” to Excel without having to pay a royalty, so that’s nice.

Here’s my uninteresting Excel story: My first spreadsheet program was SuperCalc. I remember we had orange screens on our PCs. Eventually we graduated to VGA monitors and Lotus 1-2-3 v.1A. I stuck with that version for a long time. I had tons of keystroke macros – whatever the heck those were called in Lotus – and I wasn’t giving them up. Excel burst onto the scene and I barely blinked. I don’t need that fancy new stuff. I used v.1A until 1992 and I think 1-2-3 was on v4 by then.

In 1992, I miraculously got a job at KPMG (nee Peat Marwick). Apple was a client of KPMG, so everyone got a Mac and used Office. It was like living in hell. I was a PC, Lotus 1-2-3 guy and I was forced to use these toys in business. Over time, I got used to my Mac Plus, then my SE30, then my PowerBook. And, of course, I got used to Excel and its obviously superior features to the version of 1-2-3 I was using. I don’t remember what version of Excel that was, I just know that most people in my office sucked at using it. Thankfully 25 years later, every office worker is, at a minimum, competent at Excel. What? That’s not true, you say? There are still people who work with Excel and aren’t competent? What the hell have they been doing for the last 25 years? It’s not like learning Excel is exactly cutting edge. End rant.

Being forced to use Office was probably a pivotal point in my life. (Using Macs in an accounting firm in the early ’90s was just stupid.) Pivotal though it was, I think what really turned me into an Excel geek was an intranet message board that KPMG had. I think they called it the KPMG Knowledgebase, but my memory isn’t so good. I could go on the message board and answer people’s questions about Excel. And I was hooked. Then it was on to newsgroups (nntp), a blog, and the crazy post-Microsoft newsgroup period that has mostly meant for me. What the heck did I do after MS closed the newsgroups but before SO? I don’t remember. I know I visited once, saw a terrible answer from a moderator, and saw that the moderator had selected his own answer as “the” answer. I haven’t been back.

When I first started on the newsgroups, I was more of an Access guy than an Excel guy. I was surely answering more Access questions than Excel at the beginning. It was when I started reading Chip Pearson, Rob Bovey, Stephen Bullen, and others posting about VBA that the tables turned. I realize that Access has VBA, but the Excel object model was, and is, a thing of a beauty. I still do plenty of Access work, but it pales in comparison to the time I spend in Excel.

Other random memories:

  • At my first MVP Summit, everyone thought I was going to be a 60-year-old guy and I was in my mid-thirties. I guess I came off as cantankerous mature in my newsgroup postings.
  • I remember after a year of DDoE, a bunch of fellow Excellers joined as authors. There were some great posts back then.
  • I remember applying for a job and taking an Excel and an Access test. I aced them both. The secretary was looking at me like I was a witch. (If you’re reading this blog, you could ace them too.)
  • I remember planning an Australian Excel conference over beers and actually going through with it. If I had a nickel for every plan I made over beers that came to fruition, I’d have a nickel.

Happy birthday Excel!

September 30, 1985 Excel was launched and I therefore wish Excel a very happy birthday and many healthy years to come!

Debra Dalgleish over at the contextures blog has gathered a nice set of stories on how people first “met” Excel.

To celebrate this great event, I’m offering a 30 percent discount on my products for 30 days.
Redeem your discount on RefTreeAnalyser and The Excel File Remediation tool by entering this coupon code: EXCEL30

So have a piece of cake with your coffee today and have one of those “those were the days” moments.

Jan Karel Pieterse

Connection Properties of External Data Ranges

I have a workbook with several connections to SQL Server. When I need to change the SQL statement, I do that in Connection Properties.

I added a command to the QAT to show the connection properties dialog, but there’s something I don’t like about it. If I’m in a table with a connection, it’s pretty likely that I want to see the properties of that particular connection and not just a list of all connections. Of course I’m awesome at naming my connections so I don’t have to guess which is which, but if you weren’t so awesome you might have trouble distinguishing them.

The long-term answer is to write my own interface to change the things I want to change. But in the mean time, I want to open the connections dialog and highlight the connection related to the table I’m in, if any.

Public Sub ShowConnection()
    Dim qt As QueryTable
    Dim sConName As String
    Dim i As Long
    On Error Resume Next
        Set qt = ActiveCell.ListObject.QueryTable
    On Error GoTo 0
    If Not qt Is Nothing Then
        sConName = qt.WorkbookConnection.Name
        Application.CommandBars.ExecuteMso "Connections"
        Application.Wait Now + TimeSerial(0, 0, 2)
        For i = 1 To Len(sConName)
            SendKeys Mid$(sConName, i, 1)
        Next i
        Application.CommandBars.ExecuteMso "Connections"
    End If
End Sub

When I open the Connections dialog, I can start typing the name of the connection to get down to it. For example, I could start typing “dup” and it will highlight the first connection that starts with those keys.

With SendKeys, I can type the entire name. First I see if the ActiveCell is in a QueryTable. If it’s not, I just open the dialog. If it is, I open the dialog, wait a couple seconds, then send all the keys in the connection’s name. SendKeys can be very dangerous, but we’re just experimenting here.

What the above code actually does is open the Connections dialog, wait for it to close, then send all those keystrokes into the ActiveCell. Dangerous. And not helpful. Apparently the Connections dialog is modal and all code is suspended until it’s closed. I did a little searching and found this command, which does not help.


Maybe the old CommandBars behave differently than the Ribbon.

Application.CommandBars.FindControl(, 11205).Execute

Nope. Same as ExecuteMso. One last try. This opens the dialog with SendKeys.

        sConName = qt.WorkbookConnection.Name
        SendKeys "%ao"
        Application.Wait Now + TimeSerial(0, 0, 2)
        For i = 1 To Len(sConName)
            SendKeys Mid$(sConName, i, 1)
        Next i

And it works. For some reason sending Alt+A+O opens the Connections dialog modeless, the SendKeys executes, and takes me to the “active” connection. I have a couple of applications on my machine that like to steal the focus, so I try to avoid SendKeys whenever I can (which is always). In this code, I’m using it twice, so I won’t be using it all. Interesting, though, that it seems to be the only way to get what I want.

Along the way, I discovered I could get to the “active” connection’s property sheet with this key sequence:

  1. right-click key
  2. b
  3. a
  4. tab
  5. tab
  6. enter

I guess that will work. It’s a lot of keystrokes, though.

MaxMinFair Rewrite

I read Charles William’s MaxMinFair algorithm and I didn’t like his approach. That’s typical. I’ll read somebody’s code and think “They’re making that too hard”. Then I’ll set about rewriting it. In this case, as in most cases, it turns out that it is that hard, but I wasn’t going to convince myself until I tried it. I ended up with a different approach that’s not shorter, not easier to read, and not easier to follow. Oh well, here it is anyway.

Function MaxMinFairDK(Supply As Double, Demands As Variant) As Variant
    Dim dPrior As Double
    Dim vaReturn As Variant
    Dim dAvailable As Double
    Dim i As Long, j As Long
    Dim dTemp As Double
    Dim wf As WorksheetFunction
    On Error GoTo ErrHandler
    Set wf = Application.WorksheetFunction
    If IsObject(Demands) Then Demands = Demands.Value2 'make range array
    dAvailable = Abs(Supply) 'ignore negative supplies
    If Not IsArray(Demands) Then
        'One demand = min of supply or demand
        MaxMinFairDK = Array(dAvailable, Demands)(Abs(dAvailable > Demands))
        'Excel returns NA when you use too many columns
        If UBound(Demands, 2) > 1 Then Err.Raise xlErrNA
        'Assume everybody gets everything they want
        ReDim vaReturn(LBound(Demands, 1) To UBound(Demands, 1), 1 To 1)
        vaReturn = Demands
        For i = UBound(Demands, 1) To LBound(Demands, 1) Step -1
            'If there's enough, do nothing except reduce what's available
            If dAvailable / i > (wf.Large(Demands, i) - dPrior) Then
                dAvailable = dAvailable - ((wf.Large(Demands, i) - dPrior) * i)
                dPrior = wf.Large(Demands, i)
                'Once there's not enough, everyone splits what's left
                For j = LBound(Demands, 1) To UBound(Demands, 1)
                    If Demands(j, 1) > dPrior Then
                        vaReturn(j, 1) = dPrior + (dAvailable / i)
                    End If
                Next j
                Exit For
            End If
        Next i
        MaxMinFairDK = vaReturn
    End If
    Exit Function
    MaxMinFairDK = CVErr(Err.Number)
    Resume ErrExit
End Function

In Charles’s implementation, he allocates an equal amount of the supply to each node, then takes back what that node didn’t need and puts it back in the available pool. When I was looking at the results, I was thinking that the smallest n nodes simply get their demand and only when there’s not enough to go around do we need to do something different than allocate the full demand.

In my implementation, I start by giving everyone what they demand. Then I start with the smallest demand, and if I can accommodate that amount for everyone, I just reduce the amount available and move to the second smallest demand. At some point (the sixth smallest demand in Charles’s data) I can’t meet that demand and still give everyone an equal share. At that point, I give anyone who hasn’t had their demand met an equal amount – the amount that’s already been distributed plus an equal share of what’s left.

Rank Demand Incremental Demand Allocated Remaining
7 0.70 0.70 4.90 13.40
6 1.00 0.30 1.80 11.60
5 1.30 0.30 1.50 10.10
4 2.00 0.70 2.80 7.30
3 3.50 1.50 4.50 2.80
2 7.40 3.90 7.80 (5.00)
1 10.00 2.60 2.60 (7.60)

In the first iteration, I hand out 0.70 to everyone because I have enough supply to do that. In the second iteration, I had out the differential, 0.30, to everyone who’s left because I have enough supply remaining. When I get to #2, I can’t hand out 3.90 to the remaining two nodes because I don’t have enough supply. I’ve allocated up to 3.5 to anyone who’s demanded it, so the last two get the 3.5 plus half of the 2.8 that remains.

Although I didn’t accomplish anything, it was still a fun exercise.

From True and False to Yes and No

I’m writing some code to turn the contents of class modules into an XML file for Affordable Care Act compliance purposes. The XML file spec says that my flag for whether the dependent is a spouse is “Y” or “N”. In my class, I have a Relation property that can be “Son”, “Daughter”, or “Spouse”. I made a new property to return the “Y” or “N”.

Public Property Get IsSpouseXML() As String
    If Me.Relation = "Spouse" Then
        IsSpouseXML = "Y"
        IsSpouseXML = "N"
    End If
End Property

I hate writing all those lines to convert a Boolean into something else. I know it’s not that big of a deal, but it just bugs me. So I fixed it.

Public Property Get IsSpouseXML() As String
    IsSpouseXML = Split("N Y")(Abs(Me.Relation = "Spouse"))
End Property

Now that’s fancy. The comparison is made and the True or False is converted to a Long via the Abs() function (to turn True to 1 instead of -1) and the proper element of the array is selected. It’s still not good enough.

Public Property Get IsSpouse() As Boolean
    IsSpouse = Me.Relation = "Spouse"
End Property

Public Property Get IsSpouseXML() As String
    IsSpouseXML = Split("N Y")(Abs(Me.IsSpouse))
End Property

Yeah, that’s better. But it’s so specific to spouses. Spouse is a dependent that gets special attention, so I don’t mind having a dedicated property to it. It’s appropriate for the domain, I think. But if I wanted to really generalize the hell out of it, I might make an IsRelation property and then take my conversion property into a function.

Public Property Get IsRelation(ByVal sRelation As String) As Boolean
    IsRelation = Me.Relation = sRelation
End Property

Public Function ConvertBool(bValue As Boolean, vArr As Variant) As String
    ConvertBool = vArr(Abs(bValue))
End Function

Now I can have complete customization of the return string.

Public Sub TEST_IsSpouse()
    Dim clsDep As CDependent
    For Each clsDep In gclsEmployees.Employee(4).Dependents
        Debug.Print ConvertBool(clsDep.IsRelation("Spouse"), Array("Not so much", "Of course")), clsDep.Relation
    Next clsDep
End Sub

Warning: Special Cells slows to a crawl across multiple columns

Further to my previous post on Goto Special/Blanks, it looks like there is a pretty serious problem with the SpecialCells method if you use it on more than one column.

That’s because it’s blindingly fast at finding things in one column, but painfully slow in finding things in any other columns. Which is a problem, because anyone who’s anyone knows that using SpecialCells is supposed to be much more efficient than looping through a big range. Well, Mr and Mrs Anyone may want to reconsider using the loop on multiple columns. Or rather, they might want to consider looping through the columns, and use SpecialCells on each one individually. Best of both worlds.

So how fast is it on one column? And how slow on multiple columns? Let’s find out.

Here’s your test code.

Sub TestingTestingIsThisThingOn()
Dim TimeTaken As Date
Dim rng As Range
Dim Jeff As AboveAverage

TimeTaken = Now()
Set rng = Range("A:B")
rng.SpecialCells(xlCellTypeConstants, xlTextValues).Select

TimeTaken = Now() - TimeTaken

Debug.Print "UsedRange:" & vbTab & ActiveSheet.UsedRange.Address
Debug.Print "Range Searched:" & vbTab & rng.Address
Debug.Print "Time Taken:" & vbTab & Format(TimeTaken, "HH:MM:SS") & " seconds."
Debug.Print "Cells: " & Selection.Cells.Count & vbNewLine & vbNewLine
End Sub

Fill A:A with some text, such as “Blindingly Fast”, and run that sub. Here’s what I get:

UsedRange: $A:$A
Range Searched: $A:$B
Time Taken: 00:00:00 seconds.
Cells: 1048576

Now fill B:B with “Painfully Slow”, and run it again. Here’s what I get:
UsedRange: $A:$B
Range Searched: $A:$B
Time Taken: 00:02:02 seconds.
Cells: 2097152

What the…? So it took a blindingly fast 1 second to process one column, and a painfully slow three minutes to process two? That’s about as linear as my life story before I met and married Excel 2000. (My first wife. I still see her round from time to time in the forums. She hasn’t aged gracefully.)

So either there’s something SpecialCells doesn’t like about the number of columns, or there’s something it doesn’t like about the number 1048576. Let’s find out.

If you delete rows 524288 down, then you’re left with 2 columns of text totaling 1048576 cells. Here’s what I get if I run the code on that:

UsedRange: $A$1:$B$524287
Range Searched: $A:$B
Time Taken: 00:00:57 seconds.
Cells: 1048574

It took one minute to process this, as opposed to the one second it took to process the same amount of cells in one column. So it seems that it’s the multiple columns aspect that is causing the issue, and that the extra time incurred is dependent on the number of cells in that 2nd column. In fact, if I fill those blank cells in column A with our text (while leaving half of Column B with blanks) and rerun the code, then it doesn’t take Excel any longer to process those extra half million cells:

UsedRange: $A:$B
Range Searched: $A:$B
Time Taken: 00:00:52 seconds.
Cells: 1572863

So it seems the issue is in how Excel is handling the union of all those cells it identified in the first column with any cells it identifies in the second. And given how slow it is, I wouldn’t be surprised if it’s unioning those cells one at a time.

The same thing goes for blanks, by the way. This turned up in the comments of Doug Jenkin’s latest post.

Moral of the story: Do NOT use SpecialCells on more than one column at a time.

Question: How are you supposed to bring this stuff to MS’s attention, apart from posting it somewhere conspicuous like here?


Over in the comments of his blog, Doug Jenkins hypothesizes:

A possibility is that a single column is treated as being a single array with 1 million values, but two columns are treated as being 1 million arrays, each with 2 values. When the operation is done on the spreadsheet with F5-Special, the name box shows that it works row by row, rather than all of Col A then all of Col B.

I think he’s right. And also after further testing, I realize that those times are down to how long Excel took to search all these arrays, and not to do with how long it took Excel to union the results. Because there weren’t that many results to union in my simple example, so it took practically no time at all.

But if you set up the data like so:
Every 2nd blank
…then you see that the actual unioning takes a heck of a lot longer than the searching. For instance, as per above it took nearly two minutes for SpecialCells to search 1 million rows across two columns and identify a handful of blank areas. Whereas from the below table, you can see that with the greatly increased number of blanks that it’s got to union, it took over two minutes to union just 45,000 cells in one column.