Author Archive

An Even More Better AutoFilter

You knew I wasn’t going to let this go, didn’t you?

I started with snb’s rewrite. I really don’t want to use the SelectionChange event. It runs whenever you move around the spreadsheet and that’s just wasteful. I like how snb did the heavy lifting on SheetActivate, then only burns processors when you change a cell. I probably still need some error checking (and by probably I mean definitely) but here’s what I have so far.

Public gclsApp As CApp

Public Sub Auto_Open()
   
    Set gclsApp = New CApp
   
End Sub

Why do I always create my event class and then set the App property equal to the Excel.Application? Why not just do that in the class Initialize event? Stay tuned.

Private WithEvents mclsApp As Application
Private Const msDELIM As String = "||"

Public Property Set App(ByVal clsApp As Application): Set mclsApp = clsApp: End Property
Public Property Get App() As Application: Set App = mclsApp: End Property

Private Sub Class_Initialize()
   
    Set mclsApp = Application
    If Not ActiveSheet Is Nothing Then
        mclsApp_SheetActivate ActiveSheet
    End If
   
End Sub

I got rid of the OldValue property as I’m using snb’s method. I added a constant delimeter that I’ll never use in a table header. Then I set my application right in the Initialize event, which I should have been doing all along. Finally, I need to load up the AlternativeText for any sheets just in case it’s not done yet.

Private Sub mclsApp_SheetActivate(ByVal Sh As Object)
   
    Dim lo As ListObject
   
    If Sh.Type = xlWorksheet Then
        For Each lo In Sh.ListObjects
            lo.AlternativeText = Join(Application.Index(lo.HeaderRowRange.Value, 1, 0), msDELIM)
        Next lo
    End If
   
End Sub

This is right out of snb’s code. Join the header into a big string separated by double pipe, then stick it in the AlternativeText property for safe keeping.

Private Sub mclsApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   
    Dim lo As ListObject
    Dim rLoHeader As Range
    Dim sHeader As String
   
    'See if the target is in the header of a listobject
    On Error Resume Next
        Set rLoHeader = Nothing
        Set rLoHeader = Intersect(Target, Target.ListObject.HeaderRowRange)
    On Error GoTo 0
       
    Application.EnableEvents = False
   
    If Not rLoHeader Is Nothing Then
        Set lo = Target.ListObject
               
        'if the user starts the entry with two spaces, they want to change the header
        'so don't fire the code, just change the header sans the spaces
        If Left$(Target.Value, 2) = Space(2) Then
            sHeader = Mid$(Target.Value, 3, Len(Target.Value))
        Else
            'Filter based on the value typed
            sHeader = Split(lo.AlternativeText, msDELIM)(Target.Column - lo.Range.Columns(1).Column)
                       
            'I ran into that code firing twice problem when I changed this line. I brute forced the
            'sucker by seeing if the Target.Value is the same as the header.
            If Target.Value <> sHeader Then
                'If the user enters more than one value separated by a space, it will filter on all those
                'values.
                lo.Range.AutoFilter lo.ListColumns(Target.Value).Index, Split(Target.Value), xlFilterValues
            End If
        End If
       
        Target.Value = sHeader
    End If
   
    Application.EnableEvents = True
   
End Sub

First I make sure that the cell being changed is in the header of a ListObject (Excel Table in UI speak). This will disastrously fail (I assume) if you change two cells at once.

Next I added some code that will allow me to actually change the header if I want to. If I precede the entry by two spaces, the code will assume I want to change the header and not filter. Then it removes the two spaces and changes the header without filtering. If I type {Space}{Space}MyDate in the Date field, it will change the header to MyDate and not filter.

Joe commented that you could separate values with a comma to filter on more than on thing. Good idea. I like spaces better, so instead of filtering on Target.Value, I pass the AutoFilter method an array and use xlFilterValues. The Split function produces an array by splitting a String on space.

When I made this change to the AutoFilter method, I ran into my old friend double-event-trigger-for-damn-reason. I beat that problem over the head by checking if the search term was the same as the header – a characteristic of the second bullshit trigger. This introduces a bug when you want to filter the State field on the word “State”. Nothing will happen. I don’t care. I’m done with that problem.

It’s working awesomely and I’m about ready to put it in the PMW to give some real-life test.

One more thing. If you want to filter on partial names you have to include an asterisk. Entering Col* Ala* will give you Colorado and Alaska (from my Sample data – Alabama didn’t make the cut, I guess). If you type *hi*, you’ll get Ohio, New Hampshire, and Washington.

OK, really the last thing. If you want to filter on dates by typing multiple dates, you have to type the full year.

You can download BetterAutoFilter.zip

