Building a VBA-Powered Excel Configuration Management System: Store, Retrieve, and Version-Control Workbook Settings Using a Dedicated Config Sheet and INI-Style Architecture

You've built a sophisticated Excel workbook — maybe a dynamic reporting dashboard or a multi-workbook consolidation engine — and now you're facing the configuration problem. Database connection strings are hardcoded into procedures. The output folder path exists in three different macros, none of which agree when the server gets renamed. A colleague changes the refresh interval in one place and wonders why nothing else updates. You've graduated from "workbook" to "application," and your application desperately needs a configuration layer.

The approach most developers reach for first — named ranges scattered across sheets, or Const declarations at the top of modules — works until it doesn't. Named ranges break when sheets are renamed or deleted. Module-level constants require a developer to open the VBA editor every time a business user needs to change a file path. Neither approach has any concept of history, rollback, or environment (dev vs. production). What you need is what software engineers have had for decades: a proper configuration management system.

By the end of this lesson, you'll have built a complete, production-ready configuration management system inside Excel. It will store settings in a structured, human-readable config sheet using INI-style sections. It will expose a clean API for reading and writing settings from any macro in your workbook. And it will maintain a version history so you can see who changed what, when, and roll back to any previous state. This is the infrastructure layer that serious Excel applications are built on.

What you'll learn:

• How to design an INI-style config sheet architecture that separates sections, keys, and values cleanly
• How to build a ConfigManager VBA module with a full read/write/delete API that any other module can call
• How to implement version control for settings, including timestamped history and rollback functionality
• How to validate and type-cast configuration values so callers get the correct data types
• How to protect the config sheet from accidental edits while keeping it accessible to the VBA layer

---

Prerequisites

This lesson is pitched at expert VBA practitioners. You should be comfortable with:

• Writing and organizing VBA modules and working with the Excel Object Model
• Working with VBA arrays and collections for in-memory data structures
• Basic error handling patterns in VBA
• Writing VBA procedures and functions including Function return types and optional parameters

Familiarity with INI file formats from any language will help but isn't required.

---

Why INI-Style Architecture Is the Right Model for Excel Config

Before writing a single line of VBA, it's worth understanding why the INI-style model is the right architectural choice, and what alternatives look like when they fail at scale.

The classic INI format groups settings into named sections, and within each section, stores key-value pairs:

[Database]
ConnectionString=DRIVER={SQL Server};SERVER=PROD-SQL-01;DATABASE=Financials
CommandTimeout=30
MaxRows=50000

[Reporting]
OutputFolder=\\fileserver\reports\finance\
DateFormat=YYYY-MM-DD
IncludeSubtotals=TRUE

[Email]
SMTPServer=smtp.company.com
RecipientList=finance@company.com;cfo@company.com
SendOnCompletion=TRUE

This three-layer hierarchy — workbook → section → key — maps perfectly to a spreadsheet grid. Each row represents one key-value pair, with a section column acting as the grouping mechanism. The human-readability is intentional: a business analyst with no VBA knowledge should be able to look at the config sheet and understand what each setting does.

Compare this to the alternatives:

Module-level constants are the fastest approach but the most brittle. They require recompiling (saving the file) to change, they can't be changed by non-developers, and they provide no audit trail.

Named ranges as config values feel elegant until your workbook grows. Named ranges don't support grouping, they clutter the Name Manager, and they're trivially easy to delete by accident. As covered in Mastering Excel's Name Manager, named ranges are powerful tools but they're not a configuration system.

The Windows Registry is genuinely appropriate for machine-level settings (and VBA can write to it), but it's a terrible choice for workbook-level settings that need to travel with the file.

External INI or JSON files are the right answer for standalone applications but introduce a dependency: the file has to exist at a path that doesn't change. For a workbook that might be emailed, stored on SharePoint, or run from three different machines, self-contained configuration is far more robust.

The INI-style config sheet gives you the best of all worlds: human-readable, self-contained, structured, and accessible to both VBA and anyone who can read a spreadsheet.

---

Designing the Config Sheet Structure

The physical layout of your config sheet matters. Get it right now and everything downstream is easier. Get it wrong and you'll be refactoring it after you've already written the retrieval engine.

Here's the column structure we'll use:

Col A	Col B	Col C	Col D	Col E	Col F
Section	Key	Value	DataType	Description	LastModified

• Section (A): The INI section name. Examples: Database, Reporting, UI, Email. Case-insensitive in our engine, but store consistently.
• Key (B): The setting name within the section. ConnectionString, OutputFolder, MaxRetries. Unique within a section.
• Value (C): The raw string value. Everything is stored as text. The engine will cast it to the right type on retrieval.
• DataType (D): A type hint: String, Long, Double, Boolean, Date. This enables type-safe retrieval and validation.
• Description (E): Human-readable explanation of what this setting does. This is the inline documentation for your application.
• LastModified (F): Timestamp of the last write to this row, updated automatically by the engine.

