Array difference?

[Steph]

Hi. Was hoping someone could clarify something for me. What is the
difference between the following:

Dim arr1()
arr1 = Array("Sheet1", "Sheet2", "Sheet3")

Dim arr2 As Sheets
Set arr2 = Sheets(Array("Sheet1", "Sheet2", "Sheet3"))

I've been reading the message boards a lot lately, and have seen bopth
methods being used for creating arrays. I think one is set up as a
variable, but not sure. Anyway, they apparently differ in their use of
syntax, becasue I have tried meshing both styles into my code, and one style
works while the other does not.

Just a general clarification would be greatly appreciated, or even better a
link to a site that describes in detail the difference? Thanks a bunch!

 

[Bob Phillips]

The first is creating an array of strings, the second is creating a sheets
object using an array of strings to define the sheets. So the latter will be
an object of 3 sheets.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jim Cone]

Steph,

Maybe the following will be of some help...

From "Using Arrays" in VBA help...
You can declare an array to work with a set of values of the same data type.
An array is a single variable with many compartments to store values,
while a typical variable has only one storage compartment in which it can store
only one value.
Refer to the array as a whole when you want to refer to all the values it holds,
or you can refer to its individual elements.

My note: you cannot use an "Array" without specifying how many items it contains.
So Dim arr1() is of no use until you ReDim it...
ReDim arr1(1 to 10)

From "Sheets Collection Object" in VBA help...
A collection of all the sheets in the specified or active workbook.
The Sheets collection can contain Chart or Worksheet objects.
Use Sheets(array) to specify more than one sheet.
The following example moves the sheets named "Sheet4" and "Sheet5"
to the beginning of the workbook.
Sheets(Array("Sheet4", "Sheet5")).Move before:=Sheets(1)

Regards,
Jim Cone
San Francisco, CA