创建并显示Msgbox，并将数组内容和字长输出到工作表

Question

我正在尝试创建一个Msgbox，它显示工作表上一列中的所有单词。然后创建另一个消息框，其中包含单词数以及超过5个字符的单词的计数。我不太确定如何用下面的变量创建Msgbox，所以我希望能得到一些帮助！

到目前为止，我编写了一个使用GetOpenFilename从文本文件导入单行文本的sub。然后，我使用split使用分隔符来解析文件。

文本文件的内容如下：

扎格，金鹰，老鹰，孔雀，灰狗，金格里芬，雄狮，火枪手

下面是我到目前为止的代码：

Sub ImportTextFile()

Dim fileName As Variant     ' array will hold the file name or names to import

Dim i As Integer            ' counter for loops
Dim nLargeWords As Integer  ' counter for words longer than 5 characters

Dim dataLine As String      ' string to hold a single line of text from file
Dim lineCount As Integer

Dim arrWords() As String    ' array that will hold the contents of the text file
Dim msg As String           ' string for building the first message box

Const delim = ","           ' Added a constant for the delimiter in Split function

With Range("Output_lbl")
         Range(.Offset(1, 1), .Offset(100, 2)).ClearContents
End With

'============Navigate to file location and get filename of text file


fileName = Application.GetOpenFilename(, , "Select a text file", , False)       ' Used GetOpenFilename method to navigate to the file and obtain the file name.


'============Import file into temp string variable then parse into an array


Open fileName For Input As #1       ' To open the text file.
    i = 0
    With Range("Output_lbl")
        Do Until EOF(1)

            Line Input #1, dataLine             ' Setting the first line of the text file to a string variable.
            arrWords() = Split(dataLine, delim)         ' Using Split to parse the file. The delimiter was determined to be "," which was declared a constant above.
            For i = LBound(arrWords()) To UBound(arrWords())        ' Parse into an array.
                .Offset(i, 0) = Trim(arrWords(i))
            Next i
            i = i + 1
        Loop
    End With

Close #1                            ' To close the text file.


'============Create and display the message boxes and output array contents and word lengths to the output sheet.


End Sub

一个消息框的格式应如下所示：

文件中的单词。

Zags
Golden Eagles
Hawks
Peacocks
Greyhounds
Golden Griffins
Dons
Musketeers

另一个消息框的格式应如下所示：

Microsoft Excel

Total Words:    8
Large Words:    5

Answer 1

未使用数组的示例代码。您只需修改循环，以查看数组中的变量索引，而不是列循环中的变量行。然后，下面使用的逻辑应该可以很容易地扩展到您的问题。

Option Explicit

Sub Example()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim LR As Long, i As Long
Dim MyString As String, MyCounter As Long, Arr

LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

For i = 1 To LR
    MyString = MyString & ws.Range("A" & i) & ", "

    If Len(ws.Range("A" & i)) > 5 Then
        MyCounter = MyCounter + 1
    End If
Next i

MyString = Left(MyString, Len(MyString) - 2) 'Remove last ", "
Arr = Split(MyString, ", ")

MsgBox "Total Words: " & UBound(Arr) + 1 & vbNewLine & "Large Words: " & MyCounter


End Sub

​

​