Interface to a Word VBA macro (Word 2003 SP2)

Topic

New Reply

I have written several VBA “toggle” macros that enable the user to place the cursor in a word and have the word replaced by a different term. For example, if the user’s cursor is in “that” and the activating key is pressed, “that” is changed to “which”. The attached is a portion of the code for one of the toggle macros. There are several toggle macros because each focuses on particular types of changes.

This macro is used by several of us as we edit book manuscripts. (We are not on a central server; each of us works out of our home on standalone computers.) Unfortunately, I am the only person who has any clue about VBA (albeit what I know is very limited). Consequently, when someone comes across something they want to add to the macro, and this occurs with great frequency, they tell me what it is, I add it to the appropriate macro, and I then redistribute the template to everyone with a list of the newest additions..

What I want to do is create a macro that opens a dialog box in which the enduser enters the word to be changed and what it is to be changed to (both case sensitive), and indicates to which macro it is to be added by checkbox (that is, the macros would be listed and the enduser would select one). When the enduser clicks OK, this macro would then add the appropriate code to the checkmarked toggle macro. I have tried to create such a macro but with my limited knowledge of VBA have been wholly unsuccessful. So my question: Is what I want to do possible? If it is, any help would be appreciated.

Thanks.

Reply | Quote

Replies

The code does not seem to deal in straight replacement (for example, it applies superscript in some situations). I don’t see how you could take that into account in automated macro creation.

Reply | Quote

I am not concerned about the “aberrations” such as the superscripting because that occurs infrequently. If an interface can be done that would just handle the straight text, that would be perfect. The usual entries have no superscripting or subscripting, which would be the only variations from straight text in any case. What I really want is for the enduser to have a box that says, e.g.,

Change [word] of [number] characters
To [word]

Add to: [checkbox] General Words
[checkbox] Medical Acronyms
[checkbox] [other macro choices]

Add Cancel

and then have automatically added to the checked macro and in the correct place the following code with the information filled in:

ElseIf target = “[change word]” Then
ActiveDocument.Range(Start:=aaa, End:=aaa + [number of characters]).Delete
Selection.TypeText Text:=”[to word]”

Of ocurse, it would be even better if the enduser didn’t have to provide the character count, that the macro could provide it automatically.

Is this interface possible?

Reply | Quote

Wouldn’t it be much easier to use AutoText for this? No code needed at all, all users can add their own preferences.

Reply | Quote

Hans, autotext doesn’t accomplish the same thing with the ease of the macro. In addition there are usability limitations. Consider the following. In the sample code I provided, if the editor comes to a sentence that reads, for example,

“the distance between the patient’s eyes was 2.5 centimeters”

