My book was published in 2014 and part of the writing process was creating an Index. To make the task easier I wrote a macro to assist me. Adding an Index to a large document can improve its usefulness. I am sharing the file I used in this blog post.

Warning: The Index layout may not meet your requirements, so you should do a test to see if the structure is what you need. If you are happy with the results then create your own Index. I offer the software as is and do not guarantee it will work. Use it at your own risk. I do not support the software, but I may fix it if you identify bugs. Feel free to amend it.

Indexes

Indexes are great for large documents, but creating them can be difficult and time consuming. This file and macro reduces the time and the difficulty, plus it makes it easier to amend the Index going forward. You can download the file using the button below.

Download Example File

This file includes the Index from my book as an example. (If you like the contents you can learn more about the book here).

There are three sheets.

1. Input – the first sheet is where you define your topics and specify the pages.
2. Index – the second sheet is where the macro creates the completed Index.
3. Copy to Word – the third sheet has instructions on copying the Index to Word where it will appear more like a printed Index.

The macro has been listed at the bottom of this post if you are interested. I have added comments to explain the various parts of the macro.

Three Levels

The Index created can have one, two or three levels. The second level allows you to specify topics underneath a main heading and you can another level under that if required. The Arrays formulas topic is an example in the screen shot below. The topic is Array formulas on row 31. There are no pages allocated to it as it is the heading row. The pages are all allocated to the sub-topics (rows 32 to 36) that are sub-level topics of Array formulas.

My book only used two levels in the Index.

Using the Input page

1. In column A create a list of main topics you know will be included. Enter them in any order.
2. Once finished sort the topics in alphabetic order
3. Insert blank rows and add sub-level topics in alphabetic order in columns B and/or C if required.
4. Go through the document and enter an x on the intersection of the row of the topic and the column of the page.
5. If you are using Level Two or Level Three topics don’t enter any x entries on the heading row.
6. Enter an x for every page involved.
7. If you find a topic not already included, insert a whole blank row in the sheet in the correct alphabetic sequence and enter the name in column A and an x in the correct column.

Creating the Index

Once completed click the Create Index button at the top of the Input sheet and review the Index Sheet.

Copying to Word

Use the instructions on the Copy to Word sheet to copy the Index to Word and you are ready to go.

The macro code is shown below

Sub Create_Index()
' Procedure: Create_Index
' DateTime: 10/02/2014 8:04:28 PM
' Author: Neale Blackwood
' Description: This macro creates an index based on the entries in the Input sheet
' Note: there are tab characters placed in the resulting entries that aren't
' visible in the Index sheet listing. These become visible when pasted into Word.
'--
 
Const cstrProcedure = "Create_Index"
On Error GoTo HandleError
 
Dim r, c
Dim ws As Worksheet
Dim wsInp As Worksheet
Dim strPages As String
 
Set ws = Sheet2
Set wsInp = Sheet1
 
'clear the Index sheet
ws.Cells.ClearContents
 
'2000 topics included
For r = 2 To 2000
 
  'on each row clear the page numbers captured
  strPages = ""
 
  'handles 1000 pages
  For c = 1 To 1000
 
    If c <= 3 Then
    'topics columns
    'directly capture the entries in the first three columns
      If wsInp.Cells(r, c) <> "" Then
 
        ws.Cells(r, c) = wsInp.Cells(r, c)
 
      End If
 
    Else 'page columns
 
      If wsInp.Cells(r, c) <> "" Then
 
        If wsInp.Cells(r, c + 1) <> "" Then
          'if the next column has an entry then use the dash to join the numbers
          If Right(strPages, 1) <> "-" Then
            'insert the page number and a dash
            strPages = strPages & ", " & wsInp.Cells(1, c) & "-"
 
          End If
 
        'if there is a dash then add the page number to the end
        ElseIf Right(strPages, 1) = "-" Then
 
          strPages = strPages & wsInp.Cells(1, c)
 
        Else 'capture the page and add a comma
 
          strPages = strPages & ", " & wsInp.Cells(1, c)
 
        End If
 
      End If
    End If
  Next c
 
  'create an index in column E based on entries in the first 4 columns
  'CHAR(9) is a tab character
  'CHAR(34) is the " character (double quotes)
  If strPages <> "" Then
 
    ws.Cells(r, 4) = strPages
 
    If ws.Cells(r, 1) <> "" Then ws.Cells(r, 5) = ws.Cells(r, 1) & ws.Cells(r, 4)
 
    If ws.Cells(r, 2) <> "" Then ws.Cells(r, 5).Formula = _ 
      "=char(9)&" & Chr(34) & ws.Cells(r, 2) & ws.Cells(r, 4) & Chr(34)
 
    If ws.Cells(r, 3) <> "" Then ws.Cells(r, 5).Formula = _ 
      "=char(9)&char(9)&" & Chr(34) & ws.Cells(r, 3) & ws.Cells(r, 4) & Chr(34)
 
  Else
 
    If ws.Cells(r, 1) <> "" Then ws.Cells(r, 5) = ws.Cells(r, 1)
 
    If ws.Cells(r, 2) <> "" Then ws.Cells(r, 5).Formula = _ 
      "=char(9)&" & Chr(34) & ws.Cells(r, 2) & Chr(34)
 
    If ws.Cells(r, 3) <> "" Then ws.Cells(r, 5).Formula = _ 
      "=char(9)&char(9)&" & Chr(34) & ws.Cells(r, 3) & Chr(34)
 
  End If
 
Next r
 
HandleExit:
  Set ws = Nothing
  Set wsInp = Nothing
  Exit Sub
 
HandleError:
  MsgBox "The macro has experienced an error on row number " & r, _
    vbOKOnly, "Macro Stopped"
  Resume HandleExit
End Sub