Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dealing with unknown array sizes
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dealing with unknown array sizes
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. -- Regards, OssieMac "Robert Crandal" wrote: 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! . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dealing with unknown array sizes
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 -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dealing with unknown array sizes
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" wrote in message ... Hi again Robert, 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dealing with unknown array sizes
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 -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dealing with unknown array sizes
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 On Sun, 7 Mar 2010 20:11:11 -0700, "Robert Crandal" wrote: 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dealing with unknown array sizes (Application.Transpose)
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! "Chip Pearson" wrote in message ... .List = Application.Transpose(Arr) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dealing with unknown array sizes (Application.Transpose)
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!). Robert Crandal wrote: 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! "Chip Pearson" wrote in message ... .List = Application.Transpose(Arr) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to easily vary (parametrize) array sizes in Excel? | Excel Discussion (Misc queries) | |||
Unknown element array | Excel Programming | |||
Array with unknown elements(apologies if reposted) | Excel Programming | |||
Formula's dealing with unknown number of rows | Excel Discussion (Misc queries) | |||
Open multiple "unknown" filenames within a macro (array setup) | Excel Programming |