BANGALORE, INDIA: A macro is a rule or a pattern that specifies how a certain input sequence (sequence of characters) should be mapped to an output sequence (sequence of characters) according to a defined procedure.These are a series of commands that are recorded so they can be played back, or executed later.
All of us have used Microsoft's Word at one time or the other, but few of us know or realize that there is a provision for writing our own code and implement them in Word. Recently, while doing a research project, I was stuck at a point where I had a huge amount of written data required to be analyzed, more specifically I had to find out individual occurrence of some words in the whole document.
This was manually impossible and not practical since the Word document was nearly 13,000 pages long and had more than 4 million words. I was left with the choice of installing Linux and then use their fabulous feature that would have let me do it easily. However, this was not possible as I hardly had any disk space left for a new partition to install Linux on. That is when I decided to use this wonderful feature in Microsoft Word. I will be using MS Word 2007 to explain the process.
Initializing the Developer Tab
Click on the Microsoft Office icon on the top left hand corner of the word document screen. Click on 'Word Options'. In the 'Popular' Tab, choose 'Show Developer Tab in The Ribbon', if not already selected. This will show a new tab in the toolbar called 'Developer.'
Setting up the macro
The following steps show how to record and save a macro:
- Click on 'Record Macro', a dialogue box will open.
- Name your macro (let it be 'test'), assign it as a button or keyboard shortcut.
- Select 'Normal.dotm' from the drop down list.
- Selecting the 'Button' option opens up a new dialogue box.
- Select 'Normal.NewMacros.test' and click on add.
- Under 'Customize Quick Access Toolbar', we can see that our macro has been added.
- Select the macro created and click on the 'Modify' button, a box opens up; select any icon for the macro and change its name(if you want to).
- Click 'OK'.
- We can see that the icon (i chose the yellow smiley) has been added at the topmost toolbar.
- Click on 'Stop Recording'.
- Now, that everything is set, click the 'macros' button in the 'Developer' tab.
- Select your macro (test) and click on 'Edit'.
- A VB editor opens up. This is where we have to write or code our macro between 'Sub' and 'End Sub'.
- Write the code, save it and close the editor. We are ready to go.
- Coding the macro
Const word_limit = 9000 'Maximum unique words allowed
Dim Word_s As String 'Raw word pulled from doc
Dim Words(word_limit) As String 'Array to hold unique words
Dim occur_unique(word_limit) As Integer ' counter for unique words
Dim Counter_word As Integer 'Number of unique words
Dim Byoccur_unique As Boolean 'Flag for sorting order
Dim total_words As Long 'Total words in the document
Dim not_find As String Words to be excluded
Dim Found As Boolean 'Temporary flag
Dim j, k, l, Temp As Integer 'Temporary variables
Dim choice As String 'How user wants to sort results
Dim tword As String '
Set up excluded words
not_find="
{#PageBreak#}
' Find out how to sort
Byoccur_unique = True
choice = InputBox("Sort by WORD or by occur_unique?", "Sort order", "WORD")
If choice = "" Then End
If UCase(choice) = "WORD" Then
Byoccur_unique = False
End If
Selection.HomeKey Unit:=wdStory
System.Cursor = wdCursorWait
Counter_word = 0
total_words = ActiveDocument.Words.Count
' Control the repeat
For Each aword In ActiveDocument.Words
Word_s = Trim(LCase(aword))
'Out of range?
If Word_s < "a" Or Word_s > "z" Then
Word_s = ""
End If
'On exclude list?
If InStr(not_find, "<" & Word_s & ">") Then
Word_s = ""
End If
If Len(Word_s) > 0 Then
Found = False
For j = 1 To Counter_word
If Words(j) = Word_s Then
occur_unique(j) = occur_unique(j) + 1
Found = True
Exit For
End If
Next j
If Not Found Then
Counter_word = Counter_word + 1
Words(Counter_word) = Word_s
occur_unique(Counter_word) = 1
End If
If Counter_word > word_limit - 1 Then
j = MsgBox("Too many words.", vbOKOnly)
Exit For
End If
End If
total_words = total_words - 1
StatusBar = "Remaining: " & total_words & ", Unique: " & Counter_word
Next aword
' Now sort it into word order
For j = 1 To Counter_word - 1
k = j
For l = j + 1 To Counter_word
If (Not Byoccur_unique And Words(l) < Words(k)) _
Or (Byoccur_unique And occur_unique(l) > occur_unique(k)) Then k = l
Next l
If k <> j Then
tword = Words(j)
Words(j) = Words(k)
Words(k) = tword
Temp = occur_unique(j)
occur_unique(j) = occur_unique(k)
occur_unique(k) = Temp
End If
StatusBar = "Sorting: " & Counter_word - j
Next j
' Now write out the results
tmpName = ActiveDocument.AttachedTemplate.FullName
Documents.Add Template:=tmpName, NewTemplate:=False
Selection.ParagraphFormat.TabStops.ClearAll
With Selection
For j = 1 To Counter_word
.TypeText Text:=Trim(Str(occur_unique(j))) _
& vbTab & Words(j) & vbCrLf
Next j
End With
System.Cursor = wdCursorNormal
j = MsgBox("There were " & Trim(Str(Counter_word)) & _
" different words ", vbOKOnly, "Finished")
Running the macro
In the topmost toolbar, an icon has been created (yellow smiley, in my case). By clicking on this macro, you are asked if you want to create a list sorted by word or by frequency. If you choose word, then the resulting list is shown in alphabetical order, whereas if you choose frequency, then the resulting list is in descending order based on how many times the word appeared in the document.
While the macro is running, the status bar indicates what is happening. Depending on the size of your document and the speed of your computer, the macro may take a while to complete (in my case my document contained 7000 words and it took about 5 seconds to generate the output)
As in the case of any branch of science, sometimes, we have to revert back to the old technologies and, we still find new uses for it, hence the phrase ?Old is Gold?.