User:Klever: Difference between revisions

Content added Content deleted
(→‎VBA Examples: KWIC index)
Line 234: Line 234:
'KWIC index
'KWIC index
'assumptions:
'assumptions:
' - all titles can be held in an array in main memory
' - all titles and catalog numbers can be held in an array in main memory
' - use the index in the array as the "catalog number"
' - disregard punctuation in titles
' - disregard punctuation in titles
' - the KWIC index itself may be too large for main memory - do not store it in memory
' - the KWIC index itself may be too large for main memory - do not store it in memory
Line 241: Line 240:
' - the catalog number
' - the catalog number
' - the title with the keyword centered in a line of given length (e.g. 80 or 120)
' - the title with the keyword centered in a line of given length (e.g. 80 or 120)
' (constant-pitch font assumed)
' (constant-width font assumed)
' note: long titles may be truncated at the beginning or the end of the line
' note: long titles may be truncated at the beginning or the end of the line


Line 248: Line 247:
Const STOPWORDS = "a an and by for is it of on or the to with " 'that last space is needed!
Const STOPWORDS = "a an and by for is it of on or the to with " 'that last space is needed!
Dim title() As String 'list of titles to be included in KWIC index
Dim title() As String 'list of titles to be included in KWIC index
Dim catno() As Integer 'list of catalog numbers
Dim ntitle As Integer 'number of titles
Dim ntitle As Integer 'number of titles
Dim index() As Integer 'holds title number and position of keyword in title
Dim index() As Integer 'holds title number and position of keyword in title
Line 253: Line 253:


Sub ReadTitles()
Sub ReadTitles()
' read or - in this case - set the titles
' read or - in this case - set the titles and catalog numbers
ntitle = 10
ntitle = 10
ReDim title(1 To ntitle)
ReDim title(1 To ntitle)
ReDim catno(1 To ntitle)
title(1) = "Microsoft Visio 2003 User's Guide"
title(1) = "Microsoft Visio 2003 User's Guide"
title(2) = "Microsoft Office Excel 2003 Inside Out"
title(2) = "Microsoft Office Excel 2003 Inside Out"
Line 266: Line 267:
title(9) = "How to do Everything with Microsoft Office Excel 2003"
title(9) = "How to do Everything with Microsoft Office Excel 2003"
title(10) = "Data Analysis Using SQL and Excel"
title(10) = "Data Analysis Using SQL and Excel"
catno(1) = 10
catno(2) = 13
catno(3) = 3435
catno(4) = 987
catno(5) = 1010
catno(6) = 1244
catno(7) = 709
catno(8) = 9088
catno(9) = 33
catno(10) = 7733
End Sub
End Sub


Line 315: Line 326:
switched = True
switched = True
Do While switched
Do While switched
'scan array for two shifted strings in the wrong order and swap them
'scan array for two shifted strings in the wrong order and swap
'(swap the index entries, not the strings)
'use case-insensitive compare
'use case-insensitive compare
switched = False
switched = False
Line 321: Line 333:
string1 = LCase(Shift(title(index(i, 1)), index(i, 2)))
string1 = LCase(Shift(title(index(i, 1)), index(i, 2)))
string2 = LCase(Shift(title(index(i + 1, 1)), index(i + 1, 2)))
string2 = LCase(Shift(title(index(i + 1, 1)), index(i + 1, 2)))
If string2 < string1 Then 'switch
If string2 < string1 Then 'swap
For j = 1 To 2
For j = 1 To 2
temp = index(i, j)
temp = index(i, j)
Line 336: Line 348:
'print the KWIC index
'print the KWIC index
spaces = Space(linelength / 2)
spaces = Space(linelength / 2)
Debug.Print "Index number", "|"; Space((linelength - 10) / 2); "KWIC string"
Debug.Print "Cat. number", "|"; Space((linelength - 10) / 2); "KWIC string"
Debug.Print String(linelength + 14, "-")
Debug.Print String(linelength + 15, "-")
For i = 1 To nkeys
For i = 1 To nkeys
atitle = title(index(i, 1))
atitle = title(index(i, 1))
ltitle = Len(atitle)
pos = index(i, 2)
pos = index(i, 2)
'create shifted string so that keyword is centered in the line
'create shifted string so that keyword is centered in the line
Line 346: Line 357:
part1 = Right$(spaces & Left$(atitle, pos - 1), linelength / 2)
part1 = Right$(spaces & Left$(atitle, pos - 1), linelength / 2)
kwicstring = Right$(part1, linelength / 2) & Left$(part2, linelength / 2)
kwicstring = Right$(part1, linelength / 2) & Left$(part2, linelength / 2)
Debug.Print index(i, 1), "|"; kwicstring
Debug.Print catno(index(i, 1)), "|"; kwicstring
Next
Next
End Sub
End Sub
Line 355: Line 366:
'set array
'set array
ReDim index(ntitle * MAXKEYS, 2)
ReDim index(ntitle * MAXKEYS, 2)
'index(.,1) is catalog nr. (here, equal to title nr.)
'index(.,1) is title nr.
'index(.,2) is keyword position in title
'index(.,2) is keyword position in title
ProcessTitles
ProcessTitles
SortTitles
SortTitles
PrintKWIC (120) 'argument is the length of the KWIC titles (excluding catalog numbers)
PrintKWIC 80 'argument is the length of the KWIC lines (excluding catalog numbers)
End Sub
End Sub
</lang>
</lang>


