如何在Excel VBA中获取数组中元素的子集? [英] How do I get a subset of elements in an array in excel vba?

问题描述

如何在excel vba中获取数组中的第3到第6个元素?类似于以下内容.

How do I get the 3rd through 6th elements in an array in excel vba? Something like the following.

    Dim x(9) as variant, y(3) as variant

    y(0:3) = x(2:5)

推荐答案

在VBA中，与python不同，例如，我们不能直接子集"数组.

In VBA, unlike in python for example, we cannot directly "subset" an array.

我们只能通过将i和j指定在特定范围内来循环关注的索引. i位于第1行和第2行之间，j位于第2列和第3列之间.当然，我们也可以按位置直接索引到数组中，例如arr(1). i只是代表行索引的变量，j列索引.

We can only loop for the indices of interest by specifying i and j to be between certain bounds e.g. i to be between rows 1 and 2, j to be between columns 2 and 3. Of course, we can also directly index into the array by position e.g. arr(1). i just a variable representing the row index, j the column index.

或者，我们可以使用Index切片"出特定的行或列；我猜您可能称其为子集，但从您的语法来看，我认为您以python的方式进行思考.

Or, we can use Index to "slice" out a particular row or column; which I guess you might call subsetting but from your syntax I think you are thinking in the manner of python for example.

Application.WorksheetFunction.Index(array,n, 0)将把n行切出数组

Application.WorksheetFunction.Index(array, 0, n)将列n切出数组

Option Explicit

Public Sub ArrayStuff()
    Dim arr(0 To 5) As Long, i As Long
    For i = 0 To 5
        arr(i) = i
    Next

    'Loop only required indices
    For i = 2 To 3
        Debug.Print arr(i)
    Next

    'Slice via Application.WorksheetFunction.Index
    Dim arr2(0 To 2, 0 To 2) As Long, j As Long, counter As Long

    For i = LBound(arr2, 1) To UBound(arr2, 1) '<==  here can specify particular rows
        For j = LBound(arr2, 2) To UBound(arr2, 2) '<== here can specify particular columns
            counter = counter + 1
            arr2(i, j) = counter
        Next
    Next

    MsgBox Join(Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Index(arr2, 0, 1)), ",") 'slice a column out
    MsgBox Join(Application.WorksheetFunction.Index(arr2, 1, 0), ",") 'slice a row out
    MsgBox Join(Application.WorksheetFunction.Index(arr2, 2, 0), ",") 'slice a row out
End Sub

这篇关于如何在Excel VBA中获取数组中元素的子集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！