I wish to dim an array like this:
Dim DataArray(4,1) as ????
where Dimension 1 is String
Dimension 2 is Integer
DImension 3 is String
and 4 is Long.

How do I declare something like that? What I usually do is just dimension
it as variant and then I am able to use all the types. But I'd like to force
a type on a dimension so I don't get "2" instead of 2 stored in an array, for
example.

Sometimes there might be 10 or 15 dimension I wish to declare differently.

Thanks.

I'd use "As Variant". But then I'd have to make sure that each element is the
data type that I want--maybe using cLng, cdouble, even checking isnumeric
first...



Mike H. wrote:

I wish to dim an array like this:
Dim DataArray(4,1) as ????
where Dimension 1 is String
Dimension 2 is Integer
DImension 3 is String
and 4 is Long.

How do I declare something like that? What I usually do is just dimension
it as variant and then I am able to use all the types. But I'd like to force
a type on a dimension so I don't get "2" instead of 2 stored in an array, for
example.

Sometimes there might be 10 or 15 dimension I wish to declare differently.

Thanks.

--

Dave Peterson

Mike
It seems like your data may be more suited for storage in a Class than
an array. A class module will give you the flexibility to have
different variable types on different fields and keep most of the
array functionality. It might be a little slower if you have a huge
number of records, but, classes are pretty fast and very powerful.
Ken


On Dec 23, 9:14*am, Mike H. wrote:
I wish to dim an array like this:
Dim DataArray(4,1) as ????
where Dimension 1 is String
Dimension 2 is Integer
DImension 3 is String
and 4 is Long.

How do I declare something like that? *What I usually do is just dimension
it as variant and then I am able to use all the types. But I'd like to force
a type on a dimension so I don't get "2" instead of 2 stored in an array, for
example. *

Sometimes there might be 10 or 15 dimension I wish to declare differently..

Thanks.

I think I have a solution for what you want to do, but first a "lesson" on
terminology as the terms you used in your question confused me as to what
you actually want. The "dimension" of an array is how many numbers there are
in the comma separated list between the parentheses. For example...

Dim DataArray(4, 1) As ???

declares a two-dimensional array whereas...

Dim DataArray(4, 1, 7, 2) As ???

declares a four-dimensional array; and so on. When using the array in code,
each of the numbers in the parentheses are called the Index for that
dimension (and they must be numbers). Specifying an index value for each
dimension references a single element in the array. So, the reason I was
confused by your question is that there is no order to these elements in an
array... there is no way to have a "Dimension 1" be an anything... the first
dimension in an array is just the index value specified in the first number
position in the array's "argument" list, the second dimension is just the
index value specified in the second number position in the array's
"argument" list and so on through whatever number of dimensions there are.

Now, if I understand where your question is driving at at-all, I think you
may want an array of Type objects instead of a just a simple array. The
syntax in use will be a little different than in a normal array, but I think
it provides the functionality you ultimately want. I'm going to give you a
made up example to show how to declare a Type object and then show you how
to use it (but you may want to lookup "Type Statement" in the VB help files
for more information on it).

This establishes a Type object that will be used to identify employees in a
company ...

Type EmployeeRecords
ID As Integer
Name As String
Address As String
Phone As Long
StartDate As Date
End Type

The Type Statement starts with the Type keyword and it is followed by the
name you want to give to the structure that follows below it (here, I have
chosen to call it EmployeeRecords) and ends with the End Type statement.
Between those two statements are placed any number of "members" of the
Type... these are declared exactly like Dim statements, but without the Dim
keyword in front of them. The above Type..End Type block is placed in the
(General)(Declarations) section of whatever module you place it in... it is
never placed inside any of your Subs or Functions themselves. In order to
use a Type, you have to declare a variable of for it. You would do that like
this (where I have chosen to name this variable Employee)...

Dim Employee As EmployeeRecords

or like this if you want to create a fixed array of them (10 array elements
in this example)...

Dim Employee(1 To 10) As EmployeeRecords

or you could make a dynamic array of them like this...

Dim Employee() As EmployeeRecords

and provide the number of dimensions via a ReDim statement within your code
when you have determined how may elements of your Employee array you will
need.

