PDA

View Full Version : Ragged Arrays - Safe Arrays



stanl
04-12-2006, 09:01 AM
I'm going to be collecting information from PDF forms using the .NET Framework System.Collections.ArrayList object. These will be ragged arrays (or variable multi-dimensional elements). I was hoping to convert to a VBA array as the data will be entered in Excel as CSV. I don't think this is possible, but the issue can be simulated with the following sub. If anyone can point out any obvious stupid mistakes I made, or suggest a workaround (just keep in mind that Excel will receive an array, not an arraylist) I would appreciate it. Stan

Sub multilist()
Dim oList, oList1, txt, n, i, j, aList
Set oList = CreateObject("System.Collections.ArrayList")
For i = 1 To 5
Set oList1 = CreateObject("System.Collections.ArrayList")
n = Int((6 - 1 + 1) * Rnd + 1)
For j = 1 To n
oList1.Add "Elem" & Int((10000) * Rnd + 1)
Next
oList.Add oList1
oList1 = 0
Next
txt = ""
n = oList.Count
For i = 0 To (n - 1)
For j = 0 To (oList.Item(i).Count - 1)
txt = txt & oList.Item(i).Item(j) & ","
Next
txt = txt & vbCrLf
Next
MsgBox txt, vbInformation, "My Ragged Array"
aList = oList.ToArray
Application.ActiveWorksheet.Cells(1, 1).Value = aList(0)
End Sub

jindon
04-12-2006, 05:40 PM
Not usre if this what you are after..

Sub multilist()
Dim oList, oList1, txt, n, i, j, aList, a()
Set oList = CreateObject("System.Collections.ArrayList")
For i = 1 To 5
Set oList1 = CreateObject("System.Collections.ArrayList")
n = Int((6 - 1 + 1) * Rnd + 1)
x = Application.Max(x, n)
For j = 1 To n
oList1.Add "Elem" & Int((10000) * Rnd + 1)
Next
oList.Add oList1
oList1 = 0
Next
txt = ""
n = oList.Count
ReDim a(n - 1, x - 1)
For i = 0 To (n - 1)
For j = 0 To (oList.Item(i).Count - 1)
a(i, j) = oList.Item(i).Item(j)
txt = txt & oList.Item(i).Item(j) & ","
Next
txt = txt & vbCrLf
Next
MsgBox txt, vbInformation, "My Ragged Array"
aList = oList.ToArray
ActiveSheet.Cells(1, 1).Resize(i, x) = a
End Sub

stanl
04-13-2006, 04:13 AM
The results are what I'm after, but I have to assume the ArrayList is already constructed then convert (if possible) to a safe-array. Your code builds the array alonside the arraylist. The following doesn't work but is more in line with how the arraylist is accepted by a sub - Stan



Sub multilist()
Dim oList
oList = crlist()
aList = oList.ToArray
ActiveSheet.Cells(1, 1) = aList
End Sub

Function crlist()
Dim oList, oList1, txt, n, i, j
Set oList = CreateObject("System.Collections.ArrayList")
For i = 1 To 5
Set oList1 = CreateObject("System.Collections.ArrayList")
n = Int((6 - 1 + 1) * Rnd + 1)
For j = 1 To n
oList1.Add "Elem" & Int((10000) * Rnd + 1)
Next
oList.Add oList1
oList1 = 0
Next
crlist = oList
End Function