Building a VBA-Powered Parameter Query System: Dynamically Filter, Aggregate, and Export Dataset Slices to Separate Workbooks on Demand

Introduction

Picture this: your organization tracks sales transactions across 14 regional offices, and every Monday morning you spend two hours manually filtering the master dataset, copying region-specific slices to separate workbooks, calculating summary metrics, and emailing the files to the relevant managers. By the time you're done, it's nearly noon and the data is already hours stale. If this scenario sounds familiar — whether the domain is sales territories, product categories, client accounts, or cost centers — you're about to automate your Monday mornings out of existence.

This lesson builds a complete, production-ready parameter query system in VBA. The system accepts multiple filter criteria through a UserForm interface, applies them against a master dataset, aggregates the results, and exports precisely sliced workbooks to a designated output folder — all on demand, in seconds. This is not a simplified demo. You'll write code that handles real-world messiness: partial matches, multi-value selections, aggregation logic, dynamic file naming, and error recovery.

By the end of this lesson, you'll have a reusable system you can drop onto virtually any master dataset and start exporting filtered slices immediately.

What you'll learn:

• How to design a parameter query engine that accepts multiple simultaneous filter conditions
• How to efficiently loop through large datasets and apply multi-criteria filtering logic
• How to aggregate filtered data (sums, counts, averages) before writing to output
• How to programmatically create, populate, format, and save separate workbooks
• How to build a UserForm front-end that drives the entire query and export pipeline

---

Prerequisites

You should be comfortable with VBA fundamentals before diving in. Specifically, you'll want to understand how VBA variables, data types, and control structures work, and you should have experience working with ranges, cells, and worksheets in VBA. Familiarity with Excel Tables (ListObjects) is helpful since we'll use one as our master data source. If UserForms are new to you, the lesson on building UserForms for custom data entry interfaces will give you the grounding you need.

---

Designing the System Architecture

Before writing a single line of VBA, let's map what we're actually building. A parameter query system has four distinct layers:

1. The data layer — the master dataset living in an Excel Table on a sheet called MasterData
2. The parameter layer — a UserForm that collects filter criteria from the user
3. The processing layer — VBA procedures that read parameters, loop through the dataset, apply filters, and aggregate results
4. The export layer — procedures that create new workbooks, write the filtered and aggregated data, format them, and save to disk

These layers communicate through a central parameter object — a Type structure that holds all the query settings. This design means each layer is decoupled: you can change the UserForm without touching the filter logic, and you can change the export format without touching the aggregation.

Setting Up the Master Dataset

Our working scenario: a national retailer with transaction-level sales data. The master table, named tblSales, lives on the MasterData sheet and has these columns:

Column	Type	Description
TransactionID	Text	Unique identifier
Region	Text	Northeast, Southeast, Midwest, West, Southwest
Category	Text	Electronics, Apparel, Furniture, Sporting Goods
Salesperson	Text	Rep name
TransactionDate	Date	Sale date
Units	Integer	Quantity sold
UnitPrice	Currency	Price per unit
Revenue	Currency	Units × UnitPrice
ReturnFlag	Boolean	TRUE if returned

A realistic dataset for this exercise should have at least 5,000 rows — enough that looping takes a measurable moment, which helps you appreciate the performance decisions we make along the way. You can generate synthetic data using SEQUENCE and RANDARRAY, or use any transactional dataset you have available.

---

Defining the Parameter Type Structure

The cleanest way to pass query parameters through multiple procedures is with a user-defined Type. Add this to a standard module called modTypes:

' modTypes.bas
' Centralized type definitions for the parameter query system

Public Type QueryParameters
    ' Filter criteria
    Regions()       As String   ' Array of selected regions (multi-select)
    Categories()    As String   ' Array of selected categories
    Salesperson     As String   ' Partial match on salesperson name
    DateFrom        As Date
    DateTo          As Date
    ExcludeReturns  As Boolean
    
    ' Aggregation options
    AggregateBy     As String   ' "Region", "Category", "Salesperson", or "None"
    IncludeDetail   As Boolean  ' Write row-level data in addition to summary
    
    ' Export options
    OutputFolder    As String
    FilePrefix      As String
    SplitByField    As String   ' If set, creates one workbook per unique value
