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