VBA Speed question - one huge array (400000 rows) vs reading every k-th row

[astrbac]

Hello,

I was wondering, is it more efficient to:

1. grab huge range A1:M400,000 into an Array 1, create a new subArray 1 to use, from Array 1 in VB (every 12th row)
2. OR loop through range with "i = i + 11" every time, creating a subArray 1 this way?

I'm thinking it has to be 1 but do we know at which point does it become not so efficient? This is almost half of the available range in Excel.

Also, how do I dump an array/destruct variable in VB after I'm done with it (if I don't want to wait End Sub)?

Thanks!

 

[GTO]

Greetings astbac,

It will definitely be faster to plunk a large range into an array and work on it there. In fact, the range does not need to be all that large. As far as dumping the array, as long as we test that it is an array (since I used variants), we can use Erase.

Here's an easy example for a blank workbook. In a Standard Module:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> setup()<br>  <SPAN style="color:#00007F">With</SPAN> Range("A1:M400000")<br>    .FormulaArray = "=""R""&ROW()&""C""&COLUMN()"<br>    .Value = .Value<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> example()<br><SPAN style="color:#00007F">Dim</SPAN> vntInput, vntOutput<br><SPAN style="color:#00007F">Dim</SPAN> RowIndex <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ColIndex <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#007F00">'</SPAN><br>  vntInput = Range("A1:M400000").Value<br>  <SPAN style="color:#00007F">ReDim</SPAN> vntOutput(LBound(vntInput) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(vntInput), <SPAN style="color:#00007F">LBound</SPAN>(vntInput, 2) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(vntInput, 2))<br><SPAN style="color:#007F00">'</SPAN><br>  <SPAN style="color:#00007F">For</SPAN> RowIndex = 1 <SPAN style="color:#00007F">To</SPAN> 400000 <SPAN style="color:#00007F">Step</SPAN> 12<br>    i = i + 1<br>    <SPAN style="color:#00007F">For</SPAN> ColIndex = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(vntInput, 2)<br>      vntOutput(i, ColIndex) = vntInput(RowIndex, ColIndex)<br>    <SPAN style="color:#00007F">Next</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#007F00">'</SPAN><br>  <SPAN style="color:#007F00">'clear the unneeded variant</SPAN><br>  <SPAN style="color:#00007F">If</SPAN> IsArray(vntInput) <SPAN style="color:#00007F">Then</SPAN> Erase vntInput<br><SPAN style="color:#007F00">'</SPAN><br>  Range("A1:M400000").Value = vntOutput<br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Mark

 

[joeu2004]

I was wondering, is it more efficient to:

1. grab huge range A1:M400,000 into an Array 1, create a new subArray 1 to use, from Array 1 in VB (every 12th row)
2. OR loop through range with "i = i + 11" every time, creating a subArray 1 this way?

[....] Also, how do I dump an array/destruct variable in VB after I'm done with it (if I don't want to wait End Sub)?

To answer the last question first: generally, VBA returns the memory for local (non-static) variables when the procedure is exited.

ERRATA.... Sorry, I just realized that you wrote "if I don't want to wait End Sub". If you created "array 1" with the statement
v = Range("a1:m400000"), you can release the memory with the statement v = Empty.

As to the first question: it would be best if you measure it for yourself. Use the following paradigm:

Public Declare Function QueryPerformanceFrequency Lib "kernel32" _
   (ByRef freq As Currency) As Long
Public Declare Function QueryPerformanceCounter Lib "kernel32" _
   (ByRef cnt As Currency) As Long

Sub doit()
Dim sc As Currency, ec As Currency, f As Currency, df As Double
QueryPerformanceFrequency f: df = f
QueryPerformanceCounter sc
.... code to measured ....
QueryPerformanceCounter ec
MsgBox Format((ec-sc)/df, "0.000000") & " sec"
End Sub

I think you will find that #1 is faster because the communication overhead between Excel and VBA each time data is passed from cells to VBA variables is very time-consuming.

Moreover, you might avoid creating the "subarray" in #1 if you do not need the data in contiguous memory, as you would if you use some WorksheetFunction methods. You might be able to simply index every 12th row in the "array 1".

 

[kalak]

Your 1 is the faster way to go, although several ways of going about it.


Here's a VBA code that uses only a single column array. Took me about 3.5 secs to list only every 12th of the 400k rows generated by Mark's data generator.
This one also retains the original formatting of every 12th row instead of juggling only the values if that's useful.

Sub every12()

Const rws As Long = 400000
Dim u(1 To rws, 1 To 1), c As Long, i As Long

For i = 1 To rws Step 12
    u(i, 1) = 1
    c = c + 1
Next i

Cells(1, "N").Resize(rws) = u

With Range("A1:N" & rws)
    .Sort Cells(1, "N")
    .Rows(c + 1 & ":" & rws).Delete xlUp
End With

Cells(1, "N").Resize(rws).Clear
Erase u  'although from my experience own I don't find this useful

End Sub

 

[astrbac]

Thanks guys, appreciate the answers!

I was asking this because HERE is what I am really trying to do so maybe someone could pitch in over there.

Cheers!

P.S. The linked thread is about comparing a few values between two arrays and writing amounts from one to the other if all values match.

 

[kalak]

Thanks guys, appreciate the answers!

I was asking this because HERE is what I am really trying to do so maybe someone could pitch in over there.

Cheers!

P.S. The linked thread is about comparing a few values between two arrays and writing amounts from one to the other if all values match.

Why do you ask us to waste time looking at the problem in this thread when it's really a different problem you want looked at?

 

[astrbac]

Kalak,

it was never my intention of wasting anybody's time. I have gotten lots (actually I cannot emphasise that enough - LOTS) of terriffic help from people on the forums, especially this one.

I am working on a project, quite a big one and I am still a "recreational" self-taught programmer. I cannot post it whole online and ask from someone to solve it for me. It would be both very rude and I wouldn't learn.

This way, I ask bit by bit and try to complete the pocture on my own.

Once more, my apologies if you believe I wasted your time, it was truly never my intention.