End Type

Using a Type instead of a pile of module-level variables keeps your parameter state explicit and portable. When SplitByField is populated, the engine creates one output workbook per unique value of that field — so you can generate one file per region in a single run.

---

Building the UserForm Interface

Insert a UserForm named frmQueryParameters. The layout is straightforward:

• A ListBox named lstRegions (MultiSelect = fmMultiSelectMulti) populated with region values
• A ListBox named lstCategories (MultiSelect = fmMultiSelectMulti) for categories
• A TextBox named txtSalesperson for partial name matching
• Two TextBoxes named txtDateFrom and txtDateTo for the date range
• A CheckBox named chkExcludeReturns labeled "Exclude returned transactions"
• A ComboBox named cboAggregateBy with options: None, Region, Category, Salesperson
• A CheckBox named chkIncludeDetail labeled "Include row-level detail"
• A TextBox named txtOutputFolder and a CommandButton named btnBrowse to open a folder picker
• A TextBox named txtFilePrefix for the output filename prefix
• A ComboBox named cboSplitByField with options: (none), Region, Category
• CommandButtons btnRun and btnCancel

If you want to go deeper on the controls themselves, the lesson on VBA UserForm controls: ComboBoxes, ListBoxes, and MultiPage widgets covers all the nuances.

Here's the full UserForm code:

' frmQueryParameters code module

Private Sub UserForm_Initialize()
    ' Populate region list from unique values in tblSales
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim cell As Range
    Dim colIdx As Long
    Dim dict As Object
    
    Set ws = ThisWorkbook.Sheets("MasterData")
    Set tbl = ws.ListObjects("tblSales")
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' Region column
    colIdx = tbl.ListColumns("Region").Index
    For Each cell In tbl.DataBodyRange.Columns(colIdx).Cells
        If Not dict.Exists(cell.Value) Then
            dict.Add cell.Value, 1
            lstRegions.AddItem cell.Value
        End If
    Next cell
    
    dict.RemoveAll
    
    ' Category column
    colIdx = tbl.ListColumns("Category").Index
    For Each cell In tbl.DataBodyRange.Columns(colIdx).Cells
        If Not dict.Exists(cell.Value) Then
            dict.Add cell.Value, 1
            lstCategories.AddItem cell.Value
        End If
    Next cell
    
    ' Aggregate options
    cboAggregateBy.AddItem "None"
    cboAggregateBy.AddItem "Region"
    cboAggregateBy.AddItem "Category"
    cboAggregateBy.AddItem "Salesperson"
    cboAggregateBy.ListIndex = 0
    
    ' Split options
    cboSplitByField.AddItem "(none)"
    cboSplitByField.AddItem "Region"
    cboSplitByField.AddItem "Category"
    cboSplitByField.ListIndex = 0
    
    ' Default date range: last 90 days
    txtDateFrom.Text = Format(Date - 90, "yyyy-mm-dd")
    txtDateTo.Text = Format(Date, "yyyy-mm-dd")
    
    ' Default output folder
    txtOutputFolder.Text = Environ("USERPROFILE") & "\Desktop\QueryExports"
    txtFilePrefix.Text = "SalesExport_"
End Sub

Private Sub btnBrowse_Click()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    fd.Title = "Select Output Folder"
    If fd.Show = -1 Then
        txtOutputFolder.Text = fd.SelectedItems(1)
    End If
End Sub

Private Sub btnRun_Click()
    Dim params As QueryParameters
    
    ' Validate before collecting
    If Not ValidateForm(params) Then Exit Sub
    
    ' Collect parameters from form
    CollectParameters params
    
    ' Hide form and run the engine
    Me.Hide
    RunQueryEngine params
    
    MsgBox "Export complete! Files saved to:" & vbCrLf & params.OutputFolder, _
           vbInformation, "Query Complete"
    Unload Me
End Sub

Private Sub btnCancel_Click()
    Unload Me
