+ Reply to Thread
Results 1 to 19 of 19

Make code faster/better best practices

Hybrid View

  1. #1
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    There is a small time overhead involved every time you jump backwards and forwards to the Excel Applications from the VBE and vice versa. So depending on the number of cells you're processing, it might be better to read all the cell values into a VBA array, process them entirely within the VBE and then finally write the results back to the Excel App.

    HTH

  2. #2
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712
    Hi and thanks.

    Just to clarify, did you mean to read BOTH lists into individual arrays.........and process them using the arrays.

    David

  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712
    Anyone have a good / fast way to compare to arrays ?

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    Maybe as attached. The lists of old and new students should be culled for duplicates and trimmed before running.

    If you try several methods, I'd be curious as know the timing results.

    You could also just use two columns of formulas ...
    Attached Files Attached Files
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712
    thanks SHG............ i will check it out.

    DAvid

  6. #6
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712
    Thanks SHG. I am still reviewing the code you sent. My skill level not quite the same as yours but i am good enough to review it and learn some things. So thanks.

  7. #7
    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

+ Reply to Thread

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