Hi All,
I’ve just updated Charles Williams and my Name Manager utility.
New in this version is that when you delete a range name, you will be asked whether you want to unapply the range name in your formulas. This should effectively prevent any #NAME! errors from surfacing when you remove a range name.
I’m now off to remove one feature request from the list.
Any wishes? For Name Manager that is :-)
Regards,
Jan Karel Pieterse
www.jkp-ads.com
Dick Kusleika shared VBE Find, a solution that intentionally had no choices and a straightforward UI. [In his post he also pointed out a few limitations with it.] Unfortunately for him, I liked it so much that I enhanced it to the point that he might now be uncomfortable with the bells and whistles. [Among the changes I shared earlier were adding RegEx support, adding a sort UI and the associated Bubble Sort code of a 2D matrix on any number of columns, and the ability to pick up the word containing the cursor.]
This post documents all the changes from the user’s perspective and from a maintenance perspective, and shares the updated file.
In the add-in dialog box, the add-in name is now VBE Helper.
The capabilities other than the VBE Find remain untouched from DK’s version.
Additional code in the OpenClose code module confirms that the add-in can access the VBE.
All the menu (commandbar) changes are now temporary.
As far as the Find capability goes…
The keyboard access is still ‘Alt t k f’. If anyone knows how to assign a custom keyboard shortcut in the VBE…
The Find button is gone. It’s no longer needed.
The Cancel button still remains. It’s a small white X on a red background in the top right corner of the form. It remains exclusively to support exiting the form with the Esc key. So, to close the form, use either the Esc key or click the form’s X button or this button.
The form is shown modeless. This means one can interact with the code with the form still active (and the reason why the Find button is not needed).
When the form first shows, it will automatically pick up the current selection (if it does not span a line boundary) or, if there is no selection, the word containing the cursor.
If the Find field contains a token, it will be selected.
Click on any row of the results shown and the code will highlight the search item on that line of code.
The form includes support for finding by ‘whole word’, ‘match case’, and ‘pattern matching’. These are as in the default Find and use the same search mechanism. I find the ‘Pattern Matching’ somewhat less than useful since it doesn’t support the entire Regular Expression feature set.
So, there is the ‘Match with Regular Expression’ option. This uses new code added by me.
If there is an error in the search pattern (either for ‘pattern matching’ or ‘match with regular expression’ the error will be shown in red between the 2 rows of checkboxes.
The result is now sortable on any of 3 columns (module name, procedure name, if any, and line number). The priority of multiple sort columns is left-to-right in the UI. When the last checkbox is unchecked the results are left untouched (i.e., they do not return to the original sequence).
When the userform, i.e., dialog box, is closed, additional code ensures that the user is in the window where s/he was last.
If one changes the active project while the dialog box is still open a subsequent search will search the project that was the active project when the dialog box was first shown. Not the nicest of features but we’ll leave that for version 3.
What else would I like to add? Well, a custom shortcut key combo. But, I don’t know how to do that. And, if I change the code while the dialog box is still open, then, I’d like the results updated, at least when I click in the dialog box again. Unfortunately, I don’t know how to do that either since there is no userform event that triggers when one clicks in a code pane and then clicks back in the dialog box (the Activate event doesn’t work). Nor is there a VBE event to detect a code change.
Tushar Mehta
www.tushar-mehta.com
In addition to being able to sort the results of Dick Kusleika’s VBE Find I wanted the code to automagically fill in the search field. If there is a selection, the code should use that selection as long as it doesn’t span more than one line. If there is no selection, then the add-in should use the word or token currently containing the cursor. As an example, if the cursor is between the ‘s’ and the ‘u’ or between the ‘u’ and the ‘b’ of ‘Sub’ in the line Private Sub UserForm_Activate(), the add-in should prefill the search field with the word Sub. Similarly, if it is anywhere between the ‘U’ and the final ‘e’ of ‘UserForm_Activate’ it should prefill the search field with ‘UserForm_Activate’ Finally, if it was in a non-word token — i.e, between the e and the ( or between the ( and the ), it should prefill the search field with the complete non-word token, () in this case.
The code below does that. And, yes, the code can be used with any scenario with a text string and a ‘current position’ indicator. What the code does is the following. It splits the total string at the current position. Then, it picks up the string from the left of the token to the current position and, separately, the string from the current position to the end of the token. Finally, it puts the two pieces together.
The code reuses some of the Regular Expression functions developed to include regexp search patterns.
Function RegExpFindLoc(ByVal SearchWhat As String, _
ByVal SearchFor As String, _
IgnoreCase As Boolean, ByRef SearchToken As String) As Long
Static X As RegExp: If X Is Nothing Then Set X = New RegExp: X.Global = True
X.IgnoreCase = IgnoreCase
X.Pattern = SearchFor
On Error Resume Next
Dim RERslt As MatchCollection
Set RERslt = X.Execute(SearchWhat)
If Err.Number <> 0 Then
Me.ErrMsg.Caption = Err.Description
Exit Function
End If
If RERslt.Count = 0 Then Exit Function
With RERslt(0)
RegExpFindLoc = .FirstIndex + 1
SearchToken = .Value
End With
End Function
Function RegExpFind(ByVal SearchWhat As String, _
ByVal SearchFor As String, _
Optional IgnoreCase As Boolean = True) As Boolean
Static X As RegExp: If X Is Nothing Then Set X = New RegExp: X.Global = True
X.IgnoreCase = IgnoreCase
X.Pattern = SearchFor
On Error Resume Next
RegExpFind = X.Test(SearchWhat)
If Err.Number <> 0 Then
Me.ErrMsg.Caption = Err.Description
End If
End Function
Function WholeToken(ByVal Str As String, ByVal Pos As Long) As String
Dim LeftPattern As String, RightPattern As String
Dim CurrChar As String: CurrChar = Mid(Str, Pos, 1)
If RegExpFind(CurrChar, “w”) Then ‘in a word
LeftPattern = “w*” & CurrChar & “$”
RightPattern = “^” & CurrChar & “w*”
Else
LeftPattern = “W*” & CurrChar & “$”
RightPattern = “^” & CurrChar & “W*”
End If
Dim LeftLoc As Long, RightLoc As Long, _
LeftToken As String, RightToken As String
LeftLoc = RegExpFindLoc(Left(Str, Pos), LeftPattern, True, LeftToken)
RightLoc = RegExpFindLoc(Mid(Str, Pos), RightPattern, True, RightToken)
WholeToken = LeftToken & Mid(RightToken, 2)
End Function
An example of how to use the WholeToken() function is the enhancement to the Userform_Activate function. The GetSelection method returns the line and column of the start and the end of the current selection. If there is none, the start and end values are the same. So, depending on those values, the add-in uses either the selection or calls the WholeToken function to get the word (or token) containing the cursor.
In Adding Functionality – 2a I laid out the UI changes to support sorting of the results of Dick Kusleika’s VBE Find.
In this post, I develop the actual sort routine.
To recap, the task is to sort a 2D matrix on any number of columns. The sort columns are listed in a 1D array, with each entry in this array identifying one sort column. So, the sort routine’s signature is
The routine uses what is called the Bubble Sort. It is fast for “small” matrices, and will be more than adequate for our needs.
The Bubble Sort essentially goes through the matrix row by row. For each row, it compares that row with every other row between itself and the end of the matrix. If necessary, it swaps the two rows. Consequently, once we are done with a particular row, we never need to reexamine it again. And, that’s why it’s called a Bubble Sort. The results “bubble” to their correct spot.
That’s it. At its core, that’s the Bubble Sort. If we had a single key to sort on, we’d use the test DataArr(I,key-column) > DataArr (J, key-column). However, in this case, we have to cater to possibly multiple keys in the SortCols array. So, we write a slightly more complex comparison routine — and in the process also convert a number provided as a string to a numeric value.
To process multiple keys, we start with the ‘outermost’ key. If we can decide on the relative positions of the 2 records, we don’t have to check the next key. However, if the 2 key values are equal, we check the next key.
Function ArrLen(Arr, Optional whatDim As Integer = 1)
ArrLen = UBound(Arr, whatDim) – LBound(Arr, whatDim) + 1
End Function
Sub swapRow(DataArr, R1 As Long, R2 As Long)
Dim J As Long
For J = LBound(DataArr, 2) To UBound(DataArr, 2)
Dim Temp
If TypeOf DataArr(R1, J) Is Object Then
Set Temp = DataArr(R1, J)
Set DataArr(R1, J) = DataArr(R2, J)
Set DataArr(R2, J) = Temp
Else
Temp = DataArr(R1, J): DataArr(R1, J) = DataArr(R2, J): DataArr(R2, J) = Temp
End If
Next J
End Sub
Sub doSort(DataArr(), SortCols() As Integer)
If ArrLen(DataArr) = 0 Or ArrLen(SortCols) = 0 Then Exit Sub
‘Swap the rows of DataArr as needed to perform an ascending sort
Dim I As Long, J As Long
For I = LBound(DataArr) To UBound(DataArr) – 1
For J = I + 1 To UBound(DataArr)
Dim SortColIdx As Integer
SortColIdx = LBound(SortCols)
Dim PairDone As Boolean: PairDone = False
Do
Dim X1, X2, SortCol As Long
SortCol = SortCols(SortColIdx)
X1 = DataArr(I, SortCol): X2 = DataArr(J, SortCol)
If IsNumeric(X1) And IsNumeric(X2) Then X1 = CDbl(X1): X2 = CDbl(X2)
If X1 > X2 Then
swapRow DataArr, I, J
PairDone = True
ElseIf X1 < X2 Then
PairDone = True
Else
SortColIdx = SortColIdx + 1
End If
Loop Until PairDone Or SortColIdx > UBound(SortCols)
Next J
Next I
End Sub
The algorithm I eventually implemented uses an ‘index array’ to track the final sequence of the rows in the data matrix. Use of an index array means that each swap involves just 1 element rather than every column in the data matrix.
Here’s where one would see a benefit. Suppose we have 3 records with the keys 33,22, and 11, respectively. Then, the final, and desired, sequence would be the records with keys 11,22, and 33.
With a Bubble Sort we would get the following swaps: (33,22,11) -> (22,33,11) -> (22,11,33) -> (11,22,33). So, we have to swap the rows 4 times, with each swap requiring an exchange of all the columns in the 2 rows, one at a time.
Alternatively, we could build an array with the record numbers in it, i.e., (1,2,3). Now, we change only the contents of this array leaving the original array alone. So, the four swaps would yield the index array (1,2,3) -> (2,1,3) -> (2,3,1) -> (3,2,1). This gives us the sequence of the final output, i.e., row 3, row 2, and finally row 1. So, we would have 4 swaps with the index array and 3 sequence moves for the data matrix.
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.
The three event procedures simply call the checkForSort routine, as does the addition to the tbxFind_Change routine.
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.
Inspired by Dick Kusleika’s “Inflexible Find” I decided to add some flexibility to it. In a comment to his original post (http://www.dailydoseofexcel.com/archives/2011/02/10/vbe-find/#comment-58902), I indicated that the userform was now shown modeless. This meant that one could interactively search for a token and examine the associated code.
Another improvement I wanted to make was a ‘find whole word’ capability. And, as always, I wanted to do it with minimal impact on the existing code, which, in turn, meant minimal testing. Luckily DK’s coding approach works well for me.
For obvious reasons, I decided to use a Regular Expression object. It requires a reference to ‘Microsoft VBScript Regular Expressions’ library (in the VBE, Tools | References…)
I added a checkbox named FindWholeWord to the userform.
Next, I added a function in the userform’s code module to check if a string token exists inside another string.
Then, I had to make only minimal changes to the code in the tbxFind_Change routine, replacing
with
and
with
Commenting out the original code leaves an easy fallback to functioning code just in case there’s a problem with the new code.
Finally, I added an event procedure so that a change to FindWholeWord would trigger the search.
While I did test the changes, I was fairly confident I didn’t have to.
I also laid the groundwork to add ‘case matching’ through a trivial extension to the userform: add a checkbox named, say, MatchCase, and replace the ‘True’ in above call to RegExpFind with Not me.MatchCase.value
For those who know the VBE object model, there is a codemodule.find method that should have made all of the above a lot simpler. However, I encountered two problems with it.
The first had a workaround. After going through all the lines in a codemodule, the find method kept on returning the last match it found.
The second was of more concern. I would have thought that the built-in method would be much faster than the above VBA-based solution. However, for ‘whole word’ searches, the Find method had a noticeable delay. Don’t ask me why. Like I said, I would have expected it to be faster.
I ran into some strangeness while copying tables from the web to Excel. To demonstrate, I’ll use Debra’s sample data table. I run this code
That code automates Internet Explorer, navigates to the page, gets the table, puts it in the clipboard, and pastes it to a cell.
Nice. I delete the data and merge A1:C1
I run the code again and get this
Not so nice. I fixed it by adding this line to the code
That was easy enough. The problem was that I didn’t know it was merged cells causing the problem. I had some tables coming in fine and other were truncated at seemingly random spots. Once I discovered that some of the cells were merged, I deleted the rows and it ran fine. Then later, it would screw up again. Finally I figured out that the HTML table had some colspan attributes in some of the td tags. The colspan attribute, when pasted into Excel, creates merged cells. Now you know.
And if you’re wondering, the tables in question were in a website that required a log in, so I couldn’t use a web query. By automating IE, I could navigate to the login page, fill in the form, then navigate to the page with the tables I wanted. I’ll post about that as soon as I work out a few bugs.
I’ve never been a fan of the Find function as it is usually implemented. You open the Find dialog, type in your text, set some options, and go to the first instance. Then you click Find Next to go to the next instance. Excel has a Find All button that’s a little better, but is still overkill for 99% of my Find needs. That’s why I wrote Inflexible Find, which I use extensively every day.
Now I want something similar for the VBE. I want to type in some text and have it show my everywhere that text is. I don’t care about upper and lower case. I don’t care what module it’s in. Sometimes I do care about that stuff and I can use the built-in Find when I do. But for most Find operations, I just want something quicker. I set about writing an inflexible find for the VBE. It isn’t going well.
Here’s what the built-in find looks like
Here’s what my find looks like
I like mine better except for all the reasons that I don’t. There’s nothing fancy about the code. When I type something in the textbox, the listbox fills with lines that contain that string
Dim vbc As VBComponent
Dim cm As CodeModule
Dim i As Long
Dim sFindWhat As String
sFindWhat = LCase(Me.tbxFind.Text)
Me.lbxFound.Clear
If Me.Vbp.Protection = vbext_pp_none Then
For Each vbc In Me.Vbp.VBComponents
Set cm = vbc.CodeModule
For i = 1 To cm.CountOfLines
If InStr(1, LCase(cm.Lines(i, 1)), sFindWhat) > 0 Then
Me.lbxFound.AddItem vbc.Name
Me.lbxFound.List(Me.lbxFound.ListCount – 1, 1) = cm.ProcOfLine(i, vbext_pk_Proc)
Me.lbxFound.List(Me.lbxFound.ListCount – 1, 2) = i
Me.lbxFound.List(Me.lbxFound.ListCount – 1, 3) = Trim(cm.Lines(i, 1))
End If
Next i
Next vbc
End If
End Sub
This code isn’t complete yet. I was planning on shoring it up when I got some of the other details down. I’m still wrestling with those details. First, I want the userform’s parent window to be the VBE. I used code from Chip Pearson and Stephen Bullen and came up with this
And that works. Without this code, the VBE gets hidden and userform becomes the child of the main Excel application. With this code, the userform becomes the child of the VBE. Now I have two problems that I haven’t been able to resolve.
After the userform is closed, this code highlights the found (and selected) instance
When that code completes, I unload the form. After the all the code is done running, the userform designer window comes to the front of the VBE.
Needless to say, that’s not what I want to happen. I want the code module to show with the found instance highlighted. You can see in the code above that I tried to make the VBComponent a module level variable (mVBComp) and run a ShowComp procedure to get the right CodePane to the front. It didn’t work. The code module with the found instance is directly underneath the userform’s designer window – that is, if I Control+Tab it shows the code module I want. I know this is what happens when you run a userform, like in the Excel window, but I don’t know how to prevent it in this instance.
The second problem is that after I do a Find, none of my menu items work. My event handler class (that I took from Chip) loses scope and doesn’t work any more. It’s similar (or exactly like) what happens when I edit code in a project and I have to re-run the Auto_Open procedure to get my global class modules back.
I know it’s tempting to look at code that the author says doesn’t work, so here you go:
You can download VBHelpers2.zip