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. <Mi...@discussions.microsoft.com> 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." <(E-Mail Removed)> wrote in message
news:86D3B6C2-0B95-4A6B-8B6B-(E-Mail Removed)...
>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." <(E-Mail Removed)> wrote in message
> news:86D3B6C2-0B95-4A6B-8B6B-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
news:FC4B39F1-7353-438B-BEF8-(E-Mail Removed)...
> 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." <(E-Mail Removed)> wrote in message
>> news:86D3B6C2-0B95-4A6B-8B6B-(E-Mail Removed)...
>> >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." <(E-Mail Removed)> wrote in message
> > news:86D3B6C2-0B95-4A6B-8B6B-(E-Mail Removed)...
> > >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.
> >
> > .
> >