SheetActivate Event Doesn’t Fire Between Multiple Windows

SheetActivate Event Doesn’t Fire Between Multiple Windows

In VBA you use the SheetActivate event to track when a user switches from one sheet to another. Sometimes I use it to control the state of menu items that I only want available when certain sheets are active. In the workbook below, I only want the “Add Color” button enabled when the “Colors” sheet is active.

Add Color button

The SheetActivate event works fine for this, most of the time, using code similar to this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'forces the button's getEnabled code (below) to run
g_Ribbon_Invalidate
End Sub

Public Sub cmdAddColor_getEnabled(control As IRibbonControl, ByRef returnedVal)
'the button is enabled only if "Colors" is the active sheet
returnedVal = ActiveSheet Is ThisWorkbook.Worksheets("Colors")
End Sub

Below, the Add Color button has been disabled after switching to another sheet, just like I want:

Add Color button disabled

Recently I noticed this doesn’t necessarily work if the workbook has two or more windows. In that case, switching from one window to another doesn’t trigger the SheetActivate event, even if the second window has a different active sheet than the first. Below, I’ve switched from the “Colors” sheet in one window to the “No Colors Allowed!” sheet in the second window. The SheetActivate event hasn’t fired and the button is still enabled. It’s out of sync.

Add ButtonWindow Error

I guess it makes sense that SheetActivate wouldn’t fire. After all, within each window the active sheet is still the same. (Happily, the ActiveSheet property is still updated.)

In order to keep the button in sync, add a WindowActivate event to your code. Between it and the SheetActivate code, you’ll handle moves between sheets within the same window, and between windows to a different sheet:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
g_Ribbon_Invalidate
End Sub

Download!
You can check it out in this sample workbook.

Escape Accidental File Moves

Escape Accidental File Moves

Every so often I’m merrily dragging folders or files around when I realize I’m making a painful blunder. For whatever reason, in mid-drag I just want out. Sure, I could try to retrace my steps back to the source, but maybe it’s no longer in sight. Of course, I could do a Ctrl-Z after the fact and hope that Undo does. But really I just want to escape the accidental file move right now:

Esc key

So one day I did just that. I hit the Escape key and trouble was averted.

I know the real solution is to never drag and always cut/copy and paste with Ctrl shortcuts or menus. But since that’s not going to happen, at least I’ve got an escape plan.

This works in Outlook, the VBA IDE and other places as well.

Listing UserForm Accelerator Keys

Listing UserForm Accelerator Keys

Towards the end of the last post I showed a form I made to copy query properties from one table to another. That userform has 20 controls and, as always before I post something for your enjoyment, I tried to make is as user-friendly as possible.

So I set the default and cancel properties to the appropriate buttons and arrange the tab order of all the controls. When that’s done I assign accelerator keys to some of the controls. The accelerator property specifies a letter or other key, which when pressed along with the Alt key, activates that control.

button accelerator key

In the example above, a helpful but peevish developer has specified the % key as the accelerator, perhaps for the first time in history.

tab_ dialog

I enjoy the fiddly work of setting tab orders and accelerators. I’d enjoy it more if the tab order dialog weren’t so hard to read.

It’s also hard to tell which controls have which accelerators and whether you’ve already used a certain letter.

So I wrote a bit of code that takes a userform as an argument and prints the relevant control properties to a newly-minted worksheet.

Sub ListUserFormAccelerators(frm As UserForm)
Dim ControlsCount As Long
Dim i As Long
Dim ctl As msforms.Control
Dim ControlName As String
Dim ControlTabIndex As Long
Dim ControlCaption As String
Dim ControlAccelator As String
Dim ControlProperties() As Variant
Dim ws As Excel.Worksheet
Const TableHeaders As String = "TabIndex,Name,Caption,Accerator,Count"

