Conditional Formats Manager

I’ve been rewriting some user spreadsheets.

One thing I noticed after a while was that there isn’t an easy way to manage Conditional Formats.
As you may know, Conditional Formats use formulas too, so they can be very important to get right – especially if these cells are relied on to highlight errors.

Excel provides some limited ways to manage Conditional Formats.
There’s the editor itself: from the Format menu, Conditional Formatting…
and then there’s the Goto feature: from the Edit menu, Go To…, Click Special…, Select ‘Conditional Formats’ (you then have a choice between All or Same).

Those can be challenging to use if you’re dealing with a sheet full of various formats.

I put together a userform to list all of the Cells which contain Conditional Formats.
If groups of Cells contain the same Conditional Format then they appear in the same list item.

Click a List Item to select the cells.
Doubleclick a List Item to edit the Conditional Format.

I’ve not provided an XLA add-in – I’ll leave that to you. Here are instructions for building the userform.

Create a new Userform and drop a ListView control onto it. Rename the ListView control: lvwAddress
(Don’t have a ListView control in your Toolbox? Right-click in the Controls area, select Additional Controls…, tick Microsoft ListView Control)
Open the code for the Userform and drop the following code in:

Private col As New Collection
 
Private Sub UserForm_Initialize()
    Const cCaption = “Conditional Formats”, cKey = “KeyID”
 
    Dim bln As Boolean, str As String, i As Long
    Dim rngAll As Range, rng As Range, rngSel1 As Range, rngSel2 As Range
 
    Me.Caption = cCaption
 
    On Error Resume Next
    Set rngAll = ActiveCell.SpecialCells(xlCellTypeAllFormatConditions)
    On Error GoTo 0
 
    If Not rngAll Is Nothing Then
        i = 1
        For Each rng In rngAll
            Set rngSel1 = rng.SpecialCells(xlCellTypeSameFormatConditions)
            str = rngSel1.Address
            bln = False
            For Each rngSel2 In col
                If str = rngSel2.Address Then
                    bln = True
                    Exit For
                End If
            Next
            If Not bln Then
                col.Add Item:=rngSel1, Key:=cKey & ” “ & i
                i = i + 1
            End If
        Next
 
        With lvwAddress
            .ColumnHeaders.Add Text:=“Address”, Width:=.Width – 17
            .View = lvwReport
            .FullRowSelect = True
            .HideSelection = False
            .LabelEdit = lvwManual
 
            For i = 1 To col.Count
                .ListItems.Add Text:=col(i).Address(False, False), Key:=cKey & ” “ & i
            Next
 
            .Sorted = True
        End With
    End If
End Sub
 
Private Sub lvwAddress_ItemClick(ByVal Item As MSComctlLib.ListItem)
    col(lvwAddress.SelectedItem.Key).Select
End Sub
 
Private Sub lvwAddress_DblClick()
    Application.Dialogs(xlDialogConditionalFormatting).Show
End Sub

You would run the userform with this statement:

    Userform1.Show

It’s interesting to note that some simple tweaks to the above code would provide you the same management of Data Validations:
1. Change the value for the Const cCaption
2. Change xlCellTypeAllFormatConditions to xlCellTypeAllValidation
3. Change xlCellTypeSameFormatConditions to xlCellTypeSameValidation
4. Change xlDialogConditionalFormatting to xlDialogDataValidation

Worksheet Legend

I’ve been recently using the great guides from (what used to be) SpreadsheetStyle.com.
I’m a real fan of the 3 crayon rule.

I see other Excel users with a rainbow of colours on their sheets. I ask them “why all the colours?” and they reply “see, the red cells are errors and the green cells do this and the grey cells do that and… well… noone else uses this spreadsheet so only I need to know what the colours mean”.

Only they need to know! Until they get another job. Why were those cells highlighted again?

Here is where it’s nice to have a worksheet legend:

Leaving some cells dedicated to a Legend can be a little cluttered, so here is a way to put the legend in as a cell comment.

