excel - 从方阵中选择列向量的函数

Question

嗨，我是 VBA 新手，并创建了一些基本功能，但以下证明太高级了。

我想创建一个将方阵转换为其列之一的函数。

      colA  colB   colC
[1,]   1      2      3
[2,]   4      5      6
[3,]   7      8      9

所以 getcol(A1:C3,2) 会输出:

2
5
8

我不确定是否可以从函数输出范围。

它用于数组 Match 函数中，因此:

{Index(G1:G3,Match(2&2,getcol(A1:C3)&getcol(D1:F3),0))}

我尝试过的代码是:

Function getcol(a As Range, b As Integer) As Range
getcol = Range(a).EntireColumn(b)
End Function

Answer 1

这是你正在尝试的吗？我没有测试过索引/匹配。以下代码仅返回相关范围。你如何使用它取决于你。

Option Explicit

Sub Sample()
    Dim rng As Range
    Dim truncRange As Range

    Set rng = Range("A1:C3")

    Set truncRange = getcol(rng, 2)

    If Not truncRange Is Nothing Then
        MsgBox truncRange.Address
    Else
        MsgBox "One of the parameters of getcol() is incorrect"
    End If
End Sub

Function getcol(a As Range, b As Integer) As Range
    Dim rowCount As Long

    '~~> Get the total rows in that range
    rowCount = a.Rows.Count

    '~~> Extract the range from relevant position
    Set getcol = Range(a.Cells(1, b), a.Cells(rowCount, b))
End Function