arrays - 如何将剪贴板放入二维数组(excel vba)

Question

我对 Excel VBA 非常陌生，我想将剪贴板中的文本放入二维数组中，第一个分隔符为 vbNewLine，第二个分隔符为空格。如何创建大小未知的二维数组？我可以拆分一个数组，就像下面我失败的地方吗？

Sub CommandButton1_Click()
    Dim DataObj As MsForms.DataObject
    Set DataObj = New MsForms.DataObject
    Dim strArray() As String

    On Error GoTo ERRROR

    '~~> Get data from the clipboard.
    DataObj.GetFromClipboard

    '~~> Get clipboard contents
    myString = DataObj.GetText(1)

    '~~> Split into string, delimiter vbNewLine
    strArray = Split(myString, vbNewLine)

这是我失败的地方

    '~~> Split each strArray and store in strArray2, delimiter " ".
    Dim strArray2() As String
    For ii = LBound(strArray2) To UBound(strArray2)
        strArray2(ii) = Split(strArray(ii))
    Next ii

Exit Sub
ERRROR:
    If Err <> 0 Then    
    'Handel error
End If
End Sub

Answer 1

你做错了。

这是你正在尝试的吗？ ( 未测试 )

Sub CommandButton1_Click()
    Dim DataObj As MsForms.DataObject
    Dim strArray, strArray2
    Dim i As Long, j As Long
    Dim myString As String

    On Error GoTo ERRROR

    Set DataObj = New MsForms.DataObject

    '~~> Get data from the clipboard.
    DataObj.GetFromClipboard

    '~~> Get clipboard contents
    myString = DataObj.GetText(1)

    '~~> Split myString, delimiter vbNewLine
    strArray = Split(myString, vbNewLine)

    '~~> Split each strArray and store in strArray2, delimiter " ".
    For i = LBound(strArray) To UBound(strArray)
        strArray2 = Split(strArray(i))

        For j = LBound(strArray2) To UBound(strArray2)
            Debug.Print strArray2(j)
        Next j
    Next i

    Exit Sub
ERRROR:
    If Err <> 0 Then Debug.Print Err.Description
End Sub