My book was published just over four years ago 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 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.
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.
- Input – the first sheet is where you define your topics and specify the pages.
- Index – the second sheet is where the macro creates the completed Index.
- 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.
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
- In column A create a list of main topics you know will be included. Enter them in any order.
- Once finished sort the topics in alphabetic order
- Insert blank rows and add sub-level topics in alphabetic order in columns B and/or C if required.
- Go through the document and enter an x on the intersection of the row of the topic and the column of the page.
- If you are using Level Two or Level Three topics don’t enter any x entries on the heading row.
- Enter an x for every page involved.
- 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 adn 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