0

I am running into some issues trying to populate a multidimensional variant array. What I am trying to do is add a single value to row 1, column 1 in the array. However the following code does not work. 

Dim arr() As Variant
arr(1,1) = 3

Building on this, I am wishing to add more values to the array when certain conditions are met. eg 

 For c = 2 To 10001
        Sheets("Data").Select
        If Range("P" & c) = branch And Range("Q" & c) = dept And Range("R" & c) = subdept Then
            arr(UBound(arr) + 1, 1) = Range("A" & c)
        End If
    Next c

After the loop has finished and I have finished extracting the values I am after I wish to redim it as a variable length array. However, 

Redim arr()

also is not working. Also, I should point out that I have no real need for the array to be multidimensional, just variable in size. Saying that, I do not know of a way to create a 1 dimensional dynamic array in VBA.

To summarize

1) Why does arr(1,1) throw me an error?

2) Why does Redim arr() throw me an error? (I do not want to redim to a fixed size)

3) Is there a way to create a dynamic 1D array in VBA?

Note: I suppose I could declare a 1D 100 length array which I could then manipulate to work out the number of filled values in it and redim it as (0 to 100), but first wish to explore if there are any answers to my above questions.

Many thanks.

UPDATE: (my second attempt: still fails on arr(arrLen, 1) = Range("A" & c))

 arrLen = 1

    For c = 2 To 10001
        Sheets("Data").Select
        If Range("P" & c) = branch And Range("Q" & c) = dept And Range("R" & c) = subdept Then
            ReDim Preserve arr(arrLen To 1)
            arr(arrLen, 1) = Range("A" & c)
            arrLen = arrLen + 1
        End If
    Next c
Improve this question
2
  • VBA arrays are not "dynamic" - they will not grow as you add new elements: you always need to explitly resize them using ReDim [Preserve], and you can only change the last dimension in a multi-dimension array when using Preserve. patorjk.com/programming/tutorials/vbarrays.htm Commented Apr 26, 2014 at 2:02
  • Thanks for clearing that up Tim. Was hoping to avoid putting Redim Perserve in my loop as it slows everything down immensely (this loop in turn gets looped about 200 times). Oh well, time for a break anyway... Commented Apr 26, 2014 at 2:14

3 Answers 3

Reset to default
1

Working code below.

Option Base 1

Dim arr() as string
    arrLen = 1

    For c = 2 To 10001
        Sheets("Data").Select
        If Range("P" & c) = branch And Range("Q" & c) = dept And Range("R" & c) = subdept Then
            ReDim Preserve arr(1 To arrLen)
            arr(arrLen) = Range("A" & c)
            arrLen = arrLen + 1
        End If
    Next c
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I thought you wanted to avoid putting your ReDim inside the loop? You can put it outside of the loop, as I had done in the previously-accepted answer... Curious why this is superior?
1

Two reasons.

  • Your array has only 1 dimension, not two
  • Your logic should be 1 To arrLen not arrLen to 1

recut

ReDim Preserve arr(1 To arrLen)
arr(arrLen) = Range("A" & c)
arrLen = arrLen + 1
Improve this answer

2 Comments

Thanks. Initially I had declared my array as a variant though. Doesn't that make it multidimensional? (forgive me if I'm wrong)
@Chris if you Dim arr() As Variant it could be single- or multi-dimensional, but this doesn't happen until the assignment statement. So if you do arr = Array("1", "2", "3"), it will be single dimensional, but if you do arr = Range("A1:D6").Value it will be multi-dimensional. But it's not either, until it's been assigned.
1

Because you are dealing with a fixed range (cells 2 to 10001) -- or even a dynamic range, really -- you can avoid putting ReDim inside your loop.

ReDim arr(1 to 10001)
For c = 2 To 10001
    Sheets("Data").Select
    If Range("P" & c) = branch And Range("Q" & c) = dept And Range("R" & c) = subdept Then
        arr(arrLen) = Range("A" & c)
        arrLen = arrLen + 1
    End If
Next c
ReDim Preserve arr(1 to arrLen)

That should speed up your performance. So first you are making the array as big as it could ever need to be, given the known iteration of your For loop. Then you process your data, putting it in to the array, and after the loop terminates, you ReDim it back down to get rid of the empty elements.

Improve this answer

4 Comments

Cool. Will proceed as advised.
One of the other two should probably be the accepted answer, since they first solved the original problem. Mine here just attempts to optimize it to avoid the unnecessary ReDim.
Though, to calculate the value of the used elements within, won't Ubound(arr) give me 10001 instead of say 35? How to then get the sidestep that issue?
Sorry, ignore previous comment. Missed your last line of code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.