Results 1 to 19 of 19

Make code faster/better best practices

Threaded View

  1. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    A couple of changes to ignore case in name comparisons, and to avoid an error if the number of adds or drops is zero:
    Sub AddRem(rCur As Range, rOld As Range, rAdd As Range, rDrp As Range)
        Dim nCur    As Long     ' current students
        Dim nOld    As Long     ' prior students
    
        Dim asAdd() As String   ' array of new
        Dim asDrp() As String   ' array of drops
    
        Dim iCur    As Long     ' index to current students
        Dim iOld    As Long     ' index to prior students
    
        Dim nAdd    As Long     ' number of new students
        Dim nDrp    As Long     ' number of drops
    
        nCur = rCur.Count
        nOld = rOld.Count
    
        ReDim asAdd(1 To nCur)    ' max  adds is everyone here this year
        ReDim asDrp(1 To nOld)    ' max drops is everyone here last year
        
        ' clear output area
        rAdd.Resize(nCur).ClearContents
        rDrp.Resize(nOld).ClearContents
        
        ' sort both lists
        rCur.Sort Key1:=rCur(1), Order1:=xlAscending, MatchCase:=False, Header:=xlNo
        rOld.Sort Key1:=rOld(1), Order1:=xlAscending, MatchCase:=False, Header:=xlNo
    
        iCur = 1
        iOld = 1
        Do
            Select Case StrComp(rCur(iCur).Text, rOld(iOld).Text, vbTextCompare)
                Case -1    ' cur < old; current is new
                    'rCur(iCur).Select   ' debug
                    nAdd = nAdd + 1
                    asAdd(nAdd) = rCur(iCur).Text
                    iCur = iCur + 1
                
                Case 0  ' cur = old; continuing student
                    iCur = iCur + 1
                    iOld = iOld + 1
                
                Case 1  ' old < cur; old is a drop
                    'rOld(iOld).Select   ' debug
                    nDrp = nDrp + 1
                    asDrp(nDrp) = rOld(iOld).Text
                    iOld = iOld + 1
            End Select
            If iCur > nCur Or iOld > nOld Then Exit Do
        Loop
    
        ' add any remaining current students to add list
        For iCur = iCur To nCur
            nAdd = nAdd + 1
            asAdd(nAdd) = rCur(iCur).Text
        Next iCur
    
        ' add any remaining old students to drop list
        For iOld = iOld To nOld
            nDrp = nDrp + 1
            asDrp(nDrp) = rOld(iOld).Text
        Next iOld
    
        ' output each list if not empty
        If nAdd > 0 Then
            ReDim Preserve asAdd(1 To nAdd)
            rAdd.Resize(nAdd).Value = WorksheetFunction.Transpose(asAdd)
        End If
        
        If nDrp > 0 Then
            ReDim Preserve asDrp(1 To nDrp)
            rDrp.Resize(nDrp).Value = WorksheetFunction.Transpose(asDrp)
        End If
    End Sub
    Last edited by shg; 12-29-2008 at 09:20 PM. Reason: correct compare operator

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1