I used an Excel add-in for exporting my range as a picture. That saves me from writing a macro to do the same.
There are a few add-ins which can do this. I use Andy Pope’s Graphics Exporter

Highlight your range, say, A1:D15
From the Tools menu, select Graphics Exporter.
Note the Output folder and click Export.

Click a blank cell. From the Insert menu, select Comment.
Delete the text in the new comment box.
Notice how the border style is diagonal lines. Click it and it changes to a dotted border style.
From the Format menu, select Comment.
The Format Comment window appears
From the Colors and Lines tab, click the Fill Color dropdown box and select Fill Effects.
The Fill Effects window appears
From the Picture tab, click Select Picture.
Browse to where you exported that picture, select the file and click Insert.
Tick the ‘Lock picture aspect ratio’ box.
Click OK then OK again.

Depending on your comment box defaults, you’ve probably got what appears to be a tiny picture in the comment box.
You can resize it to whatever dimensions you like, but here is a tip to get your comment box matching the dimensions of your picture.
(This will only work if you ticked the ‘Lock picture aspect ratio’ as described earlier)

Resize the comment box so that it is quite tall.
Nudge the width wider and wider until it starts scaling the picture.
The point where it starts scaling the picture is where both the picture and comment box dimensions match.
Then, with the Shift key held down, drag the bottom-right handle so it resizes both height and width at the same time.
(The Shift key maintains the width/height ratio)

In the end you should end up with something like this:

Wind Direction Chart

My workmate has recently got me into going for lunchtime runs around the Refinery.
Running on sand is hard. As a person who has only ever run on the road, I found it very difficult to adjust.
I haven’t quite picked when yet, but sometimes the sand is really soft. It’s like running in quicksand.

After work, I decided to knock up a few Excel Charts to give me more information before my run.
It’s not really going to change my habits, but I feel easier knowing what I’m in for. OK, maybe I like a tailwind.

I’ve got realtime data for Tide Height, Wind Speed and Wind Direction.

Tide Height and Wind Speed I’ve done as standard Line Charts, but Wind Direction didn’t look quite right as a Line Chart so I decided to do something different.

15 minute averages extend for about 24 columns – 6 hours worth.

The formulas – a bit of Trig for the end point coordinates.
B6: 0
B7: =COS(B4/180)
B8: 0
B9: =SIN(B4/180)

The charts you see in the snapshot are Scatter Charts.

Steps to create a chart:

    Select B8:B9. Hit the AutoChart button
    Select the “Scatter with data points connected by lines without markers”. Click Next
    Select the Series tab. For X Values select B6:B7. Click Finish
    Select the Legend box and hit the Delete key to destroy it
    Doubleclick the Y Axis to bring up the “Format Axis” window. Select the Scale tab
    Set Minimum to -1 and Maximum to 1. Click OK. Hit the Delete key to destroy the Y axis
    Repeat for the X Axis
    Select the Gridlines and hit the Delete key to destroy it
    Doubleclick the Plot Area to bring up the “Format Plot Area” window
    Set the border to None and the Area to None. Click OK
    Doubleclick the line. Set the Line Color to Black. Click OK
    The line should still be selected. Note that two blobs are at the ends of the line.
    Click the blob near the center of the chart. Then, doubleclick the blob
    In the Marker section, set the Style to Circle, the Foreground to Black and the Size to 5. Click OK

    Now you need to resize the chart so it fits in the width of one column. The top-left of the chart should start in cell B11.
    This takes some fiddling since the Chart Area doesn’t want to play nice with the Chart Object which contains it.
    Set the Zoom to 200% if it helps (but make sure you set it back to 100% afterward!)

I didn’t sit there adding all of the charts, I just created the left-most one and copied them across.
But, it’s not quite as simple as a copy/paste. A normal copy/paste would have all the charts still looking at column B.

