How to Find and Replace Code in a Visual Basic Editor Macro

You can use the Find feature in the Visual Basic Editor to quickly locate the statements or properties that need editing in your Excel 2013 macro. You open the Find dialog box by choosing Edit→Find on the menu bar, clicking the Find button on the Standard toolbar, or by pressing Ctrl+F.

This dialog box is very similar to the one you use when finding entries in your Excel spreadsheet. The main difference is that the Find dialog box gives you different choices for what to search for (in addition to the familiar options for finding whole words only and matching case):

  • Current Procedure option button to search only the current programming procedure in the Code window

  • Current Module option button to search only the macros in the current module (the default)

  • Current Project option button to search all the macros in all modules within the current project

  • Selected Text option button to search only the text that you’ve selected in the Code window (this option is not available unless you’ve selected a block of text in the current code)

    image0.jpg

After you enter the Visual Basic property or statement as your search string in the Find What text box, select the search options, and click the Find Next button, Excel attempts to locate its first occurrence in the code.

When it does, the program highlights that occurrence in the current procedure, module, VBA project, or selected text block (depending on which Search option you use). To find the next occurrence, you can click the Find Next button in the Find dialog box again or, if you close this dialog box, press F3.

If you have a number of occurrences throughout the macro that require the same type of updating, you can use the Replace feature to both find and replace them in the macro code. This is particularly useful when you decide to change a particular value throughout a macro (such as selecting the cell range named "income_08" for processing instead of the range "income_07"), and you want to make sure that you don’t miss any occurrences.

To open the Replace dialog box, choose Edit→Replace on the Visual Basic Editor menu bar or press Ctrl+H. Note that you can open the Replace dialog box from within the Find dialog box by clicking its Replace button.

The Replace dialog box that appears is just like the Find dialog box, except that it contains a Replace With text box along with the Find What text box and has Replace and Replace All buttons in addition to the Find Next button.

After entering the property or statement to find in the Find What text box and the one to replace it with in the Replace With text box, click the Find Next button to locate the first occurrence in the current procedure, module, VBA project, or selected text block (depending on which Search option you use).

After this occurrence is selected in the Code window, you have it replaced with the replacement text by clicking the Replace button. Excel then locates the next occurrence, which you can then replace by clicking the Replace button or pass over to find the next occurrence by clicking the Find Next button.

Don’t use the Replace All button to replace all the occurrences in your macro unless you’re 100 percent sure that you won’t be globally replacing something that shouldn’t be replaced and possibly screwing up your macro big time.

blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.