1

Doing a little research one can find a few examples of coders complaining that in VBA one can get two different outcomes if you either run the code (F5) or you step through it (F8).

SO: VBA code only works when stepping through

Macro works in stepping through, but not when I run it - Excel 2003

I myself now had to face the same bug. Fast forward a day of connecting my head to my keyboard I have boiled my bug down to its core elements.

Could someone explain why when I run (F5) the code at the bottom of this post I get the following output.

================================
Sheet1
Book1
Microsoft Excel
================
***Original***
A
A
A
***After Works***
B
B
B
***After Does Not Work***
B
B
C

But when I step through it (F8) I get the following.

================================
Sheet1
Book1
Microsoft Excel
================
***Original***
A
A
A
***After Works***
B
B
B
***After Does Not Work***
C
C
C

Bellow is my code after I boiled away most of my lines of code that worked as intended. I know the issue is with the lines containing ".PrintCommunication" and removing it will make the code act as intended. I'm curious as to why this is? In the past I have experienced this bug (in a very different context) and was unable to resolve it. I'm hoping understanding this one will help me in the future.

Sub test()

    Dim txtEmpty As String
    txtEmpty = ""

    Dim txtA As String
    txtA = "A"

    Dim txtB As String
    txtB = "B"

    Dim txtC As String
    txtC = "C"

    Debug.Print "================" & "================"

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    'Set ws = ActiveSheet
    Debug.Print ws.Name

    Dim wk As Workbook
    Set wk = ws.Parent
    Debug.Print wk.Name

    Dim ap As Application
    Set ap = wk.Parent
    Debug.Print ap.Name
    Debug.Print "================"

    Call setPageSetupWorks(ap, ws, txtA, txtA, txtA)

    Debug.Print "***Original***"
    Call debugFooters(ws)
    Call setPageSetupWorks(ap, ws, txtB, txtB, txtB)
    Debug.Print "***After Works***"
    Call debugFooters(ws)
    Call setPageSetupDoesNotWork(ap, ws, txtC, txtC, txtC)
    Debug.Print "***After Does Not Work***"
    Call debugFooters(ws)
End Sub


Sub setPageSetupWorks(ap As Application, ws As Worksheet, txtLF As String, txtCF As String, txtRF As String)

    ap.PrintCommunication = False
    With ws.PageSetup
        .LeftFooter = txtLF
    End With
    ap.PrintCommunication = True

    ap.PrintCommunication = False
    With ws.PageSetup
        .CenterFooter = txtCF
    End With
    ap.PrintCommunication = True

    ap.PrintCommunication = False
    With ws.PageSetup
        .RightFooter = txtRF
    End With
    ap.PrintCommunication = True

End Sub

'This does not work. Left and Center footers don't get updated.
'... ... Unless you put a break point and go step by step!?!?!
Sub setPageSetupDoesNotWork(ap As Application, ws As Worksheet, txtLF As String, txtCF As String, txtRF As String)

    ap.PrintCommunication = False
    With ws.PageSetup
        .LeftFooter = txtLF
        .CenterFooter = txtCF
        .RightFooter = txtRF
    End With
    ap.PrintCommunication = True

End Sub

Sub debugFooters(ws As Worksheet)

    Debug.Print ws.PageSetup.LeftFooter
    Debug.Print ws.PageSetup.CenterFooter
    Debug.Print ws.PageSetup.RightFooter

End Sub
Tolure
  • 859
  • 1
  • 14
  • 34

0 Answers0