PDA

View Full Version : [SOLVED] Dynamic Array using Redim Preserve VS large static array?



vegard_fv
07-29-2016, 08:43 AM
Hi everyone,

I'm fairly new to VBA and starting to get the advantages of using arrays. Today I made a solution that does the job perfectly, but I am wandering if it is the "right" or "best practice" way (programaticly) to do it.

As you can see by the code example below, my code makes two arrays. One to put the raw data in, and one for results. So every time my search criteria is matched, I make the ReDim Preserve statement to Dim my result-array +1. Is this faster than just dimension a big array to start with, lets say 100.000?

Or is it some other way to solve this that would be faster and more correctly? In other program languages arrays is dynamicly "by default" - but this does not seem to be the case with VBA, or am I wrong? Would love to get some feedback! :broom:

Here's my code:


Sub SendSomeDataToResultArray()
Dim arr()
Dim counter As Integer
'Sets range to selection, checks for number of items, Redim, and then feeds it into a raw data array using the Range-object
Set Rng = Selection

ant = Rng.Count
ReDim arr(ant)
arr = Rng.Value

'Declare an array nr. 2 for the results
Dim ResultArr()

'sets the counter variable to zero
counter = 0

'Looping through the raw data array
For col = 1 To UBound(arr, 2)
For row = 1 To UBound(arr, 1)

'Search criteria, and everytime the search criterias match, ReDim and Preserve the ResultArray, and feed the match into the new available spot in the ResultArray
If InStr(arr(row, col), "Batman") > 0 Then
counter = counter + 1
ReDim Preserve ResultArr(counter)
ResultArr(counter) = arr(row, col) & "Leverandørnavn: " & arr(row + 1, col)
End If

Next row
Next col

SamT
07-29-2016, 09:54 AM
For your reading pleasure

Sub SendSomeDataToResultArray()
Dim arr()
Dim ResultArr()
Dim counter As Integer

With Selection
arr = .Value
ReDim ResultArr(.Rows.Count * .Columns.Count) 'Very Large array
ReDim ResultArr(WorksheetFunction.CountIf(.Cells, "Batman")) 'Not so large
End With

'Looping through the raw data array
For col = 1 To UBound(arr, 2)
For row = 1 To UBound(arr, 1)

If InStr(arr(row, col), "Batman") Then
ResultArr(counter) = arr(row, col) & "Leverandørnavn: " & arr(row + 1, col)
counter = counter + 1 'Default Array start index = 0
End If

Next row
Next col
ReDim Preserve ResultArr(counter) 'last loop added 1 to counter

End Sub

vegard_fv
07-29-2016, 11:07 AM
It's a great idea to check for items to find before, so the array can be dimensioned properly, one time. Thanks. But what would you do in cases you do not know how many items the array will have before you start; - is there any smart way to do it, without ReDim each time?

p45cal
07-29-2016, 11:09 AM
Or is it some other way to solve this that would be faster and more correctly? Well that depends on what you're trying to do.
Am I right in guessing hat the user will select a range several columns wide but only 2 rows deep?, and that the 2nd row will never contain Batman?
Give us some idea of what the user will select and what you expect.
You could even attach a very small workbook with a small amount of data, what the user will select and what you want the results to be. (Go Advanced, Manage attachments to attach a file.)

vegard_fv
07-29-2016, 11:27 AM
Thanks for feedback.

Please see the attached file. I'm actually doing some parsing. And always searching for the string "Lev. nr" - which is a suplyers number, and then on the line under always the suplyer is stated. Like this:

---
Lev.nr :3564 Org. nr :
8. klasse Namsskogan skole 2014/15 v/Hege Sellin Telefon:
---

The script is doing its job, but I was just curious to learn "best VBA practice", especially regarding how to handle arrays size when you don't know before how big it is (which the first message showed how to do in this spesific example).

The user will be me, selecting column A. So even if SamT got a very good solution for my problem this time, I'm still wandering: if you don't know how big an array will be (like an result-array), what do you do?

Thanx :)

SamT
07-29-2016, 12:06 PM
So even if SamT got a very good solution for my problem this time, I'm still wandering: if you don't know how big an array will be (like an result-array), what do you do?
I had no idea how big the Results array had to be. The first redim made it large enough to hold ever cell. with that, you should use the third Redim.

The second only made it large enough to hold every "batman." With that, you don't need the third Redim.

Which is fastest depends on the original data.

If there is no way to predetermine the count of results, you can Redim on every 'hit' or use a large enough array and redim it to ResultsCount at the end.

SamT
07-29-2016, 12:33 PM
Looking at your sample data, all data is only in column "A" and you only want the Strings to the left of the first double-space.

It might even be better to work with the Raw data before it is put into an Excel worksheet

Even my use of CountIf wont work because the value is not a simple constant string, for example Cell ("A249") =Lev.nr :3446_______________Org. nr :00987403829 (Underscor = space)

Since the desired Results are such a small part of the data, I would use an iterative Find.


Set Source = Range("A1").CurrentRegion 'Put in memory for speed
Set Found = Source.Find("Lev.nr")
'
'
'
Result = Trim(Left(Found, InStr(found, " ")), " ") & " Leverandørnavn: " _
& Trim(Left(Found.Offset(1), InStr(found, " ")), " ")

Redim Preserve(Resultarr, Ubound(Resultarr)+ 1))
Resultarr(Ubound(Resultarr)) = Result
'
'
'
Loop

vegard_fv
07-29-2016, 01:45 PM
Guys, youre awsome! Learned a great deal today. Thanx :-)

p45cal
07-29-2016, 02:35 PM
Since you're going to place the results in a sheet and you're running down the rows top to bottom, you don't have to create a separate results array, you can instead change the values in arr starting at the top. When done, put the arr back on a fresh sheet but only the rows which have been altered:

Sub blah()
'Application.Goto Sheets("RawData").Range("A1:A23314")
If Application.CountIf(Selection, "*Lev.nr*") > 0 Then
arr = Selection.Value 'this creates the big array
For rw = 1 To UBound(arr) - 1
If Not IsError(arr(rw, 1)) Then 'there are 2 cells with invalid formulae in (A8705 and A15961)
If InStr(1, arr(rw, 1), "Lev.nr", vbTextCompare) > 0 Then
counter = counter + 1
arr(counter, 1) = arr(rw, 1) & "Leverandørnavn: " & arr(rw + 1, 1) 'overwrite data in arr
End If
End If
Next rw
End If
Worksheets(3).Range("A1").Resize(counter) = arr 'only copy the first rows of arr into a sheet (the first counter rows)
End Sub

vegard_fv
07-30-2016, 02:20 AM
That's a very creative and fast solution, p45cal. Would never have thought about overwrite the array itself, but of course that's possible! And combined with the resize-propery, all I can say is: Thank's alot for taking the time, this really improves my coding!
:band:You rock, man - and so does this forum!!:trophy::content: