Creating an array variable with a variable number of elements

Question

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

enderland · Accepted Answer · 2013-08-23 18:34:30Z

3

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.

answered Aug 23, 2013 at 18:34

enderland

14.2k18 gold badges105 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PocketLoan Over a year ago

This has become a popular question; kudos enderland for a great response.

tigeravatar · Accepted Answer · 2013-08-23 18:32:11Z

1

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

answered Aug 23, 2013 at 18:32

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

3 Comments

Marty McVry Over a year ago

Dim cmodels as String()

tigeravatar Over a year ago

Dim cmodels as String() results in an error, and Dim cmodels(m) As String gives the same problem that is being asked about...

Marty McVry Over a year ago

Right, notice it now, it's VBA... Different from VB.

Collectives™ on Stack Overflow

Creating an array variable with a variable number of elements

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related