将范围转换为数组 [英] Convert range to an array

问题描述

我知道已经有很多与此主题相关的主题，但是我仍然找不到在这种情况下可行的解决方案.以下两个子选项始终使我出现下标超出范围"错误.

I know a lot of threads regarding this topic already exist, but I still can't find a solution that works in this scenario. The following two subs keep giving me a "subscript out of range" error.

Sub test1()
    Dim Arr() As Variant
    Arr= Range("A1:A10")

    MsgBox Arr(0)

End Sub

Sub test1()
    Dim Arr As Variant
    Arr= Range("A1:A10").Value

    MsgBox Arr(0)

End Sub

为了清楚起见，

推荐答案

已编辑，请参考以下注释.

将范围的值分配给Variant变量将导致该变量包含按行和列索引(按此顺序)的基于变量的一维2D数组，或者如果范围为1，则包含范围的实际值单元格范围.

Assigning a range's Value to a Variant variable will either result in the variable containing a one-based 2D array of Variants indexed by row and column (in this order), or containing the actual value of the range if it's a 1-cell range.

在您的特定情况下，这将起作用:

In your particular case, this would work:

Sub test1()
    Dim Arr As Variant
    Dim row As Long
    Dim col As Long

    row = 1
    col = 1

    Arr = Range("A1:A10").Value

    MsgBox Arr(row, col)
End Sub

在更通用的方法中，如果您的下游代码希望处理一个数组，但是您的范围有可能覆盖单个单元格，那么即使在这种情况下，您也可以按照以下方式强制使用数组:

In a more general approach, if your downstream code expects to deal with an array, but you range has the possibility to cover a single cell, you can force an array even in such a situation, along those lines:

Sub test2()
    Dim rng As Range
    Dim Arr As Variant
    Dim row As Integer
    Dim col As Integer

    row = 1
    col = 1

    Set rng = Range("A1:A1") '<== 1 cell only!

    Arr = rng.Value

    'Ensure we're dealing with an array even in this case.
    If Not IsArray(Arr) Then
        ReDim Arr(1 To 1, 1 To 1) As Variant
        Arr(1, 1) = rng.Value
    End If

    MsgBox Arr(row, col)
End Sub

这篇关于将范围转换为数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！