End Sub

Private Function ValidateForm(ByRef params As QueryParameters) As Boolean
    ValidateForm = True
    
    If txtOutputFolder.Text = "" Then
        MsgBox "Please specify an output folder.", vbExclamation
        ValidateForm = False
        Exit Function
    End If
    
    If Not IsDate(txtDateFrom.Text) Or Not IsDate(txtDateTo.Text) Then
        MsgBox "Please enter valid dates in yyyy-mm-dd format.", vbExclamation
        ValidateForm = False
        Exit Function
    End If
    
    If CDate(txtDateTo.Text) < CDate(txtDateFrom.Text) Then
        MsgBox "Date To cannot be earlier than Date From.", vbExclamation
        ValidateForm = False
        Exit Function
    End If
End Function

Private Sub CollectParameters(ByRef params As QueryParameters)
    Dim i As Long
    Dim selCount As Long
    
    ' Regions: collect selected items
    selCount = 0
    For i = 0 To lstRegions.ListCount - 1
        If lstRegions.Selected(i) Then selCount = selCount + 1
    Next i
    
    If selCount = 0 Then
        ' No selection = all regions
        ReDim params.Regions(0 To lstRegions.ListCount - 1)
        For i = 0 To lstRegions.ListCount - 1
            params.Regions(i) = lstRegions.List(i)
        Next i
    Else
        ReDim params.Regions(0 To selCount - 1)
        Dim idx As Long: idx = 0
        For i = 0 To lstRegions.ListCount - 1
            If lstRegions.Selected(i) Then
                params.Regions(idx) = lstRegions.List(i)
                idx = idx + 1
            End If
        Next i
    End If
    
    ' Categories: same pattern
    selCount = 0
    For i = 0 To lstCategories.ListCount - 1
        If lstCategories.Selected(i) Then selCount = selCount + 1
    Next i
    
    If selCount = 0 Then
        ReDim params.Categories(0 To lstCategories.ListCount - 1)
        For i = 0 To lstCategories.ListCount - 1
            params.Categories(i) = lstCategories.List(i)
        Next i
    Else
        ReDim params.Categories(0 To selCount - 1)
        idx = 0
        For i = 0 To lstCategories.ListCount - 1
            If lstCategories.Selected(i) Then
                params.Categories(idx) = lstCategories.List(i)
                idx = idx + 1
            End If
        Next i
    End If
    
    params.Salesperson    = Trim(txtSalesperson.Text)
    params.DateFrom       = CDate(txtDateFrom.Text)
    params.DateTo         = CDate(txtDateTo.Text)
    params.ExcludeReturns = chkExcludeReturns.Value
    params.AggregateBy    = cboAggregateBy.Text
    params.IncludeDetail  = chkIncludeDetail.Value
    params.OutputFolder   = txtOutputFolder.Text
    params.FilePrefix     = txtFilePrefix.Text
    
    If cboSplitByField.ListIndex = 0 Then
        params.SplitByField = ""
    Else
        params.SplitByField = cboSplitByField.Text
    End If
End Sub

---

The Core Filter Engine

This is where the real work happens. Add a module called modQueryEngine. The central procedure RunQueryEngine orchestrates everything: it reads the master data into memory, filters rows, optionally splits by field, aggregates, and hands results to the export layer.

Reading Data into Memory

Looping row-by-row against a worksheet range in real time is slow. For any dataset over a few hundred rows, the right move is to read the entire dataset into a 2D array first — one read operation instead of thousands.

' modQueryEngine.bas

Public Sub RunQueryEngine(ByRef params As QueryParameters)
    On Error GoTo ErrorHandler
    
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim dataArr As Variant
    Dim headers As Variant
    
    Set ws = ThisWorkbook.Sheets("MasterData")
    Set tbl = ws.ListObjects("tblSales")
    
    ' Bail early if the table is empty
    If tbl.DataBodyRange Is Nothing Then
        MsgBox "The master table contains no data.", vbExclamation
        Exit Sub
    End If
    
    ' Read entire table into memory (huge performance win)
    dataArr = tbl.DataBodyRange.Value
    
    ' Build a header-to-index dictionary for column lookups
    Dim colMap As Object
    Set colMap = BuildColumnMap(tbl)
    
    ' Ensure output folder exists
    EnsureFolder params.OutputFolder
    
    ' Route to split or unified export
    If params.SplitByField <> "" Then
        ExportSplit dataArr, colMap, params
    Else
        ExportUnified dataArr, colMap, params
    End If
    
    Exit Sub
ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Query Engine Error"
End Sub