Row 1 is a header row. Data starts at row 2. There's no sorting requirement — the engine will search for any section/key combination — but grouping by section makes the sheet legible.

Now create the ConfigHistory sheet alongside the Config sheet. This is where every change gets logged, creating your audit trail and enabling rollback:

Col A	Col B	Col C	Col D	Col E	Col F	Col G
ChangeID	Timestamp	ChangedBy	Section	Key	OldValue	NewValue

The ChangedBy field uses Application.UserName, which captures the Windows username of whoever ran the macro. Not cryptographically secure, but sufficient for accountability in most enterprise Excel environments.

Let's also define a named sheet constant at the top of our config module so the sheet names are never hardcoded twice:

' Module: ConfigManager
' Place at the top of a standard module

Option Explicit

Private Const CONFIG_SHEET_CODENAME  As String = "wsConfig"
Private Const HISTORY_SHEET_CODENAME As String = "wsConfigHistory"
Private Const CONFIG_HEADER_ROWS     As Long = 1
Private Const CONFIG_COL_SECTION     As Long = 1
Private Const CONFIG_COL_KEY         As Long = 2
Private Const CONFIG_COL_VALUE       As Long = 3
Private Const CONFIG_COL_DATATYPE    As Long = 4
Private Const CONFIG_COL_DESC        As Long = 5
Private Const CONFIG_COL_MODIFIED    As Long = 6
Private Const HISTORY_COL_CHANGEID   As Long = 1
Private Const HISTORY_COL_TIMESTAMP  As Long = 2
Private Const HISTORY_COL_USER       As Long = 3
Private Const HISTORY_COL_SECTION    As Long = 4
Private Const HISTORY_COL_KEY        As Long = 5
Private Const HISTORY_COL_OLDVAL     As Long = 6
Private Const HISTORY_COL_NEWVAL     As Long = 7

Using column index constants instead of magic numbers (Cells(row, 3)) means that if you ever decide to add a column or reorder the sheet, you change it in one place.

---

Building the Core ConfigManager Module

The engine has three fundamental operations: Find a config row, Get a value, and Set a value. Everything else — type casting, history logging, validation — wraps around these three primitives.

The Private Find Function

' Returns the row number of a config entry, or 0 if not found.
' This is the engine's core search primitive.
Private Function FindConfigRow(ByVal ws As Worksheet, _
                               ByVal sSection As String, _
                               ByVal sKey As String) As Long
    Dim lastRow As Long
    Dim i       As Long
    
    lastRow = ws.Cells(ws.Rows.Count, CONFIG_COL_SECTION).End(xlUp).Row
    
    If lastRow <= CONFIG_HEADER_ROWS Then
        FindConfigRow = 0
        Exit Function
    End If
    
    For i = CONFIG_HEADER_ROWS + 1 To lastRow
        If LCase(Trim(ws.Cells(i, CONFIG_COL_SECTION).Value)) = LCase(Trim(sSection)) And _
           LCase(Trim(ws.Cells(i, CONFIG_COL_KEY).Value)) = LCase(Trim(sKey)) Then
            FindConfigRow = i
            Exit Function
        End If
    Next i
    
    FindConfigRow = 0
End Function

A few intentional decisions here worth explaining. First, LCase(Trim(...)) on both sides makes the lookup case-insensitive and whitespace-tolerant. If a business user manually edits the sheet and adds a trailing space to "Database", your engine shouldn't silently fail. Second, we loop rather than use Application.Match because we need to match on two columns simultaneously, and a simple loop over typical config sizes (rarely more than 200 rows) is perfectly fast.

The GetConfigValue Function

' Retrieves a raw string value from the config sheet.
' Returns the defaultValue if the key doesn't exist.
Public Function GetConfigValue(ByVal sSection As String, _
                               ByVal sKey     As String, _
                               Optional ByVal defaultValue As String = "") As String
    Dim ws      As Worksheet
    Dim rowNum  As Long
    
    On Error GoTo ErrorHandler
    
    Set ws = GetConfigSheet()
    rowNum = FindConfigRow(ws, sSection, sKey)
    
    If rowNum = 0 Then
        GetConfigValue = defaultValue
        Exit Function
    End If
    
    GetConfigValue = CStr(ws.Cells(rowNum, CONFIG_COL_VALUE).Value)
    Exit Function
    
ErrorHandler:
    GetConfigValue = defaultValue
    Debug.Print "ConfigManager.GetConfigValue Error: " & Err.Number & " - " & Err.Description
End Function

The Optional defaultValue parameter is a crucial design choice. Callers can decide what "key not found" means to them. A connection string with no default should probably trigger a hard error in the calling code. A font size setting with a sensible default of "11" should just return that default gracefully. This keeps the config API flexible without forcing every caller to write its own error handling.