Output (note that some titles are truncated at the start or the end):
Output:
<pre>
<pre>
kwic
kwic
Index number | KWIC string
Cat. number | KWIC string
--------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
4 | Excel 2003 Formulas
987 | Excel 2003 Formulas
9 | How to do Everything with Microsoft Office Excel 2003
33 | Everything with Microsoft Office Excel 2003
2 | Microsoft Office Excel 2003 Inside Out
13 | Microsoft Office Excel 2003 Inside Out
3 | Mastering Excel 2003 Programming with VBA
3435 | Mastering Excel 2003 Programming with VBA
1 | Microsoft Visio 2003 User's Guide
10 | Microsoft Visio 2003 User's Guide
6 | Excel 2003 VBA Programmer's Reference
1244 | Excel 2003 VBA Programmer's Reference
8 | Beginning Excel: What-if Data Analysis Tools
9088 | Beginning Excel: What-if Data Analysis Tools
7 | Automated Data Analysis Using Excel
709 | Automated Data Analysis Using Excel
10 | Data Analysis Using SQL and Excel
7733 | Data Analysis Using SQL and Excel
7 | Automated Data Analysis Using Excel
709 | Automated Data Analysis Using Excel
8 | Beginning Excel: What-if Data Analysis Tools
9088 | Beginning Excel: What-if Data Analysis T
8 | Beginning Excel: What-if Data Analysis Tools
9088 | Beginning Excel: What-if Data Analysis Tools
7 | Automated Data Analysis Using Excel
709 | Automated Data Analysis Using Excel
10 | Data Analysis Using SQL and Excel
7733 | Data Analysis Using SQL and Excel
9 | How to do Everything with Microsoft Office Excel 2003
33 | How to do Everything with Microsoft Office Exce
5 | Excel for Scientists and Engineers
1010 | Excel for Scientists and Engineers
9 | How to do Everything with Microsoft Office Excel 2003
33 | How to do Everything with Microsoft Office Excel 2
4 | Excel 2003 Formulas
987 | Excel 2003 Formulas
9 | How to do Everything with Microsoft Office Excel 2003
33 | to do Everything with Microsoft Office Excel 2003
2 | Microsoft Office Excel 2003 Inside Out
13 | Microsoft Office Excel 2003 Inside Out
3 | Mastering Excel 2003 Programming with VBA
3435 | Mastering Excel 2003 Programming with VBA
6 | Excel 2003 VBA Programmer's Reference
1244 | Excel 2003 VBA Programmer's Reference
7 | Automated Data Analysis Using Excel
709 | Automated Data Analysis Using Excel
10 | Data Analysis Using SQL and Excel
7733 | Data Analysis Using SQL and Excel
5 | Excel for Scientists and Engineers
1010 | Excel for Scientists and Engineers
8 | Beginning Excel: What-if Data Analysis Tools
9088 | Beginning Excel: What-if Data Analysis Tools
4 | Excel 2003 Formulas
987 | Excel 2003 Formulas
1 | Microsoft Visio 2003 User's Guide
10 | Microsoft Visio 2003 User's Guide
9 | How to do Everything with Microsoft Office Excel 2003
33 | How to do Everything with Microsoft Offi
2 | Microsoft Office Excel 2003 Inside Out
13 | Microsoft Office Excel 2003 Inside Out
3 | Mastering Excel 2003 Programming with VBA
3435 | Mastering Excel 2003 Programming with VB
9 | How to do Everything with Microsoft Office Excel 2003
33 | How to do Everything with Microsoft Office Excel 2003
2 | Microsoft Office Excel 2003 Inside Out
13 | Microsoft Office Excel 2003 Inside Out
1 | Microsoft Visio 2003 User's Guide
10 | Microsoft Visio 2003 User's Guide
9 | How to do Everything with Microsoft Office Excel 2003
33 | How to do Everything with Microsoft Office Excel 2003
2 | Microsoft Office Excel 2003 Inside Out
13 | Microsoft Office Excel 2003 Inside Out
2 | Microsoft Office Excel 2003 Inside Out
13 | Microsoft Office Excel 2003 Inside Out
6 | Excel 2003 VBA Programmer's Reference
1244 | Excel 2003 VBA Programmer's Reference
3 | Mastering Excel 2003 Programming with VBA
3435 | Mastering Excel 2003 Programming with VBA
6 | Excel 2003 VBA Programmer's Reference
1244 | Excel 2003 VBA Programmer's Reference
5 | Excel for Scientists and Engineers
1010 | Excel for Scientists and Engineers
10 | Data Analysis Using SQL and Excel
7733 | Data Analysis Using SQL and Excel
8 | Beginning Excel: What-if Data Analysis Tools
9088 | Beginning Excel: What-if Data Analysis Tools
1 | Microsoft Visio 2003 User's Guide
10 | Microsoft Visio 2003 User's Guide
7 | Automated Data Analysis Using Excel
709 | Automated Data Analysis Using Excel
10 | Data Analysis Using SQL and Excel
7733 | Data Analysis Using SQL and Excel
3 | Mastering Excel 2003 Programming with VBA
3435 | Mastering Excel 2003 Programming with VBA
6 | Excel 2003 VBA Programmer's Reference
1244 | Excel 2003 VBA Programmer's Reference
1 | Microsoft Visio 2003 User's Guide
10 | Microsoft Visio 2003 User's Guide
8 | Beginning Excel: What-if Data Analysis Tools
9088 | Beginning Excel: What-if Data Analysis Tools
</pre>
</pre>


Retrieved from "https://rosettacode.org/wiki/User:Klever"