Private Function BuildColumnMap(tbl As ListObject) As Object
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    Dim col As ListColumn
    For Each col In tbl.ListColumns
        dict(col.Name) = col.Index
    Next col
    Set BuildColumnMap = dict
End Function

Private Sub EnsureFolder(folderPath As String)
    If Dir(folderPath, vbDirectory) = "" Then
        MkDir folderPath
    End If
End Sub

The Row Filtering Logic

The filter function is the heart of the engine. It evaluates each row against all active parameters and returns True if the row passes all criteria:

Private Function RowPassesFilter(dataArr As Variant, _
                                  rowNum As Long, _
                                  colMap As Object, _
                                  params As QueryParameters) As Boolean
    RowPassesFilter = False
    
    Dim rowRegion       As String
    Dim rowCategory     As String
    Dim rowSalesperson  As String
    Dim rowDate         As Date
    Dim rowReturn       As Boolean
    
    rowRegion       = CStr(dataArr(rowNum, colMap("Region")))
    rowCategory     = CStr(dataArr(rowNum, colMap("Category")))
    rowSalesperson  = CStr(dataArr(rowNum, colMap("Salesperson")))
    rowDate         = CDate(dataArr(rowNum, colMap("TransactionDate")))
    rowReturn       = CBool(dataArr(rowNum, colMap("ReturnFlag")))
    
    ' --- Region filter (multi-value) ---
    If Not ArrayContains(params.Regions, rowRegion) Then Exit Function
    
    ' --- Category filter (multi-value) ---
    If Not ArrayContains(params.Categories, rowCategory) Then Exit Function
    
    ' --- Salesperson partial match ---
    If params.Salesperson <> "" Then
        If InStr(1, LCase(rowSalesperson), LCase(params.Salesperson)) = 0 Then
            Exit Function
        End If
    End If
    
    ' --- Date range ---
    If rowDate < params.DateFrom Or rowDate > params.DateTo Then Exit Function
    
    ' --- Exclude returns ---
    If params.ExcludeReturns And rowReturn Then Exit Function
    
    RowPassesFilter = True
End Function

Private Function ArrayContains(arr() As String, val As String) As Boolean
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        If arr(i) = val Then
            ArrayContains = True
            Exit Function
        End If
    Next i
    ArrayContains = False
End Function

The Exit Function pattern (returning False implicitly by not reaching the final True assignment) is a clean short-circuit approach: as soon as any criterion fails, we stop checking. This is meaningfully faster than evaluating all conditions with a long boolean expression, especially when the first condition eliminates most rows.

---

Aggregation Logic

Aggregation is where the system earns its keep. Rather than dumping raw rows, we can group and summarize by the chosen dimension. The aggregation procedure uses a Dictionary keyed on the grouping value, accumulating revenue, units, and transaction counts:

