Filter Column by External List

Filter Column by External List

In a comment to my last post Jeff suggested it would be easy to modify that routine to filter a column by an external table, similar to some code he’s written for pivot tables. That would be very useful as part of a model or report, making it easy for users to update by just plopping new values into the table every week or what-have-you.

However, these days I mostly use Excel as a SQL development environment, running the SQL right in a table and then filtering and pivoting the results to figure out if my queries are working. For that reason, I really just wanted a form with a textbox where I could drop a bunch of values and filter by them. That utility, combined with the built-in filtering tools and the code from my last post would give me a lot of ways to parse my data.

And now, thanks to the wonder that is VBA, I have that utility. What’s more, so do you!

filter form

Above is the form in all its simple glory. I’d show an “After” picture, but I trust your imagination can supply that.

The UserForm VBA

Here’s the code for the main routine. It looks a lot like that in my last post, so be sure to check that out if you haven’t already:

Sub FilterList()
Dim FilterValues() As String
Dim ColNumberInFilterRange As Long
Dim FilterRange As Excel.Range
Dim InTable As Boolean
Dim CollUniqueValues As Collection
Dim i As Long

If ActiveSheet Is Nothing Then
    MsgBox "No active worksheet."
    Exit Sub
End If
If Me.txtValues.Text = "" Then
        MsgBox "Enter at least one value to filter by"
        Exit Sub
End If
With Selection
    If .Cells.Count = 1 And IsEmpty(ActiveCell) Then
        MsgBox "Please select a cell within one or more cells with data."
        Exit Sub
    End If
    If Union(ActiveCell.EntireColumn, .EntireColumn).Address <> ActiveCell.EntireColumn.Address Then
        MsgBox "Only select from one column"
        Exit Sub
    End If
    'Set the range to be filtered depending on whether it's a Table or not
   If Not ActiveCell.ListObject Is Nothing Then
        Set FilterRange = ActiveCell.ListObject.Range
        InTable = True
    Else
        Set FilterRange = ActiveCell.CurrentRegion
    End If
    If Union(Selection, FilterRange).Address <> FilterRange.Address Then
        MsgBox "Please make sure all cells are within the same table or contiguous area."
        Exit Sub
    End If
    'If not in a table and we're filtering a different area than currently filtered
   'then turn the existing AutoFilter off, so no error when the new area gets filtered.
   If Not InTable And ActiveSheet.AutoFilterMode Then
        If ActiveSheet.AutoFilter.Range.Address <> .CurrentRegion.Address Then
            ActiveSheet.AutoFilterMode = False
        End If
    End If

    FilterValues = Split(Me.txtValues.Text, vbCrLf)
    'Try to add every selected value to a collection - only unique values will succeed
   Set CollUniqueValues = New Collection
    For i = LBound(FilterValues) To UBound(FilterValues)
        On Error Resume Next
        CollUniqueValues.Add FilterValues(i)
        On Error GoTo 0
    Next i
    'Transfer the collection to an array for the AutoFilter function
   ReDim FilterValues(1 To CollUniqueValues.Count)
    For i = LBound(FilterValues) To UBound(FilterValues)
        FilterValues(i) = CollUniqueValues(i)
    Next i
    'Determine the index of the column to be filtered within the FilterRange
   ColNumberInFilterRange = (.Column - FilterRange.Columns(1).Column) + 1
    FilterRange.AutoFilter Field:=ColNumberInFilterRange, Criteria1:=FilterValues, Operator:=xlFilterValues
End With
End Sub

There’s one issue with this code that I haven’t solved yet. When I paste into the textbox it always puts a line feed after the last item. Unless I backspace up to the end of the last item the filter also includes blank cells, not what I’d usually want. On the other hand I don’t think I just want to get rid of that last line feed in my code because I might actually want to include a null value/blank in the filter.

Download

This download has the resizable (thanks to Chip Pearson) form for you to play with.

Next Time

Filter Using Common Household Objects!

Filter Column By All Selected Values

Filter Column By All Selected Values

Excel’s Filter by Selected Cell’s Value is a popular command, and for good reason. It’s a quick way to filter the data that you’re looking at. There are times when I’d like to take it one step further and filter by the values of multiple cells. So I wrote some code and added a “Filter By All Selected Values” item to my right-click menu. It works just like Filter by Selected Cells except you can select multiple cells.

Filter by All Selected Cells

