与元件的可变数目创建阵列变量

问题描述

我要定义一个数组变量有一个可变数目取决于从搜索返回的结果的m个元素。我得到一个错误恒前pression必需的关于

 暗淡cmodels（0至M）作为字符串
 

下面是我的完整code

 昏暗foundRange由于范围
昏暗rangeToSearch由于范围
设置rangeToSearch =选择
设置foundRange = rangeToSearch.Find（什么：=灯，后：= ActiveCell，
看着：= xlValues​​，注视：= _
xlPart，SearchOrder：= xlByRows，SearchDirection：= xlNext，MatchCase：=假_
，SearchFormat：= FALSE）第一次出现
    M = WorksheetFunction.CountIf（选择光）
 昏暗secondAddress作为字符串
如果（不foundRange是没有什么）然后
    昏暗的计数为整数：数= 0
    昏暗targetOc​​currence作为整数：targetOc​​currence = 2
    昏暗的发现作为布尔
    Z = 1
    昏暗的cmodels（0至M）作为字符串
    做，直到ž= M
    Z = z + 1
        foundRange.Activate
        设置foundRange = rangeToSearch.FindNext（foundRange）
        如果没有foundRange.Next是没有那么
        Z（米）= ActiveCell（偏移（0，2））
        万一
    循环
    万一
结束小组
 

解决方案

请参阅以下code注释：

 子redimVsRedim preserve（）    我一般声明数组我知道我将调整
    没有最初是一个固定大小..
    昏暗的cmodels（）作为字符串
    昏暗米作为整数
    M = 3    这将消灭阵列中的所有数据
    使用ReDim cmodels（0至M）作为字符串    你也可以，如果你想保存在变量的所有信息使用
    cmodels（2）=测试
    使用ReDim preserve cmodels（0至M）作为字符串    这仍然将保持测试
    Debug.Print随着REDIM preserve，该值是：＆放大器; cmodels（2）    只用REDIM
    使用ReDim cmodels（0至M）作为字符串
    Debug.Print只有REDIM，该值是：＆放大器; cmodels（2）结束小组
 

另外请注意，使用 REDIM preserve 频繁（如循环）可以是需要一些时间的操作。

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:

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

解决方案

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.

这篇关于与元件的可变数目创建阵列变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！