Okay, that takes care of creating the Type object. To use it, you refer to
the members of the Type using dot notation. For example...

Employee.ID = 123
Employee.Name = "John Jones"
etc.

If you Dim'med Employee as an array, then you would need to provide an index
value for the array...

Employee(1).ID = 123
Employee(1).Name = "Jone Jones"
etc.
Employee(2).ID = 456
Employee(2).Name = "Jane Doe"
etc.

Of course, you could iterate through the array via a For..Next loop (just
like any other array) if needed.

Okay, as I said, I **think** this kind of structure may be what you were
looking for in your original question.

--
Rick (MVP - Excel)


"Mike H." wrote in message
...
I wish to dim an array like this:
Dim DataArray(4,1) as ????
where Dimension 1 is String
Dimension 2 is Integer
DImension 3 is String
and 4 is Long.

How do I declare something like that? What I usually do is just dimension
it as variant and then I am able to use all the types. But I'd like to
force
a type on a dimension so I don't get "2" instead of 2 stored in an array,
for
example.

Sometimes there might be 10 or 15 dimension I wish to declare differently.

Thanks.

AFAIK,
You can only declare an array as ONE type.
ie: MyArray(2,2) as Integer is OK while MyArray(2 as Integer ,2 as
String) is bad syntax.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Rick Rothstein" wrote:

I think I have a solution for what you want to do, but first a "lesson" on
terminology as the terms you used in your question confused me as to what
you actually want. The "dimension" of an array is how many numbers there are
in the comma separated list between the parentheses. For example...

Dim DataArray(4, 1) As ???

declares a two-dimensional array whereas...

Dim DataArray(4, 1, 7, 2) As ???

declares a four-dimensional array; and so on. When using the array in code,
each of the numbers in the parentheses are called the Index for that
dimension (and they must be numbers). Specifying an index value for each
dimension references a single element in the array. So, the reason I was
confused by your question is that there is no order to these elements in an
array... there is no way to have a "Dimension 1" be an anything... the first
dimension in an array is just the index value specified in the first number
position in the array's "argument" list, the second dimension is just the
index value specified in the second number position in the array's
"argument" list and so on through whatever number of dimensions there are.

Now, if I understand where your question is driving at at-all, I think you
may want an array of Type objects instead of a just a simple array. The
syntax in use will be a little different than in a normal array, but I think
it provides the functionality you ultimately want. I'm going to give you a
made up example to show how to declare a Type object and then show you how
to use it (but you may want to lookup "Type Statement" in the VB help files
for more information on it).

This establishes a Type object that will be used to identify employees in a
company ...

Type EmployeeRecords
ID As Integer
Name As String
Address As String
Phone As Long
StartDate As Date
End Type

The Type Statement starts with the Type keyword and it is followed by the
name you want to give to the structure that follows below it (here, I have
chosen to call it EmployeeRecords) and ends with the End Type statement.
Between those two statements are placed any number of "members" of the
Type... these are declared exactly like Dim statements, but without the Dim
keyword in front of them. The above Type..End Type block is placed in the
(General)(Declarations) section of whatever module you place it in... it is
never placed inside any of your Subs or Functions themselves. In order to
use a Type, you have to declare a variable of for it. You would do that like
this (where I have chosen to name this variable Employee)...

Dim Employee As EmployeeRecords

or like this if you want to create a fixed array of them (10 array elements
in this example)...

Dim Employee(1 To 10) As EmployeeRecords

or you could make a dynamic array of them like this...

Dim Employee() As EmployeeRecords

and provide the number of dimensions via a ReDim statement within your code
when you have determined how may elements of your Employee array you will
need.

Okay, that takes care of creating the Type object. To use it, you refer to
the members of the Type using dot notation. For example...

Employee.ID = 123
Employee.Name = "John Jones"
etc.

If you Dim'med Employee as an array, then you would need to provide an index
value for the array...

Employee(1).ID = 123
Employee(1).Name = "Jone Jones"
etc.
Employee(2).ID = 456
Employee(2).Name = "Jane Doe"
etc.

Of course, you could iterate through the array via a For..Next loop (just
like any other array) if needed.

Okay, as I said, I **think** this kind of structure may be what you were
looking for in your original question.