Type-Safe Retrieval Functions

Returning everything as a string and forcing callers to cast is a recipe for bugs. We'll expose typed retrieval functions that handle conversion and validation:

Public Function GetConfigLong(ByVal sSection  As String, _
                              ByVal sKey      As String, _
                              Optional ByVal defaultValue As Long = 0) As Long
    Dim rawValue As String
    rawValue = GetConfigValue(sSection, sKey, CStr(defaultValue))
    
    If IsNumeric(rawValue) Then
        GetConfigLong = CLng(rawValue)
    Else
        GetConfigLong = defaultValue
        Debug.Print "ConfigManager: Key [" & sSection & "]." & sKey & _
                    " expected Long but got '" & rawValue & "'. Using default."
    End If
End Function

Public Function GetConfigDouble(ByVal sSection  As String, _
                                ByVal sKey      As String, _
                                Optional ByVal defaultValue As Double = 0) As Double
    Dim rawValue As String
    rawValue = GetConfigValue(sSection, sKey, CStr(defaultValue))
    
    If IsNumeric(rawValue) Then
        GetConfigDouble = CDbl(rawValue)
    Else
        GetConfigDouble = defaultValue
    End If
End Function

Public Function GetConfigBoolean(ByVal sSection  As String, _
                                 ByVal sKey      As String, _
                                 Optional ByVal defaultValue As Boolean = False) As Boolean
    Dim rawValue As String
    rawValue = UCase(Trim(GetConfigValue(sSection, sKey, "")))
    
    Select Case rawValue
        Case "TRUE", "YES", "1", "ON":  GetConfigBoolean = True
        Case "FALSE", "NO", "0", "OFF": GetConfigBoolean = False
        Case "":                         GetConfigBoolean = defaultValue
        Case Else
            GetConfigBoolean = defaultValue
            Debug.Print "ConfigManager: Key [" & sSection & "]." & sKey & _
                        " expected Boolean but got '" & rawValue & "'. Using default."
    End Select
End Function

