Adding Functionality – 2a – Daily Dose of Excel

Dick Kusleika’s VBE Find is useful enough that it justifies the resources need to enhance it.

In addition to adding Regular Expression searching (Adding functionality – I) a sort capability made sense since since the results include code from multiple code modules and procedures within them. The original code showed 4 elements for each match: module name, procedure name, if any, line number, and the code itself. Reasonable columns for sorting seem to be the 1st three (module, procedure, and line). For the sake of simplicity, if the user selects multiple sort criteria, the sequence will be left-to-right. So, if all three sort criteria are specified, the sort will be first by module name, then for each module by procedure name, and finally within each procedure by line. On the other hand, for a module-and-line sort the result will be first by module name and within each module by line number.

The final decision before changing the UI was how the sort would work. Rather than a custom solution for this problem, this seemed to be a good opportunity to implement a general search algorithm for a 2D matrix with one-or-more columns specified as the sort columns. For this specific task, the 2D matrix meant it could capture the contents of the lbxFound field in their entirety. The sort columns would be specified in a 1D array. So, to sort on just the line number, this array would have 1 entry in it, the value 2 (based on a starting value of 0). To sort by module name, procedure name, and line number, the array would have three entries: 0, 1, and 2.

With these decisions out of the way, it was time to change the UI. The three sort checkboxes line up with the first three columns of the results.

The UI related code changes were not a lot: One procedure to coordinate (coordinate not actually perform) the sort, event procedures for each of the three checkboxes to call this common routine, and a change to the tbxFind_Change routine to also call this common procedure.

The common procedure configured the 1D array for the multi-column sort (as explained above) and passed off the sort responsibility to an yet unwritten routine called doSort.

Sub addSortKey(aCheckBox As MSForms.CheckBox, _
ByRef SortCols() As Integer, ByVal aVal As Integer)
If Not aCheckBox.Value Then Exit Sub
SortCols(UBound(SortCols)) = aVal
ReDim Preserve SortCols(UBound(SortCols) + 1)
End Sub
Sub checkForSort()
Dim SortCols() As Integer: ReDim SortCols(0)
With Me
addSortKey .sortModule, SortCols, 0
addSortKey .sortProc, SortCols, 1
addSortKey .sortLine, SortCols, 2
End With
If UBound(SortCols) = 0 Then Exit Sub
ReDim Preserve SortCols(UBound(SortCols) – 1)
Dim DataArr(): DataArr = Me.lbxFound.List
doSort DataArr, SortCols
Me.lbxFound.Clear
Me.lbxFound.List = DataArr
End Sub

The three event procedures simply call the checkForSort routine, as does the addition to the tbxFind_Change routine.

Private Sub sortLine_Click()
checkForSort
End Sub

Private Sub sortModule_Click()
checkForSort
End Sub

Private Sub sortProc_Click()
checkForSort
End Sub

Private Sub tbxFind_Change()

{existing code}

checkForSort
ErrXIT:
End Sub

Add an empty doSort procedure and the existing code would continue to function. It would not do any sorting but the existing functionality would remain available.