VBA：区域引用数组 [英] VBA: Array of Range References

问题描述

在VBA，我想创建区域引用数组。这是我当前的尝试：

In VBA, I am trying to create an array of Range References. Here's my current attempt:

Dim columnName()  As String
Dim colIndex()    As Long
Dim colRange()    As Range

colCount = 10
ReDim columnName(colCount)
ReDim colIndex(colCount)
ReDim colRange(1 To colCount)

columnName(ID) = "ID"
'etc

For i = 1 To UBound(columnName)
    colIndex(i) = WS.Range("A1", "ZZ1").Find(columnName(i), LookIn:=xlValues, MatchCase:=False).column
    colRange(i) = WS.Range(Cells(2, colIndex(i)), Cells(LastRowIndex, colIndex(i)))
    If 1 = 1 Then 'debugging
        Application.ScreenUpdating = True
        Debug.Print colRange(i).Value
        Debug.Print colRange(i).Address
        colRange(i).Select
        Application.ScreenUpdating = False
    End If

当我尝试多个引用存储在数组中，我得到的是这样的：

When I try to store multiple references in an array, I get something like this:

i = 1
colIndex(i) = 8
Cells(2, colIndex(i)) = 123
Cells(LastRowIndex, colIndex(i)) =789
colRange(i) = Nothing

我曾试图使colRange一个变种，但似乎没有任何工作。无解我通过谷歌或计算器发现似乎解决这​​个问题。

I have tried making colRange a variant, but nothing seems to work. No solutions I found via google or StackOverflow seemed to address this.

推荐答案

另外我上面的评论，下面是一个例子。

Further to my comment above, here is an example

Sub Sample()
    Dim columnName() As String
    Dim rng As Range

    colCount = 10
    ReDim columnName(colCount)

    ID = 1

    columnName(ID) = "A"

    'MsgBox Cells(1, columnName(1)).Address
    Cells(1, columnName(1)).Value = "Blah Blah"

    Set rng = Cells(1, columnName(1))

    With rng
        MsgBox .Address
        .Value = "Something"
        '~~> Do whatever you want to do with that range here
    End With
End Sub

避免使用。选择的。就像我上面做的范围直接执行的操作。 <一href=\"http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179\">INTERESTING阅读

Avoid the use of .Select. Directly perform the action on the range like I have done above. INTERESTING READ

这篇关于VBA：区域引用数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！