Writing many cells to sheet causes Excel to not respond, replacing vlookups [closed]

Question

I have found that my code causes my Excel to 'not respond' when it gets to this for loop, For thisRow = 2 To lastWSrow(outWS). I'm guessing this is because I am writing cell by cell and the data is too much for excel to handle.

My code essentially does 55 of vlookups on sheet2. It would be similar to doing vlookup(c2&y2,Sheet1!I:J,2,false) but manually it takes long to do.

Could anyone look at this loop and help me optimize this area? I have left only the part needed adjustment. I have put comments on each line to provide description.

outws = sheet2

For thisRow = 2 To lastWSrow(outWS) '2 to last row of sheet2
    For thisCol = 1 To UBound(mappings, 1)

        'create unique key
        thisScen = outWS.Cells(thisRow, posIDcol).Value & "|" & mappings(thisCol, 1)

        'search
        thisDataRow = findInArrCol(thisScen, 1, scenData)

        'write to sheet2
        If thisDataRow = 0 Then
            If outWS.Cells(thisRow, posUnitsCol).Value <> 0 Then 'missing scenario
                outWS.Cells(thisRow, mappings(thisCol, 3)).Value = "No data"
                outWS.Cells(thisRow, mappings(thisCol, 3)).Value = 0

            End If
        Else
            If mappings(thisCol, 1) = "irpv01|PV01_Swap_1M" Then
                outWS.Cells(thisRow, PV01_1MCol).Value = outWS.Cells(thisRow, PV01_1MCol).Value + scenData(thisDataRow, 2) * scenData(thisDataRow, 3) 'since map PV01_0D to PV01_1m already, sum PV01_1M to that column in database.
            Else
                outWS.Cells(thisRow, mappings(thisCol, 3)).Value = scenData(thisDataRow, 2) * scenData(thisDataRow, 3) 'need to scale by position units to get correct risk
            End If
        End If

    Next thisCol
Next thisRow

findInArrCol

Public Function findInArrCol(matchVal As Variant, matchCol As Long, sortedArr() As Variant, Optional nearest As String) As Long

    Dim low As Long, mid As Long, high As Long

    findInArrCol = 0
    low = 1
    high = UBound(sortedArr, 1)
    Do While low <= high
        mid = (low + high) / 2
        If sortedArr(mid, matchCol) = matchVal Then
            findInArrCol = mid
            Exit Function
        ElseIf sortedArr(mid, matchCol) < matchVal Then
            low = mid + 1
        Else
            high = mid - 1
        End If
    Loop

    If findInArrCol = 0 Then
        If nearest = "lessThan" Then
            findInArrCol = WorksheetFunction.Max(high, 1)
        End If
        If nearest = "greaterThan" Then
            findInArrCol = WorksheetFunction.Min(low, UBound(sortedArr, 1))
        End If
    End If

End Function

Answer 1

As was explained in comments elsewhere, the Excel VBA thread is sending messages to the WM message queue to process information, but the same thread is running the for/next loop(s) and will not begin as you had the code until the loops are complete. By inserting a DoEvents, it causes the VBA code to perform slower because it then allocates some time to the O/S to take care of other pending WM messages and returns back to the VBA code. Too many DoEvents will greatly slow down your code processing and can even be harmful in some cases where another process can actually take over (hog) more processor time. The solution is to use DoEvents sparingly to allow Excel to process the rows a few at a time. What the ideal number is varies by situation, but you can experiment with various batch sizes determined by using modulo arithmetic.

Modify your loop in this way:

        Next thisCol
        If (thisRow Mod 100) = 0 Then
            DoEvents
        End If
    Next thisRow

You can tweak this number by replacing 100 with 10, 50, 250, 500, etc. to see what provides the most desirable result.

Answer 2

A couple of points:

At one stage, you write to the same cell twice. I don't know how many times you loop, but this will certainly add to the time:

                    outWS.Cells(thisRow, mappings(thisCol, 3)).Value = "No data"
                    outWS.Cells(thisRow, mappings(thisCol, 3)).Value = 0

We can't see how you findInArrCol(thisScen, 1, scenData). For all we know, this could be an expensive and inefficient operation. I would guess that this is looping within a loop (that is also within a loop) - O(n2) at least? Especially if your "find" is looping through a range instead of an array.

Without enough context, cannot provide any further help than that.

Answer 3

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

' Place actual code between the above and below

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True    

The above will generally fix what you are experiencing.

The first part turns off the auto calculation that happens after any change to the sheet. It also turns off screen updating, so it isn't showing you the changes that are taking place.

Last part turns both back on, so the document behaves as normal.