Solved: can you get vba to declare variables based on an argument passed

[philfer]

Hello,

If I am writing a function where the user can pass a range that may have 1 cell or 5 cells or 10 cells is it possible to declare arrays in vba based on how many cells are passed in the range argument.

So, the user will select 5 cells. My code will count the number of cells in the ParamArray argument and then declare an array for each one. Another time there may be 10 items in the ParamArray argument another time there may be 100.

The value in each cell passed in the range will be used to find each instance of it in the workbook. The address will be put into the array and then the array used to populate a listbox with all the addresses.

I could make life easier by :-

1) Requiring a search for only 1 item at a time
2) Limiting the search to only five items (so I can declare the arrays in the function)

But I was just wondering if anyone could think of a solution as the thinking/engineering and construction of the solution could probably be used in other contexts

Thanks everyone
Phil

[Bob Phillips]

Use an array of arrays

[vba]

Dim myArray As Variant
Dim tmp As Variant
Dim i As Long

ReDim myArray(LBound(rng) To UBound(rng))
For i = LBound(rng) To UBound(rng)

tmp = rng(i)
myArray(i) = tmp
Next i
[/vba]