It must be one of the most asked questions about macros - why has it not been addressed in the macro language after all these years?

Anonymous
2013-02-19T05:49:37+00:00

To those of us who are just users and not programmers, it seems like MS Word "macros" -- particularly recorded macros -- ought to include the capability to be easily constructed to (1) go to some identifiable place in a document and "do" some things starting from that point (such as "find" a new paragraph immediately followed by the word "publish", then go to the end of that line of text and insert "**") and then (here comes the sticking point) (2) repeat that series of activities over and over UNTIL some identifiable point is reached in the document [e.g., the end of the page; the end of the document; the end of the paragraph]. 

But for some reason, Word macros cannot be made to do that in an "easy for non-programmers to understand" sort of way.  I've had it explained to me several times that I must use references to "ranges" and/or "counts" and all sorts of other commands that seem overly complicated to just "go to a place and do this", then go to the next such place, and do the same thing", and keep doing that UNTIL . . .  It's certainly nothing that can be accomplished by recording a macro (as it logically should be.)   With help, I'll finally get a macro cobbled together to do what I need done.  But when the next occasion to do something like that comes along -- maybe a year later -- I've forgotten all those inscrutable concepts and I once again find myself just needing to record a macro doing some set of activities and then have it repeat itself through the document.

I know it's easy for programmers to understand all these arcane requirements to be able to get a macro to work through a document that way, but a lot of us "out here" are just regular users who occasionally need to be able to do repetitive tasks within a particular document and believe it should be easier to accomplish this sort of thing than it appears the macro language permits. 

Can someone explain why the macro language cannot be made to easily allow a macro to just do something like: 

   DO         [find <text>, then move from there to another point 8 characters away and then make some change to the text at that point], then     LOOP (DOing that same series of things again} UNTIL [the End of the Page or the End of the Document is reached], then just Stop the macro from further executing.

I'm sure I haven't used the right syntax above, but I hope you get the point - it ought to be just that simple to accomplish that sort of chore, but it isn't. 

Can anyone explain WHY the macro language CAN'T work this way or, if it could be made to do so, why Microsoft has not done it in all these many years while countless ordinary users have struggled with (and posted questions all over the Internet about) this specific concept?

Microsoft 365 and Office | Word | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2013-02-24T17:37:41+00:00

D PaulDalton,

Glad I could help and I understand your comments and concerns. 

Unfortunately, I don't know if your suggestions will ever get any traction even if they made it to the "right person.  In line with what Jay and Peter have said, my experience is that VBA becomes less and less complete as Word progresses rather than more and more complete.  For example, everything that you could do as a user with a drawing or shape object in Word 2003, you could do with VBA.  With the new graphics engine introduced with Word 2010, there are many things that a user can do that now cannot be done with VBA.  Most of the things that you can do with those graphics are not even recorded.  I suspect that the recorder will go away before it becomes anything like you envision.  Still, it is a good and valid vision.

Good luck.

Was this answer helpful?

0 comments
Answer accepted by question author
Anonymous
2013-02-22T17:33:28+00:00

I have a few observations, and that's probably about all I'll be able to contribute here.

