VBA - Collections/Arrays/Sorting

[William Benson]

Hi, recently learned the joy of pasting an entire 2D array into an excel
range in one step instead of looping through array elements and pasting into
individual cells. I use a collection to populate the array and the downside
is everything comes out as strings, regardless of the data type of the items
originally read into the collection. Also case sensitivity is ignored (makes
me very unhappy). Finally, am not aware of a "quick" method in VBA to sort
an array of several hundred thousand elements ... would sorting a collection
be quicker? Below is the code, if helpful in answering, thanks.

Bill



Sub ProcessCollection(mCol As Collection, strSht As String)

Dim i As Long, j As Long
Dim PasteRange As Range
Dim PasteColumnCount As Long
Dim PasteRowCount As Long
Dim PasteArray
Dim Itm
Dim Sht As Worksheet

If mCol.Count Mod 65536 = 0 Then
PasteColumnCount = mCol.Count \ 65536
Else
PasteColumnCount = mCol.Count \ 65536 + 1
End If

'Set up Row Dimension from Test1
If PasteColumnCount > 1 Then
PasteRowCount = 65536
Else
PasteRowCount = mCol.Count
End If

'Define an array which is large enough to hold all the elements
'of the collection which resulted from Test1
ReDim PasteArray(1 To PasteRowCount, 1 To PasteColumnCount)
i = 1: j = 0

For Each Itm In mCol
If j + 1 > 65536 Then
j = 1
i = i + 1
Else
j = j + 1
End If
PasteArray(j, i) = mCol(CStr(Itm))
Next Itm
Sheets.Add
ActiveSheet.Name = strSht

Set PasteRange = Worksheets(strSht).Range(Worksheets(strSht).Range("A1"),
Worksheets(strSht).Range("A1").Offset(PasteRowCount - 1, PasteColumnCount -
1))
PasteRange.Value = PasteArray
End Sub

 

[Jim Cone]

William,

This line is converting your data to strings...
PasteArray(j, i) = mCol(CStr(Itm))
Change it to...
PasteArray(j, i) = mCol(Itm)

The Dictionary object could be used to replace the
Collection object. It is case sensitive.

Everybody has some pet sort technique, including me, however I have never
had the need to sort a multi-dimensional array of several hundred thousand
elements. So, I will pass on this one.

Regards,
Jim Cone
San Francisco, USA

 

[Arvi Laanemets]

Hi

Before start with sorting a multi-dimension array, you have to define, how
do you sort it. With 2D array, you have there options:
a) the first row is sorted, then second one, etc.
b) The first column is sorted, then second one, etc.
c) Theoretically there are other algorithms, p.e. you can handle the 2D
array as a 1D one (it is possible p.e. in FoxPro) but such approach destroys
any original connections - you simply create some new array with same
elements.

With both algorithms, any sorting can be looked at, as sorting a collection
of 1D arrays. So I think you can easily adopt the function provided in
Microsoft KB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;133135

 

[William Benson]

Selection b)

except that it would be first 65536, next 65536, and so on.

Thus with these multiple assignments of portions of the collection to an
array, sorting, and reconstituting the collection from the sorted values, I
imagine there is some performance issues roughly equivalent or worse to just
sorting a huge 1D array? Probably not worth it, I am not running on a Cray
Mainframe



Thank for the helpful insights. I looked at KB item as well.

 

[William Benson]

Jim,

I converted the items to strings on the way into the collection because I
was getting error messages. I think I read that the key had to be a string.

Here is the code where I build the collection, can you recommend an
alternative and demonstrate what it would look like if I instead used the
Dictionary object? Thanks!

If RangeOneSet Then
For Each Cell In ActiveWorkbook.Names("RangeOne").RefersToRange
mcolRangeOneOriginal.Add Cell.Value, CStr(Cell.Value)
Next Cell
End If

 

[Jim Cone]

William,

The code for a Dictionary object is similar to the Collection object,
but not identical.
A project reference to "Microsoft Scripting Runtime" is required.

Dim dicRangeOneOriginal as Scripting.Dictionary
Set dicRangeOneOriginal = New Scripting.Dictionary

The key and item positions are reversed in a Dic. object
and both are required...
dicRangeOneOriginal.Add CStr(Cell.Value), Cell.Value

But, unless you are looking for / eliminating duplicates, the
Key can simply be an empty string...
dicRangeOneOriginal.Add vbNullString, Cell.Value

Regards,
Jim Cone
San Francisco, USA