How to Standardize Repeating Business Documents Across Teams
Standardize recurring documents with one visible operating contract: what data enters, which templates are approved, how files are named, who reviews releases, and how changes reach every team.
Quick answer: standardize the operating contract before the files
A repeatable document becomes a team standard only when the same source fields, approved template release, naming rule, output format, reviewer, and change path are used consistently.
Group truly equivalent outputs into document families. Define one contract per family. Register every team workflow against that contract. Pilot representative records. Release only after ownership and review are explicit. Audit the register regularly for drift.
Define six layers of the shared standard
1. Document family
State which outputs are genuinely equivalent. Similar appearance does not prove that teams share the same purpose, fields, review, or retention needs.
2. Data contract
Define exact field names, required and optional values, IDs, display-ready dates and amounts, image references, and allowed statuses.
3. Template contract
Name the approved DOCX files, destinations, styles, blank behavior, maximum tested content, and template version.
4. Output contract
Define file naming, DOCX or PDF output, folder ownership, overwrite rules, completion status, and exception handling.
5. Review contract
Name the process owner and review owner, blocking defects, evidence to retain, and the authority that approves a release.
6. Change contract
Define how a field, template, naming rule, or module change is proposed, tested, released, communicated, and retired.
Standardize what is shared and preserve real differences
Good candidates for one family
The teams use the same business purpose, data fields, template layout, output rule, reviewer role, and release criteria. Differences are limited to record values.
Keep separate families
The outputs have different legal purpose, mandatory wording, review authority, template set, retention rule, or downstream consumer. Combining them would hide a real control difference.
A shared drive location does not identify who approves content, investigates failures, coordinates changes, or retires an obsolete release.
Avoid the universal template
One template filled with team-specific conditions can become harder to test than several explicit families.
Avoid local silent copies
A centrally approved template is ineffective if operators continue using renamed desktop copies.
Build a standards register that teams can actually maintain
Repeated rows for the same DocumentFamily must carry the same workflow shape, template list, template version, data-contract version, and output-name pattern.
A–F: identity, ownership, and shape
WorkflowID; DocumentFamily; TeamName; ProcessOwner; WorkflowShape; TemplateFiles.
G–L: version and control state
TemplateVersion; DataContractVersion; OutputNamePattern; ReviewOwner; ReleaseStatus; Notes.
Use FACTORY or CONTRACTOR for WorkflowShape. Use DRAFT, PILOT, APPROVED, or RETIRED for ReleaseStatus. Separate Contractor template filenames with a literal vertical bar. Store WorkflowID as a true text value.
Move changes through one visible release process
DRAFT
Define the proposed contract and owner. Do not present it as a production standard.
PILOT
Test normal, long, blank, image, formatting, and failure cases with representative source records.
APPROVED
Record the reviewer, publish the approved files, communicate the adoption date, and preserve release evidence.
RETIRED
Record the replacement or retention decision. Prevent the retired release from remaining the easiest copy to find.
Free VBA helper: audit the cross-team standards register
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Create StandardsRegister with the exact A–L headers above. Put approved DOCX files in StandardsAssets\Templates beside the workbook.
Exact contract values
Rejects altered headers, normalized statuses, leading or trailing spaces, control characters, missing required values, and duplicate workflow IDs.
Family drift
Compares shape, template list, template version, data-contract version, and output pattern across every row in the same document family.
Safe template presence check
Rejects unsafe filenames and wrong extensions before checking the configured template folder. It does not open or authenticate DOCX content.
Approval ownership
Requires a ReviewOwner for APPROVED rows and warns when the process and review owners are the same.
Option Explicit
Private Const REGISTER_SHEET As String = "StandardsRegister"
Private Const AUDIT_PREFIX As String = "StandardsAudit_"
Private Const TEMPLATE_ROOT As String = "\StandardsAssets\Templates"
Private Const FIRST_DATA_ROW As Long = 2
Private Const FILE_ATTRIBUTES As Long = 39
Private Const SHAPE_FACTORY As String = "FACTORY"
Private Const SHAPE_CONTRACTOR As String = "CONTRACTOR"
Private Const STATUS_DRAFT As String = "DRAFT"
Private Const STATUS_PILOT As String = "PILOT"
Private Const STATUS_APPROVED As String = "APPROVED"
Private Const STATUS_RETIRED As String = "RETIRED"
Private Const COL_WORKFLOW_ID As Long = 1
Private Const COL_DOCUMENT_FAMILY As Long = 2
Private Const COL_TEAM_NAME As Long = 3
Private Const COL_PROCESS_OWNER As Long = 4
Private Const COL_WORKFLOW_SHAPE As Long = 5
Private Const COL_TEMPLATE_FILES As Long = 6
Private Const COL_TEMPLATE_VERSION As Long = 7
Private Const COL_DATA_VERSION As Long = 8
Private Const COL_OUTPUT_PATTERN As Long = 9
Private Const COL_REVIEW_OWNER As Long = 10
Private Const COL_RELEASE_STATUS As Long = 11
Private Const COL_NOTES As Long = 12
Public Sub AuditCrossTeamDocumentStandards()
Dim wb As Workbook
Dim registerWs As Worksheet
Dim auditWs As Worksheet
Dim lastCell As Range
Dim lastRow As Long
Dim rowNumber As Long
Dim auditRow As Long
Dim workflowId As String
Dim documentFamily As String
Dim teamName As String
Dim processOwner As String
Dim workflowShape As String
Dim templateFiles As String
Dim templateVersion As String
Dim dataVersion As String
Dim outputPattern As String
Dim reviewOwner As String
Dim releaseStatus As String
Dim notesText As String
Dim workflowRows As Object
Dim familyContracts As Object
Dim familyFirstRows As Object
Dim contractText As String
Dim errorCount As Long
Dim warningCount As Long
Dim approvedCount As Long
Dim auditName As String
Dim errorMessage As String
On Error GoTo CleanFail
Set wb = ThisWorkbook
If Len(wb.Path) = 0 Then
Err.Raise vbObjectError + 3500, , _
"Save this audit workbook as .xlsm before running the audit."
End If
If wb.ReadOnly Then
Err.Raise vbObjectError + 3501, , _
"Open a writable copy of the audit workbook."
End If
If wb.ProtectStructure Then
Err.Raise vbObjectError + 3502, , _
"Unprotect the workbook structure before adding an audit sheet."
End If
Set registerWs = RequireWorksheet(wb, REGISTER_SHEET)
ValidateHeaders registerWs
Set lastCell = registerWs.Range("A:L").Find(What:="*", _
After:=registerWs.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False)
If lastCell Is Nothing Then
Err.Raise vbObjectError + 3503, , _
"The StandardsRegister sheet is empty."
End If
If lastCell.Row < FIRST_DATA_ROW Then
Err.Raise vbObjectError + 3503, , _
"The StandardsRegister sheet has no workflow rows."
End If
lastRow = lastCell.Row
Set workflowRows = CreateObject("Scripting.Dictionary")
workflowRows.CompareMode = vbTextCompare
Set familyContracts = CreateObject("Scripting.Dictionary")
familyContracts.CompareMode = vbTextCompare
Set familyFirstRows = CreateObject("Scripting.Dictionary")
familyFirstRows.CompareMode = vbTextCompare
auditName = NextAuditSheetName(wb)
Set auditWs = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
auditWs.Name = auditName
PrepareAuditSheet auditWs
auditRow = 2
For rowNumber = FIRST_DATA_ROW To lastRow
If WorksheetFunction.CountA(registerWs.Range( _
registerWs.Cells(rowNumber, 1), _
registerWs.Cells(rowNumber, 12))) = 0 Then
GoTo NextRow
End If
workflowId = ReadRequiredText(registerWs.Cells(rowNumber, COL_WORKFLOW_ID), _
"WorkflowID", rowNumber, auditWs, auditRow, errorCount, warningCount)
documentFamily = ReadRequiredText(registerWs.Cells(rowNumber, COL_DOCUMENT_FAMILY), _
"DocumentFamily", rowNumber, auditWs, auditRow, errorCount, warningCount)
teamName = ReadRequiredText(registerWs.Cells(rowNumber, COL_TEAM_NAME), _
"TeamName", rowNumber, auditWs, auditRow, errorCount, warningCount)
processOwner = ReadRequiredText(registerWs.Cells(rowNumber, COL_PROCESS_OWNER), _
"ProcessOwner", rowNumber, auditWs, auditRow, errorCount, warningCount)
workflowShape = ReadRequiredText(registerWs.Cells(rowNumber, COL_WORKFLOW_SHAPE), _
"WorkflowShape", rowNumber, auditWs, auditRow, errorCount, warningCount)
templateFiles = ReadRequiredText(registerWs.Cells(rowNumber, COL_TEMPLATE_FILES), _
"TemplateFiles", rowNumber, auditWs, auditRow, errorCount, warningCount)
templateVersion = ReadRequiredText(registerWs.Cells(rowNumber, COL_TEMPLATE_VERSION), _
"TemplateVersion", rowNumber, auditWs, auditRow, errorCount, warningCount)
dataVersion = ReadRequiredText(registerWs.Cells(rowNumber, COL_DATA_VERSION), _
"DataContractVersion", rowNumber, auditWs, auditRow, errorCount, warningCount)
outputPattern = ReadRequiredText(registerWs.Cells(rowNumber, COL_OUTPUT_PATTERN), _
"OutputNamePattern", rowNumber, auditWs, auditRow, errorCount, warningCount)
reviewOwner = ReadOptionalText(registerWs.Cells(rowNumber, COL_REVIEW_OWNER), _
"ReviewOwner", rowNumber, auditWs, auditRow, errorCount, warningCount)
releaseStatus = ReadRequiredText(registerWs.Cells(rowNumber, COL_RELEASE_STATUS), _
"ReleaseStatus", rowNumber, auditWs, auditRow, errorCount, warningCount)
notesText = ReadOptionalText(registerWs.Cells(rowNumber, COL_NOTES), _
"Notes", rowNumber, auditWs, auditRow, errorCount, warningCount)
RequireTextValue registerWs.Cells(rowNumber, COL_WORKFLOW_ID), _
"WorkflowID", rowNumber, auditWs, auditRow, errorCount, warningCount
ValidateWorkflowId workflowId, rowNumber, workflowRows, auditWs, auditRow, _
errorCount, warningCount
ValidateExactValue workflowShape, Array(SHAPE_FACTORY, SHAPE_CONTRACTOR), _
"WorkflowShape", "Use FACTORY or CONTRACTOR exactly.", rowNumber, _
auditWs, auditRow, errorCount, warningCount
ValidateExactValue releaseStatus, _
Array(STATUS_DRAFT, STATUS_PILOT, STATUS_APPROVED, STATUS_RETIRED), _
"ReleaseStatus", _
"Use DRAFT, PILOT, APPROVED, or RETIRED exactly.", rowNumber, _
auditWs, auditRow, errorCount, warningCount
ValidateTemplateList wb.Path & TEMPLATE_ROOT, templateFiles, workflowShape, _
rowNumber, auditWs, auditRow, errorCount, warningCount
ValidateOutputPattern outputPattern, rowNumber, auditWs, auditRow, _
errorCount, warningCount
ValidateApproval releaseStatus, processOwner, reviewOwner, notesText, _
rowNumber, auditWs, auditRow, errorCount, warningCount
If releaseStatus = STATUS_APPROVED Then approvedCount = approvedCount + 1
If Len(documentFamily) > 0 And Len(workflowShape) > 0 And _
Len(templateFiles) > 0 And Len(templateVersion) > 0 And _
Len(dataVersion) > 0 And Len(outputPattern) > 0 Then
contractText = BuildContract(workflowShape, templateFiles, _
templateVersion, dataVersion, outputPattern)
ValidateFamilyContract documentFamily, contractText, rowNumber, _
familyContracts, familyFirstRows, auditWs, auditRow, _
errorCount, warningCount
End If
NextRow:
Next rowNumber
FinishAuditSheet auditWs, errorCount, warningCount, approvedCount
wb.Save
MsgBox "Standards audit complete." & vbCrLf & _
"Errors: " & errorCount & vbCrLf & _
"Warnings: " & warningCount & vbCrLf & _
"Approved rows: " & approvedCount & vbCrLf & _
"Report: " & auditName, _
IIf(errorCount = 0, vbInformation, vbExclamation), _
"Cross-team document standards"
Exit Sub
CleanFail:
errorMessage = Err.Description
MsgBox "The standards audit stopped: " & errorMessage, _
vbCritical, "Cross-team document standards"
End Sub
Private Function RequireWorksheet(ByVal wb As Workbook, _
ByVal sheetName As String) As Worksheet
On Error Resume Next
Set RequireWorksheet = wb.Worksheets(sheetName)
On Error GoTo 0
If RequireWorksheet Is Nothing Then
Err.Raise vbObjectError + 3510, , _
"Required worksheet not found: " & sheetName
End If
End Function
Private Sub ValidateHeaders(ByVal ws As Worksheet)
Dim expected As Variant
Dim columnNumber As Long
Dim actualText As String
expected = Array("WorkflowID", "DocumentFamily", "TeamName", _
"ProcessOwner", "WorkflowShape", "TemplateFiles", _
"TemplateVersion", "DataContractVersion", "OutputNamePattern", _
"ReviewOwner", "ReleaseStatus", "Notes")
For columnNumber = 1 To 12
actualText = CStr(ws.Cells(1, columnNumber).Value2)
If StrComp(actualText, CStr(expected(columnNumber - 1)), _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 3511, , _
"Header " & ws.Cells(1, columnNumber).Address(False, False) & _
" must match exactly: " & expected(columnNumber - 1)
End If
Next columnNumber
End Sub
Private Function ReadRequiredText(ByVal sourceCell As Range, _
ByVal fieldName As String, _
ByVal rowNumber As Long, _
ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByRef errorCount As Long, _
ByRef warningCount As Long) As String
ReadRequiredText = ReadCellText(sourceCell, fieldName, rowNumber, _
auditWs, auditRow, errorCount, warningCount)
If Len(ReadRequiredText) = 0 Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
"Required value is blank.", errorCount, warningCount
End If
End Function
Private Function ReadOptionalText(ByVal sourceCell As Range, _
ByVal fieldName As String, _
ByVal rowNumber As Long, _
ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByRef errorCount As Long, _
ByRef warningCount As Long) As String
ReadOptionalText = ReadCellText(sourceCell, fieldName, rowNumber, _
auditWs, auditRow, errorCount, warningCount)
End Function
Private Function ReadCellText(ByVal sourceCell As Range, _
ByVal fieldName As String, _
ByVal rowNumber As Long, _
ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByRef errorCount As Long, _
ByRef warningCount As Long) As String
Dim rawValue As Variant
rawValue = sourceCell.Value2
If IsError(rawValue) Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
"Cell contains an Excel error.", errorCount, warningCount
Exit Function
End If
ReadCellText = CStr(rawValue)
If StrComp(ReadCellText, Trim$(ReadCellText), vbBinaryCompare) <> 0 Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
"Value has leading or trailing spaces.", errorCount, warningCount
End If
If ContainsControlCharacter(ReadCellText) Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
"Value contains a control character.", errorCount, warningCount
End If
End Function
Private Sub RequireTextValue(ByVal sourceCell As Range, _
ByVal fieldName As String, _
ByVal rowNumber As Long, _
ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByRef errorCount As Long, _
ByRef warningCount As Long)
Dim rawValue As Variant
rawValue = sourceCell.Value2
If IsError(rawValue) Or IsEmpty(rawValue) Then Exit Sub
If VarType(rawValue) <> vbString Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
"WorkflowID must be stored as a text value, not only displayed with a text format.", _
errorCount, warningCount
ElseIf sourceCell.NumberFormat <> "@" Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "WARNING", _
"The value is text, but format the WorkflowID column as Text before future imports.", _
errorCount, warningCount
End If
End Sub
Private Sub ValidateWorkflowId(ByVal workflowId As String, _
ByVal rowNumber As Long, _
ByVal workflowRows As Object, _
ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByRef errorCount As Long, _
ByRef warningCount As Long)
If Len(workflowId) = 0 Then Exit Sub
If workflowRows.Exists(workflowId) Then
WriteAuditRow auditWs, auditRow, rowNumber, "WorkflowID", "ERROR", _
"Duplicate WorkflowID; first used on row " & _
workflowRows(workflowId) & ".", errorCount, warningCount
Else
workflowRows.Add workflowId, rowNumber
End If
End Sub
Private Sub ValidateExactValue(ByVal actualValue As String, _
ByVal allowedValues As Variant, _
ByVal fieldName As String, _
ByVal errorText As String, _
ByVal rowNumber As Long, _
ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByRef errorCount As Long, _
ByRef warningCount As Long)
Dim item As Variant
Dim isAllowed As Boolean
For Each item In allowedValues
If StrComp(actualValue, CStr(item), vbBinaryCompare) = 0 Then
isAllowed = True
Exit For
End If
Next item
If Not isAllowed Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
errorText, errorCount, warningCount
End If
End Sub
Private Sub ValidateTemplateList(ByVal templateFolder As String, _
ByVal listText As String, _
ByVal workflowShape As String, _
ByVal rowNumber As Long, _
ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByRef errorCount As Long, _
ByRef warningCount As Long)
Dim items As Variant
Dim item As Variant
Dim templateName As String
Dim seen As Object
Dim nameIsValid As Boolean
Dim extensionIsValid As Boolean
If Len(listText) = 0 Then Exit Sub
items = Split(listText, "|")
If workflowShape = SHAPE_FACTORY And UBound(items) <> 0 Then
WriteAuditRow auditWs, auditRow, rowNumber, "TemplateFiles", _
"ERROR", "FACTORY requires exactly one Word template.", _
errorCount, warningCount
End If
If workflowShape = SHAPE_CONTRACTOR And UBound(items) < 1 Then
WriteAuditRow auditWs, auditRow, rowNumber, "TemplateFiles", _
"ERROR", "CONTRACTOR requires at least two different templates.", _
errorCount, warningCount
End If
Set seen = CreateObject("Scripting.Dictionary")
seen.CompareMode = vbTextCompare
For Each item In items
templateName = CStr(item)
nameIsValid = ValidateAssetName(templateName, "TemplateFiles", _
rowNumber, auditWs, auditRow, errorCount, warningCount)
extensionIsValid = ValidateFileExtension(templateName, "docx", _
"TemplateFiles", rowNumber, auditWs, auditRow, _
errorCount, warningCount)
If seen.Exists(templateName) Then
WriteAuditRow auditWs, auditRow, rowNumber, "TemplateFiles", _
"ERROR", "Duplicate template filename: " & templateName, _
errorCount, warningCount
Else
seen.Add templateName, True
End If
If nameIsValid And extensionIsValid Then
CheckFileEntry templateFolder & "\" & templateName, _
"TemplateFiles", rowNumber, auditWs, auditRow, _
errorCount, warningCount
End If
Next item
End Sub
Private Function ValidateAssetName(ByVal fileName As String, _
ByVal fieldName As String, _
ByVal rowNumber As Long, _
ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByRef errorCount As Long, _
ByRef warningCount As Long) As Boolean
Dim forbidden As Variant
Dim item As Variant
Dim baseName As String
ValidateAssetName = True
forbidden = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
If Len(fileName) = 0 Or _
StrComp(fileName, Trim$(fileName), vbBinaryCompare) <> 0 Or _
Right$(fileName, 1) = "." Or Right$(fileName, 1) = " " Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
"Filename is blank or has unsafe outer characters.", _
errorCount, warningCount
ValidateAssetName = False
End If
For Each item In forbidden
If InStr(1, fileName, CStr(item), vbBinaryCompare) > 0 Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
"Filename contains a forbidden Windows character.", _
errorCount, warningCount
ValidateAssetName = False
End If
Next item
If ContainsControlCharacter(fileName) Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
"Filename contains a Windows control character.", _
errorCount, warningCount
ValidateAssetName = False
End If
baseName = fileName
If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
baseName = Left$(baseName, InStr(1, baseName, ".", _
vbBinaryCompare) - 1)
End If
If IsReservedWindowsName(UCase$(baseName)) Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
"Filename starts with a reserved Windows device name.", _
errorCount, warningCount
ValidateAssetName = False
End If
End Function
Private Function ValidateFileExtension(ByVal fileName As String, _
ByVal allowedExtension As String, _
ByVal fieldName As String, _
ByVal rowNumber As Long, _
ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByRef errorCount As Long, _
ByRef warningCount As Long) As Boolean
Dim dotPosition As Long
Dim extension As String
dotPosition = InStrRev(fileName, ".")
If dotPosition > 1 And dotPosition < Len(fileName) Then
extension = LCase$(Mid$(fileName, dotPosition + 1))
End If
ValidateFileExtension = _
(StrComp(extension, allowedExtension, vbBinaryCompare) = 0)
If Not ValidateFileExtension Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
"Template must use the ." & allowedExtension & _
" extension: " & fileName, errorCount, warningCount
End If
End Function
Private Sub CheckFileEntry(ByVal filePath As String, _
ByVal fieldName As String, _
ByVal rowNumber As Long, _
ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByRef errorCount As Long, _
ByRef warningCount As Long)
Dim foundName As String
Dim attributes As Long
On Error GoTo CheckFailed
foundName = Dir$(filePath, FILE_ATTRIBUTES)
If Len(foundName) = 0 Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
"Template file was not found: " & filePath, _
errorCount, warningCount
Exit Sub
End If
attributes = GetAttr(filePath)
If (attributes And vbDirectory) = vbDirectory Then
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
"Expected a file but found a folder: " & filePath, _
errorCount, warningCount
Exit Sub
End If
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "PASS", _
"Template file entry was found: " & filePath, _
errorCount, warningCount
Exit Sub
CheckFailed:
WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
"Could not check path: " & filePath & ". " & Err.Description, _
errorCount, warningCount
End Sub
Private Sub ValidateOutputPattern(ByVal patternText As String, _
ByVal rowNumber As Long, _
ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByRef errorCount As Long, _
ByRef warningCount As Long)
Dim forbidden As Variant
Dim item As Variant
If Len(patternText) = 0 Then Exit Sub
forbidden = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
For Each item In forbidden
If InStr(1, patternText, CStr(item), vbBinaryCompare) > 0 Then
WriteAuditRow auditWs, auditRow, rowNumber, _
"OutputNamePattern", "ERROR", _
"Output pattern contains a forbidden filename character.", _
errorCount, warningCount
Exit For
End If
Next item
If ContainsControlCharacter(patternText) Then
WriteAuditRow auditWs, auditRow, rowNumber, _
"OutputNamePattern", "ERROR", _
"Output pattern contains a control character.", _
errorCount, warningCount
End If
End Sub
Private Sub ValidateApproval(ByVal releaseStatus As String, _
ByVal processOwner As String, _
ByVal reviewOwner As String, _
ByVal notesText As String, _
ByVal rowNumber As Long, _
ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByRef errorCount As Long, _
ByRef warningCount As Long)
If releaseStatus = STATUS_APPROVED And Len(reviewOwner) = 0 Then
WriteAuditRow auditWs, auditRow, rowNumber, "ReviewOwner", _
"ERROR", "APPROVED requires a named ReviewOwner.", _
errorCount, warningCount
End If
If releaseStatus = STATUS_APPROVED And _
StrComp(processOwner, reviewOwner, vbTextCompare) = 0 Then
WriteAuditRow auditWs, auditRow, rowNumber, "ReviewOwner", _
"WARNING", _
"ProcessOwner and ReviewOwner are the same; confirm segregation of review.", _
errorCount, warningCount
End If
If releaseStatus = STATUS_RETIRED And Len(notesText) = 0 Then
WriteAuditRow auditWs, auditRow, rowNumber, "Notes", "WARNING", _
"RETIRED should record the replacement or retention decision.", _
errorCount, warningCount
End If
End Sub
Private Function BuildContract(ByVal workflowShape As String, _
ByVal templateFiles As String, _
ByVal templateVersion As String, _
ByVal dataVersion As String, _
ByVal outputPattern As String) As String
BuildContract = workflowShape & ChrW(30) & templateFiles & _
ChrW(30) & templateVersion & ChrW(30) & dataVersion & _
ChrW(30) & outputPattern
End Function
Private Sub ValidateFamilyContract(ByVal documentFamily As String, _
ByVal contractText As String, _
ByVal rowNumber As Long, _
ByVal familyContracts As Object, _
ByVal familyFirstRows As Object, _
ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByRef errorCount As Long, _
ByRef warningCount As Long)
If familyContracts.Exists(documentFamily) Then
If StrComp(CStr(familyContracts(documentFamily)), contractText, _
vbBinaryCompare) <> 0 Then
WriteAuditRow auditWs, auditRow, rowNumber, _
"DocumentFamily", "ERROR", _
"Contract differs from the first row for this family (row " & _
familyFirstRows(documentFamily) & ").", _
errorCount, warningCount
End If
Else
familyContracts.Add documentFamily, contractText
familyFirstRows.Add documentFamily, rowNumber
End If
End Sub
Private Function ContainsControlCharacter(ByVal textValue As String) As Boolean
Dim position As Long
Dim characterCode As Long
For position = 1 To Len(textValue)
characterCode = AscW(Mid$(textValue, position, 1))
If characterCode >= 0 And characterCode < 32 Then
ContainsControlCharacter = True
Exit Function
End If
Next position
End Function
Private Function IsReservedWindowsName(ByVal upperBase As String) As Boolean
Dim finalCharacter As String
Select Case upperBase
Case "CON", "PRN", "AUX", "NUL", "CLOCK$"
IsReservedWindowsName = True
Exit Function
End Select
If Left$(upperBase, 3) = "COM" Or Left$(upperBase, 3) = "LPT" Then
If Len(upperBase) = 4 And Mid$(upperBase, 4, 1) Like "[1-9]" Then
IsReservedWindowsName = True
Exit Function
End If
If Len(upperBase) = 4 Then
finalCharacter = Mid$(upperBase, 4, 1)
If finalCharacter = ChrW$(&HB9) Or _
finalCharacter = ChrW$(&HB2) Or _
finalCharacter = ChrW$(&HB3) Then
IsReservedWindowsName = True
End If
End If
End If
End Function
Private Function NextAuditSheetName(ByVal wb As Workbook) As String
Dim candidate As String
Dim suffix As Long
candidate = AUDIT_PREFIX & Format$(Now, "yyyymmdd_hhnnss")
If Not WorksheetExists(wb, candidate) Then
NextAuditSheetName = candidate
Exit Function
End If
For suffix = 1 To 99
candidate = Left$(AUDIT_PREFIX & Format$(Now, "yyyymmdd_hhnnss"), _
28) & "_" & Format$(suffix, "00")
If Not WorksheetExists(wb, candidate) Then
NextAuditSheetName = candidate
Exit Function
End If
Next suffix
Err.Raise vbObjectError + 3512, , _
"Could not create a unique audit sheet name."
End Function
Private Function WorksheetExists(ByVal wb As Workbook, _
ByVal sheetName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = wb.Worksheets(sheetName)
On Error GoTo 0
WorksheetExists = Not ws Is Nothing
End Function
Private Sub PrepareAuditSheet(ByVal auditWs As Worksheet)
auditWs.Cells(1, 1).Value = "SourceRow"
auditWs.Cells(1, 2).Value = "Field"
auditWs.Cells(1, 3).Value = "Result"
auditWs.Cells(1, 4).Value = "Message"
auditWs.Cells(1, 5).Value = "CheckedAt"
auditWs.Cells(1, 6).Value = "AuditScope"
auditWs.Rows(1).Font.Bold = True
auditWs.Range("A1:F1").Interior.Color = RGB(31, 78, 121)
auditWs.Range("A1:F1").Font.Color = RGB(255, 255, 255)
auditWs.Columns("A:F").ColumnWidth = 24
auditWs.Columns("D").ColumnWidth = 72
auditWs.Columns("F").ColumnWidth = 48
End Sub
Private Sub WriteAuditRow(ByVal auditWs As Worksheet, _
ByRef auditRow As Long, _
ByVal sourceRow As Long, _
ByVal fieldName As String, _
ByVal resultText As String, _
ByVal messageText As String, _
ByRef errorCount As Long, _
ByRef warningCount As Long)
auditWs.Cells(auditRow, 1).Value = sourceRow
auditWs.Cells(auditRow, 2).Value = fieldName
auditWs.Cells(auditRow, 3).Value = resultText
auditWs.Cells(auditRow, 4).Value = messageText
auditWs.Cells(auditRow, 5).Value = Now
auditWs.Cells(auditRow, 6).Value = _
"Read-only template presence and register consistency audit"
If resultText = "ERROR" Then errorCount = errorCount + 1
If resultText = "WARNING" Then warningCount = warningCount + 1
auditRow = auditRow + 1
End Sub
Private Sub FinishAuditSheet(ByVal auditWs As Worksheet, _
ByVal errorCount As Long, _
ByVal warningCount As Long, _
ByVal approvedCount As Long)
auditWs.Range("H1:I4").ClearContents
auditWs.Range("H1").Value = "Summary"
auditWs.Range("H2").Value = "Errors"
auditWs.Range("I2").Value = errorCount
auditWs.Range("H3").Value = "Warnings"
auditWs.Range("I3").Value = warningCount
auditWs.Range("H4").Value = "Rows marked APPROVED"
auditWs.Range("I4").Value = approvedCount
auditWs.Columns("A:I").VerticalAlignment = xlTop
auditWs.Columns("D").WrapText = True
auditWs.Range("A1:F1").AutoFilter
auditWs.Activate
auditWs.Range("A2").Select
ActiveWindow.FreezePanes = True
End SubOfficial VBA references: Workbook.ReadOnly and Dictionary CompareMode.
Apply the standard in DocxForge Pro
DocxForge Pro supports two different workflow shapes. Standardize each family around the shape that matches its real output.
Contractor
One case reuses shared data across several different documents created from different Word templates.
Factory
Many independent Excel rows use one standard Word template, producing a separate output for each selected row.




Keep the standard alive after launch
Quarterly ownership review
Confirm that owners, reviewers, active teams, and downstream consumers are still correct.
Release inventory
Keep active and retired template versions distinguishable. Remove ambiguous shortcuts and uncontrolled duplicates.
Representative regression set
Retain approved test records that exercise blanks, long values, tables, images, names, and PDF pagination.
Exception register
Record valid team exceptions with an owner and review date instead of hiding them in local instructions.
Adoption evidence
Compare registered workflows with actual operators and output folders to find teams still using earlier routes.
Retirement decision
Archive old releases according to retention rules without leaving them available for accidental production use.
Frequently asked questions
What should be standardized across teams?
Standardize the data contract, approved Word templates, template and data-contract versions, output naming, review ownership, release status, and change process. Do not force unrelated document families into one template merely to reduce the template count.
Does standardization mean every team uses one Word template?
No. A document family should use one approved contract, but different families may need different templates. Factory uses one standard template for many independent rows. Contractor uses several different templates for the documents in one case.
What does the VBA audit change?
It adds a timestamped audit worksheet to the saved .xlsm workbook and saves that workbook. It checks register values and whether referenced template file entries exist. It does not open template contents, generate documents, or alter production files.
Can the audit prove that a template is approved?
No. A filename and version value are registry evidence, not authentication of the DOCX content. Keep the approved release in a controlled location and use your normal access, review, and integrity controls.
How should teams roll out a new template version?
Publish the new contract and template together, test representative records, record the reviewer, update the register, and retire the previous release only after the agreed transition and retention steps are complete.
Where do DocxForge Pro Factory and Contractor fit?
Use Factory when many independent Excel rows use one standard Word template. Use Contractor when one case reuses shared data across several different documents created from different Word templates.