Here’s the result:

filtered ccells

There’s one situation where this feature is especially handy: that’s when I need to see all instances in one column of items that match a single characteristic in another column. For instance, “I want to see all the pie orders from customers who have ordered Red Velvet Cheesecake.”

I could do this operation with a Countif formula like…

=COUNTIFS([Name],[@Name],[Pie],"Red velvet cheesecake")>0

and then filter to all the rows that return TRUE.

COUNTIFS filter

But this new routine allows me to do this without helper columns.

Here’s How

First, select “Red Velvet Cheesecake,” then right-click and choose the regular old Filter > Filter by Selected Cell’s Value. You’ll see all the rows with that value.

Filter Step 1

Next, select all the customers and run the “Filter by All Selected Values” code. This will filter the Customers column by only the three currently visible names. This is the part that I don’t see how to do easily without this code.

Filter Step 2

Finally, clear the filter from “Pies” that you set in the first step. The result will match that above with the COUNTIFS formula.

A Brief Commercial Announcement

If you’re wondering how I got those things in my context menu see the MenuRighter page.

The Code

The code I wrote works for tables and contiguous areas.

Sub Filter_By_All_Selected_Values()

Dim FilterValues() As String
Dim i As Long, j As Long
Dim ColNumberInFilterRange As Long
Dim FilterRange As Excel.Range
Dim InTable As Boolean
Dim CollUniqueValues As Collection
Const MAX_FILTER_CELLS As Long = 10000

'Make sure we don't select cells hidden by filter
With Selection.SpecialCells(xlCellTypeVisible)
    If Union(ActiveCell.EntireColumn, .EntireColumn).Address <> ActiveCell.EntireColumn.Address Then
        MsgBox "Only select from one column"
        Exit Sub
    End If
    If .Areas.Count = 1 And .Rows.Count = 1 Then
        MsgBox "Select more than one cell"
        Exit Sub
    End If
    If .Cells.Count > MAX_FILTER_CELLS Then
        MsgBox "Cannot select more than " & MAX_FILTER_CELLS & " cells."
        Exit Sub
    End If

    'Set the range to be filtered depending on whether it's a Table or not
   If Not ActiveCell.ListObject Is Nothing Then
        Set FilterRange = ActiveCell.ListObject.Range
        InTable = True
    Else
        Set FilterRange = ActiveCell.CurrentRegion
    End If
    If Union(Selection, FilterRange).Address <> FilterRange.Address Then
        MsgBox "Please make sure all cells are within the same table or contiguous area."
        Exit Sub
    End If

    'If not in a table and we're filtering a different area than currently filtered
   'then turn the existing AutoFilter off, so no error when the new area gets filtered.
   If Not InTable And ActiveSheet.AutoFilterMode Then
        If ActiveSheet.AutoFilter.Range.Address <> .CurrentRegion.Address Then
            ActiveSheet.AutoFilterMode = False
        End If
    End If

    'Try to add every selected value to a collection - only unique values will succeed
   Set CollUniqueValues = New Collection
    For i = 1 To .Areas.Count
        For j = 1 To .Areas(i).Cells.Count
            On Error Resume Next
            CollUniqueValues.Add .Areas(i).Cells(j).Text, .Areas(i).Cells(j).Text
            On Error GoTo 0
        Next j
    Next i
    'Transfer the collection to an array for the AutoFilter function
   ReDim Preserve FilterValues(1 To CollUniqueValues.Count)
    For i = LBound(FilterValues) To UBound(FilterValues)
        FilterValues(i) = CollUniqueValues(i)
    Next i
    'Determine the index of the column to be filtered within the FilterRange
   ColNumberInFilterRange = (.Column - FilterRange.Columns(1).Column) + 1
    FilterRange.AutoFilter Field:=ColNumberInFilterRange, Criteria1:=FilterValues, Operator:=xlFilterValues
End With
End Sub

How it Works

The first part of the code consists of a few checks to make sure the selection is more than one cell, is only in one column, and that you haven’t selected a huge number of cells. This last one is controlled by the MAX_FILTER_CELLS constant. I set it at 10,000, a number that ran with no noticeable delay.

After that the code checks whether the ActiveCell is in a table or not. In either case, it assigns the range to be filtered to the FilterRange variable. It then checks that all the currently selected cells reside within either the same table or contiguous area.

The code uses our old friend the “get unique items using a collection” trick. It then applies a filter to the FilterRange.