Private Function AggregateData(filteredRows() As Variant, _
                                 colMap As Object, _
                                 aggregateBy As String) As Object
    ' Returns a Dictionary:
    '   Key = group label
    '   Value = Array(TotalRevenue, TotalUnits, TxCount)
    
    Dim result As Object
    Set result = CreateObject("Scripting.Dictionary")
    
    Dim i As Long
    Dim groupKey As String
    Dim revenue As Double
    Dim units As Long
    
    For i = 1 To UBound(filteredRows, 1)
        ' Determine the group key
        Select Case aggregateBy
            Case "Region":      groupKey = CStr(filteredRows(i, colMap("Region")))
            Case "Category":    groupKey = CStr(filteredRows(i, colMap("Category")))
            Case "Salesperson": groupKey = CStr(filteredRows(i, colMap("Salesperson")))
            Case Else:          groupKey = "All Transactions"
        End Select
        
        revenue = CDbl(filteredRows(i, colMap("Revenue")))
        units   = CLng(filteredRows(i, colMap("Units")))
        
        If result.Exists(groupKey) Then
            Dim existing() As Variant
            existing = result(groupKey)
            existing(0) = existing(0) + revenue
            existing(1) = existing(1) + units
            existing(2) = existing(2) + 1
            result(groupKey) = existing
        Else
            Dim newEntry(2) As Variant
            newEntry(0) = revenue
            newEntry(1) = units
            newEntry(2) = 1
            result(groupKey) = newEntry
        End If
    Next i
    
    Set AggregateData = result
End Function

---

The Export Layer

Now we connect filtering and aggregation to actual file output. There are two export modes: Unified (one workbook for the entire filtered dataset) and Split (one workbook per unique value of the split field).

Collecting Filtered Rows

Before exporting, we need to collect qualifying rows into a clean array:

Private Function CollectFilteredRows(dataArr As Variant, _
                                      colMap As Object, _
                                      params As QueryParameters) As Variant
    ' First pass: count qualifying rows
    Dim totalRows As Long
    totalRows = UBound(dataArr, 1)
    Dim colCount As Long
    colCount = UBound(dataArr, 2)
    
    Dim passCount As Long
    passCount = 0
    Dim i As Long
    
    For i = 1 To totalRows
        If RowPassesFilter(dataArr, i, colMap, params) Then
            passCount = passCount + 1
        End If
    Next i
    
    If passCount = 0 Then
        CollectFilteredRows = Array()  ' Empty — caller must handle
        Exit Function
    End If
    
    ' Second pass: copy qualifying rows
    Dim result() As Variant
    ReDim result(1 To passCount, 1 To colCount)
    
    Dim destRow As Long
    destRow = 1
    
    For i = 1 To totalRows
        If RowPassesFilter(dataArr, i, colMap, params) Then
            Dim j As Long
            For j = 1 To colCount
                result(destRow, j) = dataArr(i, j)
            Next j
            destRow = destRow + 1
        End If
    Next i
    
    CollectFilteredRows = result
End Function

Unified Export

Private Sub ExportUnified(dataArr As Variant, colMap As Object, params As QueryParameters)
    Dim filteredRows As Variant
    filteredRows = CollectFilteredRows(dataArr, colMap, params)
    
    If IsEmpty(filteredRows) Or (IsArray(filteredRows) And UBound(filteredRows) = -1) Then
        MsgBox "No rows matched the filter criteria.", vbInformation
        Exit Sub
    End If
    
    Dim fileName As String
    fileName = params.OutputFolder & "\" & params.FilePrefix & _
               Format(Now, "yyyymmdd_HHmmss") & ".xlsx"
    
    WriteWorkbook filteredRows, colMap, params, fileName, "All Results"
End Sub

Split Export

The split export discovers unique values of the split field within the already-filtered dataset, then writes one workbook per value:

