Dealing with unknown array sizes

[Robert Crandal]

I have a multi-dimensional array which is initially defined
as follows:

Dim MyArr() as String

My VBA code will then search all rows that contain data.
(BTW, each row contains 5 columns of data.)

If a row of data is found that matches the search criteria,
I want to place all that string data into the "MyArr" array.
So, if my search yields 230 rows of string data, I want my final
array size to be EXACTLY 230 rows by 5 columns, or:

ReDim MyArr (1 to 230, 1 to 5)

Can I redimension the array each time a search hit is
found and continuously add data to this array?? I guess I'm
really looking for a string array that grows each time a search
hit is found.

I know I could create an oversized array to begin with, but
in my case it is important that my array size be EXACTLY
the same number as the total number of rows that I will place
in the array. Anybody know what I can do???

Thank you!

 

[OssieMac]

Hi robert,

You can only redim preserve the last dimension of an array. It is very
confusing from the programming point of view but you need to put the known
fixed number of columns in the first dimension and the unknown number of rows
in the 2nd dimension and redim preserve each time you want to add a row. The
following code for the redim preserve.

ReDim Preserve myArr(1 To 5, 1 To UBound(myArr, 2) + 1)

Have fun. It will test you logic turning the data around.

 

[OssieMac]

Hi again Robert,

I decided that it would be a worthwile exercise to create an example.

To test the example, enter some data in the first 5 columns of Sheet1 for 20
or so rows.

Run the code and it will populate the array and then output the contents of
the array to Sheet2.

Sub ReDimPreserveExample()

'Example of ReDim Preserve.
'Only last dimension can be ReDim Preserve
Dim myArr()

Dim C As Long 'Cols and 1st dimension in array
Dim R As Long 'Rows and 2nd dimension in array

With Sheets("Sheet1")
For C = 1 To 5
For R = 1 To 30
'Only ReDim on first loop of C
If C = 1 Then
ReDim Preserve myArr(1 To 5, 1 To R)
End If

myArr(C, R) = .Cells(R, C)

Next R
Next C
End With

MsgBox "# Elements in 1st dimension: " _
& UBound(myArr, 1) & vbLf & _
"# Elements in 2nd dimension: " _
& UBound(myArr, 2)

With Sheets("Sheet2")
For C = 1 To UBound(myArr, 1)
For R = 1 To UBound(myArr, 2)
.Cells(R, C) = myArr(C, R)
Next R
Next C
End With

End Sub

 

[Robert Crandal]

Ossie,

I just got to thinking, maybe there is a much easier solution
to my problem....

I am using a multi-dimensional string array because it seems
to be the easiest way to populate a multi-column listbox
control with string data. If I have an array of strings, I can
populate the listbox control by using the following code:

Userform1.Listbox1.List = MyArray

The first dimension of the array seems to control how many
rows will be displayed in the listbox. So, if the 1st dimension
of the array is set at 600, but the array only contains 2 rows
of data, that means the listbox will contain 600 rows: 2 rows
will contain data, but there will be 598 blank rows in the listbox
(which is unacceptable)!

So, do you know if I can somehow tell the listbox control to
only create N rows or items for the first N rows of the
array that actually contain data?? Maybe this would be easier?

BTW, thank you for taking the time write that great code below.
It really is awesome, but I'm just wondering if it might be
overkill.

Thank you!

Robert

 

[OssieMac]

Hi Robert,

The only way I can think of is to firstly find the first blank cell in the
array and then dimension another array to the correct size and copy the data
into it.

Sub RemoveBlanksFromArray()
Dim myArr(1 To 500, 1 To 5)
Dim myListArr()
Dim r As Long
Dim c As Long

'I have used dummy data to create an array
For r = 1 To 500
For c = 1 To 5
myArr(r, c) = Cells(r, c)
Next c
Next r

'Test for first blank element
For r = 1 To 500
If myArr(r, 1) = "" Then
Exit For
End If
Next r

'Subtract 1 from r because r is where
'the blank element was found.
r = r - 1

'Redimension a new array
ReDim myListArr(1 To r, 1 To 5)

'Copy the data into a new array
For r = 1 To UBound(myListArr, 1)
For c = 1 To 5
myListArr(r, c) = myArr(r, c)
Next c
Next r

'Use the new array for the listbox
With UserForm1
.ListBox1.List = myListArr()
.Show
End With

'Or on a worksheet
'Use the new array for the listbox
'With Sheets("Sheet2")
' .ListBox1.Object.List = myListArr()
'End With


End Sub

 

[Chip Pearson]

A good way to do this is to first determine a maximum number of rows
for the listbox, a value that you can be sure you will never exceed.
Then, create your array in the normal manner, and once the array is
loaded, Redim Preserve it do the the actual used size. Since you can
redim only the last dimenion of an array, and for a listbox that is
the number of columns, not rows, you have to transpose your array when
loading the listbox.

' we can be sure the array will never exceed this size
Const MAX_SIZE As Long = 1000
Dim RealSize As Long
Dim Arr() As String

ReDim Arr(1 To 3, 1 To MAX_SIZE)
' populate the array with your values. note that
' these array indexes are by column then row, rather
' than row by column.
Arr(1, 1) = "r1 c1"
Arr(2, 1) = "r1 c2"
Arr(3, 1) = "r1 c3"
RealSize = RealSize + 1
Arr(1, 2) = "r2 c1"
Arr(2, 2) = "r2 c2"
Arr(3, 2) = "r2 c3"
RealSize = RealSize + 1
' and so on for the whole array

' shrink array to acutal size
ReDim Preserve Arr(1 To 3, 1 To RealSize)
With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "50;50;50"
' transpose the array to swap rows/columns
.List = Application.Transpose(Arr)
End With

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Robert Crandal]

Chip,

Cool, I just have one question for you. I looked all over the
VBA documention and I don't see anything to indicate that
the ".Transpose" method is a member of the Application
object.

Will the "Transpose" function still work okay even though it
doesn't show up in the function list after I type "Application."???

As always, thanks for your great advice!

 

[Dave Peterson]

Before xl97 (I think), the way to access the worksheet functions from code was
to use:

Application.functionnamehere

xl97 introduced this kind of thing:
application.worksheetfunction.functionnamehere
or
worksheetfunction.functionnamehere

For the most part, all three are interchangeable.

But not always!

Two examples where they are not are with:

application.vlookup() and application.match
and
application.worksheetfunction.vlookup() and
application.worksheetfunction.match()

These behave different if there is no match.

=======
And because Chip is old <vbg> and grew up with excel, he continues to use
application.functionnamehere. About the only thing he loses is the VBE's
intellisense (but that's not useful for these anyway!).