Video: How Do I: Use the Find Method in an Excel Add-in?

Presenter:Steve Hansen

In this video, Steve Hansen shows how to build an Excel add-in that finds items in all open workbooks.


The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video

Minute 0
Feminine mystique is in the office of com is this how to use the find method in an Excel island and road like to demonstrate his how to use the find method which is similar to the finest in the user interface or some of them will do is say to those new method and Excel findings items and sheets ordered within the work of others will go to build an add in that will search all the cells in all of the open workbooks so I couldn t Excel 2007 and an person into news and a user control which I will
Minute 1
Only read to a custom task pane and in this user control office for the Xbox a button or a label in the list box salute suspects is going to be where he will enter the word that he will search for label for the list box which is to store the results of your search and button to execute the search is that some of the simple layout of the controls or selects halfway decent and women in the list box simply list items
Minute 2
That the text on the label to be search results and then when you click the button and you ll execute a search if the tech stocks skull that text search string because when you click the search button for persons or do is clear of any items that are currently in the list box from a previous search for them and called the clear method on the items collection
Minute 3
And let them execute the search unless there is actually some text in the text box so collective that it will then go back over to be the setting class and just as some of the initial items here simply need a custom task pane variable to hold a reference to my custom testing a controlled will pull a reference to my user control menu similar to store the results of a search and import the namespace to the collections that
Minute 4
Eric Library and then the clear variable to a list of found items and for simplicity will do here is as good a quick class called my phone the item class class and in my list is going to hold list of these found items slur the business class and implement it quickly only reduced to clear some public variables inside a class like one to hold the name of the workbook going to hold the name of the worksheet and item was found on in the finals are going to hold the address of this so that the item is found to
Minute 5
So that I didn t create the control in these startup event handler and click the custom task pane adding the control to the testing and set the task pane to be visible than the primary shooter will be called find all solicitous of the other knucklehead in update my butt and search click event handler to call the find all methods in the mobile
Minute 6
Since we ll use a list of found items to storeowner all the search results to add a method in my user control to to that list and have all the items to the list box so I need to do in the ad items routine says Italy to the items in the list and fridge list up early to item in the list I ll add it to the list items list box in the ad in a format that XL uses when you link a link between workbooks so that format is an
Minute 7
Workbook and brackets in square brackets followed by the name of the worksheet followed by an exclamation point followed by the range address this and they ll go back to the Senate and then worked on the final procedure and also do call another procedure to create that actually serves as an individual worksheet so the search through all the open work books I m going to going to do a use for
Minute 8
Us into French workbook and application workbooks and an inside is another four weeks to iterate through the worksheets in the given workbook only do then is a future that s actually exit in perform the search in the worksheet so good and couldn t define example procedure taking one the worksheet argument and one string argument representing the texts that we should be looking for
Minute 9
Filipinos who lived with the find method next is that and clinics don t have a built in way to know when they ve found things that have already been found or two and then they searched the entire range of things researched so you have to implement find in for a next inside a loop and keep track of what has been found and is you find things compared to make sure it s a something that has already been found so once you find something you store the first one in the new check the address of everything else that is found to make sure that you keep finding the same things over in with you so enter a nonterminating loop
Minute 10
It is really a call that I shall find that it here so I m going to call find all the cells in what she fastened the argument that also for the fine young men from the start of the first stone workbook with researching the Excel values so the guys of the cell I can a definite partial match that I don t have to match the entire contents of the cell the direction will be next and I couldn t worry about that later case so the easy condition to handle it is if you don t find anything at all which case a minute if not range is nothing so that s not for
Minute 11
Is not nothing is going to simply exit the fine example procedure if it does find something I need to save that address of the first found item that s critical as what you need to do is compare the address of each found item with the address of the first item he found and so assume as opposed to already know you know that fighting has searched all the items in the worksheet and else looping back and finding things that already found before so if Range found it is actually a value or range and save the address and then going to populate the life found item with the address of the
Minute 12
Item would mean so IntelliSense isn t to enjoy everything about the issues you raise found that and you get a worksheet object to do another that and you get to a workbook object and then you can do name to get the workbook named likewise with worksheet name is in the range phone got pinch hitter worksheet and in named to get the name of a worksheet software I them from the attitude of items collection are going to qualify next procedure in and I m going to go do this whenever I found in and so appear in salute was a while to do while loop and so is the address of was found to not eat the profound address
Minute 13
Negron and keep executing fines and with each item I find storm the details in a found item object and then add that found item to my found items list really when you take away here if you re interested in using find which is a very click it is how to terminate the loop when you ve been how to know when you ve found all the items are to be found
Minute 14
For you to do and it is searched through all the virtues nor the books I ve got a list of found items similar to go and call me at items procedure on my control and pass it in the list of found items for the couple of worksheets to test return is lately littered with simple test strings Salesians visit to the workbooks and before Jesus just a few women here and clicking tests read fantastic looking search can see
Minute 15
Things are returned very quickly that concludes a study on how to use the Excel climate and thanks for a great day

Click here to go to original video page