Detecting page breaks in VBA only works when stepping through code

Question

I created a function that returns the row number where a page break will occur on a sheet. The function works perfectly when I step through each line of code but acts differently when I run the code without any breakpoints. Additionally, the subroutine that uses this function calls it twice.

When running the code without any breaks or "Step into" (F8), the first function call always works correctly, but the second function call is always incorrect. When running the Subroutine via stepping through each line of code, the first and second function call produce the correct results. Why is this happening?

Private Function COA_FindNextPageBreak(r As Long) As Long
'Purpose: Find the next pagebreak on COA sheet,
'Function returns the first cell address on page (first row of new page)
'r is the last row of data on COA sheet.
Dim ws As Worksheet
Dim pb As HPageBreak    'Page Break loc = top left cell.
Set ws = ThisWorkbook.Worksheets("CORE_COA")

'Add cell value to the next 50 rows (col A)
'HPageBreaks detects page breaks within the rows
'all rows after row "r" have row height = 10
ws.Range(ws.Cells(r + 1, "A"), ws.Cells(r + 51, "A")).Value = "x"

With ws
    'Find the next page break after the last row of data
    For Each pb In ws.HPageBreaks
        Debug.Print "pb row: " & pb.Location.Row
        
        If pb.Location.Row > r Then
            COA_FindNextPageBreak = pb.Location.Row
            Exit For
        End If
    Next pb
End With

'Remove the x's - No longer needed.
ws.Range(ws.Cells(r + 1, "A"), ws.Cells(r + 51, "A")).Value = ""
End Function

Below are the results of running the subroutine twice. First print out is by running the code (no steps/breaks) and the second print out is stepping through the subroutine and function calls. The first time the function in question (COA_FindNextPageBreak) is called, it is called through a wrapper function called COA_FindLastRowOnPage that returns the row number from COA_FindNextPageBreak minus 1. The second function call, when running the code via F5, always results in FRNewPage equaling 0. There is something going on with HPageBreaks, but I don't know why.

Code ran via F5
LRPage - FindLastRowOnPage (Lrow = 34)
r: 34
pb row: 21
pb row: 40
LRPage: 39
(LPRage == 39)

FRNewPage - FindNextPageBreak (Lrow = 34)
r: 34
pb row: 21
FRNewPage: 0
(FRNewPage = 40)

-----------------------------------------------
Code ran via Step Into (F8)
LRPage - FindLastRowOnPage (Lrow = 34)
r: 34
pb row: 21
pb row: 40
LRPage: 39
(LPRage == 39)

FRNewPage - FindNextPageBreak (Lrow = 34)
r: 34
pb row: 21
pb row: 40
FRNewPage: 40
(FRNewPage = 40)

Answer 1

May not be the solution, but thinking that page-breaks need to be recalculated based on the changes, and unless you add Doevents, EXCEL will not be able to do it during code execution, you should try adding:

Application.Calculate
Do While 
  Application.CalculationState <> xlDone
  DoEvents
Loop

between the last update of values/formats and the loop for page breaks.