One Big Master Template vs Multiple Focused Templates: Which Is Easier to Maintain?
Choose the smallest template boundary that keeps changes, ownership, testing, and releases understandable—then govern the resulting library as a system.
Quick answer: optimize for change boundaries, not file count
A single file looks simpler in an inventory. It is not simpler if one clause, header, or conditional section forces a full regression test across unrelated documents. Multiple focused templates are usually easier to maintain when each file represents one coherent document type with a clear owner and release path.
Use focused templates plus shared styles and assets. Choose a master template only when the variants share the same structure, owner, approval cycle, and test matrix—and are expected to remain coupled.
Use six signals to choose the template boundary
Change coupling
If the same policy or layout change must land in every variant at the same time, a shared template can reduce duplicate edits.
Ownership
If Legal owns one document and Operations owns another, separate templates make authority and approval clearer.
Release cadence
Documents that release independently should not be forced into one version number and one deployment event.
Regression scope
Count every output variant that must be retested after a small change. A large matrix is a real maintenance cost.
Layout divergence
Different sections, tables, headers, numbering, and page rules are evidence of different templates—not merely more conditions.
Shared components
Branding, styles, approved clauses, and assets can remain shared without placing every document in one Word file.
When one master template can remain maintainable
A master works best when the outputs are variants of one document—not a folder of unrelated documents hidden inside conditions.
One stable document family
The variants share page structure, styles, headers, fields, and review criteria. Differences are limited and predictable.
One accountable owner
One team can approve the entire file and accept responsibility for every supported branch in a release.
One release train
All variants can be versioned, tested, and deployed together without blocking an unrelated team.
A bounded test matrix
The team has named test cases for every condition combination and can run them before each release.
If editors need a map to understand which sections belong to which document, the master has probably crossed from reuse into hidden coupling.
When focused templates are easier to maintain
Focused templates reduce the scope of each change. The trade-off is that the library itself must be managed deliberately.
Different document purposes
An agreement, cover letter, certificate, inspection report, and invoice have different layout and review needs even when they reuse the same data.
Different owners or approvers
Each file can have an explicit business owner, approved version, and release history.
Independent releases
A change to one document can be tested and published without reopening every other output.
Smaller regression tests
Reviewers test the document that changed plus shared components that actually changed—not every branch in one giant file.
Cleaner workflow mapping
Each document type maps to an obvious template instead of relying on a growing set of conditional rules.
Safer retirement
An obsolete document can be marked retired and removed from active selection without editing the internals of a master.
Separate document boundaries without duplicating the design system
Keep approved logos, fonts, styles, color rules, standard clauses, naming conventions, and release checks under common governance. A focused template should reuse that foundation rather than fork it silently.
Style-set identifier
Record the shared style set or design baseline used by each template so coordinated updates are searchable.
Named owner and version
Every active file needs an owner, visible version, approval status, and an accountable release decision.
Explicit workflow mode
Record whether a template is intended for Contractor or Factory. Do not infer the workflow from a filename.
Retirement status
Mark superseded files RETIRED instead of leaving ambiguous copies in an active folder.
Create a template register before the library becomes invisible
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Create TemplateRegister, put the exact headers in row 1, and store text fields as text without leading or trailing whitespace.
A — TemplateID; B — TemplateFile; C — DocumentType; D — WorkflowMode; E — Owner; F — Version; G — ApprovalStatus; H — ExpectedBookmarks; I — ExpectedContentControls; J — SharedStyleSet.
Exact controlled values
WorkflowMode must be CONTRACTOR or FACTORY. ApprovalStatus must be DRAFT, APPROVED, or RETIRED.
Library folder
Place registered .docx or .dotx files in TemplateLibrary beside the workbook. Enter filenames only, never absolute or relative paths.
Expected structural counts
Record non-negative whole numbers for bookmarks and content controls. A mismatch flags drift; it does not prove the template is semantically correct.
Append-only evidence
The helper appends a RunID to TemplatePortfolioAudit instead of erasing earlier audit runs.
Free VBA helper: audit the registered template portfolio
The macro performs a full register and file preflight before Word starts. It then opens each valid template read-only, records file metadata and structural counts, logs per-template errors, and never modifies the source templates.
Register integrity
Checks exact headers and values, unique template IDs and filenames, safe Windows filenames, extensions, file presence, and non-zero size.
Read-only Word inspection
Uses one hidden Word instance to count bookmarks, content controls, and sections for each valid template.
Per-template results
A file that Word cannot open receives ERROR while the audit continues with the remaining valid files.
No architecture guess
The report supplies evidence. It does not declare a master or focused design correct from a count threshold.
Option Explicit
Private Const REGISTER_SHEET As String = "TemplateRegister"
Private Const AUDIT_SHEET As String = "TemplatePortfolioAudit"
Private Const LIBRARY_FOLDER As String = "TemplateLibrary"
Private Const FIRST_DATA_ROW As Long = 2
Private Const COL_TEMPLATE_ID As Long = 1
Private Const COL_TEMPLATE_FILE As Long = 2
Private Const COL_DOCUMENT_TYPE As Long = 3
Private Const COL_WORKFLOW_MODE As Long = 4
Private Const COL_OWNER As Long = 5
Private Const COL_VERSION As Long = 6
Private Const COL_APPROVAL_STATUS As Long = 7
Private Const COL_EXPECTED_BOOKMARKS As Long = 8
Private Const COL_EXPECTED_CONTROLS As Long = 9
Private Const COL_SHARED_STYLE_SET As Long = 10
Private Const WD_ALERTS_NONE As Long = 0
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const FILE_ATTRIBUTES As Long = 39
Public Sub AuditWordTemplatePortfolio()
Dim wb As Workbook
Dim registerWs As Worksheet
Dim auditWs As Worksheet
Dim wdApp As Object
Dim templateIds As Object
Dim templateFiles As Object
Dim lastRow As Long
Dim rowCount As Long
Dim rowNumber As Long
Dim validCount As Long
Dim errorCount As Long
Dim nextAuditRow As Long
Dim runId As String
Dim libraryPath As String
Dim values As Variant
Dim rowValid() As Boolean
Dim rowMessage() As String
Dim filePaths() As String
Dim fileBytes() As Double
Dim fileModified() As Date
Dim actualBookmarks As Long
Dim actualControls As Long
Dim sectionCount As Long
Dim auditError As String
Dim resultText As String
Dim messageText As String
On Error GoTo CleanFail
Set wb = ThisWorkbook
If wb.Path = vbNullString Then
Err.Raise vbObjectError + 4100, , _
"Save this workbook as an .xlsm file before running the audit."
End If
If wb.ReadOnly Then
Err.Raise vbObjectError + 4101, , _
"The workbook is read-only. Open a writable copy before running the audit."
End If
Set registerWs = GetRequiredWorksheet(wb, REGISTER_SHEET)
If registerWs.ProtectContents Then
Err.Raise vbObjectError + 4102, , _
"The TemplateRegister sheet is protected. Unprotect it before running the audit."
End If
ValidateRegisterHeaders registerWs
lastRow = LastUsedRow(registerWs)
If lastRow < FIRST_DATA_ROW Then
Err.Raise vbObjectError + 4103, , _
"No template records were found on TemplateRegister."
End If
rowCount = lastRow - FIRST_DATA_ROW + 1
values = registerWs.Range( _
registerWs.Cells(FIRST_DATA_ROW, COL_TEMPLATE_ID), _
registerWs.Cells(lastRow, COL_SHARED_STYLE_SET)).Value2
ReDim rowValid(1 To rowCount)
ReDim rowMessage(1 To rowCount)
ReDim filePaths(1 To rowCount)
ReDim fileBytes(1 To rowCount)
ReDim fileModified(1 To rowCount)
libraryPath = wb.Path & Application.PathSeparator & LIBRARY_FOLDER
RequireFolder libraryPath
Set templateIds = CreateObject("Scripting.Dictionary")
templateIds.CompareMode = vbTextCompare
Set templateFiles = CreateObject("Scripting.Dictionary")
templateFiles.CompareMode = vbTextCompare
' Pass 1 validates every register row and file before Word starts.
For rowNumber = 1 To rowCount
PreflightRegisterRow values, rowNumber, FIRST_DATA_ROW + rowNumber - 1, _
libraryPath, templateIds, templateFiles, rowValid(rowNumber), _
rowMessage(rowNumber), filePaths(rowNumber), _
fileBytes(rowNumber), fileModified(rowNumber)
If rowValid(rowNumber) Then
validCount = validCount + 1
Else
errorCount = errorCount + 1
End If
Next rowNumber
Set auditWs = GetOrCreateAuditSheet(wb)
ValidateAuditHeaders auditWs
nextAuditRow = NextAuditRow(auditWs)
runId = Format$(Now, "yyyymmdd-hhnnss")
' Invalid register rows are logged without touching the file system again.
For rowNumber = 1 To rowCount
If Not rowValid(rowNumber) Then
AppendAuditRow auditWs, nextAuditRow, runId, values, rowNumber, _
0, Empty, Empty, Empty, Empty, Empty, Empty, "ERROR", rowMessage(rowNumber)
nextAuditRow = nextAuditRow + 1
End If
Next rowNumber
If validCount > 0 Then
Set wdApp = StartOwnedWord()
For rowNumber = 1 To rowCount
If rowValid(rowNumber) Then
actualBookmarks = 0
actualControls = 0
sectionCount = 0
auditError = AuditOneTemplate( _
wdApp, filePaths(rowNumber), actualBookmarks, _
actualControls, sectionCount)
If Len(auditError) > 0 Then
resultText = "ERROR"
messageText = auditError
errorCount = errorCount + 1
Else
CompareTemplateCounts values, rowNumber, actualBookmarks, _
actualControls, resultText, messageText
If resultText = "ERROR" Then errorCount = errorCount + 1
End If
AppendAuditRow auditWs, nextAuditRow, runId, values, rowNumber, _
fileBytes(rowNumber), fileModified(rowNumber), _
actualBookmarks, CLng(values(rowNumber, COL_EXPECTED_BOOKMARKS)), _
actualControls, CLng(values(rowNumber, COL_EXPECTED_CONTROLS)), _
sectionCount, resultText, messageText
nextAuditRow = nextAuditRow + 1
End If
Next rowNumber
End If
StopOwnedWord wdApp
auditWs.Columns("A:R").AutoFit
wb.Save
MsgBox "Template portfolio audit complete." & vbCrLf & _
"Run ID: " & runId & vbCrLf & _
"Registered templates: " & rowCount & vbCrLf & _
"Errors: " & errorCount & vbCrLf & _
"Review: " & AUDIT_SHEET, vbInformation
Exit Sub
CleanFail:
StopOwnedWord wdApp
MsgBox "Audit stopped: " & Err.Description, vbCritical
End Sub
Private Sub PreflightRegisterRow( _
ByRef values As Variant, _
ByVal dataIndex As Long, _
ByVal sheetRow As Long, _
ByVal libraryPath As String, _
ByVal templateIds As Object, _
ByVal templateFiles As Object, _
ByRef isValid As Boolean, _
ByRef validationMessage As String, _
ByRef fullPath As String, _
ByRef sizeBytes As Double, _
ByRef modifiedAt As Date)
Dim templateId As String
Dim templateFile As String
Dim documentType As String
Dim workflowMode As String
Dim ownerName As String
Dim versionText As String
Dim approvalStatus As String
Dim sharedStyleSet As String
Dim expectedBookmarks As Long
Dim expectedControls As Long
On Error GoTo InvalidRow
templateId = RequireExactText(values(dataIndex, COL_TEMPLATE_ID), _
"TemplateID", sheetRow)
templateFile = RequireExactText(values(dataIndex, COL_TEMPLATE_FILE), _
"TemplateFile", sheetRow)
documentType = RequireExactText(values(dataIndex, COL_DOCUMENT_TYPE), _
"DocumentType", sheetRow)
workflowMode = RequireExactText(values(dataIndex, COL_WORKFLOW_MODE), _
"WorkflowMode", sheetRow)
ownerName = RequireExactText(values(dataIndex, COL_OWNER), _
"Owner", sheetRow)
versionText = RequireExactText(values(dataIndex, COL_VERSION), _
"Version", sheetRow)
approvalStatus = RequireExactText(values(dataIndex, COL_APPROVAL_STATUS), _
"ApprovalStatus", sheetRow)
expectedBookmarks = RequireWholeNumber( _
values(dataIndex, COL_EXPECTED_BOOKMARKS), "ExpectedBookmarks", sheetRow)
expectedControls = RequireWholeNumber( _
values(dataIndex, COL_EXPECTED_CONTROLS), _
"ExpectedContentControls", sheetRow)
sharedStyleSet = RequireExactText(values(dataIndex, COL_SHARED_STYLE_SET), _
"SharedStyleSet", sheetRow)
If workflowMode <> "CONTRACTOR" And workflowMode <> "FACTORY" Then
Err.Raise vbObjectError + 4110, , _
"Row " & sheetRow & ": WorkflowMode must be exactly CONTRACTOR or FACTORY."
End If
If approvalStatus <> "DRAFT" And approvalStatus <> "APPROVED" And _
approvalStatus <> "RETIRED" Then
Err.Raise vbObjectError + 4111, , _
"Row " & sheetRow & _
": ApprovalStatus must be exactly DRAFT, APPROVED, or RETIRED."
End If
ValidateIdentifier templateId, "TemplateID", sheetRow
ValidateTemplateFileName templateFile, sheetRow
If templateIds.Exists(templateId) Then
Err.Raise vbObjectError + 4112, , _
"Row " & sheetRow & ": duplicate TemplateID; first used in row " & _
templateIds(templateId) & "."
End If
templateIds.Add templateId, sheetRow
If templateFiles.Exists(templateFile) Then
Err.Raise vbObjectError + 4113, , _
"Row " & sheetRow & ": duplicate TemplateFile; first used in row " & _
templateFiles(templateFile) & "."
End If
templateFiles.Add templateFile, sheetRow
fullPath = libraryPath & Application.PathSeparator & templateFile
ReadFileMetadata fullPath, sizeBytes, modifiedAt
If sizeBytes <= 0 Then
Err.Raise vbObjectError + 4114, , _
"Row " & sheetRow & ": template file is empty: " & fullPath
End If
' Assignments make every required field visibly part of the contract.
If Len(documentType) = 0 Or Len(ownerName) = 0 Or Len(versionText) = 0 Or _
Len(sharedStyleSet) = 0 Or expectedBookmarks < 0 Or expectedControls < 0 Then
Err.Raise vbObjectError + 4115, , "Invalid template contract in row " & sheetRow & "."
End If
isValid = True
validationMessage = vbNullString
Exit Sub
InvalidRow:
isValid = False
validationMessage = Err.Description
Err.Clear
End Sub
Private Function AuditOneTemplate( _
ByVal wdApp As Object, _
ByVal templatePath As String, _
ByRef bookmarkCount As Long, _
ByRef contentControlCount As Long, _
ByRef sectionCount As Long) As String
Dim doc As Object
On Error GoTo AuditFailed
Set doc = wdApp.Documents.Open(templatePath, False, True, False)
bookmarkCount = doc.Bookmarks.Count
contentControlCount = doc.ContentControls.Count
sectionCount = doc.Sections.Count
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
AuditOneTemplate = vbNullString
Exit Function
AuditFailed:
AuditOneTemplate = "Word could not audit the read-only template: " & Err.Description
On Error Resume Next
If Not doc Is Nothing Then doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
On Error GoTo 0
End Function
Private Sub CompareTemplateCounts( _
ByRef values As Variant, _
ByVal dataIndex As Long, _
ByVal actualBookmarks As Long, _
ByVal actualControls As Long, _
ByRef resultText As String, _
ByRef messageText As String)
Dim expectedBookmarks As Long
Dim expectedControls As Long
expectedBookmarks = CLng(values(dataIndex, COL_EXPECTED_BOOKMARKS))
expectedControls = CLng(values(dataIndex, COL_EXPECTED_CONTROLS))
resultText = "PASS"
messageText = "Counts match the registered template contract."
If actualBookmarks <> expectedBookmarks Then
resultText = "ERROR"
messageText = "Bookmark count mismatch: expected " & expectedBookmarks & _
", found " & actualBookmarks & "."
End If
If actualControls <> expectedControls Then
If resultText = "ERROR" Then messageText = messageText & " "
resultText = "ERROR"
messageText = messageText & "Content-control count mismatch: expected " & _
expectedControls & ", found " & actualControls & "."
End If
End Sub
Private Function StartOwnedWord() As Object
Dim app As Object
On Error GoTo StartFailed
Set app = CreateObject("Word.Application")
app.Visible = False
app.ScreenUpdating = False
app.DisplayAlerts = WD_ALERTS_NONE
Set StartOwnedWord = app
Exit Function
StartFailed:
Err.Raise vbObjectError + 4120, , _
"Microsoft Word could not be started: " & Err.Description
End Function
Private Sub StopOwnedWord(ByRef wdApp As Object)
On Error Resume Next
If Not wdApp Is Nothing Then
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
End If
Set wdApp = Nothing
On Error GoTo 0
End Sub
Private Function RequireExactText( _
ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal sheetRow As Long) As String
Dim textValue As String
If IsError(rawValue) Then
Err.Raise vbObjectError + 4130, , _
"Row " & sheetRow & ": " & fieldName & " contains an Excel error."
End If
If VarType(rawValue) <> vbString Then
Err.Raise vbObjectError + 4131, , _
"Row " & sheetRow & ": " & fieldName & " must be stored as text."
End If
textValue = CStr(rawValue)
If Len(textValue) = 0 Then
Err.Raise vbObjectError + 4132, , _
"Row " & sheetRow & ": " & fieldName & " is required."
End If
If textValue <> Trim$(textValue) Then
Err.Raise vbObjectError + 4133, , _
"Row " & sheetRow & ": " & fieldName & _
" has leading or trailing whitespace."
End If
If ContainsControlCharacter(textValue) Then
Err.Raise vbObjectError + 4134, , _
"Row " & sheetRow & ": " & fieldName & _
" contains a control character."
End If
RequireExactText = textValue
End Function
Private Function RequireWholeNumber( _
ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal sheetRow As Long) As Long
Dim numericValue As Double
If IsError(rawValue) Or Not IsNumeric(rawValue) Then
Err.Raise vbObjectError + 4140, , _
"Row " & sheetRow & ": " & fieldName & _
" must be a whole number of zero or greater."
End If
numericValue = CDbl(rawValue)
If numericValue < 0 Or numericValue <> Fix(numericValue) Or _
numericValue > 2147483647# Then
Err.Raise vbObjectError + 4141, , _
"Row " & sheetRow & ": " & fieldName & _
" must be a whole number of zero or greater."
End If
RequireWholeNumber = CLng(numericValue)
End Function
Private Sub ValidateIdentifier( _
ByVal value As String, _
ByVal fieldName As String, _
ByVal sheetRow As Long)
Dim index As Long
Dim character As String
For index = 1 To Len(value)
character = Mid$(value, index, 1)
If Not (character Like "[A-Za-z0-9_-]") Then
Err.Raise vbObjectError + 4150, , _
"Row " & sheetRow & ": " & fieldName & _
" may contain only letters, digits, underscore, and hyphen."
End If
Next index
End Sub
Private Sub ValidateTemplateFileName(ByVal fileName As String, ByVal sheetRow As Long)
Dim extensionText As String
Dim baseName As String
Dim dotPosition As Long
Dim forbidden As Variant
Dim item As Variant
If InStr(1, fileName, "\", vbBinaryCompare) > 0 Or _
InStr(1, fileName, "/", vbBinaryCompare) > 0 Or _
InStr(1, fileName, ":", vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 4160, , _
"Row " & sheetRow & ": TemplateFile must be a file name, not a path."
End If
forbidden = Array("*", "?", Chr$(34), "<", ">", "|")
For Each item In forbidden
If InStr(1, fileName, CStr(item), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 4161, , _
"Row " & sheetRow & ": TemplateFile contains a forbidden Windows character."
End If
Next item
If Right$(fileName, 1) = "." Or Right$(fileName, 1) = " " Then
Err.Raise vbObjectError + 4162, , _
"Row " & sheetRow & ": TemplateFile cannot end with a dot or space."
End If
dotPosition = InStrRev(fileName, ".")
If dotPosition <= 1 Then
Err.Raise vbObjectError + 4163, , _
"Row " & sheetRow & ": TemplateFile must end in .docx or .dotx."
End If
extensionText = LCase$(Mid$(fileName, dotPosition))
If extensionText <> ".docx" And extensionText <> ".dotx" Then
Err.Raise vbObjectError + 4163, , _
"Row " & sheetRow & ": TemplateFile must end in .docx or .dotx."
End If
baseName = Left$(fileName, InStr(1, fileName, ".") - 1)
If IsReservedWindowsName(baseName) Then
Err.Raise vbObjectError + 4164, , _
"Row " & sheetRow & ": TemplateFile uses a reserved Windows name."
End If
End Sub
Private Function IsReservedWindowsName(ByVal baseName As String) As Boolean
Dim normalized As String
Dim suffix As String
normalized = UCase$(baseName)
Select Case normalized
Case "CON", "PRN", "AUX", "NUL", "CLOCK$"
IsReservedWindowsName = True
Exit Function
End Select
If Left$(normalized, 3) = "COM" Or Left$(normalized, 3) = "LPT" Then
suffix = Mid$(normalized, 4)
If suffix Like "[1-9]" Or suffix = ChrW$(&HB9) Or _
suffix = ChrW$(&HB2) Or suffix = ChrW$(&HB3) Then
IsReservedWindowsName = True
End If
End If
End Function
Private Function ContainsControlCharacter(ByVal value As String) As Boolean
Dim index As Long
Dim codePoint As Long
For index = 1 To Len(value)
codePoint = AscW(Mid$(value, index, 1))
If codePoint >= 0 And codePoint <= 31 Then
ContainsControlCharacter = True
Exit Function
End If
Next index
End Function
Private Sub ReadFileMetadata( _
ByVal filePath As String, _
ByRef sizeBytes As Double, _
ByRef modifiedAt As Date)
Dim foundName As String
On Error GoTo FileError
foundName = Dir$(filePath, FILE_ATTRIBUTES)
If Len(foundName) = 0 Then
Err.Raise vbObjectError + 4170, , "Template file was not found: " & filePath
End If
sizeBytes = CDbl(FileLen(filePath))
modifiedAt = FileDateTime(filePath)
Exit Sub
FileError:
Err.Raise vbObjectError + 4171, , _
"Template file could not be checked: " & filePath & ". " & Err.Description
End Sub
Private Sub RequireFolder(ByVal folderPath As String)
Dim attributes As Long
On Error GoTo FolderError
attributes = GetAttr(folderPath)
If (attributes And vbDirectory) = 0 Then
Err.Raise vbObjectError + 4180, , _
"TemplateLibrary is not a folder: " & folderPath
End If
Exit Sub
FolderError:
Err.Raise vbObjectError + 4181, , _
"TemplateLibrary could not be accessed: " & folderPath & ". " & Err.Description
End Sub
Private Function GetRequiredWorksheet( _
ByVal wb As Workbook, _
ByVal sheetName As String) As Worksheet
On Error Resume Next
Set GetRequiredWorksheet = wb.Worksheets(sheetName)
On Error GoTo 0
If GetRequiredWorksheet Is Nothing Then
Err.Raise vbObjectError + 4190, , _
"Required worksheet not found: " & sheetName
End If
End Function
Private Function GetOrCreateAuditSheet(ByVal wb As Workbook) As Worksheet
On Error Resume Next
Set GetOrCreateAuditSheet = wb.Worksheets(AUDIT_SHEET)
On Error GoTo 0
If GetOrCreateAuditSheet Is Nothing Then
Set GetOrCreateAuditSheet = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
GetOrCreateAuditSheet.Name = AUDIT_SHEET
WriteAuditHeaders GetOrCreateAuditSheet
End If
End Function
Private Function LastUsedRow(ByVal ws As Worksheet) As Long
Dim lastCell As Range
Set lastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False)
If lastCell Is Nothing Then
LastUsedRow = 0
Else
LastUsedRow = lastCell.Row
End If
End Function
Private Sub ValidateRegisterHeaders(ByVal ws As Worksheet)
Dim headers As Variant
Dim columnNumber As Long
Dim rawValue As Variant
headers = Array("TemplateID", "TemplateFile", "DocumentType", _
"WorkflowMode", "Owner", "Version", "ApprovalStatus", _
"ExpectedBookmarks", "ExpectedContentControls", "SharedStyleSet")
For columnNumber = 1 To UBound(headers) + 1
rawValue = ws.Cells(1, columnNumber).Value2
If IsError(rawValue) Or VarType(rawValue) <> vbString Then
Err.Raise vbObjectError + 4200, , _
"Header " & ws.Cells(1, columnNumber).Address(False, False) & _
" must be stored as exact text."
End If
If CStr(rawValue) <> CStr(headers(columnNumber - 1)) Then
Err.Raise vbObjectError + 4201, , _
"Expected header " & headers(columnNumber - 1) & " in " & _
ws.Cells(1, columnNumber).Address(False, False) & "."
End If
Next columnNumber
End Sub
Private Sub WriteAuditHeaders(ByVal ws As Worksheet)
Dim headers As Variant
Dim columnNumber As Long
headers = AuditHeaders()
For columnNumber = 1 To UBound(headers) + 1
ws.Cells(1, columnNumber).Value = headers(columnNumber - 1)
Next columnNumber
End Sub
Private Sub ValidateAuditHeaders(ByVal ws As Worksheet)
Dim headers As Variant
Dim columnNumber As Long
headers = AuditHeaders()
For columnNumber = 1 To UBound(headers) + 1
If CStr(ws.Cells(1, columnNumber).Value2) <> _
CStr(headers(columnNumber - 1)) Then
Err.Raise vbObjectError + 4210, , _
"The TemplatePortfolioAudit headers do not match the expected contract."
End If
Next columnNumber
End Sub
Private Function AuditHeaders() As Variant
AuditHeaders = Array("RunID", "TemplateID", "TemplateFile", _
"DocumentType", "WorkflowMode", "Owner", "Version", _
"ApprovalStatus", "FileBytes", "FileModified", "ActualBookmarks", _
"ExpectedBookmarks", "ActualContentControls", _
"ExpectedContentControls", "SectionCount", "Result", "Message", "LoggedAt")
End Function
Private Function NextAuditRow(ByVal ws As Worksheet) As Long
Dim lastRow As Long
lastRow = LastUsedRow(ws)
If lastRow < 1 Then
WriteAuditHeaders ws
NextAuditRow = 2
Else
NextAuditRow = lastRow + 1
End If
End Function
Private Sub AppendAuditRow( _
ByVal ws As Worksheet, _
ByVal targetRow As Long, _
ByVal runId As String, _
ByRef values As Variant, _
ByVal dataIndex As Long, _
ByVal sizeBytes As Variant, _
ByVal modifiedAt As Variant, _
ByVal actualBookmarks As Variant, _
ByVal expectedBookmarks As Variant, _
ByVal actualControls As Variant, _
ByVal expectedControls As Variant, _
ByVal sectionCount As Variant, _
ByVal resultText As String, _
ByVal messageText As String)
ws.Cells(targetRow, 1).Value = runId
ws.Cells(targetRow, 2).Value = values(dataIndex, COL_TEMPLATE_ID)
ws.Cells(targetRow, 3).Value = values(dataIndex, COL_TEMPLATE_FILE)
ws.Cells(targetRow, 4).Value = values(dataIndex, COL_DOCUMENT_TYPE)
ws.Cells(targetRow, 5).Value = values(dataIndex, COL_WORKFLOW_MODE)
ws.Cells(targetRow, 6).Value = values(dataIndex, COL_OWNER)
ws.Cells(targetRow, 7).Value = values(dataIndex, COL_VERSION)
ws.Cells(targetRow, 8).Value = values(dataIndex, COL_APPROVAL_STATUS)
ws.Cells(targetRow, 9).Value = sizeBytes
ws.Cells(targetRow, 10).Value = modifiedAt
ws.Cells(targetRow, 11).Value = actualBookmarks
ws.Cells(targetRow, 12).Value = expectedBookmarks
ws.Cells(targetRow, 13).Value = actualControls
ws.Cells(targetRow, 14).Value = expectedControls
ws.Cells(targetRow, 15).Value = sectionCount
ws.Cells(targetRow, 16).Value = resultText
ws.Cells(targetRow, 17).Value = messageText
ws.Cells(targetRow, 18).Value = Now
End SubThe inspection uses Word collections and read-only opening documented by Microsoft: Document.Bookmarks, Document.ContentControls, Document.Sections, and Documents.Open.
Interpret the audit as evidence, not a design score
Unexpected count change
Investigate whether a bookmark or content control was intentionally added or silently removed. Update the approved contract only after review.
Many owners in one file
This is evidence of approval coupling. Clarify one accountable owner or split the document boundary.
One style set across many files
This is healthy reuse if the style set is governed and the document releases remain independent.
One file serving both modes
Review whether that is intentional. Contractor templates form different documents in a case pack; Factory applies one standard template to many rows.
High section count
It is a review signal, not proof of poor design. Complex reports can legitimately need sections.
PASS result
PASS means the file matches the registered structural counts. It does not validate clauses, data mappings, appearance, accessibility, or approval authority.
Split or consolidate through a controlled pilot
Choose one document family with known maintenance pain. Baseline its current change time, regression cases, defects, approval delays, and release steps before changing the template boundary.
Split a master deliberately
Extract one coherent document type, preserve the shared style set, assign an owner and version, and compare its test scope with the old branch.
Consolidate focused variants carefully
Merge only variants that share structure, owner, release cadence, and approval. Do not consolidate merely because their fields overlap.
Run parallel outputs
Generate representative records through the old and new design, then compare text, tables, images, numbering, headers, footers, and pagination.
Retire with traceability
Mark the superseded template RETIRED, keep the decision and version history, and remove it from active workflow selection only after acceptance.
Map focused templates to the correct DocxForge Pro workflow
DocxForge Pro includes Contractor and Factory. The modules use focused templates in different units of work; neither requires one giant conditional master.
Contractor: one case, several different documents
Review one case dataset, then generate several different documents for that case from different Word templates. A case may need an agreement, cover letter, checklist, and summary, each with its own layout and approval boundary.
Factory: many rows, one standard template
Select many independent Excel rows and apply one standard Word template to each selected row. Every row creates its own document, so the template stays focused on one repeatable document type.
Contractor manages a set of different templates around one reviewed case. Factory manages one tested template across a row batch. Keep their template registrations explicit.
Contractor: different templates in one case
Review the shared case data, then choose the focused documents required for that case.


Factory: one focused template across many rows
Review independent Excel rows, then generate each row through one standard template.


Frequently asked questions
Are multiple focused Word templates always easier to maintain?
No. They usually reduce regression scope and make ownership clearer, but they also create a portfolio that needs naming, versioning, shared styles, approvals, and retirement rules. Without those controls, duplication can become the new maintenance problem.
When is one master template a reasonable choice?
Use one master when the outputs genuinely share the same layout, styles, release cycle, owner, and test plan, and their differences are small and stable. Do not choose it merely to keep the file count low.
What is the main risk of a large master template?
A local change can have a wide blast radius. Conditional sections, shared headers, numbering, fields, and layout rules interact inside one file, so every release needs regression tests across all supported variants.
How do focused templates avoid inconsistent branding?
Keep the documents focused but share a governed style set, approved assets, naming rules, reusable building blocks, and a common release checklist. Focused does not mean independently designed from scratch.
Does the VBA helper decide whether I need a master template?
No. It audits the registered portfolio: ownership, workflow mode, approval status, file presence, file metadata, bookmark counts, content-control counts, and section counts. The architectural decision still depends on change coupling, ownership, testing, and release cadence.
How do Contractor and Factory use templates differently?
Contractor starts with one reviewed case and creates several different documents from different Word templates. Factory starts with many independent Excel rows and applies one standard Word template to each selected row, creating a separate document per row.