I would like to declare a multi-dimensional array with different types, such
as:

dim array(200,4) where (x,1)=Long, (x,2)=String, (x,3)=Date, (x,4)=Date

Can this be done or should I leave it as Variant? After reading Mr.
Pearson's page (http://www.cpearson.com/excel/variables.htm) regarding
variables I'm hesitant to leave it...
--
Adios,
Clay Harryman

You can't declare an array with different types, but maybe an array of UDT's
could help you out:

Option Explicit
Private Type ColumnData
lCol1 As Long
strCol2 As String
daCol3 As Date
daCol4 As Date
End Type

Sub test()

Dim uCData As ColumnData
Dim arr(1 To 10) As ColumnData

uCData.lCol1 = 1
uCData.strCol2 = "test"
uCData.daCol3 = "24/04/2003"
uCData.daCol4 = "25/04/2003"

arr(1) = uCData

MsgBox Weekday(arr(1).daCol3)

End Sub


RBS


"Clayman" <(E-Mail Removed)> wrote in message
news:A307B10E-7382-4E3C-9A81-(E-Mail Removed)...
>I would like to declare a multi-dimensional array with different types,
>such
> as:
>
> dim array(200,4) where (x,1)=Long, (x,2)=String, (x,3)=Date, (x,4)=Date
>
> Can this be done or should I leave it as Variant? After reading Mr.
> Pearson's page (http://www.cpearson.com/excel/variables.htm) regarding
> variables I'm hesitant to leave it...
> --
> Adios,
> Clay Harryman

Thank you. This looks like it will work. I'm not in a position to try it
right now, but I'll let you know.
--
Adios,
Clay Harryman


"RB Smissaert" wrote:

> You can't declare an array with different types, but maybe an array of UDT's
> could help you out:
>
> Option Explicit
> Private Type ColumnData
> lCol1 As Long
> strCol2 As String
> daCol3 As Date
> daCol4 As Date
> End Type
>
> Sub test()
>
> Dim uCData As ColumnData
> Dim arr(1 To 10) As ColumnData
>
> uCData.lCol1 = 1
> uCData.strCol2 = "test"
> uCData.daCol3 = "24/04/2003"
> uCData.daCol4 = "25/04/2003"
>
> arr(1) = uCData
>
> MsgBox Weekday(arr(1).daCol3)
>
> End Sub
>
>
> RBS
>
>
> "Clayman" <(E-Mail Removed)> wrote in message
> news:A307B10E-7382-4E3C-9A81-(E-Mail Removed)...
> >I would like to declare a multi-dimensional array with different types,
> >such
> > as:
> >
> > dim array(200,4) where (x,1)=Long, (x,2)=String, (x,3)=Date, (x,4)=Date
> >
> > Can this be done or should I leave it as Variant? After reading Mr.
> > Pearson's page (http://www.cpearson.com/excel/variables.htm) regarding
> > variables I'm hesitant to leave it...
> > --
> > Adios,
> > Clay Harryman
>
>

Hi Clay,
Is there any reason you cannot to use a pair of 1D arrays for your Longs and
Strings and a 2D array for your dates. However 200x4 array is not large and
I doubt you'd notice any performance loss by declaring as a Variant.

If you particularly want to fix mixed data types within the array, eg for
coercing to correct data type, you could do -

Dim v(1 To 4)
Dim arrL(1 To 200) As Long
Dim arrS(1 To 200) As String
Dim arrD1(1 To 200) As Date, arrD2(1 To 200) As Date
'arrL(1) = 123
'arrS(1) = "abc"
'arrD1(1) = Date
'arrD2(1) = Date + 1
v(1) = arrL
v(2) = arrS
v(3) = arrD1
v(4) = arrD2

v(1)(2) = 6.789
MsgBox v(1)(2) ' 7 rounded to a long

v(3)(2) = CLng(Date) ' a number
MsgBox v(3)(2) ' a date

v(1)(3) = "abc" ' error type mismatch, expecting a long

Regards,
Peter T

"Clayman" <(E-Mail Removed)> wrote in message
news:A307B10E-7382-4E3C-9A81-(E-Mail Removed)...
> I would like to declare a multi-dimensional array with different types,
such
> as:
>
> dim array(200,4) where (x,1)=Long, (x,2)=String, (x,3)=Date, (x,4)=Date
>
> Can this be done or should I leave it as Variant? After reading Mr.
> Pearson's page (http://www.cpearson.com/excel/variables.htm) regarding
> variables I'm hesitant to leave it...
> --
> Adios,
> Clay Harryman