ControlsCount = frm.Controls.Count
ReDim ControlProperties(1 To ControlsCount, 1 To 4)
For i = 1 To ControlsCount
    Set ctl = frm.Controls(i - 1)
    ControlName = ctl.Name
    ControlTabIndex = ctl.TabIndex
    ControlCaption = ""
    ControlAccelator = ""
    'some controls don't have the next two properties
   On Error Resume Next
    ControlCaption = ctl.Caption
    ControlAccelator = ctl.Accelerator
    On Error GoTo 0
    ControlProperties(i, 1) = ControlTabIndex
    ControlProperties(i, 2) = ControlName
    ControlProperties(i, 3) = ControlCaption
    ControlProperties(i, 4) = ControlAccelator
Next i
Set ws = Workbooks.Add.Worksheets(1)
With ws
    .Range("A1:E1") = Split(TableHeaders, ",")
    .Range("A2").Resize(ControlsCount, 4) = ControlProperties
    With .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes)
        .Name = "tblControlProperties"
        .ListColumns("Count").DataBodyRange.FormulaR1C1 = "=COUNTIF([Accerator],[@Accerator])"
        With .Sort
            .SortFields.Add Key:=Range("tblControlProperties[TabIndex]")
            .Header = xlYes
            .Apply
        End With
        .Range.Columns.AutoFit
    End With
    'Want to close without prompt to save
   .Parent.Saved = True
End With
End Sub

The code loops through a form’s controls and ultimately adds them all to an array which is dumped into a worksheet created in the code.

You’d call it like this:

Sub HereYouGo()
ListUserFormAccelerators frmCopyTableQuery
End Sub

And the result looks like this (click on it to open in its own, larger, window):

code output

You may notice that some of the accelerators are doubled above. Each pair is for a label, followed by a textbox with no accelerator and then by a checkbox, which has the second occurrence of that acceelator. There’s two things going on here. The first is that if a control isn’t a Tab Stop, like a label, then the accelerator will take you to the next control, in this case the textbox. The second is that I wanted the user to be taken to the associated checkbox if they hit the accelerator again.

I can’t figure out how to call this code from another project. I messed around with Application.VBE.VBProjects, but can’t get it to work. Another nice thing would be the inverse of this code, a routine that would apply the tab order and accelerators from the worksheet to the userform.

Tables’ Edit Query Dialog

Tables’ Edit Query Dialog

One of my favorite Excel features is the Edit OLE DB Query dialog, where you can edit a table’s data connection and SQL. If you’re not familiar with table data connections, I’m talking about tables created by clicking something like “From Access” in the ribbon’s Data menu. Besides Access, you can connect to other databases, Excel files, the web, and who knows what else. Here’s a nice example of connecting to Access.

To get to the Edit OLE DB Query dialog, right-click in a table and choose “Table” then “Edit…”

Each time I do so I’m filled with child-like wonder at the ability to throw in a new connection string, switching, for example, from Access to SQL Server.

Edit Query dialog 2

In addition you can change from the default Command Type of “Table” – which returns all the contents of a table, query or view – to “SQL,” which allows you to enter SQL directly into the Command Text box.

As far as I can tell this SQL can be as complex as what you would use directly in that type of database. For example, you can use With statements with a SQL Server connection.

That being said, and depending on my access privilegees, I try to limit the amount of SQL on the Excel side. If possible, I connect to an existing database view and then maybe filter it in the Command Text box. So with a SQL Server view called vwScoresAllYears, I might narrow the results with SQL like “SELECT * FROM vwScoresAllYears WHERE vwScoresAllYears.year IN (2012,2013).”

As cool as that is – and it is – it pales next to the ability to switch connections to a completely different type of database. One time we were porting a project from Access to SQL Server. The front end was a big old workbook with a data table pointing at an Access query. The table had lots of calculated columns and several pivot tables pointing at it. To do the switch, we could have created a new table pointing at the SQL Server view, rebuilt the calculated columns and pointed the pivot tables at it. Instead, I just switched the connection string so that it pointed at the SQL Server view. Since the output columns were exactly the same, the transition was barely noticeable. I just hit refresh and the SQL Server data poured into the table.

The following two pics show what I mean. The first connection is to an Access database…
Access connection

The second connection is to a SQL Server database:SQL Server connection

