Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays to replace very slow loops ?
Hi I had a post here asking if my loops that run very slowly can be made any
quick. One suggestion was to replace with an Array. Now I'm new to VBA and have no idea how to go about Arrays. Below is my code where I'm finding dulicates based on name fields deleting from that and adding to another sheet. Here I have 2 text boxes to enter position of Names Fields and and an option to choose entire FName to be searched instead of just FName initial. Now all that I did is replace my r and k range with arrays but I get and error at this line : If Trim(UCase(Array1(i).Cells(n, strFNameCol))) = Trim(UCase(Array1(i).Cells(m, strFNameCol))) And _ Trim(UCase(Array1(i).Cells(n, strLNameCol))) = Trim(UCase(Array1(i).Cells(m, strLNameCol))) . It says runtime error "Runtime error 9 Subscript out of Range" My code as below: Private Sub CmdSubmitNames_Click() Dim r As Range, _ k As Range Dim sh As Excel.Worksheet Dim strFNameCol As String, _ strLNameCol As String Dim intCounter As Integer, _ intTotDB As Integer, _ totRows As Integer, _ intDupFound As Integer, _ intTotDB2 As Integer, _ i As Integer, _ intTotfile As Integer Dim Array1(), _ Array2() totRows = 1026 intCounter = 0 strFNameCol = TxtFNCol.Value strLNameCol = TxtLNCol.Value Set r = ActiveWorkbook.ActiveSheet.Range("A:AS") Set sh = ActiveWorkbook.Worksheets.Add Set k = sh.Range("A:AS") Array1 = r.Value Array2 = k.Value intTotDB = 1 n = 2 For n = 2 To 1000 If (Array1(i).Cells(n, strFNameCol)) < "" Or _ (Array1(i).Cells(n, strLNameCol)) < "" Then For m = n + 1 To 1000 If OptEntireFNSearch Then If Trim(UCase(Array1(i).Cells(n, strFNameCol))) = Trim(UCase(Array1( i).Cells(m, strFNameCol))) And _ Trim(UCase(Array1(i).Cells(n, strLNameCol))) = Trim(UCase(Array1(i).Cells(m, strLNameCol))) Then intDupFound = 1 Array2(i).Rows(intTotDB).Value = Array1(i).Rows(m).Value intTotDB = intTotDB + 1 Array1(i).Rows(m).Delete m = m - 1 totRows = totRows - 1 End If Else If Trim(UCase(Left(Array1(i).Cells(n, strFNameCol), 1))) = Trim(UCase(Left(Array1(i).Cells(m, strFNameCol), 1))) And _ Trim(UCase(Array1(i).Cells(n, strLNameCol))) = Trim(UCase(Array1(i).Cells(m, strLNameCol))) Then intDupFound = 1 Array2(i).Rows(intTotDB).Value = Array1(i).Rows(m).Value intTotDB = intTotDB + 1 Array1(i).Rows(m).Delete m = m - 1 totRows = totRows - 1 End If End If Next m If intDupFound = 1 Then Array2(i).Rows(intTotDB).Value = Array1(i).Rows(n).Value intTotDB = intTotDB + 1 Array1(i).Rows(n).Delete totRows = totRows - 1 n = n - 1 intDupFound = 0 End If End If Next n End_of_Data: MsgBox "Data Extracted" End Sub Thanks In Advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays to replace very slow loops ?
An array assiged as you have will be a 2 dimensional array.
Since you are picking up values. the array elements will not have properties like row and so forth. Since you delete a row when you find a match, you can then throw out your array since it will no longer match your worksheet. -- Regards, Tom Ogilvy "vbastarter" wrote in message ... Hi I had a post here asking if my loops that run very slowly can be made any quick. One suggestion was to replace with an Array. Now I'm new to VBA and have no idea how to go about Arrays. Below is my code where I'm finding dulicates based on name fields deleting from that and adding to another sheet. Here I have 2 text boxes to enter position of Names Fields and and an option to choose entire FName to be searched instead of just FName initial. Now all that I did is replace my r and k range with arrays but I get and error at this line : If Trim(UCase(Array1(i).Cells(n, strFNameCol))) = Trim(UCase(Array1(i).Cells(m, strFNameCol))) And _ Trim(UCase(Array1(i).Cells(n, strLNameCol))) = Trim(UCase(Array1(i).Cells(m, strLNameCol))) . It says runtime error "Runtime error 9 Subscript out of Range" My code as below: Private Sub CmdSubmitNames_Click() Dim r As Range, _ k As Range Dim sh As Excel.Worksheet Dim strFNameCol As String, _ strLNameCol As String Dim intCounter As Integer, _ intTotDB As Integer, _ totRows As Integer, _ intDupFound As Integer, _ intTotDB2 As Integer, _ i As Integer, _ intTotfile As Integer Dim Array1(), _ Array2() totRows = 1026 intCounter = 0 strFNameCol = TxtFNCol.Value strLNameCol = TxtLNCol.Value Set r = ActiveWorkbook.ActiveSheet.Range("A:AS") Set sh = ActiveWorkbook.Worksheets.Add Set k = sh.Range("A:AS") Array1 = r.Value Array2 = k.Value intTotDB = 1 n = 2 For n = 2 To 1000 If (Array1(i).Cells(n, strFNameCol)) < "" Or _ (Array1(i).Cells(n, strLNameCol)) < "" Then For m = n + 1 To 1000 If OptEntireFNSearch Then If Trim(UCase(Array1(i).Cells(n, strFNameCol))) = Trim(UCase(Array1( i).Cells(m, strFNameCol))) And _ Trim(UCase(Array1(i).Cells(n, strLNameCol))) = Trim(UCase(Array1(i).Cells(m, strLNameCol))) Then intDupFound = 1 Array2(i).Rows(intTotDB).Value = Array1(i).Rows(m).Value intTotDB = intTotDB + 1 Array1(i).Rows(m).Delete m = m - 1 totRows = totRows - 1 End If Else If Trim(UCase(Left(Array1(i).Cells(n, strFNameCol), 1))) = Trim(UCase(Left(Array1(i).Cells(m, strFNameCol), 1))) And _ Trim(UCase(Array1(i).Cells(n, strLNameCol))) = Trim(UCase(Array1(i).Cells(m, strLNameCol))) Then intDupFound = 1 Array2(i).Rows(intTotDB).Value = Array1(i).Rows(m).Value intTotDB = intTotDB + 1 Array1(i).Rows(m).Delete m = m - 1 totRows = totRows - 1 End If End If Next m If intDupFound = 1 Then Array2(i).Rows(intTotDB).Value = Array1(i).Rows(n).Value intTotDB = intTotDB + 1 Array1(i).Rows(n).Delete totRows = totRows - 1 n = n - 1 intDupFound = 0 End If End If Next n End_of_Data: MsgBox "Data Extracted" End Sub Thanks In Advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays to replace very slow loops ?
Tom Ogilvy wrote:
An array assiged as you have will be a 2 dimensional array. Since you are picking up values. the array elements will not have properties like row and so forth. Since you delete a row when you find a match, you can then throw out your array since it will no longer match your worksheet. It may well be that the OP is not yet up for working with VBA arrays, notwithstanding the dramatic speed of execution advantage they can provide. If he/she did want to mount the subject matter, here are a few comments. For starters, recognize that after Dim r As Range Set r = ActiveWorkbook.ActiveSheet.Range("A:AS") in the syntax r.Cells(n, strFNameCol) the Cells method is redundant; switch to r(n,strFNameCol) Then after Set r = ActiveWorkbook.ActiveSheet.Range("A:AS") Dim Array1 As Variant Array1 = r you can replace r(n,strFNameCol) with Array1(n,strFNameCol) to refer to the same element in the array that matches the corresponding element in the range. That is, with either the range or the array, n is the row index and strFNameCol is the column index. Another correction to be made (although it doesn't relate to the difference between looping through arrays and looping through ranges) is that in your code Trim(whatever. . .) should be Application.Trim(whatever...) The third point of significance is that r.Rows(m).Delete is not a syntax that works for arrays and a substitute needs to be crafted to delete a row of an array. Tom Ogilvy's point above about deleting a row when you find a match is a bit beside the mark. Of course the changed array will no longer match the worksheet; that's the point--you are not making the changes directly to the worksheet; that's why the code executes faster. If the code is rewritten to make the same changes to the array(s) that your prior code intends to make to the range(s), you could then easily transfer the arrays to the worksheet as ranges to replace the prior unchanged worksheets. It might also be that it is more efficient to just keep track of the rows to be deleted rather than deleting them in the arrays, and then delete the targeted rows directly from the worksheet once the targeting has been accomplished by looping through the arrays. It is not a trivial exercise and may well be beyond what the OP cares to deal with at present, despite the likely speed of execution improvement. If so, well and good. If not, if the OP posts back with an email address I will contact him to provide some additional guidance. Alan Beban |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays to replace very slow loops ?
Comment in line.
"Alan Beban" wrote in message ... For starters, recognize that after Dim r As Range Set r = ActiveWorkbook.ActiveSheet.Range("A:AS") in the syntax r.Cells(n, strFNameCol) the Cells method is redundant; switch to r(n,strFNameCol) Then after Set r = ActiveWorkbook.ActiveSheet.Range("A:AS") Dim Array1 As Variant Array1 = r you can replace r(n,strFNameCol) with Array1(n,strFNameCol) to refer to the same element in the array that matches the corresponding element in the range. That is, with either the range or the array, n is the row index and strFNameCol is the column index. Since OP has dimmed strFNameCol as String and gotten it from a control, good possibility it is a letter and would not work in the array. It would need to be converted to a number as implied by you use of "column index" Another correction to be made (although it doesn't relate to the difference between looping through arrays and looping through ranges) is that in your code Trim(whatever. . .) should be Application.Trim(whatever...) No reason to use Application.Trim if the intent is to remove spaces from the front and back. The VBA trim works fine for this. The third point of significance is that r.Rows(m).Delete is not a syntax that works for arrays and a substitute needs to be crafted to delete a row of an array. Tom Ogilvy's point above about deleting a row when you find a match is a bit beside the mark. Appears the OP's intent is to delete a row as soon as a duplicate is ound - not change the array. This would make the array used to make the decision not match the new layout of the data on the worksheet. There is no built in method for deleting a "row" in an array without reconstructing the whole array. Not sure why this makes the point beside the mark. Of course the changed array will no longer match the worksheet; that's the point--you are not making the changes directly to the worksheet; that's why the code executes faster. If the code is rewritten to make the same changes to the array(s) that your prior code intends to make to the range(s), you could then easily transfer the arrays to the worksheet as ranges to replace the prior unchanged worksheets. It might also be that it is more efficient to just keep track of the rows to be deleted rather than deleting them in the arrays, and then delete the targeted rows directly from the worksheet once the targeting has been accomplished by looping through the arrays. It is not a trivial exercise and may well be beyond what the OP cares to deal with at present, despite the likely speed of execution improvement. If so, well and good. If not, if the OP posts back with an email address I will contact him to provide some additional guidance. Alan Beban It seems to me, using a couple of dummy columns to identify the rows to delete and deleting them enmasse with Special cells would offer similar speed advantages without the complexity you appear to suggest. -- Regards, Tom Ogilvy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays to replace very slow loops ?
Tom Ogilvy wrote:
Comment in line. "Alan Beban" wrote in message ... For starters, recognize that after Dim r As Range Set r = ActiveWorkbook.ActiveSheet.Range("A:AS") in the syntax r.Cells(n, strFNameCol) the Cells method is redundant; switch to r(n,strFNameCol) Then after Set r = ActiveWorkbook.ActiveSheet.Range("A:AS") Dim Array1 As Variant Array1 = r you can replace r(n,strFNameCol) with Array1(n,strFNameCol) to refer to the same element in the array that matches the corresponding element in the range. That is, with either the range or the array, n is the row index and strFNameCol is the column index. Since OP has dimmed strFNameCol as String and gotten it from a control, good possibility it is a letter and would not work in the array. It would need to be converted to a number as implied by you use of "column index" Granted; it would have to be converted to a number. Another correction to be made (although it doesn't relate to the difference between looping through arrays and looping through ranges) is that in your code Trim(whatever. . .) should be Application.Trim(whatever...) No reason to use Application.Trim if the intent is to remove spaces from the front and back. The VBA trim works fine for this. Granted; depends on the OP's intention for Trim. The third point of significance is that r.Rows(m).Delete is not a syntax that works for arrays and a substitute needs to be crafted to delete a row of an array. Tom Ogilvy's point above about deleting a row when you find a match is a bit beside the mark. Appears the OP's intent is to delete a row as soon as a duplicate is ound - not change the array. This would make the array used to make the decision not match the new layout of the data on the worksheet. There is no built in method for deleting a "row" in an array without reconstructing the whole array. Not sure why this makes the point beside the mark. I thought it was fairly clear that my comment assumed that the code was rewritten to delete the row from the array within the loop, notwithstanding that that would not be by built-in functions. That was the point of my comment "The third point of significance is that r.Rows(m).Delete is not a syntax that works for arrays and a substitute needs to be crafted to delete a row of an array." Although "reconstructing the whole array" may sound daunting, it can often be accomplished by a fairly straightforward loop in memory. Of course the changed array will no longer match the worksheet; that's the point--you are not making the changes directly to the worksheet; that's why the code executes faster. If the code is rewritten to make the same changes to the array(s) that your prior code intends to make to the range(s), you could then easily transfer the arrays to the worksheet as ranges to replace the prior unchanged worksheets. It might also be that it is more efficient to just keep track of the rows to be deleted rather than deleting them in the arrays, and then delete the targeted rows directly from the worksheet once the targeting has been accomplished by looping through the arrays. It is not a trivial exercise and may well be beyond what the OP cares to deal with at present, despite the likely speed of execution improvement. If so, well and good. If not, if the OP posts back with an email address I will contact him to provide some additional guidance. Alan Beban It seems to me, using a couple of dummy columns to identify the rows to delete and deleting them enmasse with Special cells would offer similar speed advantages without the complexity you appear to suggest. If it would, then it should certainly be considered. I would suspect that since the identification of the targeted rows would continue to be accomplished by looping through ranges rather than looping through arrays, and only the individual deletions of each row would be replaced by the en masse deletion, similar speed advantages would not in fact be achieved; but maybe someone will be up for measuring that approach. Or the OP might simply implement it and see whether he/she achieves some improvement. I think elaboration of the general concept of looping through arrays instead of ranges is worthwhile because looping through memory is so often dramatically faster than looping through worksheet ranges; thanks for your comments. Alan Beban |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays to replace very slow loops ?
If it would, then it should certainly be considered. I would suspect
that since the identification of the targeted rows would continue to be accomplished by looping through ranges rather than looping through arrays, and only the individual deletions of each row would be replaced by the en masse deletion, similar speed advantages would not in fact be achieved; but maybe someone will be up for measuring that approach. Or the OP might simply implement it and see whether he/she achieves some improvement. I posted code in the original thread that would do it and no looping was required. It certainly isn't a new technique and is well proven in terms of speed. Here it is again: assume this can be determined by looking at the values in column A Sub DeleteDups() Dim rng As Range Columns(2).Insert Set rng = Range(Cells(1, 1), _ Cells(Rows.Count, 1).End(xlUp)) rng.Offset(0, 1).Formula = _ "=if(countif($A$1:A1,A1)1,na(),false)" rng.Offset(0, 1).SpecialCells(xlFormulas, _ xlErrors).EntireRow.Delete Columns(2).Delete End Sub -- Regards, Tom Ogilvy "Alan Beban" wrote in message ... Tom Ogilvy wrote: Comment in line. "Alan Beban" wrote in message ... For starters, recognize that after Dim r As Range Set r = ActiveWorkbook.ActiveSheet.Range("A:AS") in the syntax r.Cells(n, strFNameCol) the Cells method is redundant; switch to r(n,strFNameCol) Then after Set r = ActiveWorkbook.ActiveSheet.Range("A:AS") Dim Array1 As Variant Array1 = r you can replace r(n,strFNameCol) with Array1(n,strFNameCol) to refer to the same element in the array that matches the corresponding element in the range. That is, with either the range or the array, n is the row index and strFNameCol is the column index. Since OP has dimmed strFNameCol as String and gotten it from a control, good possibility it is a letter and would not work in the array. It would need to be converted to a number as implied by you use of "column index" Granted; it would have to be converted to a number. Another correction to be made (although it doesn't relate to the difference between looping through arrays and looping through ranges) is that in your code Trim(whatever. . .) should be Application.Trim(whatever...) No reason to use Application.Trim if the intent is to remove spaces from the front and back. The VBA trim works fine for this. Granted; depends on the OP's intention for Trim. The third point of significance is that r.Rows(m).Delete is not a syntax that works for arrays and a substitute needs to be crafted to delete a row of an array. Tom Ogilvy's point above about deleting a row when you find a match is a bit beside the mark. Appears the OP's intent is to delete a row as soon as a duplicate is ound - not change the array. This would make the array used to make the decision not match the new layout of the data on the worksheet. There is no built in method for deleting a "row" in an array without reconstructing the whole array. Not sure why this makes the point beside the mark. I thought it was fairly clear that my comment assumed that the code was rewritten to delete the row from the array within the loop, notwithstanding that that would not be by built-in functions. That was the point of my comment "The third point of significance is that r.Rows(m).Delete is not a syntax that works for arrays and a substitute needs to be crafted to delete a row of an array." Although "reconstructing the whole array" may sound daunting, it can often be accomplished by a fairly straightforward loop in memory. Of course the changed array will no longer match the worksheet; that's the point--you are not making the changes directly to the worksheet; that's why the code executes faster. If the code is rewritten to make the same changes to the array(s) that your prior code intends to make to the range(s), you could then easily transfer the arrays to the worksheet as ranges to replace the prior unchanged worksheets. It might also be that it is more efficient to just keep track of the rows to be deleted rather than deleting them in the arrays, and then delete the targeted rows directly from the worksheet once the targeting has been accomplished by looping through the arrays. It is not a trivial exercise and may well be beyond what the OP cares to deal with at present, despite the likely speed of execution improvement. If so, well and good. If not, if the OP posts back with an email address I will contact him to provide some additional guidance. Alan Beban It seems to me, using a couple of dummy columns to identify the rows to delete and deleting them enmasse with Special cells would offer similar speed advantages without the complexity you appear to suggest. If it would, then it should certainly be considered. I would suspect that since the identification of the targeted rows would continue to be accomplished by looping through ranges rather than looping through arrays, and only the individual deletions of each row would be replaced by the en masse deletion, similar speed advantages would not in fact be achieved; but maybe someone will be up for measuring that approach. Or the OP might simply implement it and see whether he/she achieves some improvement. I think elaboration of the general concept of looping through arrays instead of ranges is worthwhile because looping through memory is so often dramatically faster than looping through worksheet ranges; thanks for your comments. Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow Excel Navigation with Up / Down Arrow and slow scrolling | Excel Discussion (Misc queries) | |||
slow replace function | Excel Worksheet Functions | |||
How come replace is so slow? | Excel Discussion (Misc queries) | |||
Formula Arrays VERY SLOW in Excel 2002 | Excel Worksheet Functions | |||
Excel 2000 Slow Loops | Excel Programming |