Charts can not be relative.
That is, you can’t move the chart over a column and expect it to now look at column C.
Ranges do it fine, you just remove the $ and that’s relative.
It appears that charts can’t use Relative References. It’s Absolute References only.

Here is why VBA is my friend.
While copying the original chart, it finds the column part of the Chart formula and adjust it to look at the destination column.

Make sure the Chart is selected, then run this macro:

Sub CopySelectedChartOver()
    Const cColumns = 23
 
    Dim i As Long, cob As ChartObject
    Dim str As String, strCol As String, lngCol As Long
 
    Select Case TypeName(Selection)
        Case “PlotArea”, “ChartArea” ‘ensure the chart has been selected first
           Set cob = Selection.Parent.Parent
            strCol = cob.Chart.SeriesCollection(1).Formula
 
            i = InStr(1, strCol, “$”) ‘look for the column part of the =SERIES() formula
           If i > 0 Then
                strCol = Mid(strCol, i + 1, 2) ‘ take the AB out of “$AB123:…”
               If Not (Right(strCol, 1) >= “A” And Right(strCol, 1) <= “Z”) Then strCol = Left(strCol, 1)
                lngCol = Range(strCol & “1”).Column
 
                For i = 1 To cColumns
                    cob.Copy
                    cob.TopLeftCell.Offset(, i).Select
                    str = Split(Columns(lngCol).Offset(, i).Address, “:”)(0) ‘get the “AC” bit from a column number
                   ActiveSheet.Paste
                    With Selection.Parent.SeriesCollection(1)
                        .Formula = Replace(.Formula, “$” & strCol, str) ‘replace the old $AB with $AC
                   End With
                Next
            End If
 
        Case Else
            MsgBox “The source chart must first be selected”, vbExclamation, “Selection Error”
    End Select
End Sub

Finally, you can pretty it up by hiding the Coordinates. Set the Number Format for Rows 6:9 as ;;; (3 semi-colons)

Pattern Matching

I’ve just recently had a task where I had to do table lookups by a unique Code.
It was a pretty special sort of Code though. The first character has special meaning and the last two characters have special meaning.

I had the option of creating a complicated mapping table, but then I recalled my unix days… Regular Expressions!
Regular Expressions are great. They provide syntax for advanced string pattern matching.

In this example I want to find the Code which starts with a digit and ends with the letter J followed by the number 9.
The text in cell A3 is the pattern which matches what I’m looking for. In this case, that pattern matches the Code 9SGJ9.
I could have used ^(S|8)YRUP$ to match 8YRUP (or SYRUP if it were in the list of Codes).
The pattern matching syntax is sophisticated. The documentation is really worth a look.

The formula in cell B3 is:
=INDEX(B6:B15, RegMatch($A$6:$A$15, $A$3))

RegMatch is a User Defined Function in VBA for handling the pattern matching.
Regular Expression functions are already provided by Microsoft’s VBScript library so I just wrote a wrapper around it and called that RegMatch.

You’ll need to reference “Microsoft VBScript Regular Expressions”.
On my machine it’s version 5.5, but a later version is available for download from Microsoft’s website.

Dim reg As New RegExp
 
Public Function RegMatch(Source As Range, Pattern As String, Optional IgnoreCase As Boolean = True, Optional MultiLine As Boolean = True) As Long
    Dim rng As Range, i As Long, j As Long
 
    reg.IgnoreCase = IgnoreCase
    reg.MultiLine = MultiLine
    reg.Pattern = Pattern
 
    i = 0: j = 0
    For Each rng In Source
        i = i + 1
        If reg.test(rng.Value) Then
            j = i
            Exit For
        End If
    Next
    RegMatch = j
End Function

As I’m writing this post I’ve been looking on the Internet for other examples.
A great newsgroup post by Harlan Grove offers similar wrappers around the scripting library.

The documentation for VBScript Regular Expressions is available from Microsoft’s website. Personally, I prefer the offline version.

Multicolumn Sum with Wildcard