If you’re wondering, the connection shown in the first picture in this post is to another Excel workbook. In that case there are three worksheets serving as tables. The SQL looks a bit different, because when referring to Excel sheets or ranges, the names are surrounded in brackets, and followed by dollar signs in the case of sheets. If the idea of using SQL on Excel workbooks is new to you, this MSDN page is a good start.

I often find myself copying query settings from one table to another. For a while I’d open the Edit OLE DB Query dialog for a table, copy the connection data, open the dialog for the second one and paste the data. That got kind of boring, so guess what? That’s right, I wrote a tool to do what I want:

Copy Properties tool

The way it works is you select a Source table – by clicking into a table and clicking the form button – and then do the same for the Target. You then select which parts of the query – Connection, Type, or Text – to copy over. You can also just edit the text in the Connection or Command Text boxes. Clicking the Copy button just copies the selected properties from the source side of the form to the target side – it doesn’t change the query properties themselves. You can make further edits in the target textboxes as needed. Clicking the Set Properties button copies applies the properties to the target table and attempts to refresh the table. If the refresh fails you get an error message. At that point you can tinker with the properties some more or click the Restore Last Good button, which will revert the table properties to the last working query.

This is different than the built-in dialog, which just reverts to the last working query. I find this ability to tweak a query until it works quite handy. Another advantage is that it opens up queries created by MS Query without the dialog telling you that you won’t be able to edit it.

To make it user-friendly for Dick and other shortcut-only types, the checkboxes can be reached by clicking the Alt Key combo for that property twice.

I uploaded an empty workbook that has a button to display this modeless form. If you download it be sure to save your work before trying this tool. I’ve used it for a few months now with no big issues, but better safe than sorry. Here’s the link.

New Job!

New Job!

I recently said goodbye to my wonderful co-workers at Portland Public Schools and am now serving as a Report and System Developer for Kaiser Permanente. I’m excited to be part of Kaiser and impressed by its history and organization. Given the state of US health care, it will be an interesting place to work.

I’ll be doing less Excel and more SQL. And with luck my 18-mile round trip commute will give me the calves of a steroid-free Lance Armstrong. Or at least of my 7th-grade Spanish teacher, who had really big calves.

I’ve got a backlog of ideas for posts here, and just need the time and energy for them. A post often takes me upwards of 20 hours, sometimes a lot longer as I figure stuff out.

Whatever your endeavors in 2014, I wish you good luck.

Pivot Table Per-Item Color Scales

Pivot Table Per-Item Color Scales

Let’s take another journey to the nexus of conditional formatting and pivot tables. My latest foray involved adding per-item color-scales to a pivot table, something like this, where the color scale is applied separately to each quarter:

color scale by quarter

You can see, for example, that the very high sales of $9,999,999 for January in Region 1 affect the color scale for the other cells in Quarter 1. However, the color scale within other quarters are unaffected, and a 99,999 value in Quarter 2 gets the same green that the much higher value did in Quarter 1.

Here’s another view with each month getting its own scale:

color scale by month

Finally, this shows icon sets by region. Notice that although they span each region for all months, the icon sets ignore the subtotals, which is often preferable:

icons by region

My code allows you to include or include or ignore subtotals in each item’s color scale. I went about this a couple of ways before settling on the PivotTable.PivotSelect method. I learned about this method recently in a comment by Mike Alexander on one of Jeff Weir’s massive DDOE posts. I can’t find any good documentation, but in Excel 2007 forward you can do things like:

pvt.PivotSelect "'Row Grand Total'", xlDataOnly

Select a pivot table’s Row Grand Total area

pvt.PivotSelect "'Region'['East';Data]", xlDataOnly

Select the data rows for the East PivotItem of the Region PivotField

(There is a PivotSelect method in 2003, but these types of statements failed.)

It looks like PivotSelect is the VBA version of clicking, for example, a single subtotal in a pivot table and thereby selecting them all. I figured out the syntax by turning on the Macro Recorder and doing those types of selections:

selecting pivot parts in Excel

I’m not crazy about using PivotSelect – I assume all that selecting will slow things down on big pivot tables. On the other hand it seems to work consistently. I had much more complicated code that worked most of the time, but only most.

