A fun little assignment. Someone had, via the Record Macro, functionality had written some code which would copy the highlighted text from one document and into another. The idea was that the second document would be some form of Glossary Document. However, after a few days the recorded macro started to fail and so the call went out to see if the code could be improved.
One of the great problems about using the code from the Record Macro function in a production environment is that things in Real Life rarely mimic what the conditions which were present when the macro was recorded. This is why we always say that anything that comes from Record Macro has to be rewritten entirely after the general idea has be taken.
The first thing that must be understood is that the Record Macro does just what it says; it records what you do. The code it produces should be considered to be more of a log of what has happened rather than real useable code. Certainly it comes nowhere close to what we would call good VBA standard code.
The second thing is to realise is that in recorded code there is no error handling and there is no form of defensive programming at all. Neither do we expect there to be. In this scenario, we have a requirement to copy the selected text from one document to another. What happens if there are no documents open? Or just one document? What happens if the glossary document is closed, or will opening another document cause problems and, also, what happens if one tries to copy text from the glossary document to itself?
All of these are Real World issues and have to be addresed by the code. Clearly the Record Macro function cannot cater for these problems and this is why we stress that by all means use the Record Macro function but then be prepared to work on the code until it does what is really required.
In this case we were told of another requirement; if the highlighted text is only a point and not a range then the remainder of the word to the selected text must be included. Now we come to conditional requirements which, of course, no Record Macro would ever cope with. So, this is simple, if the selection is a point and is on the left hand side of a word then the word gets expanded. Of course, if the selection point is at the end of the word then it has to move to the right to the start of the next word and then select that. The specification is getting a little more involved.
There are a number of ways in which this problem can be solved and this is the way which we provided a solution. There are perhaps dozens of other methods, no doubt more elegant than this one, but this seemed to work and the user went away happy.
From the specification it is clear that we are going to have to identify the Glossary document. This variable would then have to be held globally. This, therefore, means that a specific start-up template would have to be created. Within this template would have placed the pointer to the Glossary Template as a Public variable but we decided to hold this within a Class.
Below is the code for clsGlossary:
Option Explicit Private m_oGlossaryDocument As Document Sub DefineGlossaryDocument() If Documents.Count < 2 Then MsgBox "Not enough documents open. Please ensure that at least two documents are open.", _ vbOKOnly, "Error - Set Glossary Document" Exit Sub End If If Not m_oGlossaryDocument Is Nothing Then If MsgBox("Do you wish to change the Glossay Document from " & _ m_oGlossaryDocument.Name & "?", vbYesNo, "Set Glossary Document") = vbNo Then Exit Sub End If End If Set m_oGlossaryDocument = ActiveDocument MsgBox "Glossary Document is now defined to be " & m_oGlossaryDocument.Name, _ vbOKOnly, "Set Glossary Document" End Sub Property Get Name() As String Name = m_oGlossaryDocument.Name End Property Property Get IsOpen() As Boolean Dim oDoc As Document Dim bRetVal As Boolean bRetVal = False If Not m_oGlossaryDocument Is Nothing Then For Each oDoc In Documents If oDoc.Name = m_oGlossaryDocument.Name Then bRetVal = True Exit For End If Next End If IsOpen = bRetVal End Property Public Sub CopySelection(oRange As Range) Dim oActiveDocument As Document If Not m_oGlossaryDocument Is Nothing Then Set oActiveDocument = ActiveDocument m_oGlossaryDocument.Activate Application.Selection.Range = oRange oActiveDocument.Activate End If End Sub
The glossary document pointer is held privately within this class. Before any action can be taken again the Glossary document it has to be defined. If the Glossary document is already defined then the user is prompted to see if he wants to define a new glossary document. Of course, there will have to be at least documents open before this can be done as there is no point doing anything until the required number of documents are present.
One important property and procedure is the IsOpen() property. This checks to see if the Glossary Document is still open and the other is the CopySelection() routine which, of course, copies the text from the active document to the glossary document.
All of these routines are called from the module, modGlossary:
Option Explicit Public oGlossary As clsGlossary Public Sub DefineGlossaryDocument() If oGlossary Is Nothing Then Set oGlossary = New clsGlossary End If oGlossary.DefineGlossaryDocument End Sub Public Sub CopySelectedText() Dim oRange As Range If Documents.Count < 2 Then MsgBox "There needs to be at least documents open to contine this operation.", _ vbOKOnly + vbExclamation, "Error: Glossary" Exit Sub End If If oGlossary Is Nothing Then MsgBox "Please define the Glossary Document before continuing.", _ vbOKOnly + vbExclamation, "Error: Glossary" Else If ActiveDocument.Name = oGlossary.Name Then MsgBox "This operation cannot work if the Glossary Document is selected.", _ vbOKOnly + vbExclamation, "Error: Glossary" Else If Not oGlossary.IsOpen() Then MsgBox "The Glossay Document seems to have vanished.", _ vbOKOnly + vbExclamation, "Error: Glossary" Else ' All of the obvious pitfalls are avoided let's move the data from one document to another Set oRange = Application.Selection.Range If oRange.Start = oRange.End Then Application.Selection.MoveRight unit:=wdWord, Count:=1, Extend:=wdExtend Set oRange = Application.Selection.Range If Trim$(oRange.Text) = "" Then Application.Selection.MoveRight unit:=wdWord, Count:=1, Extend:=wdExtend Set oRange = Application.Selection.Range End If oGlossary.CopySelection oRange Else oGlossary.CopySelection oRange End If End If End If End If End Sub Public Sub About() Dim oAbout As frmAbout Set oAbout = New frmAbout oAbout.Show Unload oAbout Set oAbout = Nothing End Sub
There are three public procedures in this module. Each is linked to a button in the toolbar. The first, DefineGlossaryDocument() defines or redefines the Glossary Document. The second, CopySelectedText(), copies the text (subject to a few criteria) from the active document to the Glossary document and then last, About(), displays a simple form which shows the author's details.
The interesting part of the code is in the CopySelectedText() routine. The first two checks are to see if there are enough documents open (there has to be at least two) and that the Glossary Document is defined. If not then the user is given a warning. Then given that there is a Glossary Document defined and that there is enough documents open the check has to be made that the current document isn't the Glossary Document and that the Glossary Document is open still.
Once these tests have been performed, and passed, then the actual copying of the text can go ahead. If the selection range is zero length then the selection is moved to the right until the selection covers the remainder of the current or the whole of the next word. Once that is done then the CopySelection() method is called from the clsGlossary object.
Note that this works by passing the Range object of the selected text and not by putting the text through the clipboard. This is because the user may have something of importance within the clipboard and it is the responsibility of the programmer to ensure that the contents of the clipboard are not disturbed unless absolutely necessary.
So, in summary, we can see that the code has been expanded to take up a fair few lines. However, please take note that the core of the code which does the actual required functionality contains just a few lines and the rest of the code within the module and class is mostly concerned with defensive programming.
This code can be downloaded from here.
If there are any suggestions for updates or comments then please drop us a mail at email@example.com.