I have a 100page long .docx format document. I'm using a macro written in VBS to extract some information and then just generate a table from them.  I iterate through the paragraphs and store the found strings in 3 separate arrays.However, the loop is unreasonably slow. It takes 3 min to complete on a relatively fast computer. Can you take a look at it and tell me what causes this slowness?'TODO : Add checks, exception handling, dynamic user options, probes, style checks, and fix slowness.Sub genTable()Dim objDoc'''''''''''''''''''''Modify these''''''''''''''''Dim ColumnName1, ColumnName2, ColumnName3, magicStringColumnName1 = foo1ColumnName2 = foo2ColumnName3 = foo3magicString = ASD321:  ' we search for this stringSet objDoc = ActiveDocument ' Because we run inside of word as macro''''''''''''''''''''''''''''''''''''''''''''''''''Const MAX = 200 ' using fixed sized arrays, mod this if you'll have more than 200 entries.Dim vulnerabilityArr(MAX) ' initializing the arraysDim severityArr(MAX)Dim paragraphArr(MAX)Dim counter ' will count the processed entries in thiscounter = 0Dim currParagraph ' will be set in loopDim tmpArray() As String ' for splitting    For pIndex = 1 To objDoc.Paragraphs.Count ' THIS LOOP IS SLOW        currParagraph = objDoc.Paragraphs(pIndex)        currParagraph = Left(currParagraph, Len(currParagraph) - 1) 'remove junk character        If InStr(1, currParagraph, magicString) > 0 Then ' assuming this string is always present and the other two target data is near it            tmpArray = Split(currParagraph) ' extract level            currParagraph = objDoc.Paragraphs(pIndex - 1) 'assuming the previous paragraph is the vuln. name            'Storing the 3 extracted data            vulnerabilityArr(counter) = currParagraph            severityArr(counter) = tmpArray(1)            paragraphArr(counter) = objDoc.Paragraphs(pIndex - 1).Range.ListFormat.ListString ' for some weird reason I cant use currParagraph here            counter = counter + 1 ' adjusting index        End If    Next pIndexobjDoc.Tables.Add objDoc.Paragraphs(objDoc.Paragraphs.Count).Range, counter, 3, True, TrueSet objTable = objDoc.Tables(objDoc.Tables.Count) 'select last tableobjTable.Cell(1, 1).Range.Text = ColumnName1objTable.Cell(1, 2).Range.Text = ColumnName2objTable.Cell(1, 3).Range.Text = ColumnName3For RowIndex = 0 To counter    objTable.Cell(RowIndex, 1).Range.Text = paragraphArr(RowIndex)    objTable.Cell(RowIndex, 2).Range.Text = vulnerabilityArr(RowIndex)    objTable.Cell(RowIndex, 3).Range.Text = severityArr(RowIndex)    Next    RowIndexy = RowIndex + 1objTable.AutoFormat (25)End Sub
Extracting data from a Word document is too slow
performance;vba;vbscript;ms word
Maybe I'm deeply wrong, but please test:-- Almost all your variables are of type Variant, even the ones which could be Long, like counter. Define each variable with the correct type (it will be faster), in the form:   Option Explicit  Dim counter as Long  Dim ColumnName1 as String, ColumnName2 as String-- You dont really use pIndex. Try:Dim currParagraph As Paragraph               ' will be set in loopFor Each currParagraph In objDoc.Paragraphs  ' THIS LOOP IS SLOW-- I dont see why you make a copy of the paragraph only to 'remove junk character ?for some weird reason I cant use currParagraph here: I have always heard that this information is with this junk character stickAll will:'TODO : Add checks, exception handling, dynamic user options, probes, style checks, and fix slowness.Option ExplicitSub genTable()Dim objDoc'''''''''''''''''''''Modify these''''''''''''''''Dim ColumnName1 As String, ColumnName2 As String, ColumnName3 As String, magicString As StringColumnName1 = foo1ColumnName2 = foo2ColumnName3 = foo3magicString = ASD321:  ' we search for this stringSet objDoc = ActiveDocument ' Because we run inside of word as macro''''''''''''''''''''''''''''''''''''''''''''''''''Const MAX = 200 ' using fixed sized arrays, mod this if you'll have more than 200 entries.Dim vulnerabilityArr(MAX) As Paragraph ' initializing the arraysDim severityArr(MAX) As StringDim paragraphArr(MAX) As StringDim counter As Long ' will count the processed entries in thiscounter = 0Dim tmpArray() As String ' for splittingDim currParagraph As Paragraph, prevParagraph As Paragraph ' will be set in loopFor Each currParagraph In objDoc.Paragraphs  ' THIS LOOP IS SLOW                                                  ' currParagraph = Left(currParagraph, Len(currParagraph) - 1) 'remove junk character    If InStr(currParagraph, magicString) > 0 Then ' assuming this string is always present and the other two target data is near it        tmpArray = Split(currParagraph.Range.Text)             ' extract level        Set prevParagraph = currParagraph.Previous    ' assuming the previous paragraph is the vuln. name            'Storing the 3 extracted data        Set vulnerabilityArr(counter) = prevParagraph        severityArr(counter) = tmpArray(1)        paragraphArr(counter) = prevParagraph.Range.ListFormat.ListString ' for some weird reason I cant use currParagraph here        counter = counter + 1 ' adjusting index    End IfNext currParagraphDim objTable As TableSet objTable = objDoc.Tables.Add(objDoc.Paragraphs(objDoc.Paragraphs.Count).Range, counter + 1, 3, True, True)                                 'Set objTable = objDoc.Tables(objDoc.Tables.Count) 'select last tableobjTable.Cell(1, 1).Range.Text = ColumnName1objTable.Cell(1, 2).Range.Text = ColumnName2objTable.Cell(1, 3).Range.Text = ColumnName3Dim RowIndex As Long, RowIndexy As LongFor RowIndex = 0 To counter - 1    objTable.Cell(RowIndex + 2, 1).Range.Text = paragraphArr(RowIndex)    objTable.Cell(RowIndex + 2, 2).Range.Text = vulnerabilityArr(RowIndex).Range.Text    objTable.Cell(RowIndex + 2, 3).Range.Text = severityArr(RowIndex)NextRowIndexy = RowIndex + 1objTable.AutoFormat (25)End Sub