The trickiest part of the code was defining the conditions that require an Exit Sub, such as selecting in multiple columns. That’s mainly because something like “Selection.Columns.Count” only returns the count of columns in the first Area of a selection. The same thing applies to the Selection’s CurrentRegion. I got around these limitations by using Union and the fact that Selection.Address returns the address for all areas in the the selection.

Download

Here’s a workbook with the code and the pie orders table so you can easily try the filtering I did above.

Filter by Selected Cell’s Value – Bug With Blanks

Filter by Selected Cell’s Value – Bug With Blanks

I’ve found a bug in Excel 2010 that I don’t find described at all when I search the internet. It involves the Filter by Selected Cell’s Value command and blank cells.

The Issue

In Excel 2010 when I select a blank cell in a table, or a non-table and then choose “Filter by Selected Cell’s Value” from the right-click menu, the column is filtered by what look like Chinese and non-printable characters. It occurs for me every time in a Table. In an informal list it sometimes only occurs after filters are applied.

Testing

I’ve seen the issue on all five Excel 2010 computers I’ve tried, all with Windows 7. I tried it on one computer with Office 2013 and couldn’t replicate the bug.

How to Replicate It

Create a table like this one. It can actually be just one column:

table

Right-click in one of the blank cells and choose Filter > Filter by Selected Cell’s Value. (The mouse-averse can do Menu Key, E, V as described in this wmfexcel post).

filter menu

When I do this all rows are filtered away and I can see the odd filter criteria in the little tooltip that appears:

preview

This image show the filter dropdown and the Number Filters dialog (if you do column A you’ll see a Text Filters dialog instead):

filter dialog

What I’d expect, and see in Excel 2013, is that the (Blanks) item in the dropdown would be checked.

You can see that there are a mix of non-printing characters and what Google Translate sometimes recognizes as Chinese characters. I see different character combinations on different computers. Here’s one of my home computers:

translation

Workarounds

The obvious one is to select “(Blanks)” in the filter dropdown. Another is to upgrade to Excel 2013.

A partial one is to use add the “Autofilter” control to the Ribbon. This only works for non-Table lists though. With a Table it only turns the filters on and off. I think this control goes back to Excel 2003 – you can add it to a right-click menu by using MenuRighter and choosing Add Command > Data > Autofilter.

So, Did Everybody Else Already Know This?

I’m always hesitant to cry “bug,” but this seems pretty clear. My other fear is looking silly for announcing something everybody already knew. Feel free to comfort me, or not.

I haven’t tried it in Excel 2007 yet. If anybody wants to that would be great.

Missing Addin User Warning

Missing Addin User Warning

I develop workbooks that require an addin for some of their functionality. For example, a set of school staffing models whose addin allows for selecting data sources, building pivot tables and saving timestamped copies. I like this structure because it’s very maintainable – it’s much easier to fix a bug or add a feature in one addin than it is to try to track down a bunch of workbooks and update their VBA. Easier on me and better for my users. Of course, people’s computers get re-imaged or replaced and IT departments are unlikely, to say the least, to re-install a VBA addin. Folks often don’t realize their addins are gone and that’s why their model is broken. Yesterday I got an idea about how to warn users about a missing addin. I’m still tinkering with it, but the idea is quite simple.

Use a UDF in the Addin

What I did was create the world’s simplest UDF and put it in the addin:

Public Function IsAddinLoaded() As Boolean
IsAddinLoaded = True
End Function

It doesn’t have to be boolean. It could be a string and return “Cult of the Flying Spaghetti.” That’s because it’s only purpose is to fail when it’s not there.

Missing Addin = Missing UDF = opportunity for IsError formula. So, for example, in cell A1 of the addin-dependent workbook put something like:

="Ye Olde Spreadsheet " & IF(ISERROR(isaddinloaded()*TODAY()),"requires the Green Eyeshade Addin!
Please install it.","is Good to Go")

Then add a little conditional formatting based on the missing addin condition and you get something like this. (I tried referring to the UDF directly in the conditional formatting, but it looks like that’s not allowed.)

Green Eyeshade required

The formula contains a volatile function: TODAY. That’s so if the addin is uninstalled the error is activated with the next change to the worksheet. I’d like to avoid the volatile function and I’ve tried to do this in the VBA itself, but haven’t figured out anything yet.

The message does change to the non-warning one as soon as the addin is installed.

Green Eyeshade installed

The addin a very simple one made up just for this post. It’s called Green Eyeshade and it’s for modernizing old spreadsheets like this one. Its ribbon tab is shown when a workbook meeting certain criteria is opened. Here’s the ribbon tab:

Green Eyeshade tab

And here’s the modernization button in action (wonderful what you can find by googling “Peltier pie charts”).

Green Eyeshade modernized

This idea could also be applied to the problem of warning users to enable VBA when opening xlsm files. I’ve never been a big fan of the “hide everything in the workbook except a warning page” although I do like Mike Alexander’s clear explanation. Anyways, something like this might work as an alternative in that case too.

Download

Here’s a zip file with the xlam addin and the target “olde spreadsheet” xlsx file.

MenuRighter Update!

MenuRighter Update!

MenuRighter is my addin for modifying right-click menus, also known as context menus. It’s been around for three years and downloaded a few thousand times. It’s one of the cooler things I’ve ever made. I’ve just finished making some changes to it.

With MenuRighter you can turn this…

table context menu

into this…

better table context menu

With four clicks I removed the Cut, Copy, Paste and Clear Contents buttons from the List Range Popup (Table) menu. With a few more I added my entire Personal Macro workbook (the cleverly named “myBar”) and its Tables sub-menu. Using MenuRighter’s new search functionality I quickly found and added two filter options along with the “Select Visible Cells” button.

Here’s what MenuRighter looks like. It’s divided into the Source menus and controls on the left and Target ones on the right. The Target menu is the one you’re adding controls to:

MenuRighter form

Removing and Adding Controls
And here’s a couple of quick videos showing how I did the deleting from, and adding to, in the Table context menu. This first one shows how to delete the controls – just select one and click Remove. For even faster removal you can double-click the control in the list:

removing controls

Here I’m selecting the spot to insert the “Clear Selected Filter” and “Filter by Selected Cell’s Value” controls in the Target listbox. Then I filter in the Source combobox, click the Add button, and “hey Presto” they’re added. For even faster addition you can double-click the control to be added.

adding controls

In both examples above, I clicked the “Apply Changes” button to actually modify the context menu. Up until then the “Reset to Current” button will revert the Target listbox to the context menu’s current setup. And at any time you can use the “Reset to Default” button to reset the menu back to Excel’s default.

Moving Controls

You can see in the clip above that controls are added above the selected control in the target menu. (If no control is selected the new control is added at the top. You can also use the Up, Down, Top and Bottom buttons to rearrange and controls in the target menu.

Settings

“But wait” I hear you say, “how did you know to choose to the “List Range Popup” menu to modify the context menu for a Table. The answer is MenuRighter’s handy “Show Labels on Menus” checkbox. It temporarily adds a control with the name and index to the bottom of each right-click menu.

show labels on menus

There’s two other setting checkboxes. “Double-type dots” has to with MenuRighter convention for showing the Menu..Sub-menu..Control hierarchy. For instance, the Open control on the File is shown as Add Command..File..Open. If your search filter involves one of those “double-dots” I didn’t want you to have to type both, so, with that option checked, typing one dot gets you two.

The other Setting “Match Source Names” has to do with typing in the Source dropdown list at the top. That list contains all the toolbars that you can filter through in your search for controls. With the option checked, the combobox autofills the dropdown with the closest-matching item in the list. With it unchecked, it just filters based on whatever you type. It’s hard to describe, so just try it out.

The “Execute” Button
One other helpful new feature is the “Execute” button. When you’ve selected a control in the source list, and if that control is enabled, click Execute to run that control. For example, if you’ve selected Add Command..File..New and you click Execute, you’ll get a new workbook. (Pro Tip – choose the New command without three dots).

Search Tips

If you’ve used MenuRighter before, you’ll see that the main changes involve finding controls. The old version uses more of an Explorer type model of drilling down to controls. This new one lets you filter, with two big advantages: it’s faster and it’s more helpful if you have no idea where to start looking.

For example, the “filter by Selected Cell’s” value is a new control since 2003, so only shows in three context menus. Start typing “Filter by” into the Source combobox and you’ll see your choices instantly. On the other hand the Camera tool has been around (and hidden) forever. Type in “camera” and you’ve found it! “Freeze Panes” is another one that’s easy to find this way.

If you just want to scan through menus you can do it by scrolling through the seventy-or-so menus in the Source combobox. Or, you can choose menus in the Target combobox and click the “Show Current” button. Here’s an example of showing the Add Command controls. (Add Commands represents all the controls that were available in Excel 2003’s menu modification dialog):

Add Command menus

Looking for A Few Testers

I’ll be posting this new version soon, but in the meantime it would be great to have a few folks try it out. If you’re interested please let me know in the comments section or through the contact form.

Jeff Weir’s Contest

Jeff Weir’s Contest

Frequent visitor to this site, soon-to-be-published Excel author, prodigious blog poster and all-around bon vivant, Jeff Weir, is having a contest over at Daily Dose of Excel. The prize is what promises to be one of the most entertaining Excel books ever – Excel for Superheroes & Evil Geniuses

The contest involves first names of Excel personalities, animal pictures and goofy captions. There’s even a “Doug” one!

You’re supposed to enter in your own name, but if I were to submit one for Jeff it would look like this:

Jeff the beaver

Thanks Jeff for all your support of this blog, your boundless enthusiasm and, of course, for all the yuks.

Check it out:

The Great Your Name Here Competition

Tweaking View Side by Side

Tweaking View Side by Side

I use View Side by Side a lot. That means I spend a bit of time fiddling with the two windows because I don’t always want each to take up half the space. I often make one smaller and then adjust the second to have it fill the rest of the screen. This weekend I set about tweaking View Side by Side to adjust one window when the other is resized.

Along the way I learned a few interesting things. The most important to this code was figuring out how tell if View Side by Side is activated. I also noticed some issues with View Side by Side in Excel 2013.

Below is a screenshot of what happens when you resize a window in View Side by Side. You can see that it needs to be followed up with some more fiddling to snug the lower window up against the upper one:

regular View Side by Side

The code I came up with is in an application-level event. It uses a function to test whether View Side by Side is active. It’s designed to work in Excel 2010 (and 2007, although untested) as well as 2013. The 2013 tweaks were a little trickier, because Application.Height returned the same thing as Window.Height in 2013. This means the code needs to store the lower windows top location before resizing it.

You’ll also note that I used On Error Resume Next more freely than normally. After all we’re just resizing windows here, and it’s easier than testing for odd things like heights less than zero:

Public WithEvents app As Excel.Application

'application-level event in a class module
Private Sub app_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)
Dim UpperWindow As Excel.Window
Dim LowerWindow As Excel.Window
Dim OldLowerWindowTop As Double
Dim LowerWindowChange As Double

'See function below
If SideBySideOn Then
    With Application
        .EnableEvents = False
        'assign the two windows,
       'based on which is uppermost
       If .Windows(1).Top <= .Windows(2).Top Then
            Set UpperWindow = .Windows(1)
            Set LowerWindow = .Windows(2)
        Else
            Set UpperWindow = .Windows(2)
            Set LowerWindow = .Windows(1)
        End If

        'easy way to deal with heights less than zero, etc.
       On Error Resume Next
        'for Excel 2010 could just use Application.Height
       'but in Excel 2013 each window is its own Application
       If ActiveWindow.Caption = UpperWindow.Caption Then
            OldLowerWindowTop = LowerWindow.Top
            LowerWindow.Top = UpperWindow.Top + UpperWindow.Height
            LowerWindowChange = LowerWindow.Top - OldLowerWindowTop
            LowerWindow.Width = UpperWindow.Width
            LowerWindow.Height = LowerWindow.Height - LowerWindowChange
        Else
            UpperWindow.Height = LowerWindow.Top
            UpperWindow.Width = LowerWindow.Width
        End If
        On Error GoTo 0
        .EnableEvents = True
    End With
End If
End Sub

This code only responds to resizes involving the two horizontal borders in the middle, as well as the right-hand borders. So, if you drag the top border of the upper window nothing adjusts.

Application-Level Event

The sample workbook sets an application-level class in its Workbook_Open event. I’ve posted on this before and there’s lots of good info out there, like this Chip Pearson page.

How Do You Tell if View Side by Side is Active?

That’s an interesting question! Here’s what I tried:

  1. First, I looked for some type of setting, like Application.ViewSideBySideEnabled. I can’t find one. Instead there’s two methods: CompareSideBySideWith and the rather drastic-sounding BreakSideBySide.
  2. It would be nice if you could just get the state of the Ribbon’s View Side by Side button. That would tell you whether it’s active or not. But I don’t know of any way to do this.
  3. You can, however, check the state of Excel 2003-style command buttons. So I tried looking at Application.CommandBars(1).Controls("Window").Controls("Compare Side By Side With").State to check whether it’s pressed. It turns out that control – ID 7698 – has no state. Instead it switches captions from “Compare Side by Side With” (followed by a workbook name if there’s only one choice) to “Close Side By Side.”
  4. Based on #3 I considered getting the state by checking whether the first five letters are “Close” or “Compa.” But of course that wouldn’t work for folks with non-English Excel versions, and I generally shy away from using labels when I can use ID’s.
  5. So, finally I decided to use the state of the “Reset Window Position” button. This button is only enabled when View Side by Side is active.

This leads to a one-line function that checks that button’s status using its ID of 7874. There is one final wrinkle, which is that this button doesn’t appear by default in the Excel 2003 “Window” toolbar. By default it lives only in the “Add Command” toolbar which was the one that appeared in 2003 when you modified a toolbar. No problem. We can find number 7874 wherever it lives by searching through all the commandbars using FindControl. Here’s the function:

Function SideBySideOn() As Boolean
'Reset Window Position
SideBySideOn = CommandBars.FindControl(ID:=7874).Enabled
End Function

Here’s how it looks with the code running:

tweaked View Side by Side

Other Stuff Learned Along the Way

  • If you click the Reset Window Position button with the lower window selected, it moves to the top. I’d never noticed that.
  • There is no Window.Move event
  • View Side by Side in Excel 2013 is harder to use. This makes sense, since one of the biggest changes in Excel 2013 is the change to a Single-Document-Interface, making it easier to separate workbooks to multiple monitors. However it’s hard to grab the right edge of a workbook inside View Side by Side. And, as noted above, Application.Height and Window.Height now seem to be the same thing. This makes sense most of the time, but not when you are in View Side by Side mode and trying to compare the height of one of the workbooks/windows to the overall height.

Download!

Here you are my friend.

Changing Pivot Field Label’s Case

Changing Pivot Field Label’s Case

After last week’s prodigious post I return with a tiny tip: how to change the case of the text in a pivot field’s (or item’s) label. Here’s an example of the problem:

Can't fix case

Above, I’ve noticed that drunken monkeys have hacked my computer and tried to make me look bad by messing up the “Country” label. “Not so fast!” I say and retype it correctly. However, when I click back out of the cell the label reverts to the weird capitalization. Hmmm, perhaps these fiendish monkeys are smarter than I thought!

But no, it’s just one of those Excel quirks. Simply retyping the same letters with the correct case doesn’t fix it. You have to actually change or add a character, leave the cell, and then go back in and fix the case, like this:

Step 1 – add an “x” and hit Enter

Fix Case step 1

Step 2 – type it the way you want it

Fix Case step 2

I could have sworn I’ve seen this same quirk elsewhere in Excel, or perhaps in Windows Explorer, but I looked around and don’t find similar behavior anywhere. If you have, please let me know where, and if this trick works.

Autofilter VBA Operator Parameters

Autofilter VBA Operator Parameters

The other day I was flipping back and forth between two tables with related sets of data and comparing the rows for one person in each set. I do this kind of thing all the time and often end up filtering each table to the same person, location, date, or whatever, in order to compare their rows more easily. I thought “Wouldn’t it be great to write a little utility that filters a field in one table based on the same criteria as a filter in another table?” Like many questions that begin with “Wouldn’t it be great to write a little utility” this one led me on a voyage of discovery. The utility isn’t finished, but I know a lot more about Autofilter VBA operator parameters.

My biggest discovery is that the parameters used in the Range.Autofilter method to set a filter don’t always match the properties of the Filter object used to read the criteria. For instance, you filter on a cell’s color by passing its RGB value to Criteria1. But when reading the RGB value of a Filter object, Criteria1 suddenly has a Color property. More about this and some other such wrinkles as we go along.

I also realized there are a bunch of xlDynamicFilter settings – like averages and dates – that all use the same operator and are specified by setting the Criteria1 argument.

I also noticed a typo in the xlFilterAllDatesInPeriodFebruray operator. I wonder if anybody has ever even used it?

The Basics – Setting a Filter in VBA

To review the basics of setting Autofilters in VBA you can peruse the skimpy help page. To add a bit to that, let’s return to one of my favorite tables:

Pie Table

Its columns have several characteristices on which you can filter, including text, dates and colors. Here it’s filtered to names that contain “A”, pies with the word “Berry”, dates in the current month and puke-green:

Pie Table Filtered

To filter the pies to just berry you have to load their names into an array and set Criteria1 and Operator like:

Sub PieTableFilters()
Dim lo As Excel.ListObject
Dim BerryPies As Variant

Set lo = ActiveWorkbook.Worksheets("pie orders").ListObjects(1)
BerryPies = Array("Strawberry", "Blueberry", "Razzleberry", "Boysenberry")
lo.DataBodyRange.AutoFilter field:=2, _
    Criteria1:=BerryPies, Operator:=xlFilterValues
End Sub

Discovery #1 – An xlFilterValues Array Set With Two Values Becomes an xlOR Operator:
To read the filter created by the code above, I’d still use the xlFilterValues operator and read the array from Criteria1. However, if the column was filtered to only two types of pies, I’d use the xlOR operator and Criteria1 and Criteria2. This is unchanged since Johh Walkenbach posted about Stephen Bullen’s function to read filters settings for Excel 2003.

Similarly, if I set a filter with a one-element array, e.g., only one type of pie, and then read the filter operator it returns a 0.

The Basics, Continued – Setting a Color Filter

Here’s how to set the filter for the last column. It’s based on cell color. Note that it works for Conditional Formatting colors as well as regular fill colors:

lo.DataBodyRange.AutoFilter field:=4, _
    Criteria1:=8643294, Operator:=xlFilterCellColor

Here 8643294 is the RGB value of that greenish color. In order to determine that RGB in code, you need to use something like:

Set lo = ActiveWorkbook.Worksheets("pie orders").ListObjects(1)
Debug.Print lo.AutoFilter.Filters(4).Criteria1.Color

Discovery #2 – The Filter.Criteria1 Property Sometimes has Sub-Properties, Like Color:
Note that the RGB isn’t in Criteria1 – it’s in Criteria1.Color, a property I only discovered by digging around in the Locals window:

http://yoursumbuddy.com/wp-content/uploads/2015/02/Post_0076_cell_color_in_locals_window.jpg

Also note that there are a bunch of other properties there, like Pattern, etc.

Further, if a column is filtered by conditional formatting icon (yes you can do that) using the xlFilterIcon(10) parameter then Criteria1 contains an Icon property. This property is numbered 1 to 4 (I think) and relates to the position of the icon in its group.

Discovery #3: xlDynamicFilter and Its Many Settings
The xlFilterDynamic operator, enum 11, is a broad category of settings. You narrow them down in Criteria1. So, for instance, you can filter a column to the current month like:

lo.DataBodyRange.AutoFilter field:=3, _
    Criteria1:=XlDynamicFilterCriteria.xlFilterThisMonth, _
    Operator:=xlFilterDynamic

This is a good time to mention the chart below that contains all of these xlDynamicFilter operators and their enums. Looking at it you’ll note that in addition to a whole lot of date filters (all of which appear in Excel’s front end as well) there’s also the Above Average and Below Average filters.

Discovery #4: Top 10 Settings Are Weird

To set a Top 10 item in VBA you do something like this:

lo.DataBodyRange.AutoFilter field:=4, _
    Criteria1:=3, Operator:=xlTop10Items

Here, I’ve actually filtered column 4 to the top three items (As stated in Help, Criteria1 contains the actual number of items to filter). If I look at the filter settings for that column in the table I’ll see that Number Filters is checked and the dialog shows “Top 10.” That makes sense.

However if we look at the locals window right after the line above is executed, we see that the number 3 which we coded in Criteria1 is replaced by a greater than formula:

Top 3 in locals window

Further, if I then wanted to apply this filter to another column based only on what I’m able to read in VBA, I’d have to change it to:

'ValuePulledFromAnotherFilter contains ">=230"
lo.DataBodyRange.AutoFilter field:=4, _
    Criteria1:=ValuePulledFromAnotherFilter

Note that I don’t use an operator at all. We could also use xlFilterValues or xlOr. Using nothing best reflects what shows in the locals window, where the operator changes to 0 after the code above is executed.

The Chart

Below is a chart, contained in a OneDrive usable and downloadable workbook, that summarizes much of the above. It includes all the possible Autofilter VBA Operator parameters, as well as all the sub-parameters for the xlFilterDynamic operator.

Discovery #5: February is the Hardest Month (to spell)

I like to automate things as much as possible, so generating the list of xlFilterDynamic properties was kind of a pain. To get the list of constants from the Object Browser I had to copy and paste one by one into Excel, where I cobbled together a line of VBA for each one, like:

Debug.Print “xlFilterAboveAverage: ” & xlFilterAboveAverage

Of course I saw I didn’t need to paste all the month operators into Excel since I could just drag “January” down eleven more cells and append the generated month names. Minutes saved!

But when I ran the code it choked on February. It was then I noticed that its constant is misspelled.

Two More Tips

1. Use Enum Numbers, not Constants: This misspelling of February points out a good practice when using any set of enums: use the number, not the constants. For example, use 8, not xlFilterValues. This helps in case the constants are changed in future Excel versions, and with international compatibility, late binding and, at least in this case, spelling errors. Of course it’s not nearly as readable and you have to figure out the enums.

2. The Locals Window is Your Friend: I don’t use the Locals window very much, but in working with these Autofilter settings, it was hugely helpful. It revealed things that I don’t think I’d have figured out any other way.

Conclusion – Building a Utility

Armed with my newfound knowledge, I could come close to building a utility that copies filters from one table to another, or one like the Walkenbach/Bullen filter lister linked at the beginning of this post. In most cases I could just transfer the filter by using the source Operator and Criteria1 (and Criteria2 where applicable)

But as we’ve seen, there’s a couple of filter types where the operator and/or criteria change. The worst of these is a Top 10 filter. My first thought was to count the number of unfiltered cells, but if the column has duplicates, e.g., your top value appears three times, that won’t always be accurate. In addition, other columns could be filtered which could also throw off the count.

Transferring a color scale filter would be even trickier as it’s very possible the same color wouldn’t exist in the second table.

Whew! Long post! Leave a comment if you got this far and let me know what you think.

Book1.Saved = True

Book1.Saved = True

You may be familiar with VBA’s Workbook.Saved property. It’s read/write and lets you determine, or set, whether a workbook has been saved. Reading is straightforward but setting is more interesting. Declaring MyBook.Saved = True doesn’t actually save the workbook, it just tells Excel to act like it’s saved. So, if you don’t make any changes to the workbook and you go to close it, you won’t be prompted to save.

floss

If you had asked me if it’s possible to set the Saved property on a never-been-saved workbook I would have said, “I don’t know, let me try it and get back to you.” Well you didn’t ask, but I’m getting back to you anyways. It turns out you can. You can open up a brand-new workbook, type “Movin’ to Montana soon” in A1 and “Gonna be a dental floss tycoon” in A2, then enter Book1.Saved in the immediate window, and you won’t be prompted to save if you close it.

I didn’t expect that. It kind of makes sense that you can declare an existing workbook to be saved, but one that doesn’t even have a location yet? That doesn’t sit right with the left side of my brain, which would prefer that the property be re-named to something like Workbook.YouWontBePromptedToSaveUnlessYouChangeSomething.

To expand on the above paragraph, a never-been-saved workbook doesn’t have a path. You can test the ActiveWorkbook with ActiveWorkbook.Path = "", which returns True if the workbook has never been saved. I’d thought maybe setting Saved to True would change the Path property to the never-been-saved workbook location, but it doesn’t. Also, if you do decide to save, the Save As menu pops up, just as you’d hope. So it looks like setting Saved to True changes nothing except that you won’t be prompted to save.

I’ve found this useful for a certain kind of utility: one that prints out information which I want to examine but not keep. For instance, there’s a routine I use a lot that compares the sheets in two workbooks. It opens a new workbook and prints information about all the sheets in each book and whether they occur in the other book and, if so, the extent to which they match. The new workbook with the comparison is called “wbComparison,” so the last line of code is:

wbComparison.Saved = True

I know it’s a small thing, but it pleases me to look over the comparison and close it without being prompted to save.

Here’s a very simple sample for your testing pleasure:

Sub ShowZappaLyrics()
Dim wbZappa As Excel.Workbook

Set wbZappa = Workbooks.Add
With wbZappa
    With .Worksheets(1)
        .Cells(1, 1) = "Movin' to Montana soon"
        .Cells(2, 1) = "Gonna be a Dental Floss tycoon"
    End With
    .Saved = True
End With
End Sub