--
Rick (MVP - Excel)


"Mike H." wrote in message
...
I wish to dim an array like this:
Dim DataArray(4,1) as ????
where Dimension 1 is String
Dimension 2 is Integer
DImension 3 is String
and 4 is Long.

How do I declare something like that? What I usually do is just dimension
it as variant and then I am able to use all the types. But I'd like to
force
a type on a dimension so I don't get "2" instead of 2 stored in an array,
for
example.

Sometimes there might be 10 or 15 dimension I wish to declare differently.

Thanks.

.

Did you really mean to post your message as a response to my message?

--
Rick (MVP - Excel)


"Gary Brown" wrote in message
...
AFAIK,
You can only declare an array as ONE type.
ie: MyArray(2,2) as Integer is OK while MyArray(2 as Integer ,2 as
String) is bad syntax.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Rick Rothstein" wrote:

I think I have a solution for what you want to do, but first a "lesson"
on
terminology as the terms you used in your question confused me as to what
you actually want. The "dimension" of an array is how many numbers there
are
in the comma separated list between the parentheses. For example...

Dim DataArray(4, 1) As ???

declares a two-dimensional array whereas...

Dim DataArray(4, 1, 7, 2) As ???

declares a four-dimensional array; and so on. When using the array in
code,
each of the numbers in the parentheses are called the Index for that
dimension (and they must be numbers). Specifying an index value for each
dimension references a single element in the array. So, the reason I was
confused by your question is that there is no order to these elements in
an
array... there is no way to have a "Dimension 1" be an anything... the
first
dimension in an array is just the index value specified in the first
number
position in the array's "argument" list, the second dimension is just the
index value specified in the second number position in the array's
"argument" list and so on through whatever number of dimensions there
are.

Now, if I understand where your question is driving at at-all, I think
you
may want an array of Type objects instead of a just a simple array. The
syntax in use will be a little different than in a normal array, but I
think
it provides the functionality you ultimately want. I'm going to give you
a
made up example to show how to declare a Type object and then show you
how
to use it (but you may want to lookup "Type Statement" in the VB help
files
for more information on it).

This establishes a Type object that will be used to identify employees in
a
company ...

Type EmployeeRecords
ID As Integer
Name As String
Address As String
Phone As Long
StartDate As Date
End Type

The Type Statement starts with the Type keyword and it is followed by the
name you want to give to the structure that follows below it (here, I
have
chosen to call it EmployeeRecords) and ends with the End Type statement.
Between those two statements are placed any number of "members" of the
Type... these are declared exactly like Dim statements, but without the
Dim
keyword in front of them. The above Type..End Type block is placed in the
(General)(Declarations) section of whatever module you place it in... it
is
never placed inside any of your Subs or Functions themselves. In order to
use a Type, you have to declare a variable of for it. You would do that
like
this (where I have chosen to name this variable Employee)...

Dim Employee As EmployeeRecords

or like this if you want to create a fixed array of them (10 array
elements
in this example)...

Dim Employee(1 To 10) As EmployeeRecords

or you could make a dynamic array of them like this...

Dim Employee() As EmployeeRecords

and provide the number of dimensions via a ReDim statement within your
code
when you have determined how may elements of your Employee array you will
need.

Okay, that takes care of creating the Type object. To use it, you refer
to
the members of the Type using dot notation. For example...

Employee.ID = 123
Employee.Name = "John Jones"
etc.

If you Dim'med Employee as an array, then you would need to provide an
index
value for the array...

Employee(1).ID = 123
Employee(1).Name = "Jone Jones"
etc.
Employee(2).ID = 456
Employee(2).Name = "Jane Doe"
etc.

Of course, you could iterate through the array via a For..Next loop (just
like any other array) if needed.

Okay, as I said, I **think** this kind of structure may be what you were
looking for in your original question.

--
Rick (MVP - Excel)


"Mike H." wrote in message
...
I wish to dim an array like this:
Dim DataArray(4,1) as ????
where Dimension 1 is String
Dimension 2 is Integer
DImension 3 is String
and 4 is Long.

How do I declare something like that? What I usually do is just
dimension
it as variant and then I am able to use all the types. But I'd like to
force
a type on a dimension so I don't get "2" instead of 2 stored in an
array,
for
example.