Public Function GetConfigDate(ByVal sSection  As String, _
                              ByVal sKey      As String, _
                              Optional ByVal defaultValue As Date = #1/1/1900#) As Date
    Dim rawValue As String
    rawValue = GetConfigValue(sSection, sKey, "")
    
    If IsDate(rawValue) Then
        GetConfigDate = CDate(rawValue)
    Else
        GetConfigDate = defaultValue
    End If
End Function

The Boolean handler deserves special attention. Real-world config files are inconsistent — sometimes TRUE, sometimes True, sometimes 1, sometimes Yes. Rather than accepting only one format and silently returning the wrong answer when it doesn't match, we accept all reasonable representations. This is the kind of defensive programming that distinguishes production-quality tools from scripts that work until they meet a real user.

The SetConfigValue Procedure

Writing is more complex than reading because it needs to handle two cases: updating an existing row and creating a new one. It also needs to log the change to history.

Public Sub SetConfigValue(ByVal sSection     As String, _
                          ByVal sKey         As String, _
                          ByVal sValue       As String, _
                          Optional ByVal sDataType    As String = "String", _
                          Optional ByVal sDescription As String = "")
    Dim ws       As Worksheet
    Dim wsHist   As Worksheet
    Dim rowNum   As Long
    Dim oldValue As String
    Dim isNew    As Boolean
    
    On Error GoTo ErrorHandler
    
    Set ws     = GetConfigSheet()
    Set wsHist = GetHistorySheet()
    
    ' Temporarily unprotect if protected
    If ws.ProtectContents Then ws.Unprotect Password:=GetSheetPassword()
    
    rowNum = FindConfigRow(ws, sSection, sKey)
    isNew  = (rowNum = 0)
    
    If isNew Then
        ' Append a new row
        rowNum = ws.Cells(ws.Rows.Count, CONFIG_COL_SECTION).End(xlUp).Row + 1
        oldValue = ""
        ws.Cells(rowNum, CONFIG_COL_SECTION).Value = sSection
        ws.Cells(rowNum, CONFIG_COL_KEY).Value     = sKey
        ws.Cells(rowNum, CONFIG_COL_DATATYPE).Value = sDataType
    Else
        oldValue = CStr(ws.Cells(rowNum, CONFIG_COL_VALUE).Value)
    End If
    
    ' Only write and log if the value actually changed
    If isNew Or (oldValue <> sValue) Then
        ws.Cells(rowNum, CONFIG_COL_VALUE).Value    = sValue
        ws.Cells(rowNum, CONFIG_COL_MODIFIED).Value = Now()
        ws.Cells(rowNum, CONFIG_COL_MODIFIED).NumberFormat = "yyyy-mm-dd hh:mm:ss"
        
        If sDescription <> "" Then
            ws.Cells(rowNum, CONFIG_COL_DESC).Value = sDescription
        End If
        
        ' Log to history
        LogConfigChange wsHist, sSection, sKey, oldValue, sValue
    End If
    
    ' Re-protect the sheet
    If ws.ProtectContents = False Then
        ws.Protect Password:=GetSheetPassword(), _
                   UserInterfaceOnly:=True, _
                   AllowFormattingCells:=True
    End If
    
    Exit Sub
    
ErrorHandler:
    MsgBox "ConfigManager.SetConfigValue failed:" & vbNewLine & _
           "Section: " & sSection & ", Key: " & sKey & vbNewLine & _
           "Error " & Err.Number & ": " & Err.Description, vbCritical
End Sub

The UserInterfaceOnly:=True argument in the Protect call is one of the most useful and least-known options in Excel's protection API. When set, it means VBA code can continue to modify the sheet without needing to unprotect it first, but a human using the keyboard or mouse cannot make changes. This is exactly the behavior we want: our engine can write settings, but a user can't accidentally overwrite the ConnectionString by fat-fingering a cell.

The History Logger

Private Sub LogConfigChange(ByVal wsHist   As Worksheet, _
                             ByVal sSection As String, _
                             ByVal sKey     As String, _
                             ByVal oldVal   As String, _
                             ByVal newVal   As String)
    Dim nextRow  As Long
    Dim changeID As Long
    
    nextRow  = wsHist.Cells(wsHist.Rows.Count, HISTORY_COL_CHANGEID).End(xlUp).Row + 1
    
    ' Generate a sequential change ID
    If nextRow = 2 Then
        changeID = 1
    Else
        changeID = CLng(wsHist.Cells(nextRow - 1, HISTORY_COL_CHANGEID).Value) + 1
    End If
    
    wsHist.Cells(nextRow, HISTORY_COL_CHANGEID).Value  = changeID
    wsHist.Cells(nextRow, HISTORY_COL_TIMESTAMP).Value = Now()
    wsHist.Cells(nextRow, HISTORY_COL_TIMESTAMP).NumberFormat = "yyyy-mm-dd hh:mm:ss"
    wsHist.Cells(nextRow, HISTORY_COL_USER).Value      = Application.UserName
    wsHist.Cells(nextRow, HISTORY_COL_SECTION).Value   = sSection
    wsHist.Cells(nextRow, HISTORY_COL_KEY).Value       = sKey
    wsHist.Cells(nextRow, HISTORY_COL_OLDVAL).Value    = oldVal
    wsHist.Cells(nextRow, HISTORY_COL_NEWVAL).Value    = newVal
End Sub

---

Adding Delete and Batch Operations

A configuration system that can only read and write individual keys is useful, but real applications need more surgical operations.

Deleting a Config Entry

Public Sub DeleteConfigValue(ByVal sSection As String, ByVal sKey As String)
    Dim ws     As Worksheet
    Dim rowNum As Long
    Dim oldVal As String
    
    Set ws = GetConfigSheet()
    If ws.ProtectContents Then ws.Unprotect Password:=GetSheetPassword()
    
    rowNum = FindConfigRow(ws, sSection, sKey)
    
    If rowNum > 0 Then
        oldVal = CStr(ws.Cells(rowNum, CONFIG_COL_VALUE).Value)
        ws.Rows(rowNum).Delete
        LogConfigChange GetHistorySheet(), sSection, sKey, oldVal, "[DELETED]"
    End If
    
    ws.Protect Password:=GetSheetPassword(), UserInterfaceOnly:=True
End Sub

Batch Export to Dictionary

For procedures that need to read many settings at once (like a startup initialization that loads 20 settings before running), making 20 individual calls to GetConfigValue means 20 round trips to the worksheet. It's faster to load an entire section at once into a Scripting.Dictionary:

' Returns a Scripting.Dictionary of all key-value pairs in a section.
' Requires reference to Microsoft Scripting Runtime (or use late binding).
Public Function GetConfigSection(ByVal sSection As String) As Object
    Dim ws      As Worksheet
    Dim dict    As Object
    Dim lastRow As Long
    Dim i       As Long
    
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare  ' Case-insensitive key lookup
    
    Set ws = GetConfigSheet()
    lastRow = ws.Cells(ws.Rows.Count, CONFIG_COL_SECTION).End(xlUp).Row
    
    For i = CONFIG_HEADER_ROWS + 1 To lastRow
        If LCase(Trim(ws.Cells(i, CONFIG_COL_SECTION).Value)) = LCase(sSection) Then
            dict(CStr(ws.Cells(i, CONFIG_COL_KEY).Value)) = _
                CStr(ws.Cells(i, CONFIG_COL_VALUE).Value)
        End If
    Next i
    
    Set GetConfigSection = dict
End Function

Calling code then looks like this — clean, readable, and performant:

Sub InitializeReportingEngine()
    Dim cfg As Object
    Set cfg = GetConfigSection("Reporting")
    
    Dim outputFolder  As String
    Dim dateFormat    As String
    Dim maxRetries    As Long
    
    outputFolder = cfg("OutputFolder")
    dateFormat   = cfg("DateFormat")
    maxRetries   = CLng(cfg("MaxRetries"))
    
    ' ... rest of initialization
End Sub

---

Implementing Version Control and Rollback

The history log is useful for auditing but becomes genuinely powerful when you can programmatically roll back to a previous state. Here's how to implement that.

Rolling Back a Single Key

' Rolls back a specific key to its value at a given ChangeID.
' Returns True if rollback was successful.
Public Function RollbackToChangeID(ByVal targetChangeID As Long, _
                                   ByVal sSection       As String, _
                                   ByVal sKey           As String) As Boolean
    Dim wsHist   As Worksheet
    Dim lastRow  As Long
    Dim i        As Long
    Dim targetVal As String
    Dim found    As Boolean
    
    Set wsHist = GetHistorySheet()
    lastRow    = wsHist.Cells(wsHist.Rows.Count, HISTORY_COL_CHANGEID).End(xlUp).Row
    found      = False
    
    For i = CONFIG_HEADER_ROWS + 1 To lastRow
        If CLng(wsHist.Cells(i, HISTORY_COL_CHANGEID).Value) = targetChangeID And _
           LCase(wsHist.Cells(i, HISTORY_COL_SECTION).Value) = LCase(sSection) And _
           LCase(wsHist.Cells(i, HISTORY_COL_KEY).Value) = LCase(sKey) Then
            ' The OldValue at this ChangeID is what we want to restore
            targetVal = CStr(wsHist.Cells(i, HISTORY_COL_OLDVAL).Value)
            found = True
            Exit For
        End If
    Next i
    
    If found Then
        SetConfigValue sSection, sKey, targetVal
        RollbackToChangeID = True
    Else
        RollbackToChangeID = False
    End If
End Function

Rolling Back an Entire Config Snapshot

For more dramatic rollbacks — "restore everything to how it was at 9 AM before anyone touched it" — you want to snapshot the entire config state at a timestamp:

' Restores all config values to their state just before a given timestamp.
' This is a "point-in-time" restore of the entire config.
Public Sub RestoreConfigToTimestamp(ByVal targetTime As Date)
    Dim wsHist   As Worksheet
    Dim ws       As Worksheet
    Dim lastRow  As Long
    Dim i        As Long
    Dim sectionKey As String
    Dim restored As Object  ' Scripting.Dictionary to track what we've restored
    
    Set wsHist  = GetHistorySheet()
    Set ws      = GetConfigSheet()
    Set restored = CreateObject("Scripting.Dictionary")
    restored.CompareMode = vbTextCompare
    
    lastRow = wsHist.Cells(wsHist.Rows.Count, HISTORY_COL_CHANGEID).End(xlUp).Row
    
    ' Walk history BACKWARDS from most recent, restoring the FIRST occurrence
    ' of each key we find that predates our target timestamp
    Dim i2 As Long
    For i2 = lastRow To CONFIG_HEADER_ROWS + 1 Step -1
        Dim rowTime As Date
        rowTime = CDate(wsHist.Cells(i2, HISTORY_COL_TIMESTAMP).Value)
        
        If rowTime < targetTime Then
            Dim sec As String, ky As String
            sec = CStr(wsHist.Cells(i2, HISTORY_COL_SECTION).Value)
            ky  = CStr(wsHist.Cells(i2, HISTORY_COL_KEY).Value)
            sectionKey = LCase(sec & "||" & ky)
            
            If Not restored.Exists(sectionKey) Then
                ' The NewValue at this row was the value in effect just after this change
                ' We want OldValue for "before" this change, or NewValue for "at" this change
                ' For point-in-time restore TO targetTime, we use the NewValue of the 
                ' most recent change BEFORE targetTime
                Dim valToRestore As String
                valToRestore = CStr(wsHist.Cells(i2, HISTORY_COL_NEWVAL).Value)
                
                If valToRestore <> "[DELETED]" Then
                    SetConfigValue sec, ky, valToRestore
                End If
                restored(sectionKey) = True
            End If
        End If
    Next i2
    
    MsgBox "Config restored to state at " & Format(targetTime, "yyyy-mm-dd hh:mm:ss") & _
           vbNewLine & restored.Count & " settings restored.", vbInformation
End Sub

---

Initialization, Sheet Setup, and Protection

All this machinery needs to be set up when the workbook opens. The Workbook_Open event is where you do that.

The Setup Routine

' Module: ConfigManager
' Called from Workbook_Open to ensure config sheets exist and are protected correctly.
Public Sub InitializeConfigSystem()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    ' Ensure Config sheet exists
    EnsureSheetExists wb, "Config", "wsConfig"
    EnsureSheetExists wb, "ConfigHistory", "wsConfigHistory"
    
    ' Set up headers if sheets are empty
    SetupConfigHeaders wb.Worksheets("Config")
    SetupHistoryHeaders wb.Worksheets("ConfigHistory")
    
    ' Apply UI-only protection (must be done on every open)
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Config")
    ws.Protect Password:=GetSheetPassword(), _
               UserInterfaceOnly:=True, _
               AllowFormattingCells:=True
End Sub

Private Sub EnsureSheetExists(ByVal wb As Workbook, _
                               ByVal sheetName As String, _
                               ByVal codeName As String)
    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = wb.Worksheets(sheetName)
    On Error GoTo 0
    
    If ws Is Nothing Then
        Set ws = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
        ws.Name = sheetName
        ' Note: CodeName must be set manually via the Properties window in the VBA editor.
        ' You cannot set CodeName programmatically in VBA (it's a design-time property).
    End If
End Sub

Private Sub SetupConfigHeaders(ByVal ws As Worksheet)
    If ws.Cells(1, 1).Value = "Section" Then Exit Sub  ' Already set up
    
    ws.Cells(1, CONFIG_COL_SECTION).Value  = "Section"
    ws.Cells(1, CONFIG_COL_KEY).Value      = "Key"
    ws.Cells(1, CONFIG_COL_VALUE).Value    = "Value"
    ws.Cells(1, CONFIG_COL_DATATYPE).Value = "DataType"
    ws.Cells(1, CONFIG_COL_DESC).Value     = "Description"
    ws.Cells(1, CONFIG_COL_MODIFIED).Value = "LastModified"
    
    ' Freeze the header row
    ws.Activate
    ws.Rows(2).Select
    ActiveWindow.FreezePanes = True
    
    ' Format header row
    With ws.Rows(1).Font
        .Bold = True
        .Size = 11
    End With
End Sub

And in ThisWorkbook:

Private Sub Workbook_Open()
    InitializeConfigSystem
    ' ... rest of workbook startup
End Sub

The Password Helper

' Returns the sheet protection password.
' In production, consider storing this in a more secure location.
' For most Excel workbooks, sheet protection is a deterrent, not a security control.
Private Function GetSheetPassword() As String
    GetSheetPassword = "Cfg@Mgr2024!"
End Function

---

Performance Optimization: The In-Memory Cache

For workbooks where many macros call GetConfigValue frequently (think event-driven workflows where a Worksheet_Change event fires repeatedly), the repeated cell reads add up. The solution is an in-memory cache that loads all config values at startup and serves subsequent reads from memory.

' Module-level cache
Private mConfigCache    As Object  ' Scripting.Dictionary
Private mCacheLoaded    As Boolean

Private Sub LoadConfigCache()
    Dim ws      As Worksheet
    Dim lastRow As Long
    Dim i       As Long
    Dim cacheKey As String
    
    Set mConfigCache = CreateObject("Scripting.Dictionary")
    mConfigCache.CompareMode = vbTextCompare
    
    Set ws = GetConfigSheet()
    lastRow = ws.Cells(ws.Rows.Count, CONFIG_COL_SECTION).End(xlUp).Row
    
    For i = CONFIG_HEADER_ROWS + 1 To lastRow
        cacheKey = LCase(Trim(ws.Cells(i, CONFIG_COL_SECTION).Value)) & "||" & _
                   LCase(Trim(ws.Cells(i, CONFIG_COL_KEY).Value))
        mConfigCache(cacheKey) = CStr(ws.Cells(i, CONFIG_COL_VALUE).Value)
    Next i
    
    mCacheLoaded = True
End Sub

' Call this after any SetConfigValue to invalidate the cache
Public Sub InvalidateCache()
    mCacheLoaded = False
    If Not mConfigCache Is Nothing Then
        mConfigCache.RemoveAll
    End If
End Sub

' Modified GetConfigValue that uses cache
Public Function GetConfigValue(ByVal sSection As String, _
                               ByVal sKey     As String, _
                               Optional ByVal defaultValue As String = "") As String
    Dim cacheKey As String
    
    If Not mCacheLoaded Then LoadConfigCache
    
    cacheKey = LCase(Trim(sSection)) & "||" & LCase(Trim(sKey))
    
    If mConfigCache.Exists(cacheKey) Then
        GetConfigValue = mConfigCache(cacheKey)
    Else
        GetConfigValue = defaultValue
    End If
End Function

With this cache in place, a workbook that reads 50 config values during a refresh cycle makes exactly one pass over the config sheet (when the cache is cold) and then serves all subsequent reads from an in-memory dictionary. The difference is measurable on slower machines or larger config sheets.

