DIm Variables

[Todd Huttenstine]

Hey guys I have 70 variables. Each one of these variables
is equal to a DataField in a PivotTable.

I am creating a variable for each field so I can
programmatically control them from a listbox.

Now because I have 70 fields I need 70 variables. What
should I set the variable to and how should I do it with
the least amount of code. Many times I have seen the
following for example:

Dim ExmplVariable as Object, ExmplVariable2, ExmplVariable3

Can I do this all on one line and do I have to use "as
object" for each variable or can I do it only once?

Thanks
Todd Huttenstine

 

[Bob Phillips]

Todd,

No you cannot.

In the example you give, all except Exmpl1Variable are type Variant. Each
variable has to be explicitly typed.

--

HTH

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

 

[Stephan Kassanke]

Hey guys I have 70 variables. Each one of these variables
is equal to a DataField in a PivotTable.

I am creating a variable for each field so I can
programmatically control them from a listbox.

Now because I have 70 fields I need 70 variables. What
should I set the variable to and how should I do it with
the least amount of code. Many times I have seen the
following for example:

Dim ExmplVariable as Object, ExmplVariable2, ExmplVariable3

Can I do this all on one line and do I have to use "as
object" for each variable or can I do it only once?

Thanks
Todd Huttenstine

Hi Todd,

no, you can't declare all variables in one line in the way indicated above.

Dim ExmplVariable as Object, ExmplVariable2, ExmplVariable3

is equivalent to

Dim ExmplVariable as Object
Dim ExmplVariable2 as Variant
Dim ExmplVariable3 as Variant

which is obviously not what you want to do. May I suggest that using an
array is probably the best way to proceed here.

Dim myPFields(1 To 70) As Object

allows you to dim an array named myPFields which can be accessed via
myPFields(index)= some_value, where index can take values from 1 to 70.

cheers,
Stephan

 

[Frank Kabel]

Hi Todd
as Bob already explained: Only the first variable is
defined as object.
If you need many variables you may consider using an
array. something like
Dim avar(70) as integer

 

[Jake Marx]

Hi Todd,

As Bob notes, you must use As Object (or As PivotField) for each variable.
An array of objects would work, as others have noted.

Alternatively, you could use a collection to store your data:

Dim colDF As Collection
Dim pf As PivotField

Set colDF = New Collection

For Each pf In Sheet1.PivotTables(1).PivotFields
colDF.Add pf
Next pf


'/ get count of items in collection
Debug.Print colDF.Count

'/ show first item in collection
Debug.Print colDF(1).Name & ": " & colDF(1).DataType

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]