Microsoft Office Forums - View Single Post - [Solved] Changing Recorded Macro Selection/Clipboard Method to Range/FormattedText Method

zanodor · 07-13-2022, 02:06 PM

Hello and good morning to folks Down Under,

In my experience of the last 5-10 days trying to find VBA codes online to suit my needs, a recurring theme was "don't use selection method." Use the other method. Not only does the selection method will spew out errors, while running a long macro it will prevent you from using the clipboard on your PC. Practically you need to stay away from your PC while the macro is executing because it can wreak havoc on the procedure (I have ClipAngel Clipboard Manager on my tray and I can see what's happening).

Yet in spite of all the admonitions and instructions on all kinds of forums, it's the Selection/Clipboard method that will be implemented if you record macros (which is what people with no knowledge of VBA will do).

I was searching a bit online to find a thread that addresses this issue and I could not so I am basically half-doing it for the benefit of others who run into issues like mine.

Now for my problem.
I am running a macro involving many thousands of files and while it was working great a few days ago it is now crashing with too much CPU being used.
In Task Manager on Windows you can see that the MBs used increase by each second and there's probably a limit (not just on 32bit versions of Office that I am using) and will ultimately crash.

I followed the advice of others a few days ago and disabled background pagination and took care to start the macro while in Draft View (Web View worked better for me, mind you) and things were going swimmingly a couple of days but not anymore. The macro will run for a half hour and crashes Word with no errors.

I am pretty sure the reason behind this crash is employing too many subroutines with the selection method (I call 4 subs in a main sub that saves everything out in a different folder).

Now the question is (and while I am posing it, I am also searching online and tinkering with my own makeshift subs trying to figure out how to make it work on my own):
How do you change some code produced by the Macro Recorder to the method employed by those who instruct everyone on every thread and every forum, namely the Range and FormattedText method which would make things smooth and let you use your PC (so you can copy and paste stuff and generally work on it at the same time)?

What I am especially interested in is the Whole ActiveDocument Range (anybody can change that range to any specific lesser ranges) and using the .Replacement.Text = "^&" replace mode because for most of what I need to change, I need to keep my original text intact and just change some formatting (bold to not bold, etc.). These are usually words or phrases, not paragraphs or sections. Of course all instances within the Range/Activedocument needs to be tackled so a loop of some sort would be involved.

So if I'm not mistaken, the method to be used would involve setting the ActiveDocument.Range and use With.Find to Execute on All or a Do While loop.

So my question is, how would I get the other method for the following machine-recorded code:

Code:

Sub ChangeToWikiLinks
Selection.Find.ClearFormatting
    With Selection.Find.Font
        .Size = 10
        .Bold = True
    End With
    Selection.Find.Replacement.ClearFormatting
    Selection.Find.Replacement.Font.Bold = False
    With Selection.Find
        .Text = ""
        .Replacement.Text = "[[^&]]"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
End Sub

The MatchCase and other stuff can easily be copied from a recorded macro from the editor if one is not familiar with them but the important steps before and after are what I am interested in.
I was looking for examples and instructions online but I am more likely to run into Q'n'A's in Excel (cells, tables) and find it hard to reconstruct or translate ideas into the Word environment. Sometimes by the time you find something you can work with you'll be on 30-40 tabs on the browser.

I have made up my mind to not run anything (to do with this method of Find and Replace) anymore and change all my code to the new method given (hopefully) because the machine-generated (recorded) code just doesn't cut it.
I want my macros to run smoothly while I rest at night and not have to check for any crashes.

Thanks all for taking the time to read. I am afraid the post became a little too verbose to fit the bill for a pinned thread but this is important for novices and threads like these should be pinned.
Maybe an admin here could point me to a thread that deals with this Selection Method drama..

P.S. I may not even formulated the title well enough but an admin can change it. They will know what this is all about. Maybe Find and Replace could have been added.
At least Office should give us a warning after recording or give us the choice which method we'd want to use.

Cheers,

Zan