to change “centimeters” to “cm”, the editor simply puts her cursor somewhere in the word “centimeters” and presses the activation key (which is a single key) . With AutoText, the editor has to (a) select the word to be replaced, ( open the AutoText menu, © find the correct main menu, (d) find the correct submenu, perhaps having to go through several, and (e) select the correct replacement and click it. As the number of entries increases, the time it takes to find the replacement text increases.

Also, consider the variations. Some combinations require multiple variations. For example, the “which” and “that” combination has 4 possibilities: that > which; which > that; That > Which; and Which > That. The “principal” and “principle” combination has 8 possibilities principal > principle; Principal > Principle; principals > principles; Principals > Principles; principle > principal; Principle > Principal; principles > principals; and Principles > Principals. As you can see, the list can grow. Consequently, it is much quicker to press a single key than to use AutoText.

Reply | Quote

There have been posts in the past concerning self-modifying macros; they seemed quite messy because the tools available and security restrictions complicate the process.

Have you considered an external data store, such as a .txt, .ini, .mdb or .xml file, to store your search strings? You could create a global index when the document/template is loaded and then work from that copy. Then you would only need to distribute a new macro when the nature of the search itself changed (somewhat like the division of labor in antivirus programs between scanning engines and detection signatures). Of course, to avoid overwriting the user’s own idiosyncratic searches, you actually would want to split the data into two stores (like the Microsoft dictionary and user’s Custom dictionary).

Reply | Quote

Thanks for the suggestion, but it does not apply to these particular macros — at least I don’t think it does. There seems to be a continuing misunderstanding about the macros for which I want the interface: they are not search (or find and replace) macros. The macros are used as the editor reads the manuscript and are designed to facilitate making editing changes based on context, by which I mean that the editor has to consider context and style in deciding whether a particular term is properly used.

Consider as an example the words “since” and “because”. You cannot do a find and replace to change every instance of “since” to “because” because the determination of which is correct depends on its use in context. They both have a causal sense, so in common English they are used interchangeably to convey a causal connection (“since the virus produces symptoms”, “because the virus produces symptoms”), but grammatically there is a distinction and “since” is used to convey a sense of time (“since he turned 18 he has lived alone”) and “because” to convey a causal connection (“because he turned 18 he could legally drink alcohol”). Consider the difference in meaning that occurs when replacing “since” with “because” in this example: “because he turned 18 he has lived alone”. Which does the author mean: the cause of his living alone was that he turned 18 or he has lived alone from the time he turned?

My point is that the toggle macros serve a different purpose than a search macro and cannot (should not) be used in the same manner. It has been suggested that a find and replace macro that asks each time whether to make a change would work, and it will for some things, but not for what the toggle macros are intended to do. Additionally, if you had to run through a list of 50 terms in a 125-page document answering yes or no regarding replacement, the edting time would increase dramatically and the number of errors introduced would increase.

OTOH, if there is a way to adapt the suggestion of having the terms in a .txt file rather than as part of the VBA macro yet essentially work the same (i.e., as a toggle rather than as a search and replace), I am open to that; in fact, I think that would probably work better, especially because words and phrases could easily be added to a .txt file. But I admit that I don’t know how to write the toggle macro so that the user could place her cursor in a word or could select a phrase and have the macro compare the word/selection against the contents of the .txt file and if found, replace the word/selection with an alternative. This warrants further investigation.

Reply | Quote

I think Jefferson’s suggestion is workable for the purpose you require but I haven’t yet seen anyone mention the thesaurus function of Word. Have you investigated the thesaurus already and discarded it for a reason? I would have expected it to provide the functionality you require especially if you were able to customise the entries within or create your own.

Reply | Quote

In the attached zip file, you’ll find two documents:
Code.doc contains a macro, and some sample text.
Replacements.doc contains a table with two columns. the left hand column contains phrases to be replaced, the right hand column their replacements. As you can see, some formatting is possible.
Place both documents in your default document folder. Open Code.doc (make sure you enable macros), click in a word such as “because” or select a phrase such as “cubic centimeters”. Then run the macro “MyReplace”. The macro will open Replacements.doc and check for a match. If found, the selection will be replaced. The document will be left open. The user can simply switch to it and edit it.
If you want to put Replacements.doc in another folder, you must specify the path in the MyReplace macro.

I haven’t tested extensively, there are probably some wrinkles that need to be ironed out, but I hope this gives you an idea.

Reply | Quote

Thanks, Hans.

I will use this code as a starting point. I tried it and it works as you said. I appreciate the help. This will give something to do over the next few weeks.

Reply | Quote

I just cobbled together come code that works pretty well for me. I created an INI-style file where the word to be changed is the Key, and the “new” word is the corresponding Value. (The Section could be anything; I’ve used [WordList].

It’s written to work with the word where the insertion point is sitting unless the user has selected text, in which case the selection is the text used for the lookup. I use Replace to change spaces to underscores, and the INI file has underscores instead of spaces in the Key.

My code is as follows, based on an INI file named “word to change.ini”, located in c:temp::

Sub ChangeWord()
Dim rng As Range
Dim sINI As String
Dim sNewWord As String
Dim sOldWord As String
sINI = “c:tempwords to change.ini”
Set rng = Selection.Words(1)
sOldWord = Trim(rng.Text)

sNewWord = System.PrivateProfileString(sINI, “WordList”, sOldWord)
rng.Text = sNewWord & ” ”
End Sub

This is the contents of the INI file:

[WordList]
that=which
centimeters=cm
lumber=lumbar
since=because
who=whom
whom=who
due_to= because of

Maybe this will help you. Individual users will be able to modify the INI file easily, as long as it’s always stored in the same folder. They just need to remember to use underscores instead of spaces in the Key. That’s not necessary in the Value (right side of equal sign). You could refine this by automatically checking for all caps or initial caps, etc. and let the code modify the replacement word accordingly. If there are multiple words that might be suitable substitutes, you could separte them with a pipe. If the code see a pipe in the value, a dialog could display with the several words in a list box from which the user would select the desired replacement. Refinements could be added for trailing punctuations and that sort of thing.

Richard Barrett

Reply | Quote

Thanks, Richard, for the suggestion and the starting code. I have a couple of questions (which will reveal how little I understand VBA ).

1. Why is the underscore needed in the Key as in due_to?
2. Is there a limit to the size of an .ini file (except for it becoming too cumbersome), or is it just like a .txt file? By size limit, I mean a maximum number of entries (e.g., a custom dictionary is limited to 5,000 entries).
3. I note that there is no space on either side of the =. Is this required or can there be a space?

I tried your code as is but am unable to get it to work properly. It does delete the existing word, but does not replace it with the new word — it simply leaves the space empty. Something for me to check into.

Reply | Quote

Rich,

The format of an INI file dicates that there be no spaces in the Key, which is the text to the left of the equals sign. There can be spaces in the Value, but there cannot be line feeds or “hard returns”.
An INI file is a TXT file, with a different extension to denote its use as what was once known as an “initializatoin” file, now replaced by the Windows Registry. You could make the extension anything you want, as long as it matches how you reference the file in the code. I generally use INI because i*t’s an INI format file, meaning that there are [Sections], under which Key=Value.
I don’t know of any specific limit on the size of an INI file. It’s a quick format as far and read/wirte go, but certainly at some point there would be a loss of efficiency. It’s by no means a high-tech database.
If you put spaces around the equals sign that does not cause a problem; it is just not the standard method of formatting an INI file.

If the word was not replaced in your testing, you probably have mistyped a filename or the path. I’ve added a little error checking so that a word is not deleted if there is nothing to replace it. In that case, a message displays “Substitute not found.” In my sample, there are spaces betwenn words*to*change.ini in the filename (here noted by asterisks). I would say that the only thing to cause an error would be if you do not have an exact match for the filename in the code, or if the Section is not correct: In my sample, that was [WordList]…. no spaces, and surrounded by square brackets. I

When Word identifies a word, it includes everything after it, up to the beginning of the next word. This means that the space after a word is part of the word, which is why I use the Trim function. If your word comes at the end of a sentence, you’ll have to have code that knocks off any punctuation, quotation mark, etc. You might want to assign the characters (spaces, punctuation) to a variable so that you can put it back when you replace the word. I didn’t add a lot of refinements to the example I sent you.

Here’s the updated code which won’t delete the word unless a replacement is available. If you still have trouble, let me know. I’ll do my best to help you when I have a bit more time.

Sub ChangeWord()
Dim rng As Range
Dim sINI As String
Dim sNewWord As String
Dim sOldWord As String
sINI = “c:tempwords to change.ini”
If Selection.Type = wdSelectionIP Then
Set rng = Selection.Words(1)
sOldWord = Trim(rng.Text)
Else ‘ user has selected text
Set rng = Selection.Range
sOldWord = Selection.Text
sOldWord = Replace(sOldWord, ” “, “_”)
End If

sNewWord = System.PrivateProfileString(sINI, “WordList”, sOldWord)
If sNewWord “” Then
rng.Text = sNewWord & ” ”
Else
MsgBox “Substitute not found.”
End If
End Sub

Reply | Quote

A couple other things…. There are limitations on INI files other than those that I’ve mentioned. For example, I don’t think it’s permissible to use non-alpha characters such as apostrophes in the Key. So don’t=do not would not be valid. I believe you mentioned that this was to be an auxiliary store to the basic macro code. So the macro code would do most of the work, probably using a Select construct and Case Else would refer to the INI file for words added by the individual users. From time to time, you (or someone else) would incorporate the several users’ word list from their INI files into the macro code. This would also serve the purpose of keeping the INI file from becoming too large. The Select construct will handle words with apostrophes and other words composed of non-alpha character, as well as foreign words with accented letters.

As I said, this is not a high-tech approach, but it’s within your skill set as well as something that can be used by others for an auxiliary/personal list.

Richard

Reply | Quote

Thank you, again, Richard. I haven’t had a chance to try the revised code or even to work with it — I’m sorry to say that earning my paycheck is distracting me from what I’d rather be doing, which is working on the macros. I probably won’t be able to get back to the macros until the weekend. Yes, this will be an auxiliary macro until I can merge the files.

I would, however, like an answer to my original question — a “yes” or “no” will do: Can an interface like I originally asked about be done? I would like to know just to satisfy my curiosity. I am grateful for all the help I have been given and the solutions suggested are the ones I will implement (with some tinkering), but no one has said “Yes, the interface can be done but . . .” or “No, VBA won’t let you create such a solution because . . .” or whatever the appropriate answer is. Thanks.

Reply | Quote

In theory, it is possible to create or modify the code of a macro using code. You need to set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library in Tools | Options, this will give you programmatic access to the Visual Basic project.
But there are several good reasons NOT to try it:
– Since manipulating code in code could easily be misused by viruses, Microsoft has disabled access to the Visual Basic project by default. You’d have to instruct all users to change this (for obvious reasons, it wouldn’t be a good idea if you could change this setting using a macro)
– I suspect that trying to modify code that is in use can easily lead to crashes.
– Frequently modifying code may eventually cause a module to become corrupt; doing so on the fly even more so.

Keeping the list of phrases out of the code means that the code will be much more stable and robust, and gives the end user a simple way of maintaining the list.

Reply | Quote

Thanks, Hans, for the answer and the explanation why not to do it.

Reply | Quote

To be honest, I didn’t give an interface much thought. I’m not 100% sure by what you mean by an interface as you detailed things in your original post. But if you want a way for users to add to the INI file which I’ve suggested, that’s quite easy.

You’d need two text controls in the dialog (plus OK, Cancel or whatever else you wish). Let’s name them txtOldWord and txtNewWord. The OK command button would cause the following code to fire:

System.PrivateProfileString (sINI, “WordList”, txtOldWord.Text) = txtNewWord.Text

Within the parens you have 1) the file name 2) the Section and 3) the Key. To the right of the equals sign you have the value. The user has entered the Key in the first text control, and has entered the Value in the second text control. The Section (WordList) is in quotes because it’s a literal string (VBA knows about the brackets because it’s a Section), and the two text fields are the two bits of text the user has typed into the dialog. For the Key field, you’ll want to use the Replace function to replace spaces with underscores; you’ll also want to disallow illegal characters: if the user enters an apostrophe, for example, display a message that such a word can’t be used. (Use the KeyPress event to monitor this.) All you do is put that line of code in your macro, and the INI file will be updated/saved…. no file to open or save. If the Key already exists, the value would be overwritten. Of course you could add code to check if the Key exists and asd the user to verify the he want to change the value. All of this assumes that the user has permission to the file, the folder and that sort of thing. Since these appear to be stand-alone PCs, I shouldn’t imagine that’s an issue. You could also add code to check that the INI file exists, although it will be created if it doest not. The first time the macro runs, it could test for the path/file and create a new folder on the local drive or under My Documents if that’s necessary.

