How do I set a dynamic array size in execution time?

Question

I'm removing duplicated strings from an array and I'm pretty sure I need to use the Redim statement to dynamically change the size of the array, but I can't get it to work right.

Here's what I tried:

Dim temp() as String
Dim vetOrg as Variant

vetOrg = Array(contRows)
ReDim vetOrg(1 To contRows)


'populates the array
For i = 1 To contRows
    vetOrg(i) = wsDeals.Cells(i + 1, 2).Value
Next i


 j = 1
    For i = 1 To contRows
        ReDim temp(1 To j)
        If vetOrg(i) <> vetOrg(i + 1) Then
            temp(j = j + 1) = vetOrg(i)
        End If
        temp(j = j + 1) = vetOrg(contRows - 1)
    Next i

if you want to keep the values in the array, you need to use redim preserve ... otherwise please detail what you think is not working. also temp(j = j + 1) wont work... make j = j +1 before that and then do temp(j). — FAB
– FAB, Commented May 24, 2019 at 20:32
maybe interest codereview.stackexchange.com/questions/215646/… — learnAsWeGo
– learnAsWeGo, Commented May 24, 2019 at 21:15
You want to use a Dictionary instead; dictionary keys are inherently unique, and I'm pretty sure it's a O(1) hash lookup to determine whether a key exists - compared to O(n) for every element you lookup in an array. You'll want to reference the Microsoft Scripting Runtime library. — Mathieu Guindon
– Mathieu Guindon, Commented May 24, 2019 at 21:18

Mathieu Guindon · Accepted Answer · 2019-05-24 21:21:05Z

2

With a Scripting.Dictionary you don't need to worry about resizing or searching for duplicate values; dictionary keys are inherently unique:

Dim vetOrg As Scripting.Dictionary
Set vetOrg = New Scripting.Dictionary

For i = 1 To contRows
    vetOrg(wsDeals.Cells(i + 1, 2).Value) = i ' value is bogus, it's the key we want.
Next i

'done. vetOrg.Keys has the unique values.

answered May 24, 2019 at 21:21

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

2 Comments

Daniel Over a year ago

Hey thanks, but how do I debug.print this array? Since now I don't know its size. I tried LBound(vetOrg) - UBound(vetOrg) but it do not seems to work.

Mathieu Guindon Over a year ago

@Daniel you can't print an array, you need to iterate it and print its elements.

Collectives™ on Stack Overflow

How do I set a dynamic array size in execution time?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related