Private Sub ExportSplit(dataArr As Variant, colMap As Object, params As QueryParameters)
    ' First, get the full filtered set
    Dim filteredRows As Variant
    filteredRows = CollectFilteredRows(dataArr, colMap, params)
    
    If IsEmpty(filteredRows) Or (IsArray(filteredRows) And UBound(filteredRows) = -1) Then
        MsgBox "No rows matched the filter criteria.", vbInformation
        Exit Sub
    End If
    
    ' Find unique values of the split field
    Dim splitColIdx As Long
    splitColIdx = colMap(params.SplitByField)
    
    Dim uniqueVals As Object
    Set uniqueVals = CreateObject("Scripting.Dictionary")
    
    Dim i As Long
    For i = 1 To UBound(filteredRows, 1)
        Dim val As String
        val = CStr(filteredRows(i, splitColIdx))
        If Not uniqueVals.Exists(val) Then uniqueVals.Add val, 1
    Next i
    
    ' For each unique value, extract its rows and write a workbook
    Dim splitVal As Variant
    For Each splitVal In uniqueVals.Keys
        Dim splitRows() As Variant
        Dim splitCount As Long
        splitCount = 0
        
        ' Count
        For i = 1 To UBound(filteredRows, 1)
            If CStr(filteredRows(i, splitColIdx)) = CStr(splitVal) Then
                splitCount = splitCount + 1
            End If
        Next i
        
        ReDim splitRows(1 To splitCount, 1 To UBound(filteredRows, 2))
        
        ' Copy
        Dim destRow As Long
        destRow = 1
        For i = 1 To UBound(filteredRows, 1)
            If CStr(filteredRows(i, splitColIdx)) = CStr(splitVal) Then
                Dim j As Long
                For j = 1 To UBound(filteredRows, 2)
                    splitRows(destRow, j) = filteredRows(i, j)
                Next j
                destRow = destRow + 1
            End If
        Next i
        
        ' Build filename: sanitize the split value for use in a filename
        Dim safeVal As String
        safeVal = SanitizeFileName(CStr(splitVal))
        
        Dim fileName As String
        fileName = params.OutputFolder & "\" & params.FilePrefix & _
                   safeVal & "_" & Format(Now, "yyyymmdd") & ".xlsx"
        
        WriteWorkbook splitRows, colMap, params, fileName, CStr(splitVal)
    Next splitVal
End Sub

Private Function SanitizeFileName(s As String) As String
    Dim illegal As Variant
    illegal = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    Dim i As Long
    For i = 0 To UBound(illegal)
        s = Join(Split(s, illegal(i)), "_")
    Next i
    SanitizeFileName = s
End Function

Writing the Workbook

This procedure creates the actual output file. It writes a summary sheet with aggregated metrics and, if requested, a detail sheet with all filtered rows:

