Welcome back to another #MacroMonday #Excel blog post in my 2019 series. Have you ever needed to find and replace some of your Excel VBA Macro code?. If you have to change your declared variable name ad nee to replace it throughout your code?. If you do not replace all the instances then the VBA code will fall over.

Here is a really simple way to find and replace code in your Excel macro. Let’s take a look at an example. The macro below is own function that will generate a random string of characters. This can be useful to generate unique passwords of any given length automatically at any time.

My Excel Macro Example.

Now, we have some variables declared in this code above. Let’s concentrate on the third variable the str as String. This variable relates to the random string that is generated. I want this piece of code to be a bit more obvious in its self-explanation. So, let’s replace this bit of VBA code with something else.

Find And Replace My Excel Macro Code.

I want to replace the following code-

str As String

With this code

MyPassword As String

The variable str appears a number of time in the code.  This code is relatively short in length, so the number of instances can be seen. In longer more complex code, the variable may not be as easily seen.  This is where the Find and Replace mrtho is useful.  It will save you  time and alot of manual work.

Follow the simple steps below to replace str with MyPassword.

• On the Edit Menu select Replace, the Replace dialog box appears.
• In the Find What field type str.
• Enter MyPassword in the Replace With field.
• There a a number of Search options, we want to search in the Current Module.
• I also want to match the case, as other code will be affected if this is left to search for simply str.
• Hit Replace All.  All str will be replace with MyPassword.
• Job Done.

How simple is that.  No manual work at all!..

What Next? Want More Tips?

So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Excel Macro Monday series. Click on the link below

Macro Mondays Blog Posts.

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.