This of course is an interface to the INI file, not to the macro code. An interface that would directly modify the original macro code itself would at the very least be dangerous. Again, this is not really code that you could shrink-wrap and sell or distribute to a disparate group of people. It seems you have a small group that’s within your control, and the limitations of this very basic code shouldn’t be a problem.

Richard

Reply | Quote

Again, thanks, Richard.

By interface, I simply meant an easy way for users to add to the list of words without having to actually edit the macro or open/close a document. I appreciate very much the explanation. I plan to tackle this over the weekend.

Believe it or not, I actually have 6 books on VBA and none of them are particularly well-written for the beginner like me. I find the O’Reilly books — especially Word Hacks and Writing Word Macros — the most useful because I can follow a real-world example that means something to me. I have a lot of difficulty with the books that isolate each item but never really put them together is something more meaningful than displaying “Hello” and without any more explanation than I can get from VBA Help.

Again, thanks for the detailed explanations.

Reply | Quote

Have a look at VBA For Dummies. It’s a little less advanced than the O’Reilly books and quite well written.

Reply | Quote

Thanks, Richard. I will check it out. I did buy yesterday yet another book — Absolute Beginner’s Guide to VBA by Paul McFedries — that looks good. I will gladly accept recommendations for VBA books regardless of whether for beginner or expert.

