Changing Recorded Macro Selection/Clipboard Method to Range/FormattedText Method

zanodor · #1 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

zanodor · #2 07-13-2022, 02:55 PM

Quote:

Originally Posted by zanodor

...

This code should be working but it's not:

Code:

Sub Tinkering()
Application.ScreenUpdating = False
Set MyRange = ActiveDocument.Content
With MyRange.Find
        .ClearFormatting
        .Bold = True
        .Font.Size = 14
        .Text = ""
        With .Replacement
          .ClearFormatting
          .Text = "[[^&]]"
          .Font.Size = 14
        End With
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        .Execute Replace:=wdReplaceAll
      End With
Application.ScreenUpdating = True
End Sub

I used the Execute by itself not the Do While Found, if that means anything.

It throws an error at Bold, while that parameter looks sound.
On this forum Q'n'A a person has a similar issue but they don't correct the code (he or she gets the usual scolding while it's not his or her fault that sometimes the error is not even on the line pointed at by the debugger, mind you), instead offer a different one so one can never learn one sample routine or have some feeling of merit or self-confidence. Am I seeing a pattern here? Anyway.

It's even possible that the other method which I strive to do is not usable for what I want to do and only the copy formatting method works. I wouldn't rule it out.

gmaxey · #3 07-14-2022, 02:33 AM

Should be .Font.Bold = True

zanodor · #4 07-14-2022, 05:30 AM

Quote:

Originally Posted by gmaxey

Should be .Font.Bold = True

You are right.
Cheers, Greg.

macropod · #5 07-14-2022, 04:16 PM

You also don't need:
.Font.Size = 14
in the replacement expression.

Code:

Sub Tinkering()
Application.ScreenUpdating = False
With ActiveDocument.Range.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Font.Bold = True
  .Font.Size = 14
  .Format = True
  .Forward = True
  .Wrap = wdFindContinue
  .Text = ""
  .Replacement.Text = "[[^&]]"
  .Execute Replace:=wdReplaceAll
 End With
Application.ScreenUpdating = True
End Sub

zanodor · #6 07-15-2022, 01:25 AM

Quote:

Originally Posted by macropod

You also don't need:
.Font.Size = 14
in the replacement expression.

Oh, it was just a snippet from somewhere else. I didn't need to specify that in any case for markdown.

In the meantime I have successfully finished all I wanted. Before that I changed the codes to non-selection but I have a hunch it was not what was causing the issue but more like the fact that the repagination setting must have been forcing itself back on (the setting was greyed out with the tick on).

I thank you all for your help, folks. May God keep you all well in these troubled times (and don't let anyone's God be called Vaccinus). (More on that as well on my Quora page @ Otto Flott.)

Cheers

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Range method Find can't find dates	jmcsa3	Excel Programming	1	05-02-2020 06:56 AM
Title case macro gets a "type mismatch" error at the Instr method	marceepoo	Word VBA	1	03-05-2020 09:41 PM
Number Format Changing in DocVariable method	afshin	Word VBA	3	08-06-2017 03:21 PM
Number Format Changing in DocVariable method	afshin	Word	0	08-02-2017 12:26 AM
Best method(s) for entering multiple notes re diverse range of topics	WeThotUWasAToad	OneNote	0	05-15-2016 02:17 PM