In order to get much further with getting Microsoft to do this, you'll need to get your idea onto their agenda. While it's possible that ideas posted here sometimes get some attention from them, you can't guarantee it: you aren't really dealing with Microsoft people (it's mostly volunteers), and certainly not the Microsoft product group people, in here. So you either need to know someone, or find a proxy (either someone close or perhaps an influential corporate that can see the value of the idea). As for timing, I don't know what their product cycle looks like now, either - it could well be changing rapidly as their Office 365 subscription service develops. But if it's the old cycle, I would guess they already have the outline if not most of the details for the version after 2013 and may be thinking about ideas after that. Also, I would be very surprised if they did not want to be absolutely sure of their ground as far as Intellectual Property is concerned.

On the technical front, the biggest technical questions for me are "what can be done in the 'DO' part of the feature?" (i.e. the one you described a few messages back)

If it's "execution of an arbitrary macro", then it can do anything VBA can do, form "something sensible" to "accidentally trash every file on the system." It can also contain infinite loops, code that results in untrappable errors - at least not trappable by VBA - and doubtless other things I haven't even thought about that may result in dismay for the user.

As long as the user is willing to take responsibility for their part of the code, I don't see any technical showstoppers*.* But that doesn't mean that such problems won't turn up.

From a support perspective though, having a half-and-half feature like that is a bit of a liability, to say the least.

Going to a slightly more detailed level, when I read your earlier messages, I got the impression that you were keen to avoid the more techie stuff involving selections, ranges etc. The trouble is that if the user still has to write VBA, e.g. to find something then move a few words to the right or whatever, they still have to understand that stuff. What's more, in the general case some code will function in (say) a paragraph in the main body of the document, but fail in a footer (say). In other words, in many cases the user would need to understand even more about their code.

When I first read your question, I had a slightly different idea as to what you were driving at. To me, how you avoid dealing with "allowing any piece of VBA" is to try to identify a small but potent set of things the user might want to do (obviously reducing the generality obtainable in VBA) and allow them to do those things and nothing else. That's one of the reasons I suggested that a change to the recorder might be needed - you'd only be able to record the things that the feature is designed to allow. Then the feature takes responsibility for making all that stuff happen. How to do that technically is a detail, but perhaps specify that the DO macro only contains calls to a number of predefined subs/functions that have been designed to behave well within the overall structure of the feature. Or define a completely new, small, non-VBA language and some VBA to interpret it.

Finally, I know we have been taking about Word and VBA. But really, we're talking about providing a set of features for repetitive processing. The Web App version of Word doesn't have any of that stuff, and perhaps it is intended that it never will. But because it doesn't have VBA, it would I think benefit far more from a feature to let its users do something like "do this in every paragraph that begins with 'Jack Robinson'"

Was this answer helpful?

0 comments

39 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Jay Freedman 208.3K Reputation points Volunteer Moderator
    2013-02-23T21:11:25+00:00

    You're correct that the recorded code is terribly inefficient, but it looks like it will (eventually) do the job. To modify it to loop through the whole document:

    • Just before the first 'Selection.Find.ClearFormatting' line, insert a new line containing the single word

                Do

    • Replace the first occurrence of 'Selection.Find.Execute' with the line

                If Not Selection.Find.Execute Then Exit Sub

    • Just before the 'End Sub' at the end of the macro, insert a new line with the single word

                Loop

    The 'Do' and the 'Loop' create a repetition of the entire macro. The repetition would be endless, except that the new If statement -- translated into plain English -- says 'Try to find the text/formatting that was specified, and if you don't find it, then shut down the macro.'

    I'd recommend running the macro on a copy of the document rather than the original, in case some mismatch between the macro's assumptions and the actual document might cause destruction of some data. Also, if the macro doesn't stop when you think it should, press Ctrl+Break; when the macro editor appears, click Run > Reset.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-02-23T20:32:40+00:00

    Peter -

    I appreciate all of your thoughts.  You have identified valid concerns that clearly must be taken into account if/when Microsoft decides to consider adding macro-building tools or options to the macro capability in the products.  Of course, I don't have the slightest idea how to get the right people at Microsoft to consider the value of adding "ease of use" improvements/tools/options to the macro capability to help non-programmer users.  Maybe they'll run across this thread or perhaps someone reading this thread will pass along the concepts to the right people". 

    In the meantime, perhaps you (or someone else) could assist me with the specific issue that started the thread.  I have copied below a recorded macro that I would like to modify to repeat itself throughout a specific document. 

    The document itself has no headers, footers, footnotes, endnotes, or comments.  But the information in the document (which comprises textual biographical information about hundreds of individuals and, in some instances, photos of those individuals) is repetitive in how the information about each individual is formatted and it could be a lot more useful if the information is presented in a table. 

    So my goal is to be able to add either delimiters (TABS) or placeholders [e.g., (*) for later insertion of delimiters or other information using find/replace], so the information in the document ultimately can be formatted into a table.  Here's the recorded macro code: 

    ' Macro1 Macro

    '

    '

        Selection.Find.ClearFormatting

        Selection.Find.Font.Bold = True

        With Selection.Find

            .Text = "Published in "

            .Replacement.Text = "**^p"

            .Forward = True

            .Wrap = wdFindContinue

            .Format = True

            .MatchCase = False

            .MatchWholeWord = False

            .MatchWildcards = False

            .MatchSoundsLike = False

            .MatchAllWordForms = False

        End With

        Selection.Find.Execute

        Selection.EndKey Unit:=wdLine

        Selection.TypeText Text:=vbTab

        Selection.Find.ClearFormatting

        Selection.Find.Replacement.ClearFormatting

        With Selection.Find

            .Text = "^p"

            .Replacement.Text = "**^p"

            .Forward = True

            .Wrap = wdFindContinue

            .Format = False

            .MatchCase = False

            .MatchWholeWord = False

            .MatchWildcards = False

            .MatchSoundsLike = False

            .MatchAllWordForms = False

        End With

        Selection.Find.Execute

        With Selection

            If .Find.Forward = True Then

                .Collapse Direction:=wdCollapseStart

            Else

                .Collapse Direction:=wdCollapseEnd

            End If

            .Find.Execute Replace:=wdReplaceOne

            If .Find.Forward = True Then

                .Collapse Direction:=wdCollapseEnd

            Else

                .Collapse Direction:=wdCollapseStart

            End If

            .Find.Execute

        End With

        Selection.EndKey Unit:=wdLine

    End Sub

    Just looking at the recorded code, it looks like the macro recorder makes "assumptions" that cause it to repeatedly insert commands that I imagine a trained programmer would know how to take care of by stating them only once somewhere in the macro (such as ".Forward = True" and ".MatchCase = False"), so this probably strikes a programmer as inefficient coding.  But I'm really not concerned about improving the efficiency of the existing code -- what I would like to know is the specific pieces of macro code that, if inserted before and after this recorded macro code, would cause that recorded macro code to execute over and over from the beginning to the end of the existing document. 

    Is that something you can help me with?

    Thanks

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-02-23T19:44:57+00:00

    Pam - 

    Thank you for your response.  I, too, formerly used a different wordprocessor and had been fairly proficient writing macros with it to accomplish very useful functionality in my practice, including document assembly and other functional goals that required repetitive action.  But changing law firms (in 2000) forced me to convert to using Word.  It didn't take very long to become comfortable with using Word in general (though I do remember that, before I could get a handle on "Styles", I had to sort of wrap my heard around the architectural difference between a "streaming text" product and one in which all of the information about a paragraph is hidden inside its terminating paragraph symbol <g>; but I eventually got the hang of using Word's Styles, too). 

    Word's macro capability has been the principal area where I have been repeatedly frustrated.  While I'm sure that the macro capability in Word is much more powerful that that in the product I previously had used, it is also vastly less intuitive for users who cannot afford to dedicate the time and effort required to also become programmers. 

    Most (if not all) of the "improvements" made to Word over the past several versions seem to have been intended (or at least advertised) to improve "ease of use" of Word's existing functional capabilities (the Ribbon interface; Quick Styles; etc.), as opposed to adding new functional capabilities to the program.  But -- as best I can remember from Word 95 (the first version I was exposed to) through Word 2010 (and I'm guessing 2013) -- there have been no such "improvements" intended to make it easier for non-programmer users to take advantage of the capabilities of the macro facility in Word (or Excel, etc.). 

    So, while "ease of use" and accessibility have been the focus of product improvements, macro capability seems to be the one area where product development decision makers have placed little (if any) attention on making improvements intended to improve the product's "ease of use" and accessibility of macro creation functionality for non-programmers. 

    I honestly don't understand whether improvements in this area haven't occurred because:

    • it simply hasn't occurred to anyone to consider adding tools to make building and using macros easier and more accessible for non-programmer users.
    • there really are valid technical reasons why it would be impossible to add such tools without risking outcomes that could not be anticipated and guarded against by limiting their use (e.g., there is no programatically effective way to limit the application of such tools to an identifiable subset of VBA commands [or command combinations] that would avoid disastrous consequences).
    • product developers philosophically do not want to consider adding tools to make using macros easier for non-programmers to use.
    • Microsoft wants to foster, and not do anything to adversely impact, the perceived economic interests of a "cottage industry" of programmers who market themselves to users and businesses to develop VBA-based macros.
    • some other reason I can't imagine at this time.

    I think you and I do share some background experiences, disappointments, and perspectives about Word.  While my immediate goal is to solve a particular (recurring) problem about macros, my ultimate goal is to try to find a way to get others to identify this as an area where the product could be "improved" for the benefit of many users and, eventually, to get Microsoft to focus attention on incorporating those improvements into its products.

    Was this answer helpful?

    0 comments