Private Sub WriteWorkbook(filteredRows() As Variant, _
                           colMap As Object, _
                           params As QueryParameters, _
                           fileName As String, _
                           sliceLabel As String)
    
    Application.ScreenUpdating = False
    
    Dim wb As Workbook
    Set wb = Workbooks.Add
    
    ' ---- SUMMARY SHEET ----
    Dim wsSummary As Worksheet
    Set wsSummary = wb.Sheets(1)
    wsSummary.Name = "Summary"
    
    ' Header block
    With wsSummary
        .Range("A1").Value = "Sales Export — " & sliceLabel
        .Range("A1").Font.Bold = True
        .Range("A1").Font.Size = 14
        .Range("A2").Value = "Generated: " & Format(Now, "yyyy-mm-dd hh:mm:ss")
        .Range("A3").Value = "Date Range: " & Format(params.DateFrom, "yyyy-mm-dd") & _
                             " to " & Format(params.DateTo, "yyyy-mm-dd")
        .Range("A4").Value = "Exclude Returns: " & IIf(params.ExcludeReturns, "Yes", "No")
        .Range("A5").Value = "Total Rows: " & UBound(filteredRows, 1)
    End With
    
    ' Aggregated summary table
    If params.AggregateBy <> "None" Then
        Dim aggData As Object
        Set aggData = AggregateData(filteredRows, colMap, params.AggregateBy)
        
        Dim startRow As Long
        startRow = 7
        
        With wsSummary
            .Cells(startRow, 1).Value = params.AggregateBy
            .Cells(startRow, 2).Value = "Total Revenue"
            .Cells(startRow, 3).Value = "Total Units"
            .Cells(startRow, 4).Value = "Transaction Count"
            .Cells(startRow, 5).Value = "Avg Revenue/Tx"
            
            ' Bold headers
            .Range(.Cells(startRow, 1), .Cells(startRow, 5)).Font.Bold = True
            .Range(.Cells(startRow, 1), .Cells(startRow, 5)).Interior.Color = RGB(68, 114, 196)
            .Range(.Cells(startRow, 1), .Cells(startRow, 5)).Font.Color = RGB(255, 255, 255)
            
            Dim r As Long
            r = startRow + 1
            Dim k As Variant
            For Each k In aggData.Keys
                Dim metrics() As Variant
                metrics = aggData(k)
                .Cells(r, 1).Value = k
                .Cells(r, 2).Value = metrics(0)
                .Cells(r, 3).Value = metrics(1)
                .Cells(r, 4).Value = metrics(2)
                .Cells(r, 5).Value = IIf(metrics(2) > 0, metrics(0) / metrics(2), 0)
                
                ' Format currency columns
                .Cells(r, 2).NumberFormat = "$#,##0.00"
                .Cells(r, 5).NumberFormat = "$#,##0.00"
                r = r + 1
            Next k
            
            .Columns("A:E").AutoFit
        End With
    End If
    
    ' ---- DETAIL SHEET (optional) ----
    If params.IncludeDetail Then
        Dim wsDetail As Worksheet
        Set wsDetail = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
        wsDetail.Name = "Detail"
        
        ' Write column headers from colMap
        Dim headers As Variant
        headers = Array("TransactionID", "Region", "Category", "Salesperson", _
                        "TransactionDate", "Units", "UnitPrice", "Revenue", "ReturnFlag")
        
        Dim h As Long
        For h = 0 To UBound(headers)
            wsDetail.Cells(1, h + 1).Value = headers(h)
        Next h
        wsDetail.Range(wsDetail.Cells(1, 1), wsDetail.Cells(1, UBound(headers) + 1)).Font.Bold = True
        
        ' Write data — paste entire array in one shot for maximum speed
        wsDetail.Range("A2").Resize(UBound(filteredRows, 1), UBound(filteredRows, 2)).Value = filteredRows
        
        ' Format date and currency columns
        wsDetail.Columns("E").NumberFormat = "yyyy-mm-dd"
        wsDetail.Columns("G").NumberFormat = "$#,##0.00"
        wsDetail.Columns("H").NumberFormat = "$#,##0.00"
        wsDetail.Columns.AutoFit
        
        ' Convert to a proper Table for easy filtering in the output file
        Dim detailRange As Range
        Set detailRange = wsDetail.Range("A1").CurrentRegion
        wsDetail.ListObjects.Add(xlSrcRange, detailRange, , xlYes).Name = "tblDetail"
    End If
    
    ' Save and close
    Application.DisplayAlerts = False
    wb.SaveAs fileName, xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    wb.Close SaveChanges:=False
    
    Application.ScreenUpdating = True
End Sub

The key performance move in WriteWorkbook is the single-shot array paste: .Range(...).Value = filteredRows. Writing a 5,000-row array to a worksheet in one assignment is dramatically faster than writing cell-by-cell in a loop. This pattern shows up in any serious VBA work and is worth internalizing deeply.

---

Wiring It All Together

Add a simple launcher procedure that any button or keyboard shortcut can call:

' In a standard module (modMain.bas)

Public Sub LaunchParameterQuerySystem()
    frmQueryParameters.Show
End Sub

Then assign this to a button on your MasterData sheet: right-click a shape or button control, choose "Assign Macro," and select LaunchParameterQuerySystem. For a more polished deployment, consider building a custom Excel ribbon button to make the launcher available from the ribbon across any session.

---

Hands-On Exercise

Now you'll extend the system with two real-world enhancements:

Exercise 1: Add a Revenue Threshold Filter

Add a MinRevenue parameter to the QueryParameters Type and a corresponding txtMinRevenue TextBox to the UserForm. Update RowPassesFilter to reject any row where Revenue < params.MinRevenue. Ensure that a blank txtMinRevenue field is interpreted as zero (no minimum).

Exercise 2: Add an Export Log

After each successful export run, append a log entry to a sheet named ExportLog in the master workbook. Each entry should record: timestamp, user name (Environ("USERNAME")), filter criteria summary, number of rows exported, number of files created, and output folder path. This audit trail is invaluable when stakeholders ask "who ran this and when did they get the numbers?"

For the log sheet approach, look at how the automated reporting system handles persistent logging — the pattern translates directly.

---

Common Mistakes & Troubleshooting

