使用变量数量的元素创建数组变量

I want to define an array variable to have a variable number of elements depending on the m number of results returned from a search. I get an error "Constant Expression Required" on:

我想定义一个数组变量，根据搜索返回的m个结果来定义一个变量的元素数量。我在:

Dim cmodels(0 To m) As String

Here is my complete code

这是我的完整代码

Dim foundRange As Range
Dim rangeToSearch As Range
Set rangeToSearch = Selection
Set foundRange = rangeToSearch.Find(What:="Lights", After:=ActiveCell,            
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False) 'First Occurrence
    m = WorksheetFunction.CountIf(Selection, "Lights")
 Dim secondAddress As String
If (Not foundRange Is Nothing) Then
    Dim count As Integer: count = 0
    Dim targetOccurrence As Integer: targetOccurrence = 2
    Dim found As Boolean
    z = 1
    Dim cmodels(0 To m) As String
    Do Until z = m
    z = z + 1
        foundRange.Activate
        Set foundRange = rangeToSearch.FindNext(foundRange)
        If Not foundRange.Next Is Nothing Then
        z(m) = ActiveCell(Offset(0, 2))
        End If
    Loop
    End If
End Sub

2 个解决方案

#1

See the following code comments:

参见以下代码注释:

Sub redimVsRedimPreserve()

    'I generally declare arrays I know I will resize
    'without a fixed size initially..
    Dim cmodels() As String
    Dim m As Integer
    m = 3

    'this will wipe out all data in the array
    ReDim cmodels(0 To m) As String

    'you can also use this if you want to save all information in the variable
    cmodels(2) = "test"
    ReDim Preserve cmodels(0 To m) As String

    'this will still keep "test"
    Debug.Print "With redim preserve, the value is: " & cmodels(2)

    'using just 'redim'
    ReDim cmodels(0 To m) As String
    Debug.Print "with just redim, the value is: " & cmodels(2)



End Sub

Also note that using Redim Preserve frequently (such as a loop) can be an operation which takes some time.

还要注意，经常使用重拨保存(例如循环)可能是一个需要花费一些时间的操作。

#2

Dim cmodels() As String
'...
ReDim cmodels(0 to m)

#1