Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lottery exercise | Excel Worksheet Functions | |||
A 13 period exercise | Excel Worksheet Functions | |||
Array Type programming | Excel Programming | |||
Programming Array Formulas in VBA - Can they be intermediate results in RAM? | Excel Programming | |||
Array formulas into VB programming | Excel Programming |