A Better AutoFilter

Jeff recently wrote about how you can type your filter criterion in a Pivot Table’s page field and it will filter it automagically. That’s awesome. I want the same thing when I filter Tables, so I started doing some experiments. To filter a table, you select the header, press Alt+{DOWN}, e to get to the search box, and type the search term.

Press Enter

I want to type ‘Colorado’ right in the header and have it filter.

VoilĂ 

How did I accomplish that magic? First I created a class module call CApp. It will be used to house my application level events. Up in the declarations section of CApp, I have this

Private WithEvents mclsApp As Application
Private msOldValue As String

Public Property Let OldValue(ByVal sOldValue As String): msOldValue = sOldValue: End Property
Public Property Get OldValue() As String: OldValue = msOldValue: End Property
Public Property Set App(ByVal clsApp As Application): Set mclsApp = clsApp: End Property
Public Property Get App() As Application: Set App = mclsApp: End Property

The mclsApp variable is declared WithEvents so that VBA exposes all the events of the Application object to me in this module. I’ll be using two of those events, SelectionChange and Change, to determine when to filter. The OldValue variable will hold the header that I’m overtyping so I can put it back. For instance, when I replace State (the column heading) with Colorado (the search term), I need to put the heading back to State.

To capture that old header value, I use the SheetSelectionChange event. Whenever the selection changes, this procedure is run.

Private Sub mclsApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    Dim rLoHeader As Range
       
    'See if the target is in the header of a listobject
    On Error Resume Next
        Set rLoHeader = Intersect(Target, Target.ListObject.HeaderRowRange)
    On Error GoTo 0
   
    'If it's in a header, save the header's column heading
    If Not rLoHeader Is Nothing Then
        Me.OldValue = Target.Value
    Else
        'Otherwise, clear the old value
        Me.OldValue = vbNullString
    End If

End Sub

If I’ve select a cell that’s in the header of a ListObject (that’s what VBA calls a Table), then save the value. This is just some test code. It needs far more error proofing, such as making sure only one cell is selected.

Next I use the SheetChange event to monitor if I type a new value in that header. First I disable events so that when I put the old header value back, it doesn’t think I’m trying to filter again.

Private Sub mclsApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   
    Dim sFilter As String
       
    Application.EnableEvents = False
   
    If Len(Me.OldValue) > 0 Then
        'Save the search term for later filtering
        sFilter = Target.Value
        'Change the header value back
        Target.Value = Me.OldValue
        'This shouldn't be necessary, but read on
        Me.OldValue = vbNullString
       
        'Filter based on the value typed
        Target.ListObject.Range.AutoFilter Target.ListObject.ListColumns(Target.Value).Index, sFilter
       
    End If
   
    Application.EnableEvents = True
   
End Sub

I really don’t mind using the built-in autofilter string of keystrokes when I’m filtering on a string or a number. But dates? That’s another story. I hate autofiltering on dates. If I want to filter the above list on June 22nd, the keystrokes are: Alt+{DOWN}, e, {TAB}{TAB}, {SPACE} to uncheck Select All, {DOWN}{DOWN}{RIGHT} to expand June, 2 2 {SPACE} to get to the second entry that starts with a ‘2’ and check it, {ENTER}.

Stupid. I should be able to get to the search box and type 6/22 and have it filter. But it doesn’t. I though this method would make filtering on dates much better. And I was right.

Did you happen to see the comment in the above code about a particular line not being necessary? I didn’t want to remove OldValue in the SheetChange event because that’s the job of the SheetSelectionChange event. I shouldn’t need to do it. I didn’t need to do it for filtering on strings, but without it, I can’t filter on numbers or dates. For some reason that I couldn’t figure out, the SheetChange event was being called twice. The first time it would filter on ‘6/22/2014′ as expected. Then it would run again (even though I clearly have turned off events) and would filter on ‘Date’ (the column header), which, of course, it can’t find in a column of actual dates.

I even tried to make my own event enabler/disabler, but it didn’t matter. Once I set OldValue to vbNullString, filtering on numbers and dates started working. The event procedure still gets called twice, but it doesn’t try to filter because OldValue isn’t there anymore.

That leaves a potential problem. If I type, say, “Montana” in B1 and enter using Ctrl+Enter rather than just Enter, the selection doesn’t change and OldValue is blank. Now, before selecting any other cells, if I type ‘Colorado’, nothing happens. That’s not a big problem for me because I have my options set to go down on enter and wouldn’t really use Ctrl+Enter in that case. But that doesn’t mean I like it. I don’t.

This hasn’t made it into my PMW yet, but I’d like to see where it can go.

You can download BetterAutoFilter.zip

A Daily Overdose of Arrays