It is possible to sum a column of values on the condition of other column’s values by using the SUMPRODUCT function.
This is much the way SUMIF works, but on more than one criteria column.

SUMIF has the ability to perform criteria matching with Wildcards, something that SUMPRODUCT isn’t built to do.
The following describes how to do Wildcard SUMPRODUCT.

In this example, I use three columns: Place, Season and Value.
I have four named ranges:
P_Rows
=COUNTA(Sheet1!$A:$A) – COUNTA(Sheet1!$A$1:$A$6)
P_Place
=OFFSET(Sheet1!$A$6, 1, 0, P_Rows)
P_Season
=OFFSET(Sheet1!$B$6, 1, 0, P_Rows)
P_Value
=OFFSET(Sheet1!$C$6, 1, 0, P_Rows)

Just taking a sidestep for a moment to discuss the non-wildcard method:
Imagine that the Place in cell A3 is Auckland.
The formula in cell C3 could be:
=SUMPRODUCT(($A$3 = P_Place) * ($B$3 = P_Season), P_Value)

But that would not give you wildcard ability.

The formula, which was used in the screenshot, is as follows:
=SUMPRODUCT(ISNUMBER(SEARCH(C0 & $A$3 & C0, C0 & P_Place & C0)) * ($B$3 = P_Season), P_Value)

SEARCH returns a number when there is a match, #VALUE! when there is no match. ISNUMBER converts this to TRUE/FALSE.
The act of multiplying Booleans together results in 1 or 0, which in turn SUMPRODUCT multiplys with P_Value and Sums.

You might now ask “what is that C0 & $A$3 & C0 about?”
It’s used to match the whole value, not just a partial match.
Consider the effect if it were just ISNUMBER(SEARCH($A$3, P_Place)). A Place called Cromwell would also be a positive match for Well*

C0 is a named range with the formula:
=CHAR(31)
Ascii character 31 is not likely to appear in your list of Places.
You could just embed CHAR(31) in the formula – I’ve chosen to use a named range just to reduce the formula length.

Unique Values Advanced Filter

One of Excel’s features which I use frequently is the Advanced Filter – Unique Values.
It’s pretty handy for analysing data and building summary / master tables.

From Excel’s menu: Data > Filter > Advanced Filter…

Copy to another location = selected.
List range: Select the list of values you want to see unique. Include the heading.
Copy to: Select a single cell. You can only copy filtered data to the active sheet.
Unique records only = ticked.

Update:
“Copy to” must be the active sheet but “List range” may be another sheet.
Read the comments for more info. Thanks r.venkataraman!

Multi Column Match

Here is a little trick for matching two values against a two column table.
It’s much like creating a hash code for each row, then matching the hash code.

With this spreadsheet, the top section allows lookup, the lower section is the lookup table.

Here we are searching for a Value by looking up a City and Season:

The array formula in cell C3:
=MATCH(A3 & CHAR(1) & B3, A7:A16 & CHAR(1) & B7:B16, 0)
Enter the formula by pressing Ctrl+Shift+Enter.

The formula in cell D3:
=INDEX(C7:C16, C3)

We simply concatenate A3 and B3 to match against concatenated A7:A16 and B7:B16.

The CHAR(1) is to ensure uniqueness.
For example: “userdata” & “base” would also match “user” & “database”
Fixed by: “userdata” & CHAR(1) & “base” does not match “user” & CHAR(1) & “database”

Update:
Daniel M points out a different, more efficient, match lookup using boolean comparison:
The array formula in cell C3:
=MATCH(1, (A3=A7:A16) * (B3=B7:B16), 0)
Enter the formula by pressing Ctrl+Shift+Enter.

D3 remains the same.

This works because…
A3=A7:A16 results in an array of TRUE, FALSE values
Arithmetic on a boolean results in a number. eg. TRUE * FALSE = 0.
Only TRUE * TRUE will result in the “1? MATCH is looking for.