The filtered array is one row and it works, but two rows cause a Type Mismatch.

This is a classic VBA array dimension problem. When a range contains exactly one cell, .Value returns a scalar, not a 2D array. If your CollectFilteredRows function might return a single-row result, you need to handle this case explicitly — either by testing IsArray() or by using Application.Index() to force the scalar into an array.

The output file opens with dates stored as numbers.

Excel stores dates as serial numbers internally. When you paste a Variant array that contains Date values into a worksheet, the format depends on what's in the source cell. If your master data stores dates as text (common with imports), the paste will write text strings. Fix this by explicitly converting date column values with CDate() during the array copy, and always set the .NumberFormat on the destination column before pasting.

The split export creates files with identical names and overwrites earlier ones.

This happens when Format(Now, "yyyymmdd") resolves to the same value for multiple files in the same run (which it will, since they all run within the same second of the same day). The split export uses the split field value in the filename (e.g., SalesExport_Northeast_20240315.xlsx), so duplicates only occur if the split value itself duplicates across runs. For extra safety, add a run-sequence counter to the filename.

The UserForm ListBox shows duplicates.

The Dictionary.Exists() check in UserForm_Initialize should prevent this, but it will fail silently if your Region column contains mixed-case values ("northeast" vs "Northeast"). Add a LCase() normalization to the dictionary key while preserving the original case for display:

If Not dict.Exists(LCase(cell.Value)) Then
    dict.Add LCase(cell.Value), cell.Value
    lstRegions.AddItem cell.Value
End If

Performance degrades badly on the split export for large datasets.

The split export's inner loop is O(n × unique_values) — for each unique value it loops the full filtered set. For 20,000 filtered rows split across 50 categories, that's a million iterations. The fix is to pre-sort the filtered array by split field and use a single pass with a "break" when the value changes. Alternatively, use a Dictionary to group row indices during the initial scan, then extract by index rather than by value comparison. This gets you back to O(n).

---

Connecting to Broader Patterns

The parameter query engine you've built is fundamentally a mini ETL pipeline inside Excel. As your datasets grow, you may hit the ceiling of what in-memory array manipulation can handle gracefully. At that point, the natural evolution is to connect directly to a database and run the filtering server-side — the lesson on connecting Excel to external databases with VBA, SQL, and ADO picks up exactly where this one leaves off.

For scenarios where the export needs to trigger automatically on a schedule rather than on-demand, look at scheduling VBA macros with Windows Task Scheduler and Application.OnTime — the RunQueryEngine procedure you wrote today is already structured to be called without a UI, so adapting it for scheduled execution requires almost no refactoring.

And if the exported workbooks themselves need to contain charts, KPI gauges, or conditional formatting applied programmatically, building a dynamic Excel dashboard with VBA shows how to layer visual automation on top of exactly this kind of output structure.

---

Summary & Next Steps

You've built a complete, production-grade parameter query system: a UserForm that collects multi-criteria filter parameters, a high-performance filter engine that works on in-memory arrays, an aggregation layer that groups and summarizes by any dimension, and an export layer that generates properly formatted, named workbooks — one per split value if needed.

The key principles to carry forward:

• Read data into arrays once — worksheet interaction is your bottleneck; eliminate it where possible
• Separate concerns — parameter collection, filtering, aggregation, and export are independent; change one without breaking the others
• Design for zero-friction operation — sensible defaults (all regions if none selected, auto-generated filenames, auto-created output folder) make the tool actually get used
• Build in the audit trail from the start — logs and timestamps are cheap to add early and expensive to retrofit later

Next steps to deepen this system:

1. Add robust error handling with a structured log that records failed exports with their error details
2. Extend the parameter Type to support saved query profiles — serialize parameters to a hidden sheet so users can recall last-used settings
3. Integrate with automating email and file operations with VBA to automatically email each exported file to the relevant recipient immediately after creation
4. Explore converting the system into a deployable Excel Add-In so it can work against any dataset in any workbook, not just your current master file

The foundation is solid. Everything from here is a matter of wiring in more capability on top of an architecture that was designed to support it.