0

I ran into something I've not seen before, Application.ScreenUpdating = False, causing a sub to slightly misbehave. It doesn't throw an error, but it causes an off by one error in the result when turned on.

    Try
        'Globals.ThisWorkbook.Application.ScreenUpdating = False
        'if this on sub behaves unexpectedly
        For n = 13 To Globals.ThisWorkbook.Worksheets.Count

            Globals.ThisWorkbook.Worksheets(n).Activate()
            Call ESLAUpdateReportPosition(True)

        Next
        'Globals.ThisWorkbook.Application.ScreenUpdating = True

    Catch ex As Exception
        '...Error Handling
    End Try

Again, no exception is thrown, the outcome of the is just slightly wrong. The place where I suspect it is breaking within the methods is:

   SumCount1 = CurrentSheet.Cells(2, 6).value
Atl LED
  • 656
  • 2
  • 8
  • 31

1 Answers1

0

The problem is in fact in trying to take that value. Since that cell in the workbook is counting the number of times an item is used in a column, it isn't updating until the entire For loop is complete. Thus I could suppress screen updating for 1 iteration and have it work, but when I had to change multiple sheets, the updating of that value failed (though it did seem to update a few times with many iterations).

The fix for this is quite simple, tell Excel to "refresh" or calculate the values in the formulas you need. So for the above example you would use:

    CurrentSheet.Cells(2, 6).Calculate()
    SumCount1 = CurrentSheet.Cells(2, 6).value

Now you need to be careful, as this will not cause an cells referenced within that cell to update (if they have formulas as well). If you need to update a bunch of cells you can update a whole sheet by not specifying a cell: .Cells.Calculate or you could do re-calculate everything with Application.CalculateFull()

Atl LED
  • 656
  • 2
  • 8
  • 31