VBA - Collections/Arrays/Sorting

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

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


"William Benson" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>
>

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...b;en-us;133135

--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



"William Benson" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>
>

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.

"Arvi Laanemets" <[email protected]> wrote in message
news:O8pv%[email protected]...
> 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...b;en-us;133135
>
> --
> Arvi Laanemets
> ( My real mail address: arvil<at>tarkon.ee )
>
>
>
> "William Benson" <[email protected]> wrote in message
> news:%[email protected]...
>> 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,

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "William Benson" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>>
>>

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


"William Benson" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> > "William Benson" <[email protected]> wrote in message
> > news:%[email protected]...
> >> 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