Problem with 2-dimensional array

[Depez9]

Hi,

Can someone tell me why this code only shows 1 filled row and leaves
the rest blank (number of rows is correct but they are all blank but
1).


Public Function Addresses()
Dim ol As Object
Dim olns As Object
Dim objFolder As Object
Dim objAllContacts As Object
Dim Contact As Object
Dim MyAddressAs String


Set ol = New Outlook.Application


Set olns = ol.GetNamespace("MAPI")


Set MyFolder1 = olns.Folders("Public Folders")
Set MyFolder2 = MyFolder1.Folders("All Public Folders")
Set MyFolder3 = MyFolder2.Folders("MyPublicFolder")


Set objAllContacts = MyFolder3.Items


Dim TotalCount, Counter As Long
TotalCount = MyFolder3.Items.Count


Counter = 1
Dim ContactArray As Variant


For Each Contact In objAllContacts


ReDim ContactArray(Counter, 1) As Variant


MyAddress= Contact.BusinessAddressStreet + ", " +
Contact.BusinessAddressPostalCode + " " + Contact.BusinessAddressCity
ContactArray(Counter, 0) = Contact.CompanyName
ContactArray(Counter, 1) = MyAddress


Counter = Counter + 1
Next Contact


CForm.MyListBox.ColumnCount = 2
CForm.MyListBox.List = ContactArray


End Function


Thanx in advance
Greetings Depez

 

[NickHK]

Whilst this is Outlook rather than Excel, you need to check teh Help for
Redim and understand what the Preserve keyword does.

NickHK

 

[Martin Brader]

You need to add Preserve to your REDIM statement otherwise the array is
reinitialised everytime you do a REDIM

 

[Depez9]

If I use:
ReDim Preserve ContactArray(Counter, 1) As Variant

It'll give me an error about non-matching types.


Martin Brader schreef:

 

[NickHK]

Because using Preserve you can only resize the last dimension. (Missed that
in the post.)
So you would have to rotate your array and use:
ReDim Preserve ContactArray(1,Counter) As Variant

NickHK

 

[Tom Ogilvy]

You can't redim preserve a 2D array on the first dimension. Only the last
dimension

You can get around this by flipping your array by 90 degrees. Treat the
second dimension as the "row" dimension.

but it seems to me it would be just as easy to skip the array and load your
listbox as you retrieve each contact.

 

[Depez9]

This gives me the same error.
Am I missing something here?

NickHK schreef:

 

[NickHK]

You can only "Preserve" something that you have previously ReDimmed
So
ReDim ContactArray(1, 1) As Variant
ReDim Preserve ContactArray(1, 1) As Variant

But as Tom says, why not populate the list box directly and forget the array
?

NickHK

 

[Depez9]

Somehow I don't get it to work.
I want to use an array because I also want to be able to sort the list.


NickHK schreef:

 

[Tom Ogilvy]

Public Function Addresses()
Dim ol As Object
Dim olns As Object
Dim objFolder As Object
Dim objAllContacts As Object
Dim Contact As Object
Dim MyAddressAs String
Dim ContactArray as Variant

Set ol = New Outlook.Application


Set olns = ol.GetNamespace("MAPI")


Set MyFolder1 = olns.Folders("Public Folders")
Set MyFolder2 = MyFolder1.Folders("All Public Folders")
Set MyFolder3 = MyFolder2.Folders("MyPublicFolder")


Set objAllContacts = MyFolder3.Items


Dim TotalCount, Counter As Long
TotalCount = MyFolder3.Items.Count


Counter = 1
ReDim ContactArray(0 to 1, 0 to counter)


For Each Contact In objAllContacts


ReDim Preserve ContactArray(0 to 1, 0 to Contact)


MyAddress= Contact.BusinessAddressStreet + ", " +
Contact.BusinessAddressPostalCode + " " + Contact.BusinessAddressCity
ContactArray(0, Counter) = Contact.CompanyName
ContactArray(1, Counter) = MyAddress


Counter = Counter + 1
Next Contact


CForm.MyListBox.ColumnCount = 2
CForm.MyListBox.Column = ContactArray


End Function