Sometimes there might be 10 or 15 dimension I wish to declare
differently.

Thanks.

.

You are correct that an array has to be a single data type. A variant will
allow the elements of the array to hold any data type. What Rick is
demonstrating is using the Type feature to create a custom data type. Each
element of the array hold a member of that data type so he is down to a 1D
array. Each element of that type holding 4 or 5 different variables of
different types. Based on the ops description this is probably the best
solution (IMO).
--
HTH...

Jim Thomlinson


"Gary Brown" wrote:

AFAIK,
You can only declare an array as ONE type.
ie: MyArray(2,2) as Integer is OK while MyArray(2 as Integer ,2 as
String) is bad syntax.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Rick Rothstein" wrote:

I think I have a solution for what you want to do, but first a "lesson" on
terminology as the terms you used in your question confused me as to what
you actually want. The "dimension" of an array is how many numbers there are
in the comma separated list between the parentheses. For example...

Dim DataArray(4, 1) As ???

declares a two-dimensional array whereas...

Dim DataArray(4, 1, 7, 2) As ???

declares a four-dimensional array; and so on. When using the array in code,
each of the numbers in the parentheses are called the Index for that
dimension (and they must be numbers). Specifying an index value for each
dimension references a single element in the array. So, the reason I was
confused by your question is that there is no order to these elements in an
array... there is no way to have a "Dimension 1" be an anything... the first
dimension in an array is just the index value specified in the first number
position in the array's "argument" list, the second dimension is just the
index value specified in the second number position in the array's
"argument" list and so on through whatever number of dimensions there are.

Now, if I understand where your question is driving at at-all, I think you
may want an array of Type objects instead of a just a simple array. The
syntax in use will be a little different than in a normal array, but I think
it provides the functionality you ultimately want. I'm going to give you a
made up example to show how to declare a Type object and then show you how
to use it (but you may want to lookup "Type Statement" in the VB help files
for more information on it).

This establishes a Type object that will be used to identify employees in a
company ...

Type EmployeeRecords
ID As Integer
Name As String
Address As String
Phone As Long
StartDate As Date
End Type

The Type Statement starts with the Type keyword and it is followed by the
name you want to give to the structure that follows below it (here, I have
chosen to call it EmployeeRecords) and ends with the End Type statement.
Between those two statements are placed any number of "members" of the
Type... these are declared exactly like Dim statements, but without the Dim
keyword in front of them. The above Type..End Type block is placed in the
(General)(Declarations) section of whatever module you place it in... it is
never placed inside any of your Subs or Functions themselves. In order to
use a Type, you have to declare a variable of for it. You would do that like
this (where I have chosen to name this variable Employee)...

Dim Employee As EmployeeRecords

or like this if you want to create a fixed array of them (10 array elements
in this example)...

Dim Employee(1 To 10) As EmployeeRecords

or you could make a dynamic array of them like this...

Dim Employee() As EmployeeRecords

and provide the number of dimensions via a ReDim statement within your code
when you have determined how may elements of your Employee array you will
need.

Okay, that takes care of creating the Type object. To use it, you refer to
the members of the Type using dot notation. For example...

Employee.ID = 123
Employee.Name = "John Jones"
etc.

If you Dim'med Employee as an array, then you would need to provide an index
value for the array...

Employee(1).ID = 123
Employee(1).Name = "Jone Jones"
etc.
Employee(2).ID = 456
Employee(2).Name = "Jane Doe"
etc.

Of course, you could iterate through the array via a For..Next loop (just
like any other array) if needed.

Okay, as I said, I **think** this kind of structure may be what you were
looking for in your original question.

--
Rick (MVP - Excel)


"Mike H." wrote in message
...
I wish to dim an array like this:
Dim DataArray(4,1) as ????
where Dimension 1 is String
Dimension 2 is Integer
DImension 3 is String
and 4 is Long.

How do I declare something like that? What I usually do is just dimension
it as variant and then I am able to use all the types. But I'd like to
force
a type on a dimension so I don't get "2" instead of 2 stored in an array,
for
example.

Sometimes there might be 10 or 15 dimension I wish to declare differently.

Thanks.

.