Change Excel Find Formula Defaults

Many people may complain that Excel is a complicated application, but once you learn how to turn all that extra complication in your favor, things suddenly look rosey.

Using Visual Basic for Applications (VBA), the programming language of all Microsoft Office applications, you can change the defaults for any command available on Excel's many menus. In this case, you want to change the default for the Edit->Find command.

The first step is using code to display the same dialog box that Excel displays. There is good news and bad news about this step. The good news is that displaying the dialog box is simple as can be. The bad news, the many options available to change the dialog box are not defined any place that I could find, so it takes a bit of experimenting to get just the options you want.

For this example, we only needed one line of code, so I'm going to show the entire macro, complete with the Sub and End Sub container markers.

Looking at the code, you can see it just makes a reference to the Dialogs collection inside the Excel Application, specifies the FormulaFind dialog box, and tells it to Show itself. The arguments (which are seperated by commas) to customize the appearance are passed at the end of the line.

The first argument (Find what:), which is blank, is the value to be searched for. The second argument (Search in:)listed specifies whether to search by Formulas (1), Values (2), or Comments(3). The third argument (Match entire cell contents:), again left blank for our purposes, specifies whether to search the entire cell or not (True or False). The fourth argument (Search:) specifies whether to search by Rows (1) or Columns (2). I'll live it to others to determine the other arguments available for now.

Now, you have the code, how do you use it?

Visit this page to learn how to install the code into your spreadsheet. After you've installed the code, visit this page to add a button to one of your toolbars to point to the macro.