Declaring Multi-Dimensional Arrays

[Clayman]

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...

 

[RB Smissaert]

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]

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

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

 

[Peter T]

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]

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

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

 

[LOFE]

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

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...