title: Excel VBA - Footer doesn't update correctly through the code
body: <p>I have come across a strange behavior while writing code to update custom number in footer through VBA. I am using Excel 365.</p>
  <p>Objective: To write custom page numbers to each sheet.</p>
  <p>I have written below code which works to an extent.</p>
  <pre><code>Sub TestHdrFooter()
  Dim wksCurr As Worksheet
  Dim lngPageBase As Long, lngShtLen As Long
  Dim strBaseDocNumb As String, strRev As String, strExpr As String
  
  lngPageBase = 2
  strBaseDocNumb = &quot;DOC-99-100000-AA-9999-99999&quot;
  strRev = &quot;01&quot;
  lngShtLen = Len(&quot;0005&quot;)
  
  For i = 1 To ThisWorkbook.Sheets.Count
      Set wksCurr = ThisWorkbook.Sheets(i)
      With wksCurr.PageSetup
          '\\ Reset footer if something has been accidentally entered
          .LeftFooter = &quot;&quot;
          .CenterFooter = &quot;&quot;
          .RightFooter = &quot;&quot;
          
          If Len(CStr(lngPageBase)) = 1 Then
              .RightFooter = strBaseDocNumb &amp; &quot;-&quot; &amp; Application.Rept(&quot;0&quot;, lngShtLen - 1) &amp; &quot; &amp;[Page]+&quot; &amp; lngPageBase &amp; &quot;-&quot; &amp; strRev
          Else
              .RightFooter = strBaseDocNumb &amp; &quot;-&quot; &amp; Application.Rept(&quot;0&quot;, lngShtLen - 2) &amp; &quot; &amp;[Page]+&quot; &amp; lngPageBase &amp; &quot;-&quot; &amp; strRev
          End If
          
          lngPageBase = lngPageBase + .Pages.Count
      End With
  Next i
  End Sub
  </code></pre>
  <p>However, it doesn't update correctly directly as when I open it through <code>Page Setup&gt;&gt;Header/Footer</code> I see it appears as below without the opening bracket which is available in the code.</p>
  <p><a href="https://i.stack.imgur.com/VeRqu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VeRqu.png" alt="enter image description here" /></a></p>
  <p>Then comes the stranger part. When I open the &quot;custom Footer&quot; dialog on the form, it shows the missing bracket and then simply pressing &quot;OK&quot; when closing this dialog updates everything correctly without having to change anything! I am not able to guess the reason for this behavior.</p>
  <p><a href="https://i.stack.imgur.com/pInLy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pInLy.png" alt="enter image description here" /></a></p>
  <p>Questions:</p>
  <ol>
  <li><p>Is there anything wrong with my VBA code that needs to be amended?</p>
  </li>
  <li><p>Is there any way to force refreshing the expression that appears in second screenshoot if code is correct?</p>
  </li>
  </ol>