Thanks Daniel!

Sneaky Export Data from Applications

The other day I wanted to pull some data out of a standalone application.
The application is like a database. Records are presented one at a time on a single screen.
Navigation buttons allowed First, Previous, Next, Last.

So, you can input OK, print OK, but when it comes to a data dump – no way – no how. No Export menu.

The data file didn’t look nice – binary. The strings were only partly visible – I didn’t fancy that track.

I might have tried setting up a Text File Printer (a fake printer which prints to a text file), but I thought I’d try something new.

Using Spy++, I discovered all of the data on screen were contained in separate textboxes.
(no Spy++ ??, Try Winspector)

Not all of the textboxes were useful, but I could massage the data later.

So here’s what the code does:
1. Get the Application Window Handle – hWnd
2. Walk the hierarchy and GetText each window.
3. Once walking done, Click the Next button
Repeat at 2

Declare Function GetDesktopWindow Lib “user32” () As Long
Declare Function GetWindow Lib “user32” (ByVal hWnd As Long, ByVal wCmd As Long) As Long
Declare Function FindWindowEx Lib “user32” Alias “FindWindowExA” (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Declare Function SendMessage Lib “user32” Alias “SendMessageA” (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Declare Function PostMessage Lib “user32” Alias “PostMessageA” (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Declare Function GetWindowLong Lib “user32” Alias “GetWindowLongA” (ByVal hWnd As Long, ByVal nIndex As Long) As Long
 
Dim rng As Range
 
Sub Extract()
    Dim hWnd As Long, i As Long
 
    Set rng = Selection
 
    hWnd = GetDesktopWindow()
    hWnd = FindWindowLike(hWnd, “My Application *”)
 
    For i = 1 To 100
        WalkWindowsText hWnd
        Set rng = Cells(rng.Row + 1, 1)
        ClickButton hWnd, “Next”
    Next
 
    rng.Select
End Sub
 
Sub WalkWindowsText(hWndParent As Long)
    Dim hWnd As Long, lng As Long, str As String
 
    Do
        hWnd = FindWindowEx(hWndParent, hWnd, vbNullString, vbNullString)
        If hWnd <> 0 Then
            rng.Value = WindowText(hWnd)
            Set rng = rng.Offset(, 1)
            WalkWindowsText hWnd
        End If
    Loop Until hWnd = 0
End Sub
 
Function ClickButton(hWndParent As Long, ButtonText As String)
    Const WM_COMMAND = &H111, GWL_ID = (-12)
 
    Dim hWnd As Long, lngID As Long
 
    hWnd = FindWindowEx(hWndParent, 0, “Button”, ButtonText)
    lngID = GetWindowLong(hWnd, GWL_ID)
    PostMessage hWndParent, WM_COMMAND, lngID And &HFFFF&, hWnd
End Function
 
Function FindWindowLike(hWndParent As Long, Caption As String) As Long
    Const GW_HWNDNEXT = 2, GW_CHILD = 5
 
    Dim hWnd As Long, str As String, lng As Long
 
    hWnd = GetWindow(hWndParent, GW_CHILD)
    Do Until hWnd = 0
        If WindowText(hWnd) Like Caption Then
            FindWindowLike = hWnd
            Exit Do
        End If
 
        hWnd = GetWindow(hWnd, GW_HWNDNEXT)
    Loop
End Function
 
Function WindowText(hWnd As Long) As String
    Const WM_GETTEXT = &HD, WM_GETTEXTLENGTH = &HE
 
    Dim lng As Long, str As String
 
    If hWnd <> 0 Then
        lng = SendMessage(hWnd, WM_GETTEXTLENGTH, 0&, 0&) + 1
        If lng > 0 Then
            str = String$(lng, vbNullChar)
            lng = SendMessage(hWnd, WM_GETTEXT, lng, ByVal str)
            If lng > 0 Then WindowText = Left$(str, lng)
        End If
    End If
End Function