declare and initialize many variables

is there a way to do this with a vector? can't use array because i'm not sure of the size until loop ends, and dont want to then run another loop because that defeats my purpose of trying to increase efficiency.

oops, colVars.add "var" & i

Use an array but declare it for many elements then do a redim preserve to get rid of the extras...

ReDim var(1 to 100) as string

'Loop code
Do while x <= 50
    'Do whatever    
    x = x + 1
Loop

ReDim Preserve var(1 to x) as string

Thank you,

If I have a double, and date variable, do I do the same thing..?

That should work fine.  Create separate arrays for each type of variable.

sure

you can use a collection and store whatever type you want:
Dim col As New Collection

col.Add 1.01
col.Add Now
col.Add "test"

For i = 1 To col.Count
    MsgBox col(i)
Next

for collection.. col.add  "test" .. does that initilize the value col(3) to "test" or create an uninitialized col("test") variable?

initializes col(3) to "test"

you can also specify a key when adding to collection:

ie: col.add("hello world", "test")

then, col("test") should give you "hello world"

thank you,  let me try that and see if it works

It says 'expected :='

---

Dim values As Collection
values.Add("val", "SE")
 

remove parentheses:

values.Add "val", "SE"  
or
values.Add Item:="val", Key:="SE"

almost there -

i got a "complie error: named argument already specified"

--- for this code

values.Add Item:=Range("A" & cellNum & "").Value, Item:="SE" & varNum

i changed the second item to key..

ok, so what's the problem now?

I now get

"runtime error '91' Object variable or with block variable not set"

---code
    Dim cellNum, varNum As Integer
    cellNum = 2
    varNum = 1

values.Add Item:=Range("A" & cellNum).Value, key:="SE" & varNum

make sure you do:

set values = new Collection

Thank you, youve been more than helpful, I am going to award you the points.. if you wouldnt mind looking at this last thing..(i hope) it now gives me ...

"run time error '5': Invalid procedure call or argument"
--code

Range("C" & i).Value = values.Item("resourceCode" & varNum)

error from the values.item

what does
msgbox values.item("resourceCode" & varNum) show?

doesnt display gives the same invalid procedure call error..., if i have var = "resourceCode" & varNum, it displays correctly.... is there a reference i should add?

can i see the whole segment of code, including where you are adding items to the collection?

Very helpful and responsive