As a more general question. What is the relationship/difference between VBA, VB6, and VB.Net? For example, if I wrote a program in VB6, would it be usable as a macro in Word or are these languages with a common root that have become incompatible branches? I find myself confused by the different “flavors” of things called VB. And what about Visual Studio? Is that yet another incompatible flavor?

Lastly, has Microsoft indicated whether macros written in VBA for Word 2003 be usable without modification in Word 12?

Reply | Quote

Here’s a very simple explanation. Perhaps others can add more information for you: Visual Basic (as in VB6) is a retail product used to develop stand-alone applications such as EXEs. VBA uses a similar language, but it’s always part of (and specific to) another product, such as the components of MS Office, as well as other applications (WordPerfect, for example) that pay a licensing fee to Microsoft. Visual Studio, similar to VB, can be used to create code for use with Office (as well as stand-alone applications), but I don’t believe it’s suitable for global templates and add-ins. I don’t know all the details, as I’ve never used it. It was just yesterday that I read on the MS site that it has limitations in regard to global templates and add-ins.

I have not been able to find any concrete answer to your last question. Since introducing VBA, Microsoft has been very good about backward compatibility. I would expect that to be the case with Word 12, but it’s just an assumption on my part.

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

Here are two articles that provide a bit of insight as to the future of VBA in Office 12.

http://msdnisv.communityserver.org/forums/1167/ShowPost.aspx%5B/url%5D