snb, who wrote the detailed piece on dictionaries, has applied the same rigor to arrays.

I knew there would be something in there that I didn’t know (there was plenty). Under the section about identifying the existence of an item in an array, you can use a defined name:

6.2.2.3 The Excel-object ‘Names’

Assign the array to a Name’.
Excel creates an arrayformula, consisting of a string.
The item can be searched for in that string.
Embed the search string in quotation marks.
Embed numerical values in commas.

Names.Add "snb_002", a_sn
msgbox InStr(Names("snb_002").Value, """abcde""")>0
msgbox InStr(Names("snb_002").Value, ",1245,")>0

snb’s VBA for Smarties: Arrays

Back in the Saddle

And we’re back.

Leave your comments admonishing my server maintenance skills here. I promise I will never try to do the right thing again. KNOW YOUR LIMITS! That’s my new mantra.

MLB Runs By Inning

There is an office pool for the MLB post season that goes like this: Each participant is assigned a half inning per game in the ALCS, NLCS, and World Series. A participant may get the top of the 1st in one game and the bottom of the 6th in another game. There are 20 participants for each half of 10 innings and innings after 10 roll around to the beginning. Every run scored in an inning assigned to you earns you a point.

The challenge is to distribute the innings among the participants. If you always get the bottom of the 10th (an inning that’s rarely played for you non-sportsballers), you wouldn’t fair very well. But what other innings are traditionally good or bad? Is the first inning littered with runs while the fourth is sparse? I couldn’t find any historical data online, but I do have the results of a pool from last season.

Based on this data, I don’t think one post season (21 games) is enough. Why would innings 3, 6, and 7 be higher? Why would the bottom of the innings total 68 runs, 24 more than the top of the innings cumulative 44 runs?

I thought I could assign some sort of value to each half inning, then make sure that each participant got roughly the same value. But I didn’t expect such disparity in innings nor in top/bottoms, so I’m not sure that works. I think I’ll end up making sure everyone gets the 10th inning once, then an even distribution between 1-3, 4-6, and 7-9. And if someone gets the bottom of the 9th more than once, bad luck for him.

Hardware Recommendations

It seems I have server-issue reprieve, so I’ll take advantage of it by throwing up the first post in weeks. Don’t worry, it’s not Excel related. I mean, who can expect that from this site. I do have a couple posts in the hopper, I just can’t seem to login when I want to write the up.

I got an email from an old friend asking for my recommendations for a new desktop computer. I wrote a longer reply than I expected and figured I might as well post it here. That way you can give your two cents in the comments. First, I’ll summarize his question/requirements:

I want “serious scientific/number-crunching/math-oriented”, and things labeled “home”, “office”, and “gaming” don’t seem like the right fit. I would like to use this machine primarily for compiled Python, S Plus, Mathematica, and C++ dll’s I am coding for Excel and OpenOffice. Specific points:

  1. Is 8 GB RAM enough? Speed-wise, will 16 or more make a “significant” difference?
  2. Which processor: i7?
  3. Will a hybrid drive make any difference? Incidentally, I keep all of my documents on my NAS; I would, of course, keep development versions on the local HD, but really don’t need more than 150 MB (if that!) on it.
  4. Dell looks good – highly configurable — what’s your take?
  5. Monitor/Video Card: One of my Macs is 27″, and is awesome for my wife’s photo/cine avocation. I am writing you from a 17″ Mac ProBook. Works for me. So I won’t need a particularly large monitor (which one?!?); what I should do re the video card?
  6. I/O: Several USB 3.0; Wired Ethernet, of course; Bluetooth; Thunderbolt. DVD and SD drives would be great.

Here’s my reply:

I don’t profess to be an expert, but I’m happy to give you my opinion. Just take it for what it’s worth. My last three desktops were 1) custom built 2) HP HPE-150f and 3) custom built.

My Last Three Computers

  1. I read codinghorror.com about how he built his own home theater PC. I was cutting cable and needed something to run Windows Media Center for over the air TV. I somewhat followed his lead, but since mine would be out of sight, I didn’t worry about making it small or quiet. I wouldn’t recommend my particular build for your purposes because it was built with the new (at the time) Intel chipset that had HDMI out built-in.

    I was in a situation where I didn’t trust Dell anymore, didn’t know who to trust, and was interested in trying my hand at building hardware just for the experience. I made some errors along the way, but all-in-all it has been an outstanding machine. My biggest mistake was not buying a big enough power supply. I also had a hard drive go bad in the first year. Even with the missteps, the whole cost was about $1,400.

  2. I was ready to replace my Dell desktop because it was 7 years old. I had a reasonably good experience building a PC so I was going to go that route again. It was a different build, so I there was still some chances to make mistakes, but I priced it out on New Egg anyway. Then I went to HP to see what a comparable machine would cost. It was a few hundred dollars cheaper and it didn’t really make sense for me to build it myself. I got an i7 2.8Ghz with 8 GB.

    That was four years ago, or so, and this machine has been terrific. Zero problems and it does everything I want performance wise. My only problem with HP is that they have a lot of craziness going on – they announced they were getting out of the PC business, then retracted. It just gives me pause. We use HP exclusively at work (~1,000 desktops) and they are probably still my go-to manufacturer.

  3. The third desktop was for my son. It was still a better deal not to build it, but I wanted to give him the experience of building a PC from srcatch, so we went that route. It was a pretty good experience and it has been a good machine. If you’re interested in building your own, I’ll send you the component list.

