vba - 索引到大的不连续范围

Question

假设我定义了一个很大的不连续范围，也许是Range("B:B,E:E,F:F")。我将如何对范围进行索引以将其视为连续的。

例如我想做一些类似的事情

Set myRange = Range("B:B,E:E,F:F")
v = myRange.ContiguousIndex(5, 3).Value 'retrieves the value in cell F5 (row 5 col 3)

我知道的每个方法都会基于范围中的第一个单元格(“B1”)进行偏移，并且很乐意超出该范围的边界，溢出到工作簿的其余内容中。这意味着尝试访问第 5 行第 3 列将得到 D5，就好像列 C 和 D 位于我尝试索引的范围内一样。

我尝试过 Range.Cells、Range.Offset 和 Range.Range，但似乎都表现出了同样的溢出效应。

我想到的另一种方法是将值分配给变量数组并从那里手动索引，但这很快就会变得复杂，因为像这样的简单片段

Dim v() As Variant
v = myRange

只会将不连续范围的第一个区域分配到数组中，留下一个 (20^20-1)x1 数组并完全忽略 myRange 的其余部分。因此，如果我循环遍历所有区域并将它们单独分配到我不断重新分配的数组中，则可能可以将整个 myRange 放入数组中，但这远非易事，而且我最终得到的数组使用的内存远多于我想要的内存(除非我投入更多开销来修剪它，或者我任意选择较少数量的行进行复制)。

到那时，手动循环区域并自己进行索引会更加高效和简单，而无需将内容放入数组中。这最后的方法就是我目前正在做的。

问题

是否有任何现有的方法或技巧可以用来将 myRange 视为连续的，就像我所描述的那样，并以忽略不连续性的方式索引到 myRange 中？

TL;DR 如果我有

Set myRange = Range("B:B,E:E,F:F")
v = myRange.ContiguousIndex(5, 3).Value

我希望某些方法 ContigouslyIndex 返回 Range("F5").Value，而不必执行手动检查 Range.Areas 和处理所有索引的所有工作。

<小时/>

奖励问题

假设 myRange 是 Range("E:E,B:B,F:F") (注意不同的列顺序)。有没有一种好的方法可以将 E 视为第一列，B 视为第二列，F 视为第三列，这样

Set myRange = Range("E:E,B:B,F:F")
v = myRange.ContiguousIndex(5, 2).Value 'retrieves the value in cell B5

返回B5的值？这是我正在使用的方法的一个属性，我希望继续拥有它。

再说一次，我的函数可以工作，但我猜测 Excel 的所有怪癖中都隐藏着某种奇妙的方法或技巧，效果会更好。

Answer 1

我将发布我自己的解决方案，以防其他人遇到类似的问题。这是唯一对我有用的答案，因为其他答案和评论依赖于了解该范围内的区域(例如，依赖于每个区域是一个完整的单列，我无法保证这一点，因为我的范围是用户-输入并且可以跨越多列或有限数量的行)。

' Indexes into a discontiguous area as expected, ignoring cells not in Range r
' and treating areas as concatenated (and top-aligned) in the order they are specified
Public Function ContiguousIndex(r As Range, row As Long, col As Long)
    Dim area As Range

    For Each area In r.Areas
        If col <= area.Columns.count Then
            If row <= area.Rows.count Then
                ContiguousIndex = area.Cells(row, col)
                Exit Function
            Else
                Err.Raise vbObjectError + 9, , "Row Index out of bounds"
            End If
        Else
            col = col - area.Columns.count
        End If
    Next

    ' col argument > sum of all cols in all areas
    Err.Raise vbObjectError + 9, , "Col Index out of bounds"
End Function

<小时/>

值得一提的是我在评论中提到的一些内容，但可能会出乎意料:此代码将使所有区域顶部对齐，以便区域 1 中的第一行与区域 2 中的第一行具有相同的索引。 .. 等等。这会导致在调用诸如 ContigouslyIndex(Range("A1:B7,A8:B10"), 9, 2) 之类的内容时出现异常。虽然看起来很明显应该返回 B9，但事实并非如此 - 它实际上会尝试访问 A1:B7 的第 9 行、第 2 列，从而导致错误。这是因为，尽管两个不连续的范围在实际工作表上清晰地从上到下排列，但仍被视为并排排列。因此，B9 可以通过命令 ContigouslyIndex(Range("A1:B7,A8:B10"), 2, 4) 访问(不直观)。这种行为是我所需要的，但可能不是您所期望的。

为了规避这个问题，您可以使用内置的 Application.Union或Application.Intersect方法。如果可能的话，这些会自动折叠连续区域。以下所有工作:

' Every statement will print "A1:B10" - the areas are merged

' Union of separate areas
Debug.Print Union(Range("A1:B7"), Range("A8:B10")).Address

' Union of range with a known subrange
Debug.Print Union(Range("A1:B7,A8:B10"), Range("A1:B7,A8:B10").Cells(1, 1)).Address

' Union of range with itself
Debug.Print Union(Range("A1:B7,A8:B10"), Range("A1:B7,A8:B10")).Address

' Intersect of range with itself
Debug.Print Intersect(Range("A1:B7,A8:B10"), Range("A1:B7,A8:B10")).Address

如果这是索引时所需的行为，则在调用 ContigouslyIndex 之前执行列出的合并之一。请注意，如果在联合操作中未合并区域，则它们的相对不连续索引将保持不变。例如

' Yields "A:A,F:F,C:D" not "A:A,C:D,F:F" as you might desire
Debug.Print Union(Range("A:A,F:F,C:C,D:D"), Range("A:A,F:F,C:C,D:D")).Address