http://blogs.msdn.com/brian_jones/archive/…/12/438262.aspx%5B/url%5D

Reply | Quote

Hans, I have a question about the sample macro you provided earlier in this thread. When I tried it, I found that it runs more slowly (I am speaking milliseconds but I notice the difference) than the toggle macros I wrote, yet it has significantly less code and significantly fewer checks for matches to make. I am wondering why. Is it because your macro has to go to an outside source to check for matches whereas mine does it all internally? Is there something about how the macros are structured or the choices made in coding? Is it something else that makes the difference? I apologize if I my questions seem critical, it is not my intent. I am trying to figure out the ins and outs of VBA macro writing and this is something I noticed.

Reply | Quote

You have legitimate questions, I don’t interpret them as criticism.

The code I posted will be considerably slower than yours, because
a) It has to open the document with the replacements if it isn’t open yet.
Looping through the cells of a table is slow, certainly much slower than processing a Select Case statement.

Maximising the performance of Word tables on the Word MVP site has some suggestions on how to speed up processing tables in VBA.

It’s up to you to decide which approach has the right balance of maintainability, ease-of-use and performance, both for you as programmer and for the end users.

Reply | Quote

Thanks, Hans, for the referral to the article and for your patience.

Reply | Quote

Not sure, but the macro described Here might do want you want.

When you run the macro, it goes through the entire Word document and if it finds a word on the list, replaces it with the replacement word.
For example, it finds centimeter, it will replace it with cm.

I use a modified version that keeps the words for replacement in a separate text file.

Reply | Quote

Thanks for the suggestion, NeuroDoc, but the macro you suggest is essentially a find and replace macro. The macro would work great if we were correcting misspellings or something that doesn’t require making a decision based on context. The toggle macros are based on grammar decisions made in context. For example, if it were always incorrect to use “principal” because in every instance “principle” was meant, the macro you suggest would work fine. But consider this:

“The principal’s principles were immoral and the assistant principal did nothing to correct those principles.”

Any change has to be decided in context — the context not only of the current sentence, but of the preceding and succeeding sentence.

Reply | Quote

(Edited by NeuroDoc on 15-Jan-06 04:06. Example Geaphic added.)

[indent]

---

the macro you suggest is essentially a find and replace macro.

---

[/indent]
Correct, but the macro allows you to choose whether you make the change or not. It also can check for phrases, rather than single words.

One thing that might work for your multiple users, is using a text file and fso to store the changes. Each user could simply add the word to the text file.

I have a second macro that lets a user select a word or phrase, which is then copied to a dialog box. The user then can add the corrected phrase to the text box. Pressing the command button adds the combination to the text file, which is used the next time the correction macro is run.

I run the correction macro on my dictations. Then word in question appears in a userform, which offers the possible correction. It wouldn’t be hard to have a list open with several possible choices.

Another idea would be to use an exclusion dictionary. That way word principal would show up as an error during spell check and you could select the correct choice from the spell check list. See Post 19407

Reply | Quote

The more I think about your two macros, NeuroDoc, the more intrigued I am. I think your suggestion would be useful, but for a different task than the toggle macros. I know that it seems as if a F&R macro should work, but it doesn’t — we have tried that in the past, although not with option to change or not change.

Would you be willing to share with me the code you use for both the macro and the add-to-list dialog box? When a word is added to the list via your dialog box, will the word/phrase be included in the search the next time the macro is run or do you have to exit and restart Word? Can your macro deal with punctuation? For example, could I enter J. Cardiovasc. Surg. as the find phrase or microbally-derived or are there limitations as to how the find phrase is constructed? Is it case sensitive? Is there a limit as to the number of words or phrases that can be in the Find list?

Reply | Quote

Basically the F&R macro is used to clean up dictations done by Speech Recognition. In this context it works very well and i see some utility for editing. Basically it is a supersized spell check for phrases. The option to always correct is nice, for example it will fix “lumber puncture’ to “lumbar puncture” or “1 month” to “one month” without a prompt. It however won’t change “lumber jack”.

No problem sharing the code. I don’t have it on this laptop but will try to send it later today. If I get time, I want to package the whole thing into a Word Template.

It basically uses the Word find and replace function, so it can find any word or phrase and can be case sensitive. The find list is simply a text file so there is no limitation on the number of entries. If you add a word/phrase to the find list, there is no need to restart Word.

I want to add some more options, for example enable some wildcard characters in the find list. See Also Here. Thiscould really work for your purpose by including some decision making.

For some reason, I couldn’t get the wildcard searches to work when run from the VBA code. I think that there are some bugs in Word that cause a problem with this Maybe More Here?.

Reply | Quote