Advertisment

Creating visual basic macros in MS Word?

author-image
CIOL Bureau
Updated On
New Update

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. 

Advertisment

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

Advertisment

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 '
Advertisment

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

Advertisment

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

Advertisment

' 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?.

tech-news