Call InvalidateCache at the end of SetConfigValue to ensure writes are reflected immediately in subsequent reads. This gives you cache coherence: stale reads are only possible if two processes are simultaneously modifying the same workbook, which isn't a scenario Excel's single-threaded model supports.

---

Practical Usage Patterns: Putting It All Together

Here's how a production reporting macro would consume this config system:

Sub RunMonthlyFinancialReport()
    ' Load entire section at once for efficiency
    Dim dbCfg  As Object
    Dim rptCfg As Object
    
    Set dbCfg  = GetConfigSection("Database")
    Set rptCfg = GetConfigSection("Reporting")
    
    ' Type-safe retrieval
    Dim connStr     As String
    Dim outputPath  As String
    Dim maxRows     As Long
    Dim sendEmail   As Boolean
    Dim reportDate  As Date
    
    connStr    = dbCfg("ConnectionString")
    outputPath = rptCfg("OutputFolder")
    maxRows    = GetConfigLong("Database", "MaxRows", 10000)
    sendEmail  = GetConfigBoolean("Email", "SendOnCompletion", False)
    reportDate = GetConfigDate("Reporting", "ReportingDate", Date)
    
    ' Validate critical settings before doing any work
    If Len(connStr) = 0 Then
        MsgBox "No database connection string configured. " & _
               "Please set [Database].ConnectionString in the Config sheet.", vbCritical
        Exit Sub
    End If
    
    If Not FolderExists(outputPath) Then
        MsgBox "Output folder does not exist: " & outputPath, vbCritical
        Exit Sub
    End If
    
    ' Run the report...
    Debug.Print "Connecting to: " & connStr
    Debug.Print "Writing to: " & outputPath
    Debug.Print "Max rows: " & maxRows
    
    ' After successful run, update the LastRunDate in config
    SetConfigValue "Reporting", "LastRunDate", Format(Now(), "yyyy-mm-dd hh:mm:ss"), _
                   "Date", "Timestamp of most recent successful report run"
    SetConfigValue "Reporting", "LastRunUser", Application.UserName, _
                   "String", "User who last ran the report"
End Sub

This pattern — load config at the start, validate critical values before doing work, update the "last run" metadata at the end — is the standard recipe for professional Excel automation. It makes the macro self-documenting: anyone who opens the Config sheet can see when the report last ran and who ran it.

---

Integrating with UserForms for a Settings UI

For business users who need to change settings without touching the raw config sheet, you can build a simple settings dialog. This pairs naturally with the UserForms for Custom Data Entry Interfaces approach.

The settings form would surface only the user-editable settings (not internal ones like LastRunDate) and write changes back through the SetConfigValue API. The key architectural point: the form doesn't write directly to the sheet. It calls SetConfigValue, which means every change goes through the history logger, the cache invalidation, and the protection toggle. The form doesn't need to know about any of that.

' In the UserForm's OK button click handler:
Private Sub cmdOK_Click()
    ' Write user-entered values back through the config API
    SetConfigValue "Reporting", "OutputFolder",  txtOutputFolder.Text, "String", "Report output directory"
    SetConfigValue "Database",  "CommandTimeout", txtTimeout.Text,      "Long",   "SQL command timeout in seconds"
    SetConfigValue "Email",     "RecipientList",  txtRecipients.Text,   "String", "Semicolon-separated email list"
    
    MsgBox "Settings saved successfully.", vbInformation
    Me.Hide
End Sub

' In the UserForm's Initialize handler:
Private Sub UserForm_Initialize()
    txtOutputFolder.Text = GetConfigValue("Reporting", "OutputFolder", "")
    txtTimeout.Text      = GetConfigValue("Database",  "CommandTimeout", "30")
    txtRecipients.Text   = GetConfigValue("Email",     "RecipientList", "")
End Sub

---

Hands-On Exercise

Build a complete configuration management system for a fictional sales reporting workbook. Here's what to implement:

Step 1: Create the Config and ConfigHistory sheets. Set up the column structure described in this lesson. Populate the Config sheet with at least these settings:

Section	Key	Value	DataType	Description
Database	ConnectionString	(your SQL string)	String	Primary DB connection
Database	CommandTimeout	30	Long	Query timeout in seconds
Reporting	OutputFolder	C:\Reports\	String	Export destination
Reporting	IncludeSubtotals	TRUE	Boolean	Whether to add subtotal rows
Email	SendOnCompletion	FALSE	Boolean	Auto-email when report completes
Email	RecipientList	analyst@company.com	String	Comma-separated recipients

Step 2: Implement the full ConfigManager module. Include GetConfigValue, GetConfigLong, GetConfigBoolean, SetConfigValue, DeleteConfigValue, GetConfigSection, and InvalidateCache.

Step 3: Wire up Workbook_Open to call InitializeConfigSystem, which protects the Config sheet with UserInterfaceOnly:=True.

Step 4: Write a test macro called RunConfigTests that:

• Reads each value and prints it to the Immediate Window (Debug.Print)
• Changes the CommandTimeout to 60, then reads it back
• Verifies the change appears in ConfigHistory
• Rolls back CommandTimeout to 30 using RollbackToChangeID
• Verifies the rollback appears as a new history entry

Step 5: Build a simple UserForm with three text boxes for OutputFolder, CommandTimeout, and RecipientList. Wire the form to read from and write to the config system through the API.

---

Common Mistakes & Troubleshooting

The config sheet gets renamed by a well-meaning colleague. This breaks any code that references the sheet by tab name. The fix is to use CodeNames (wsConfig) rather than Worksheets("Config"). Set CodeNames in the VBA editor's Properties window (F4 on the sheet object in Project Explorer). CodeNames are stored in the workbook's internal structure and are immune to tab renames.

UserInterfaceOnly:=True stops working after save and reopen. This is expected behavior. Protection attributes including UserInterfaceOnly reset on file open. Always call ws.Protect in Workbook_Open. If you're seeing this bug, add a Debug.Print ws.ProtectContents line in your startup code to verify protection state.

Rollback restores a value to [DELETED]. This happens when RollbackToChangeID targets a history entry where the key was deleted. Add a check in the rollback function: if targetVal = "[DELETED]", call DeleteConfigValue instead of SetConfigValue.

GetConfigSection returns an empty Dictionary even though values exist. Almost always a section name mismatch. Remember the comparison is case-insensitive, but it's not whitespace-insensitive at the section level in the dictionary version. Add Trim() calls inside GetConfigSection when comparing section names, just as FindConfigRow does.

The cache returns stale values after a SetConfigValue call. Check that InvalidateCache is being called at the end of SetConfigValue. If you've overloaded GetConfigValue with the cached version, make sure the module-level mCacheLoaded flag is getting reset. A common error is having two versions of GetConfigValue in different modules — VBA will call whichever is in the same module as the calling code, which may be the uncached version.

Performance degrades with large history sheets. The history sheet grows unbounded. For long-lived workbooks, add an ArchiveHistory procedure that moves rows older than N months to an archive sheet or exports them to a CSV. History sheets with more than 10,000 rows can slow down the rollback functions, which do full linear scans.

---

Architecture Extensions Worth Considering

Environment switching. Add a top-level setting [System].Environment with values DEV, UAT, or PROD. Then add a GetEffectiveValue function that first looks for [Database.PROD].ConnectionString and falls back to [Database].ConnectionString if the environment-specific key doesn't exist. This pattern lets you maintain separate database endpoints for different environments in the same workbook without duplicating your entire config.

Config import/export. Write ExportConfigToINI and ImportConfigFromINI procedures that read/write actual .ini files using the Open...For Output...Write file I/O pattern. This lets you source-control config separately from the workbook in a text-based format that diffs cleanly in Git. Combined with the automating file operations with VBA patterns, you can build a deployment script that imports environment-specific config automatically.

Config schema validation. Add a ValidateConfig procedure that checks every required key is present, every value matches its declared DataType, and no values are empty that shouldn't be. Run this in Workbook_Open and present a formatted error report if validation fails. This is especially valuable for workbooks deployed to multiple users — it catches "fresh install" configuration problems immediately rather than letting them surface as cryptic runtime errors later.

Integration with custom ribbons. A "Settings" button in a custom ribbon that opens your settings UserForm, and a "Config History" button that activates the history sheet, makes your configuration system genuinely accessible to business users. The custom ribbon development lesson covers the XML and VBA callbacks needed to wire this up.

---

Summary & Next Steps

You now have a production-grade configuration management system that transforms your Excel workbooks from brittle, hardcoded scripts into proper applications with a configuration layer. The architecture is built around five key ideas: centralized storage on a dedicated Config sheet, INI-style section/key/value organization, a clean API that hides sheet mechanics from callers, an append-only history log that supports full rollback, and sheet protection that keeps VBA access open while deterring accidental user edits.

The most important habit to build from this lesson is never hardcode workbook-level settings into VBA procedures. The moment you write Const OUTPUT_PATH = "C:\Reports\" in a module, you've created future maintenance debt. Every path, every threshold, every toggle, every connection string belongs in the config sheet where it can be changed without touching code, tracked through history, and understood at a glance by anyone who opens the workbook.

From here, natural next steps include:

• Integrating this config system with an event-driven framework so config changes can automatically trigger dependent behaviors
• Adding a formal VBA testing framework so your config read/write API has unit tests that catch regressions when you modify the engine
• Packaging the entire ConfigManager module into an Excel Add-In that you can deploy across your organization's workbooks, giving every workbook the same configuration infrastructure

A mature workbook that users trust isn't distinguished by its formulas or its charts — it's distinguished by its reliability and maintainability. A proper configuration layer is a foundational piece of that reliability.