Operating Systems

I run Win7 and my son runs Win8. He prefers Win8, but kids are stupid, so what are you going to do. On my HTPC, if I upgrade to Win8 I have to pay extra for Windows Media Center. In other words, I’m never upgrading. And when Win7 doesn’t work anymore, I’m going Linux/XMBC for the HTPC. Sorry Microsoft, but when you stopped building problems for guys like me you should have expected this.

Your Specific Questions

  1. I bought my HP with 4 GB (the stock amount) and bought another 4 from crucial.com. You can save some pretty good money that way, but price it both ways. Just make sure you get enough slots if you’re going to add after market RAM. If I were buying today, I wouldn’t get less than 12 GB because RAM is so cheap that even an incremental increase in performance is worth it. I’d look at 16 too. But really, I’ve been running 8 GB for a while, and I have no problems, so it might be a waste of money.
  2. I like my PCs to last 5-7 years, so I tend to over buy on CPU and RAM. I don’t what’s newer and better than i7, but I definitely would not go with i5.
  3. Another misstep on building my own was not having an SSD for a boot drive. If I were building again, I would get an SSD for a primary and a Western Digital for storage. SSDs are getting more reliable at a pretty quick pace.
  4. Six years ago, or so, I stopped buying Dell. I bought them exclusively for home and work for the 10 years prior, but the quality of the components was going down and I’d had enough. I’ve heard they’ve made a bit of a comeback, quality wise, but I’m not sold yet. Concurrent with them buying cheaper parts, they moved their customer service to India. Back in the day when I didn’t have to talk to Dell customer service because their shit just worked, I wouldn’t care where it was. But with failing parts, it was a problem. I’m not trying to denigrate an entire sub-continent. It was just that the company Dell hired really sucked. I’m sure the rest of India is very knowledgeable.
  5. I have two 22″ monitors side-by-side and love it. I can’t imagine coding in Excel without the VBE on one screen and Excel on the other. Even when I code in Ruby, Python, Java or whatever, I need to have stackoverflow on one screen and the IDE on the other. I will never have one monitor again – it is the single best productivity boost in my computing career (including my keyboard shortcut mania). Both my monitors are Acer that I got from Newegg. They’re slightly different heights, but I find that piano sheet music books level them up nicely. If you can get identical, that’s even better. My work monitors are identical 22″ ASUS and I’m very happy with those too.
  6. Don’t sacrifice on USB ports. They don’t all have to be 3.0, but make sure you have double what you think you need. Even if you don’t go with two monitors, you should consider a dual output video card in case you change your mind.

I’m a couple years away from replacing my desktop, but if I were forced to do it, I would price out HP, Lenovo, and Asus. And probably Dell for good measure, but I would have to be really convinced to go back to Dell. Lenovo and Asus have awesome reputations in laptops and I don’t know why that wouldn’t translate to desktops. I bought a used Asus convertible tablet several years ago and it still runs like a champ.

End of reply

If you have any opinions on desktop hardware, please leave them in the comments.

Servers Suck

Hey, did you notice that this page loaded in a few milliseconds instead of timing out? Me too!

I don’t know what caused that period of slow down because I don’t know much about servers. I know my CPU percentage has been higher lately. Here’s what it looks like over the last 30 days.

And over the last 24 hours.

Last night I created a support ticket with Digital Ocean. And today it miraculously works. It makes me suspicious, but I can’t imagine a business model where you throttle your customers until they call you on it. I mean except for the cable and cell phone industries.

So I’m going to enjoy it while it lasts. I’m also going to get in an Excel post today before I have more problems.

More AutoHotkey Sugar

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

Definition and Last Position in the VBE

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

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

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

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

Switching tabs in Access

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

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

AHK’d!

Changing Field Size in Access

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

+F6::
    Send {F6 5}
    Return

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

Windows APIs

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

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

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

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

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

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