Programming exercise, array

[Guest]

I wish to seek advice on speeding up these processes.

I read a 180,000 lines of text file in an array in Excel 2000.
From this 180,000 element array, I used instr method to extract 150,000
elements into a new array. It takes about 2.3 seconds (see code A).

With this 150,000 element array, I performed search for keywords 6 times,
again using instr method. It takes 16 seconds (see code B).

When either the size of the array increases or I need to search for more
keywords, the processing time increases to an annoying level.

Is there any other way of achieving faster results please?

Regards
Victor

Code A
j = 0
For x = 1 To i 'i = 180,000
If InStr(1, arrLarge(x), c, vbTextCompare) > 0 Then
arrCat(j + 1) = arrLarge(x)
j = j + 1
End If
Next x

Code B
For y = 2 To x 'x = 7 thus 6 keywords
k = 0
For z = 1 To j 'j = 150,000
If InStr(1, arrCat(z), Cells(c.Row, y), vbTextCompare) > 0
Then k = k + 1
Next z
If k <> 0 Then
wsr.Cells(c.Row * 3 - 11, y) = k
End If
If k = 0 Then
wsr.Cells(c.Row * 3 - 11, y) = "NOT found"
End If
Next y

 

[Jim Cone]

In Code A, why not start with J =1
Change arrCat(J + 1) to arrCat(j)
Change j = j + 1 to j = j + 2
That would eliminate 180, 000 addition operations.

In Code B, I don't know how c.Row is arrived at, so won't comment.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Victor" <[email protected]>
wrote in message
I wish to seek advice on speeding up these processes.

I read a 180,000 lines of text file in an array in Excel 2000.
From this 180,000 element array, I used instr method to extract 150,000
elements into a new array. It takes about 2.3 seconds (see code A).

With this 150,000 element array, I performed search for keywords 6 times,
again using instr method. It takes 16 seconds (see code B).

When either the size of the array increases or I need to search for more
keywords, the processing time increases to an annoying level.

Is there any other way of achieving faster results please?

Regards
Victor

Code A
j = 0
For x = 1 To i 'i = 180,000
If InStr(1, arrLarge(x), c, vbTextCompare) > 0 Then
arrCat(j + 1) = arrLarge(x)
j = j + 1
End If
Next x

Code B
For y = 2 To x 'x = 7 thus 6 keywords
k = 0
For z = 1 To j 'j = 150,000
If InStr(1, arrCat(z), Cells(c.Row, y), vbTextCompare) > 0
Then k = k + 1
Next z
If k <> 0 Then
wsr.Cells(c.Row * 3 - 11, y) = k
End If
If k = 0 Then
wsr.Cells(c.Row * 3 - 11, y) = "NOT found"
End If
Next y

 

[Guest]

Thank you for your suggestion, Jim.
I tried, but do not notice any significant difference please

Regards
Victor

 

[RB Smissaert]

One thing I would alter is staying in arrays as long as possible and
only deal with the sheet when you have an array ready to dump
with range(cells(1), cells(r, c)) = arr

RBS

 

[Guest]

That was my belief when I started this exercise.

However, my testing with timer seems to point to, subject to sheet limit of
65536 rows:
loop 6 times using instr for 60,000 items take 6.34 sec;
dump array into sheet, count 6 times using countif take 0.36 sec;
if I repeat this, the time goes up to 0.75 sec but still much faster than
6.34 sec.

Regards
Victor

 

[RB Smissaert]

Could you speed up Instr by doing UCase and vbCompareBinary?

RBS

 

[NickHK]

If that is bottle neck, maybe the API function will help:

Declare Function CompareString Lib "kernel32" Alias "CompareStringA" (ByVal
Locale As Long, ByVal dwCmpFlags As Long, ByVal lpString1 As String, ByVal
cchCount1 As Long, ByVal lpString2 As String, ByVal cchCount2 As Long) As
Long
http://www.math.msu.su/~vfnik/WinApi/c/comparestring.html

Also, don't read the compare string each time from the worksheet; get them
in an array and use that.

NickHK

 

[Guest]

Thank you for the pointer.
Can it do "like" comparison as counting "pen" within "carpenters" please?
Seems not.
I will test it anyway.

Regards
Victor

 

[RB Smissaert]

Looking at this there is no simple way to speed up
Instr other than doing vbComparyBinary:

http://www.xbeat.net/vbspeed/

RBS

 

[RB Smissaert]

Have a look at this site:
http://www.aivosto.com/vbtips/stringopt.html

RBS

 

[Guest]

Have you considered using the filter function? I suspect (though havent
tried) as it is "built in" its going to be the fastest way to do this.