Here’s the code, which runs in Excel 2007 on:

Sub PivotItemCF(pvtFieldToFormat As Excel.PivotField, IgnoreSubtotals As Boolean)
Dim pvt As Excel.PivotTable
Dim pvtItem As Excel.PivotItem
Dim pvtField As Excel.PivotField
Dim rngTest As Excel.Range

'Lots of selecting!
Application.ScreenUpdating = False

Set pvt = pvtFieldToFormat.Parent
With pvt
    For Each pvtItem In pvtFieldToFormat.PivotItems
        'A distressing number of possible errors from hidden items.
       'If the item has no DataRange ignore it.
       On Error Resume Next
        Set rngTest = pvtItem.DataRange
        If Err.Number = 0 Then
            'Specify the Field name in case there are multiple items
           'with the same name,for example from Grouping.
           'Surround PivotItem names with single quotes in case they have spaces.
           pvt.PivotSelect pvtFieldToFormat.Name & "['" & pvtItem.Name & "';Data;Total]", xlDataOnly
            'Call routine that does the formatting
           FormatRange Selection
        End If
        On Error GoTo 0
    Next pvtItem
   
    If IgnoreSubtotals Then
        'Clear the FormatConditions from Row and Column fields
       For Each pvtField In pvt.PivotFields
            If pvtField.Orientation = xlRowField Or pvtField.Orientation = xlColumnField Then
                'I can't figure a test for whether the subtotals are visible
               'so just blindly firing away.
               On Error Resume Next
                .PivotSelect "'" & pvtField.Name & "'[All;Total]", xlDataOnly
                If Err.Number = 0 Then
                    Selection.FormatConditions.Delete
                End If
                On Error GoTo 0
            End If
        Next pvtField
        .PivotSelect "'Row Grand Total'", xlDataOnly
        Selection.FormatConditions.Delete
        .PivotSelect "'Column Grand Total'", xlDataOnly
        Selection.FormatConditions.Delete
    Else
    End If
End With

Application.ScreenUpdating = True
End Sub

The FormatRange routine is just Macro Recorder code generated by selecting part of the pivot table and applying a color scale. You can do the same thing for data bars or icons.

In the sample workbook, the code is triggered by a WorkSheet_Change event that fires whenever one of the three yellow data validation cells is changed.

Curious Case of the Replaced Date Slash

Curious Case of the Replaced Date Slash

At my beloved workplace we’ve developed a fondness for slashes in institution names. If we’d been in charge, it would be Bachmann/Turner Overdrive, the Mason/Dixon line, Obsessive/Compulsive Disorder and well, you get the idea. Aesthetics aside, this causes problems when generating workbooks based on these names. Windows doesn’t like slashes in its file handles, because it uses them as path separators (except when it doesn’t, which is why you should use Application.PathSeparator in your code).

I recently wrote a little function to strip slashes and other shady characters out of file names and replace them with dashes, which don’t stick in Windows’ craw. Up until then, my co-worker was forced to do a search and replace on the offending monikers. In doing so she pointed out that the replace dialog mistakenly says it’s replacing all the slashes in the dates on the sheet as well, even though they remain in place.

For example, take a column like this, with one name and 1,048,574 dates:

date slashes before replace

And here it is with Excel proclaiming to have replaced 2,097,149 “/”s with “-”s, when clearly it only replaced the one. Silly old Excel!

date slashes replaced

I theorized that Excel was doing a two-step operation: first counting the slashes – including those in the dates – but then only replacing the non-date ones. I can certainly see myself doing something like that. We figured this was because Excel stores dates as numbers and the human/readable dates such like “11/15/2013″ are just formats, and the slashes aren’t “really” there.

Turns out that’s wrong. I should have given Excel more credit. It really does replace the slashes if you ask it to. For example, try a find and replace with explanation points, or “bangs” as some folks call them. I get this:

date with bangs

What seems to happen with dashes is Excel recognizes them as date separators, but, at least on a computer with the slash set as the default date separator, it turns them right back into slashes. You can see this in it’s purest form by typing 11-15-2013 into a cell. It immediately converts the dashes, both in the formula bar and in the cell (at least if you’re using my laptop. I haven’t tested this extensively).

So it really is replacing all those millions of slashes with dashes. But then it converts them right back, without so much as a “by your leave.”

What’s confusing to me is that if I use a custom format like “yyyy.mm.dd” and try to replace the periods, Excel says there’s nothing to replace. That makes sense as the periods are just part of a format. But so are the slashes. What gives?

Speaking of dates, happy anniversary to my wonderful wife!

Filter as you type with FilterMatic!

Filter as you type with FilterMatic!

FilterMatic form

Whenever possible I employ lookups, crosswalks and whining to avoid correcting data by hand. Sometimes my best efforts fail, and I end up fixing lists one funky cell at a time. The horror! Happily I’ve created FilterMatic to ease the pain.

Say for instance, you’re giving a birthday party for a hundred or so of your oldest friends, all born on October 26. You’ve got this handy reference list of names, years of birth and notable accomplishment:

birth years

source:http://www.historyorb.com/today/birthdays.php


Meanwhile you’ve got another, very similar, list that calculates their age – with a lookup to the “year” column on the first list – so you can embarrass them by putting it on their place setting. The problem is some names are misspelled and you’re getting #N/A’s.

So you filter to just the #N/A’s and start to fix them. Wouldn’t it be nice if, as you fixed the errors, they were automatically filtered away? It’s true that in modern Excel tables you can do this with right-click>Filter>Re-Apply. But now with FilterMatic your corrections are instantly whisked from sight!

In the example below, I’m fixing the names, at first without FilterMatic running. After the third fix, I start it up. The first thing that happens is the filter is re-applied. After that the filter is re-applied with every change to the table and each fixed row is filtered away.

FilterMatic in Action!

FilterMatic™ works on worksheet and table filters, re-applying them on the active sheet whenever you change a cell within a filtered area. And with FilterMatic™ there’s no confusing buttons or messy dropdowns. Just turn it on to start filtering! Close it when you’re done! But wait, there’s more! Download now and receive a free puppy!

Here’s the code:

Private WithEvents app As Excel.Application
Private WithEvents wsActive As Excel.Worksheet
'My ShowModal Property must be set to False

Private Sub UserForm_Activate()
Set app = Application
If Not ActiveSheet Is Nothing Then
    Set wsActive = ActiveSheet
    FilterMatic
End If
End Sub

Private Sub app_SheetActivate(ByVal Sh As Object)
Set Sh = wsActive
End Sub

Private Sub wsActive_Change(ByVal Target As Range)
FilterMatic
End Sub

Private Sub lblFilterMatic_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'apply changes by double-clicking the form
FilterMatic
End Sub

Sub FilterMatic()
Dim lo As Excel.ListObject

Set lo = Selection.ListObject
'if the selection overlaps a table
If Not lo Is Nothing Then
    With lo
        'Table is in filter mode
       If .ShowAutoFilter Then
            .AutoFilter.ApplyFilter
        End If
    End With
Else
    'It will only re-apply a worksheet-level filter if
   'there's no tables on the sheet.
   With wsActive
        'if sheet is filtered
       If .FilterMode = True Then
            'if the selection overlaps the worksheet's filtered area
           If Not Intersect(Selection, .AutoFilter.Range) Is Nothing Then
                .AutoFilter.ApplyFilter
            End If
        End If
    End With
End If
End Sub

When the form is opened an application object is created. This object monitors all “SheetActivate” events in Excel and sets the activated worksheet to wsActive. There are three events that trigger the filtering code: opening the form, changing something in wsActive or clicking the label in the center of the form.

The FilterMatic sub checks for both ListObject and worksheet-level filters. If the selection at the time of the change intersects one, then that filter is updated. Note that if a listobject is found, it won’t go on to re-apply a worksheet filter. I tried that and couldn’t imagine a reasonable worksheet design that would have both filter types, and anyways the results were weird. Also note that if you change two listobjects at once, the filter would only be applied to one of them: whichever is the Selection.ListObject.

Here’s a workbook with the code and the lists.