This question may make the arrays moot:

I'm using this array to store vacation taken/sold information, and make it
available in a series of ListBoxes for editing. The main reason I wanted the
array was to store (invisible to the user) the transaction ID for each
record. This makes for bulky code, though.

Is it bad programming practice to store the values in the ListBoxes - not
the arrays - including an invisible ListBox to store the transaction ID? I
mean, I can hear my old CS professors screaming not to do it, but I don't
know if those voices are right...
--
Adios,
Clay Harryman


"RB Smissaert" wrote:

> You can't declare an array with different types, but maybe an array of UDT's
> could help you out:
>
> Option Explicit
> Private Type ColumnData
> lCol1 As Long
> strCol2 As String
> daCol3 As Date
> daCol4 As Date
> End Type
>
> Sub test()
>
> Dim uCData As ColumnData
> Dim arr(1 To 10) As ColumnData
>
> uCData.lCol1 = 1
> uCData.strCol2 = "test"
> uCData.daCol3 = "24/04/2003"
> uCData.daCol4 = "25/04/2003"
>
> arr(1) = uCData
>
> MsgBox Weekday(arr(1).daCol3)
>
> End Sub
>
>
> RBS
>
>
> "Clayman" <(E-Mail Removed)> wrote in message
> news:A307B10E-7382-4E3C-9A81-(E-Mail Removed)...
> >I would like to declare a multi-dimensional array with different types,
> >such
> > as:
> >
> > dim array(200,4) where (x,1)=Long, (x,2)=String, (x,3)=Date, (x,4)=Date
> >
> > Can this be done or should I leave it as Variant? After reading Mr.
> > Pearson's page (http://www.cpearson.com/excel/variables.htm) regarding
> > variables I'm hesitant to leave it...
> > --
> > Adios,
> > Clay Harryman
>
>

I guess it might be a bit late, but in the listbox, you can always set the
column that you don't want the user to see to 0 length. That way you still
get all the data that you need without going through the arrays and the
ListBox is then just populated through ListBox1.RowSource = Range("RC:RC").

ListBox1.ColumnCount = 3
ListBox1.ColumnWidths = 90;0;90

"Clayman" wrote:

> This question may make the arrays moot:
>
> I'm using this array to store vacation taken/sold information, and make it
> available in a series of ListBoxes for editing. The main reason I wanted the
> array was to store (invisible to the user) the transaction ID for each
> record. This makes for bulky code, though.
>
> Is it bad programming practice to store the values in the ListBoxes - not
> the arrays - including an invisible ListBox to store the transaction ID? I
> mean, I can hear my old CS professors screaming not to do it, but I don't
> know if those voices are right...
> --
> Adios,
> Clay Harryman
>
>
> "RB Smissaert" wrote:
>
> > You can't declare an array with different types, but maybe an array of UDT's
> > could help you out:
> >
> > Option Explicit
> > Private Type ColumnData
> > lCol1 As Long
> > strCol2 As String
> > daCol3 As Date
> > daCol4 As Date
> > End Type
> >
> > Sub test()
> >
> > Dim uCData As ColumnData
> > Dim arr(1 To 10) As ColumnData
> >
> > uCData.lCol1 = 1
> > uCData.strCol2 = "test"
> > uCData.daCol3 = "24/04/2003"
> > uCData.daCol4 = "25/04/2003"
> >
> > arr(1) = uCData
> >
> > MsgBox Weekday(arr(1).daCol3)
> >
> > End Sub
> >
> >
> > RBS
> >
> >
> > "Clayman" <(E-Mail Removed)> wrote in message
> > news:A307B10E-7382-4E3C-9A81-(E-Mail Removed)...
> > >I would like to declare a multi-dimensional array with different types,
> > >such
> > > as:
> > >
> > > dim array(200,4) where (x,1)=Long, (x,2)=String, (x,3)=Date, (x,4)=Date
> > >
> > > Can this be done or should I leave it as Variant? After reading Mr.
> > > Pearson's page (http://www.cpearson.com/excel/variables.htm) regarding
> > > variables I'm hesitant to leave it...
> > > --
> > > Adios,
> > > Clay Harryman
> >
> >