Programming exercise, array
 

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

Programming exercise, array
 

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"
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

Programming exercise, array
 

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

Regards
Victor

"Jim Cone" wrote:


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"
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