And be sure to join the email list so you won’t miss my next exciting product: PivotMasher!

Get Unique Per-Row Values With RemoveDuplicates

Get Unique Per-Row Values With RemoveDuplicates

This post consists of two topics, both involving VBA’s RemoveDuplicates method. First I discuss an error and a shortcoming in the RemoveDuplicates documentation that have confounded myself and others. After those are cleared up I’ll show how to use RemoveDuplicates to return a two-dimensional array of unique items from a range containing one or more columns.

RemoveDuplicates was added to VBA (along with it’s front-end counterpart Data>Remove Duplicates) in Excel 2007. It’s very handy, allowing you to select one or more columns in a range and and winnow it down to the rows where the combined values of those columns are unique. It does this “in-place,” leaving other areas of the sheet intact.

RemoveDuplicates’ Columns Argument isn’t Really Optional

The Excel 2013 MSDN documentation describes the variant Columns argument as an optional “Array of indexes of the columns that contain the duplicate information. If nothing is passed then it assumes all columns contain duplicate information.” (my emphasis)

Later it gives an example of leaving out the Columns argument to remove duplicates from the entire range:

“The following code sample removes duplicates with all columns from a range.”

ActiveSheet.Range("A1:C100").RemoveDuplicates

However, testing reveals that the above code does nothing. This is confirmed by the perplexed comments of various forum visitors. In truth, you have to specify the columns variant array in all cases. The help example should look like this:

ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2,3)

If you want to base the removal on just some of the columns in a range, for example columns A and C, you do it like this:

ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,3)

Using a Dynamic Columns Array

If you use RemoveDuplicates in code, there’s a good chance you’ll want to determine the array of column numbers at runtime and not hard-code them as above. As a simple test, I tried something like:

Dim ColumnsToCheck as Variant
ColumnsToCheck = Array(1,3)
ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=ColumnsToCheck

However this results in a Runtime Error ’5′ – Invalid Procedure Call or Argument.

After looking around the web a while I found that the answer is to wrap ColumnsToCheck in parentheses, so the code becomes:

Dim ColumnsToCheck as Variant
ColumnsToCheck = Array(1,3)
ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=(ColumnsToCheck)

I know, from one of my all-time favorite DDOE posts that parentheses force evaluation of what’s inside them. Others on the web said that these parens “coerce” the evaluation of the array. I don’t really see how that applies here, so if you can explain why this works, please leave a comment.

One other note about the Columns variant array – it must be zero-based.

Unique Unique Per-Row Values Array Function

Once I’d sorted out the above points, I was able to use RemoveDuplicates as the basis of a function that returns a two-dimensional variant array containing the unique rows in a range. The range can consist of multiple columns that aren’t necessarily adjacent, as in this table of hometowns and sports of 2008 US Olympic athletes:

Olympics table

In the example above I want to return a variant array of all the unique combinations of the State and Sport fields. The specific use is for a worksheet splitter, where I prompt the user for one or two columns and the cycle through the array of unique combinations, creating a separate workbook for each combination.

The code is simple enough. It takes a range as it’s argument, for example the State and Sport fields above. It copies the range it to a worksheet in a newly added workbook. We then use RemoveDuplicates on the entire UsedRange of that new sheet. Because the columns are now contiguous the code can easily assign the modified range back to a variant array. The temporary workbook is then closed without saving.

Function GetUniqueRowValues(rng As Excel.Range) As Variant
Dim wbTemp As Excel.Workbook
Dim wsTemp As Excel.Worksheet
Dim ColNums As Variant
Dim i As Long

'if only one cell selected then can't pass to two-dimensional array
If rng.Cells.Count = 1 Then
    'if the single cell is blank
   If rng.Value = vbEmpty Then
        Exit Function
    Else
        GetUniqueRowValues = rng.Value
        Exit Function
    End If
End If

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'create a temp workbook
'and copy range to it
Set wbTemp = Workbooks.Add
Set wsTemp = wbTemp.Worksheets(1)
With wsTemp
    rng.Copy
    'rng can consist of non-adjactent columns
   'pasting to a new workbook results in a
   'new range with only those columns
   .Range("A1").PasteSpecial xlPasteValues
    ReDim ColNums(0 To wsTemp.UsedRange.Columns.Count - 1)
    'Create the array of column numbers for the
   'RemoveDuplicates' Columns parameter
   For i = LBound(ColNums) To UBound(ColNums)
        ColNums(i) = i + 1
    Next i
    .UsedRange.RemoveDuplicates Columns:=(ColNums), Header:=xlGuess
    'assign the values of the remaining,
   'non-duplicated rows to the function result
   GetUniqueRowValues = .UsedRange
End With
wbTemp.Close False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Function

Note that is the original range was just one cell, the returned variant will not be an array. But you’d test for that before running this in the first place, wouldn’t you?

This code is fast for big ranges, handling over one million rows in less than five seconds on my laptop. On the other hand, it has to open and close a workbook, which takes part of a second and causes screen flicker. It’s a lot more flexible at least than my attempts to modify the Collection technique to handle large ranges with non-adjacent columns.

More Listbox Filtering With the Like Operator

More Listbox Filtering With the Like Operator

Since posting about filtering a listbox, I’ve been thinking a lot about VBA’s Like operator. I realized I could type in filters such as ‘[4-7]#‘ which matches two-digit numbers between 40 and 79. I raced to my computer to try it out, typed the opening bracket and was halted in my tracks:

invalid pattern string

It makes sense that a “[" without a "]” is untenable in a Like pattern. It needs the closing bracket to mark the end of the character set. On the other hand, a single “]” is legal and just stands for itself. You can match a left bracket by putting brackets around it, so the following expression in the Immediate Window yields True:

? "[" Like "[[]"

I set to creating a function that would detect the invalid left bracket. I tried testing whether there were more left than right brackets, but since you can have two left ones in a row, and as many right ones as you want, that wouldn’t fly. To avoid a complicated set of tests, I turned to the VBA standby of attempting something inside of On Error statements, something whose failure yields specific and helpful information, like “hey that’s an invalid pattern.”

Here was the first attempt, which seemed to do the trick:

Function ValidLikePattern(LikePattern As String) As Boolean
Dim temp As Boolean
On Error Resume Next
temp = ("A" Like LikePattern)
If Err.Number = 0 Then
    ValidLikePattern = True
End If
On Error GoTo 0
End Function

You pass it a pattern and it tests it against the simplest possible string – “A”. My assumption was the test would either pass, if there was no left bracket, or fail if there was. I was wrong on two counts.

The first is that Like actually has three possible outcomes: True, False or Null. It returns Null if either the string being compared or the pattern is an empty string.

Much more important to my function, Like is lazy. It only tests as far as needed. So, while this returns an “Invalid pattern string” message:

? "A" Like "["

This doesn’t:

? "A" Like "A["

It just returns False. I guess Like compares the first characters, sees they match and checks whether the next one is an asterisk, the only character that could match at this point. Since it’s not, Like says “whatever” and quits, ignoring the left bracket’s tenuous status.

I could fix the function – kind of – by using a really long string in place of “A” and force Like to look harder. Instead I surrounded LikePattern in asterisks:

temp = ("A" Like "*" & LikePattern & "*")

This says there could be an infinite number of characters at the beginning of the pattern, so Like needs to be thorough and compare all the way through. I suppose this slows the code. I also guess I don’t need the “*” at the end. At any rate, I can now type a left bracket into the filter textbox without an error:

filter with bracket

I updated the sample workbook from the ListBox Filter With WildCards post. It now calls the ValidLikePattern function at the beginning of the main loop. If the pattern isn’t valid, the loop is exited and the filtering stays the same until the next change in the filter textbox. Here’s the updated download.

Help Me Test Table Viewer

As I mentioned at the end of that post, I’m incorporating this logic and a bunch of features into a Table Viewer addin. I think it’s even more “freakin’ awesome” than MenuRighter. I’ve been using it every day. I just added the ability to specify exact matches, “contains”, “begins with”, etc. If you’d like to help me test it, please leave a comment or use the contact form to let me know. Or wait for my next post.