Author Archive

Autohotkey for WordPress Blogs

I’ve added some autohotkey dealies for when I’m writing blog posts. If you’re blog isn’t about Excel and/or doesn’t use the CodeColorer plugin, they won’t be of much use to you.

;
; AutoHotkey Version: 1.x
; Language:       English
; Platform:       Win9x/NT
; Author:         A.N.Other 
;
; Script Function:
;	Template script (you can customize this template by editing "ShellNew\Template.ahk" in your Windows folder)
;

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

GroupAdd, DDoEPosts, Edit Post ‹ Daily Dose of Excel — WordPress - Mozilla Firefox
GroupAdd, DDoEPosts, Add New Post ‹ Daily Dose of Excel — WordPress - Mozilla Firefox
#IfWinActive ahk_group DDoEPosts
:*:ccvb::
    SendInput [cc_vb][/cc_vb]{left 8}
	Return
	
:*:ccivb::
	SendInput [cci_vb][/cci_vb]{left 9}
	Return
	
:*:pmw::
	SendInput PMW
	Return
	
:*:vbe::
	SendInput VBE
	Return

The first one expands brackets so I can paste in VB code. The second does the same but for inline vb code.

The third and fourth take common abbreviations and expand those so the definition appears when you hover over them. More of those to come, I’ll bet.

Awesome.

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.