Who is this article for?

All Ideagen Quality Management Users with document permissions.

Ideagen Quality Management site accessis needed.

To make answering this question easier, this article is split into three sections so that you can either walk through the steps in sequence or you can jump to the section relevant to what you are looking for:

1. Question
2. Answer
3. Further Reading

1. Question

What does a complete and working Excel macro look like? 

2. Answer 

Below is a complete Excel macro with working header and footer variables. These variables can always be moved to different positions in the headers or footers.

Public Function DocProperty(property As String) As String
   Dim WB As Workbook
   On Error Resume Next
   If TypeOf Application.Caller Is Range Then
       Set WB = Application.Caller.Parent.Parent
   Else
       Set WB = ActiveWorkbook
   End If
   DocProperty = WB.CustomDocumentProperties(property)
   WB.Saved = True
End Function
Public Sub Auto_Open()
' - This macro will refresh any cells where the formula
' - is a call to the DocProperty function
'
   Dim ws As Worksheet
   Dim cell As Range 
   For Each ws In ThisWorkbook.Worksheets
      HeaderFooter ws
       For Each cell In ws.UsedRange.Cells
           If InStr(cell.Formula, "DocProperty") > 0 Then
               cell.Formula = cell.Formula
           End If
       Next cell
   Next ws
End Sub

Private Sub HeaderFooter(wks As Worksheet)
Dim sValue As String
Dim sFooter As String
wks.PageSetup.LeftHeader = "&L&B&I" & "Your Company Name Here"
wks.PageSetup.RightHeader = "&R" & "Document Title: " & Chr(13) _
& DocProperty("##TITLE##")
wks.PageSetup.LeftFooter = "&L" & "Document ID: " & DocProperty("##ID##") & Chr(13) _
& "Document Status:  " & DocProperty("##STATUS##") & Chr(13) _
& "Refer to Ideagen Quality Management for controlled version."
wks.PageSetup.RightFooter = "&R" & "Revision Number: " & DocProperty("##REVISION##") & Chr(13) _
& "Date Published: " & DocProperty("##DATE_PUBLISHED##") & Chr(13) _
& "Page: &P of &N"

End Sub

3. Further Reading

This section will provide important links to further troubleshooting and resources. 

1. External Article - Microsoft article on VBA code.