Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Multi-Dimensional Arrays
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Multi-Dimensional Arrays
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Multi-Dimensional Arrays
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Multi-Dimensional Arrays
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Multi-Dimensional Arrays
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Multi-Dimensional Arrays
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional IF formula using multi-dimensional arrays | Excel Worksheet Functions | |||
application.match with multi-dimensional arrays (syntax request) | Excel Programming | |||
Multi-dimensional arrays | Excel Programming | |||
Declaring 2 dimensional arrays | Excel Programming | |||
Declaring Dynamic Multi-dimensional Array | Excel Programming |