Workplace Q&A: How you can use a macro to set Discover defaults


Picture: Wavebreakmedia, Getty Pictures/iStockphoto

Rocky contacted me after studying the article How you can use Excel’s discover function to spotlight or delete matching values. Like many Excel customers, Rocky does not use this function’s default settings, so he should change them, usually. The defaults do not appear user-friendly, and I additionally discover myself altering them usually.

Should you’re in the identical boat—you are a frequent consumer of Excel’s Discover function—you are not utterly caught. On this article, I will present you a easy macro that units this function’s default settings while you open the workbook. It isn’t a magic bullet, however it’s useful.

I am utilizing Workplace 365’s (desktop) Excel on a Home windows 10 64-bit system. This macro will work in earlier variations, however the on-line model does not help macros. You may obtain the demonstration .xlsm file and the .bas module or enter the macro code manually. Do not attempt to copy and paste the code from this net web page; the Visible Fundamental Editor (VBE) will complain about phantom characters you could’t see.

SEE: System replace coverage template obtain (Tech Professional Analysis)

The defaults

As a result of this text is about defaults, let’s evaluate them rapidly:

What specifies the search string.

Inside allows you to decide the place you search—the lively sheet or your complete workbook. Sheet is the default. You will need to change this selection to go looking your complete workbook.

Search determines the search course (type of)—by columns or by rows. You will not miss knowledge by not altering this selection, however you would possibly pace issues up a bit. By Rows is the default setting.

Look in allows you to restrict a search—to formulation, values, or feedback. Having the mistaken possibility set can actually mess issues up, as I discussed earlier. Formulation (oddly sufficient) is the default. Use Formulation to rapidly replace a reference.

Match case will discover solely these values that match the case used within the Discover what discipline. Disabled is the default.

Match total cell contents will discover solely these values that match solely the characters entered within the Discover what discipline. It is a good way to seek out precise matches or related matches, relying in your setting. Disabled is the default.

Format allows you to seek for particular codecs. That is extraordinarily helpful in case you do not rely on types; you should utilize Change to replace codecs with a easy search process. Disabled is the default. To entry choices, proven in Determine A, click on the Choices button. These choices will provide help to fine-tune the duty:

Once you use Excel’s Discover function, you will want to vary the settings in response to your process. Excel will bear in mind your settings, which will be helpful, or not. The one approach to make use of this function effectively is to recollect to evaluate the defaults each time you employ it.

The macro

The macro in Itemizing A units the Discover function’s Look in possibility while you open the workbook. It occurs behind the scenes. The macro depends on VBA’s Discover() perform, which automates the Discover function (Discover & Choose within the Enhancing group on the Residence tab). When utilizing the consumer interface, you reset defaults by selecting settings from a dropdown—generally altering them each time you employ the function. Utilizing the FIND() perform, you go those self same settings utilizing parameters, robotically.

Itemizing A

Sub Auto_Open()
‘Set Discover function’s default LookIn possibility when the file opens.
Dim c As Vary
Set c = Cells.Discover(What:=””, LookIn:=xlValues)
Finish Sub

To enter the macro right into a workbook, press Alt+F11 to launch the VBE. From the Insert menu, select Module. Enter the macro and return to Excel. Should you’re utilizing a ribbon model, make sure you save the workbook as a macro-enabled file (.xlsm). After saving the workbook, shut and reopen it.

Opening the workbook triggers this straightforward macro, which units solely the What and LookIn parameters. There are various extra; this perform makes use of the next kind:

vary.Discover(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

the place vary is an expression that returns a Vary object. The parameters are much like the consumer interface function’s choices, however not the identical:

First, there’s an additional one, After. This parameter specifies the (single) cell the place the search begins—it corresponds to the lively cell when looking out manually utilizing the dialog. Second, there isn’t any WithIn parameter as a result of this perform searches vary. Within the case of our macro, vary is the Cells assortment.

Solely the What parameter is required; all others are non-obligatory and help the Variant knowledge kind (every part). Desk A lists the totally different constants for every parameter. The constants in daring are the defaults.

Desk A

Parameter Constants LookIn xlFormulas, xlValues, xlNotes LookAt xlPart, xlWhole SearchOrder xlByRows, xlByColumns SearchDirection xlNext, xlPrevious MatchCase True for case-sensitive; False MatchByte True for double-byte character match; False (use just for double-byte language help)

Discover that not all the VBA arguments are the identical because the consumer interface. As an illustration, as a substitute of Search, VBA makes use of SearchOrder; in case you use Search, VBA will return an error.

So as to add extra management, merely add the suitable parameters. For instance, the process in Itemizing B units LookIn, LookAt, and SearchOrder. Add it to a workbook, save the workbook as a macro-enabled file if needed, shut it, after which reopen it.

Itemizing B

Sub Auto_Open()
‘Set Discover function’s default LookIn possibility when the file opens.
Dim c As Vary
Set c = Cells.Discover(What:=””, LookIn:=xlValues, LookAt:=xlWhole, _
Finish Sub

Then, evaluate the Discover choices through the consumer interface, as proven in Determine A, after opening the workbook.

Determine A


The macro adjustments three default settings.

Not a magic bullet

For higher or worse, generally Excel appears to disregard the macro’s settings. As an illustration, in case you use the Change keyboard shortcut, Ctrl+H, the settings will not be the identical as these you set utilizing the macro. The reality is, Change is not the identical as Discover. Should you use Ctrl+F to show the Discover dialog, you will discover the choices are the identical as these the macro set, till you employ Change or Ctrl+H, then these settings usurp the macro’s. It is the best way the function works—Excel saves the present choices and makes use of them till you alter them.

Rocky put the macro in a clean macro-enabled file and saved it in XLStart, so the macro is on the market in every new workbook. If you do not know learn how to discover XLStart in your system, learn Fast tip: Discover Excel’s XLStart folder quick. The article is outdated, however it nonetheless works.

Microsoft Weekly E-newsletter

Be your organization’s Microsoft insider with the assistance of those Home windows and Workplace tutorials and our consultants’ analyses of Microsoft’s enterprise merchandise.
Delivered Mondays and Wednesdays

Enroll immediately

Ship me your query about Workplace

I reply readers’ questions once I can, however there isn’t any assure. Do not ship recordsdata until requested; preliminary requests for assist that arrive with hooked up recordsdata might be deleted unread. You may ship screenshots of your knowledge to assist make clear your query. When contacting me, be as particular as doable. For instance, “Please troubleshoot my workbook and repair what’s mistaken” in all probability will not get a response, however “Are you able to inform me why this method is not returning the anticipated outcomes?” would possibly. Please point out the app and model that you just’re utilizing. I am not reimbursed by TechRepublic for my time or experience when serving to readers, nor do I ask for a payment from readers I assist. You may contact me at

See additionally

Supply hyperlink

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Check Also