I populate a ListBox with the results of whatever sql select statement I enter into a database. After the ListBox is populated, I need the option of sorting any column properly regardless of whether it contains numeric or text data. Luckily, my columns are typically one or the other, not mixed. In my listbox CompareRows I have the following code which tests whether the value in row 0 of the column whose header I’ve clicked isNumeric. If true it sorts numerically, if not, it uses the built-in default lexicographical sort function. It seems to work, but I’m wondering:
is there a better way?
What should be done if I ever have a column with mixed data?
if it is this simple, why wasn’t it built in as the default function?
if isNumeric(Me.CellValueAt(0, column)) then
result = Sign(Val(Me.CellValueAt(row1, column)) - Val(Me.CellValueAt( row2, column)))
for your purposes ? unlikely - the “best way” is the one that does what you want
Thats going to depend on your goals
If you want to sort like Finder thats one type
If you want lexicographical thats different
It will depend on what you decide, or your users decide, is “the right way”
Because IsNumeric may not be appropriate for everyone
I realize this is a wishy washy answer but whats “right” will vary from application to application and the goals the software and this list display have
Thanks Norman. For a long time I had to sort the ListBox lexicographically which didn’t suit my need for also being able to sort numeric columns properly. This was especially true because my Listbox can be populated with a wide variety of data with various numbers of columns and orders that made using Case statements within the CompareRows function to identify the data type impossibly complex. After fiddling about, I finally stumbled on how to accommodate both types of sorts and was 1) amazed at how simple the code was and 2) also amazed that such a simple solution wasn’t mentioned in the documentation. While you’ve noted that it wouldn’t work for everyone, particularly those whose columns contain a mixture of numeric and text cells, I think it would work pretty well for many users.