Excel VBA Gotcha of the Day…

By wheat | Published May 28, 2004

Here’s a bit of Excel VBA code from a button in a workbook I created recently (line extender characters ["_"] removed for clarity). This code sorts the sheet (well, the first 1000 rows of it, minus the header row) ascendingly by column C. The worksheet in question had a series of buttons similar to this for sorting thing on the fly:

Private Sub sort_by_client_Click()
     Rows("1:1000").sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

Don’t let the sheer ugliness of it throw you, that’s the nature of Excel VBA. Here’s the deal: the code above works fine on Excel 2002/XP but throws an error (run-time error 1004) on Excel 2002. The reason? The last argument in the sort method didn’t exist in version 2000. You fix it just by chopping it out. Thus:

Private Sub sort_by_client_Click()
     Rows("1:1000").sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Now, run along and play. And don’t think about Excel (or any other Microsoft product) until Monday.

Popularity: 1% [?]