VBA output contents of Array to Word document

Question

so what I need to do is fill an array using an InputBox, then but that in an array, sort it alphabetically and then output it to the current word document. I have it almost most complete, the issue is it is only outputting the last word to the document. Im guessing my loop is wrong but I cannot find VBA documentation to do this to save my life. Thank you

Option Explicit

This is the main sub that declares the array

Sub Main()

Dim ListArr() As String

ListArr = Get_Input_List()

Call Bubble_Sort_Ascending(ListArr)

Call Output_List_To_Document(ListArr)

End Sub

Function to get input and fill array

Function Get_Input_List() As String()

Dim list As String

list = InputBox("Please enter words to sort separated with a comma and no spaces", "Words")
Get_Input_List = Split(list, ",")

End Function

Sorts the array alphabetically

Sub Bubble_Sort_Ascending(listNewArray() As String)

Dim SrtTemp As Variant
Dim inputWord As Variant
Dim i As Long
Dim j As Long

'Alphabetize Sheet Names in Array List
For i = LBound(listNewArray) To UBound(listNewArray)
     For j = i To UBound(listNewArray)
         If listNewArray(i) > listNewArray(j) Then
             SrtTemp = listNewArray(j)
             listNewArray(j) = listNewArray(i)
             listNewArray(i) = SrtTemp
         End If
     Next j
 Next i


End Sub

This is the problem, I cannot output the whole array to the word document. I have found plenty of documentation on how to do this into an excel spreadsheet but almost nothing for word.

Sub Output_List_To_Document(newListArray() As String)

Dim inputWord As Variant
Dim i As Long
Dim j As Long

For i = LBound(newListArray) To UBound(newListArray)
     For j = i To UBound(newListArray)
        For Each inputWord In newListArray
            ActiveDocument.Range = inputWord & vbCrLf
        Next
    Next j
Next i

End Sub

Output_List_To_Document has three nested loops. Is this intentional? Your inner For Each should be sufficient. Also, consider using the Join function, with vbCrLf as a delimiter to build a complete string, and then you can append the entire array in one go. — Zev Spitz

Comintern Comintern · Accepted Answer · 2016-09-25T18:16:38

You're over-writing ActiveDocument.Range each time through the loop. If you want to append to the end of it, you need to collapse the range to it's ending position:

Sub Output_List_To_Document(newListArray() As String)
    Dim inputWord As Variant
    Dim i As Long
    Dim j As Long

    Dim insertPos As Range
    Set insertPos = ActiveDocument.Range

    For i = LBound(newListArray) To UBound(newListArray)
         For j = i To UBound(newListArray)
            For Each inputWord In newListArray
                insertPos.Collapse wdCollapseEnd
                insertPos = inputWord & vbCrLf
            Next
        Next j
    Next i
End Sub

Note - It isn't clear why you're looping through the array with 3 nested loops. If you only need to write each word once, I suspect you're really looking for something more like this:

Sub Output_List_To_Document(newListArray() As String)
    Dim insertPos As Range
    Set insertPos = ActiveDocument.Range

    Dim inputWord As Variant
    For Each inputWord In newListArray
        insertPos.Collapse wdCollapseEnd 'Value 0, Can ignore writing it as well
        insertPos = inputWord & vbCrLf
    Next
End Sub

VBA output contents of Array to Word document

1 Answers