VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "MXG_Sheet"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'===================================================================================================
' EXCEL CLASS MODULE: MXG_Sheet
'---------------------------------------------------------------------------------------------------
' OVERVIEW:
'  - Sub-second performance for 100K+ rows, robust error handling, and best practices.
'  - Accurate and tested in enterprise environments at maximum worksheet sizes.
'  - Streamlines your code so you solve faster — not wrestle with syntax.
'  - Every function adapts to structural sheet changes without breaking your code,
'    including: Dynamic worksheets, Protected sheets, Excel Tables,
'               and workbooks on Shared Drives or Networks.
'
'     FREE: Download MXG_Sheet at www.MyExcelGenie.com
'  LICENSE: Freeware (personal or commercial use with attribution)
'           Provided as-is without warranty. No liability for usage outcomes.
'---------------------------------------------------------------------------------------------------
' FEATURES:
'  - SmartFilter - 60% faster than AutoFilter, optimized accuracy for Excel's maximum sheet size.
'  - SmartLookup - Instant multi-column composite key lookups and cache retrieval.
'  - SmartCells - Lightning-fast cell retrieval by column name, search strings, wildcards, and more.
'  - Dynamic column mapping and sheet metadata ensure structural changes never break your code.
'  - All functions seamlessly support ListObjects, standard ranges, and protected sheets.
'  - Supports simultaneous open workbooks: Teams, SharePoint, Network, Local.
'  - Robust error handling with call-stack functionality for better code maintenance.
'  - Scales effortlessly from lightweight tasks to enterprise-level workflows—without rewrites.
'  - Designed to integrate seamlessly with next-gen performance tools from www.AutomationWithExcel.
'---------------------------------------------------------------------------------------------------
' AUTHOR: Mike Libby — 30+ years of Excel/VBA expertise, with GPT-4 collaboration.
'===================================================================================================
' VERSION HISTORY:
'  - 2025.04.16 - Initial release.
'===================================================================================================

Option Explicit
'###################################################################################################
' Global Class Variables - Engineered for High-Performance Worksheet Automation
'###################################################################################################
' Core Variables
Private m_Workbook As Workbook           ' Associated workbook
Private m_Worksheet As Worksheet         ' Associated worksheet
Private m_ListObject As ListObject       ' Associated table (if any)

' Column and Row Management
Private m_HdrRowNb As Long               ' Header row number for column-based operations
Private m_ColumnHdrDict As Object        ' Optimized dictionary for rapid column name-to-number mapping

' SmartRow Column-level format and formula storage
Private m_ColumnFormatDict As Object  ' colName - format string (e.g., "00000", "@")
Private m_ColumnFormulaDict As Object       ' colName - formula string (e.g., "=C2+D2")

' SmartFilter Optimization
Private m_FindResultsColl As Collection  ' Collection storing filtered row references for instant access
Private m_FindResultsColCtr As Long      ' Tracks number of columns evaluated in SmartFilter
Private m_FindResultsLastRow As Long     ' Stores last used row to avoid unnecessary recalculations
Private m_FindColumnType As VbVarType    ' Column type being evaluated during SmartFilter execution

' DataBodyRangeX Optimization
Private m_EmptyEndRowDetection As Boolean   ' Excludes empty end rows from DataBodyRangeX for efficiency

' RegEx Object(s)
Private m_FindCriteriaOpRegex As Object  ' Pre-compiled regex for fast criteria parsing

'###################################################################################################
' Enumerators - Define allowable options for MXG_Sheet operations
'###################################################################################################
' Enum RangeConvertType - Controls SmartCells output, allowing either a Range reference or extracted values
Public Enum RangeConvertType: rctRange = 0: rctValue = 1: End Enum

' Enum MXG_StateType - Standardized status codes for validating workbook, worksheet, and data integrity
Public Enum MXG_StateType
    State_Workbook = 1000       ' Workbook is valid
    State_ReadOnly = 1001       ' Workbook is read-only
    State_Worksheet = 1002      ' Worksheet is valid
    State_HasData = 1003        ' Worksheet contains data
    State_ListObject = 1005     ' ListObject (Table) exists and is valid
    State_HeaderRow = 1006      ' Header row is valid
    State_HeaderColumn = 1007   ' Header column is valid
    State_Protection = 1008     ' Workbook or worksheet is protected
    State_SheetData = 1009      ' Data exists on the sheet
    State_Parameter = 1010      ' Parameter is valid
    State_Search = 1011         ' Search operation is valid
End Enum

' Enum LookupMetaEnum - Core metadata structure for SmartLookup
Private Enum LookupMetaEnum
    meta_KeyArr = 0            ' Sorted array of composite keys
    meta_rowNbrs = 1           ' Array of corresponding row numbers
    meta_keyDict = 2           ' Dictionary: composite key - Collection of row numbers
    meta_CachedCols = 3        ' 1D array of 2D arrays (cached columns)
    meta_CachedColNms = 4      ' Dictionary: column name - index in CachedCols
End Enum

'###################################################################################################
' VALIDATION AND ERROR HANDLING - Ensures System Stability and Traceability
'###################################################################################################
' Returns the numeric value of an MXG_StateType enum.
Public Property Get CStateType(EnumName As MXG_StateType) As Long:  CStateType = EnumName: End Property

'---------------------------------------------------------------------------------------------------
' IsValidState - Prevents invalid operations by ensuring required system states are met.
' Params:  stateType (MXG_StateType) - The state to validate.
'          criterion() (Optional) - Additional parameters for state-specific checks.
' Returns: Boolean - True if the state is valid, otherwise False.
'---------------------------------------------------------------------------------------------------
Private Function IsValidState(stateType As MXG_StateType, ParamArray Criterion()) As Boolean
    On Error Resume Next
    Select Case stateType
        Case State_Workbook:     IsValidState = Not m_Workbook Is Nothing: Exit Function
        Case State_ReadOnly:     IsValidState = Not m_Workbook.ReadOnly: Exit Function
        Case State_Worksheet:    IsValidState = Not m_Worksheet Is Nothing: Exit Function
        Case State_HasData:      IsValidState = Not (m_Worksheet.UsedRange.Cells.Count = 1 And _
                                                     IsEmpty(m_Worksheet.UsedRange.Cells(1, 1)))
                                                     Exit Function
        Case State_Protection:   IsValidState = Not (m_Worksheet.ProtectContents Or _
                                                     ThisWorkbook.ProtectStructure): Exit Function
        Case State_SheetData:    IsValidState = (m_Worksheet.UsedRange.rows.Count > 1): Exit Function
        Case State_HeaderRow:    IsValidState = HeaderRowNumber > 0 And _
                                                HeaderRowNumber < m_Worksheet.rows.Count: Exit Function
        Case State_HeaderColumn: IsValidState = m_ColumnHdrDict.exists(Criterion(0)(0)): Exit Function

        ' Check if ListObject (Table) is valid
        Case State_ListObject
            If m_ListObject Is Nothing Then IsValidState = False: Exit Function
            If m_ListObject.ListColumns.Count = 0 Then IsValidState = False: Exit Function
    End Select
    IsValidState = True
End Function

'---------------------------------------------------------------------------------------------------
' ValidateState - Enforces critical system states and raises errors when conditions are not met.
' Params:  stateType (MXG_StateType) - The state to validate.
'          criterion() (Optional) - Additional parameters for state-specific checks.
'---------------------------------------------------------------------------------------------------
Private Sub ValidateState(stateType As MXG_StateType, callerName As String, ParamArray Criterion())
    Dim temp$
    If Not IsValidState(stateType, Criterion) Then
        Select Case stateType
            Case State_Workbook:     If UBound(Criterion) >= 0 Then temp = SafeName(Criterion(0))
                                     RaiseError State_Workbook, callerName, "Workbook is not initialized: " & temp
            Case State_ReadOnly:     RaiseError State_ReadOnly, callerName, "Workbook is in Read-Only mode."
            Case State_Worksheet:    If UBound(Criterion) >= 0 Then temp = SafeName(Criterion(0))
                                     RaiseError State_Worksheet, callerName, "Worksheet is not initialized: " & temp
            Case State_HasData:      RaiseError State_HasData, callerName, "Worksheet has no data."
            Case State_ListObject:   RaiseError State_ListObject, callerName, "ListObject (Table) is missing or invalid."
            Case State_HeaderRow:    RaiseError State_HeaderRow, callerName, "Headers row is missing or invalid."
            Case State_HeaderColumn: If SafeIsArray(Criterion(0)) Then temp = Criterion(0)(0) Else temp = Criterion(0)
                                     RaiseError State_HeaderColumn, callerName, "Header column name is missing or invalid.", , temp
            Case State_Protection:   RaiseError State_Protection, callerName, "Worksheet or workbook is protected."
            Case State_SheetData:    RaiseError State_SheetData, callerName, "Worksheet does not contain structured data."
        End Select
    End If
End Sub

'---------------------------------------------------------------------------------------------------
' RaiseError - Structured error handler with stack trace, ASCII-safe context, and metadata mapping.
' Params: ErrorCode (Long)           - Error number to raise at this level.
'         FunctionName (String)      - Name of the current function raising the error (e.g., "AWE_Sheet::SmartRowGet").
'         Description (String)       - Human-readable summary of the error at this level.
'         RowNbrOrSrchVal (Variant)  - Optional. Row number or search key to include in error context.
'         ColIdxOrNm (Variant)       - Optional. Column index or name to include in error context.
'
' Rules: - Root function appears once as 'Root Function'
'        - Originating function shown only if different from root
'        - Row and Column shown only if values exist
'        - Uses SafeReference to normalize all row/column inputs
'        - Prevents duplicate function names in Err.Source
'        - All output is ASCII-only and logger-friendly
'
' Example:
'   debug.print err.description & vblf & err.source ' Output the following to the immediate window:
'   Error: Header column name is missing or invalid.
'      Workbook: 'AWE_Sheet_UnitTest.V.2025.06.05.A.xlsm'
'      Worksheet: 'Timecard'
'      Root Function: 'AWE_Sheet::GetColumnNumber'
'      Originating Function: 'AWE_Sheet::SmartRowGet'
'      Column: 'Status'
'
'   Source: ExternalCaller -> AWE_Sheet::SmartRowGet -> AWE_Sheet::GetColumnNumber
'---------------------------------------------------------------------------------------------------
Public Sub RaiseError(ErrorCode As Long, FunctionName As String, _
                      Optional Description As String = "", _
                      Optional RowNbrOrSrchVal As Variant = Nothing, _
                      Optional ColIdxOrNm As Variant = Nothing)

    ' Determine if this is the root function, and if the function and description already exist
    Dim isRoot As Boolean: isRoot = (Err.Source = "" Or Err.Source = "VBAProject")
    Dim srcExists As Boolean: srcExists = (InStr(1, LCase(Err.Source), LCase(FunctionName), vbTextCompare) > 0)
    Dim dscExists As Boolean: dscExists = (Err.Description = Description)
    
    ' Extract error context from the previous err.description if it exists
    Dim ctxLn, ctxArr, key$, val$, finalMsg$
    Dim ctxDict As Object: Set ctxDict = CreateObject("Scripting.Dictionary"): ctxDict.CompareMode = vbTextCompare
    If Not isRoot Then
        For Each ctxLn In Split(Err.Description, vbLf)
            ctxArr = Split(ctxLn, ":", 2)
            If UBound(ctxArr) = 1 Then: key = Trim$(ctxArr(0)): val = Trim$(ctxArr(1)): ctxDict(key) = val
        Next ctxLn
    End If

    ' Update missing components in the error context
    Dim errMsg$: errMsg = IIf(ctxDict.exists("Error"), ctxDict("Error"), Description)
    Dim wbName$: If Not m_Workbook Is Nothing Then wbName = m_Workbook.Name Else wbName = ""
    Dim wsName$: If Not m_Worksheet Is Nothing Then wsName = m_Worksheet.Name Else wsName = ""
    Dim row$: row = IIf(ctxDict.exists("Row"), ctxDict("Row"), SafeReference(RowNbrOrSrchVal))
    Dim col$: col = IIf(ctxDict.exists("Column"), ctxDict("Column"), SafeReference(ColIdxOrNm))
    Dim rootFunc$: rootFunc = IIf(ctxDict.exists("Root Function"), Replace(ctxDict("Root Function"), "'", ""), FunctionName)

    'Generate the final message
    finalMsg = "Error: " & errMsg & vbLf & _
               "   Workbook: '" & wbName & "'" & vbLf & _
               "   Worksheet: '" & wsName & "'" & vbLf & _
               "   Root Function: '" & Replace(rootFunc, "'", "") & "'"
    
    If LCase(FunctionName) <> LCase(rootFunc) Then finalMsg = finalMsg & vbLf & "   Originating Function: '" & FunctionName & "'"
    If row <> "" Then finalMsg = finalMsg & vbLf & "   Row: " & row
    If col <> "" Then finalMsg = finalMsg & vbLf & "   Column: '" & Replace(col, "'", "") & "'"

    ' Update the error source
    Dim newSource$: newSource = IIf(srcExists, Err.Source, FunctionName & IIf(Len(Err.Source) > 0, " -> " & Err.Source, ""))
    
    ' Raise the error using the formatted source and final message
    Err.Raise ErrorCode, newSource, Trim$(finalMsg)
End Sub

'###################################################################################################
' SHEET INITIALIZATION AND COLUMN MAPPING - Automates Setup and Prevents Breakage
'###################################################################################################
' Class Constructor - Initializes core variables for high-speed operations
Private Sub Class_Initialize()
    Set m_ColumnHdrDict = CreateObject("Scripting.Dictionary"): m_ColumnHdrDict.CompareMode = vbTextCompare
    Set m_ColumnFormatDict = CreateObject("Scripting.Dictionary")
    Set m_ColumnFormulaDict = CreateObject("Scripting.Dictionary")
    Set m_FindCriteriaOpRegex = CreateObject("VBScript.RegExp")
    With m_FindCriteriaOpRegex: .Pattern = "^(<>|<=|>=|=<|=>|<|>|=)?(.*)": .Global = False: End With
End Sub

'---------------------------------------------------------------------------------------------------
' Initialize - Automates opening workbook and sheet setup, preventing errors and manual fixes.
' Params:  SheetNameOrObj (Variant) - Worksheet, sheet name, or ListObject.
'          HeaderRowNumber (Optional) - Header row number (default: 1).
'          WorkbookFileNameOrObj (Optional) - Workbook object or file name (default: ThisWorkbook).
'          OpenReadOnly (Optional) - Open workbook as read-only (default: False).
' Example: mxgSh.Initialize "Sheet1"         ' Initialize with a local sheet.
'          mxgSh.Initialize "RemoteSheet", , "C:\File.xlsx", True  ' Readonly remote sheet.
'          mxgSh.Initialize "TableSheet"     ' Initialize with first ListObject found.
' Notes: - Reuses open workbooks to prevent errors when trying to reopen the same workbook name.
'        - Detects and integrates ListObjects if available.
'        - Throws an error if multiple ListObjects exist.
'---------------------------------------------------------------------------------------------------
Public Sub Initialize(SheetNameOrObj As Variant, Optional HeaderRowNumber As Long = 1, _
                      Optional WorkbookFileNameOrObj As Variant = Nothing, _
                      Optional OpenReadOnly As Boolean = False)
    Dim fncNm$: fncNm = "MXG_Sheet::Initialize": On Error GoTo ErrorHandler

    ' Reset core variables
    m_HdrRowNb = 0: m_ColumnHdrDict.RemoveAll
    Set m_ListObject = Nothing: Set m_Workbook = Nothing: Set m_Worksheet = Nothing
    m_EmptyEndRowDetection = True: SmartFilterClear

    ' Set Workbook
    Select Case TypeName(WorkbookFileNameOrObj)
        Case "Workbook": Set m_Workbook = WorkbookFileNameOrObj
        Case "String"
            If Not IsWorkbookOpen(CStr(WorkbookFileNameOrObj), m_Workbook) Then
                On Error Resume Next
                Set m_Workbook = Workbooks.Open(CStr(WorkbookFileNameOrObj), ReadOnly:=OpenReadOnly)
                On Error GoTo 0
            End If
        Case Else: Set m_Workbook = ThisWorkbook
    End Select
    ValidateState MXG_StateType.State_Workbook, fncNm, SafeName(WorkbookFileNameOrObj)

    ' Set Worksheet
    Set m_Worksheet = ResolveWorksheet(SheetNameOrObj)
    ValidateState MXG_StateType.State_Worksheet, fncNm, SafeName(SheetNameOrObj)

    ' Auto-detect ListObject if present
    On Error Resume Next
    If m_Worksheet.ListObjects.Count > 0 Then
        Set m_ListObject = m_Worksheet.ListObjects(1)
        m_HdrRowNb = m_ListObject.HeaderRowRange.row
    Else
        m_HdrRowNb = HeaderRowNumber
    End If
    On Error GoTo 0
    
    ' Map and Validate Header Row
    MapColumnNumbers
    Exit Sub

ErrorHandler: RaiseError Err.Number, fncNm
End Sub

Private Function SafeName(objIntOrStr As Variant) As String
    On Error Resume Next
    Select Case TypeName(objIntOrStr)
        Case "String": SafeName = objIntOrStr
        Case "Long", "Integer": SafeName = CStr(objIntOrStr)
        Case Else: SafeName = objIntOrStr.Name
                   If Len(SafeName) = 0 Then SafeName = "[" & TypeName(objIntOrStr) & "]"
    End Select
    On Error GoTo 0
End Function

'---------------------------------------------------------------------------------------------------
' IsWorkbookOpen - Checks if a workbook is already open in the current Excel session.
' Params:   FullFileName (String)         - Full file path (e.g., "C:\Files\MyWorkbook.xlsx").
'           wb (Workbook, Optional ByRef)  - [Output] If found, returns the open Workbook object.
' Returns:  Boolean - True if the workbook is open; otherwise False.
' Notes:    - Comparison is case-insensitive.
'           - Extracts workbook name from path before checking against open workbooks.
'           - If not open, wb is set to Nothing.
'---------------------------------------------------------------------------------------------------
Public Function IsWorkbookOpen(fullFileName As String, Optional ByRef wb As Workbook) As Boolean
    Dim fncNm$: fncNm = "MXG_Sheet::IsWorkbookOpen": On Error GoTo ErrorHandler
    Dim delim As String: delim = IIf(Left(fullFileName, 8) = "https://", "/", "\")
    Dim sWbNm As String: sWbNm = LCase$(Mid$(fullFileName, InStrRev(fullFileName, delim) + 1))
    sWbNm = Replace(sWbNm, "%20", " ")
    For Each wb In Application.Workbooks
        If LCase$(wb.Name) = sWbNm Then IsWorkbookOpen = True: Exit Function
    Next
    Set wb = Nothing
    Exit Function
ErrorHandler: Set wb = Nothing: RaiseError Err.Number, fncNm
End Function

'---------------------------------------------------------------------------------------------------
' MapColumnNumbers - Maps column names to numbers, ensuring integrity and preventing errors.
' Notes: - Clears existing mappings and validates header metadata.
'        - Adds column names to the dictionary, ensuring uniqueness.
'        - Raises an error if duplicate column names are found.
'---------------------------------------------------------------------------------------------------
Public Sub MapColumnNumbers()
    Dim fncNm$: fncNm = "MXG_Sheet::MapColumnNumbers": On Error GoTo ErrorHandler
    ValidateState State_HeaderRow, fncNm
    m_ColumnHdrDict.RemoveAll

    ' Retrieve header values
    Dim colNm$, colIndex As Long, arr: arr = HeaderRowRangeX.Value
    If Not SafeIsArray(arr) Then
        If Len(Trim(CStr(arr))) > 0 Then m_ColumnHdrDict.Add Trim(CStr(arr)), 1
        Exit Sub
    End If
    If Application.WorksheetFunction.CountA(HeaderRowRangeX) = 0 Then Exit Sub

    ' Process multiple headers
    For colIndex = LBound(arr, 2) To UBound(arr, 2)
        colNm = Trim(CStr(arr(1, colIndex)))
        If Len(colNm) > 0 Then
            If m_ColumnHdrDict.exists(colNm) Then
                RaiseError State_HeaderColumn, fncNm, "Duplicate column", m_HdrRowNb, colNm
            Else
                m_ColumnHdrDict.Add colNm, colIndex
            End If
        End If
    Next colIndex

    CaptureColumnFormatFormula
    Exit Sub

ErrorHandler: RaiseError Err.Number, fncNm, , m_HdrRowNb, colNm
End Sub


'###################################################################################################
' SHEET METADATA AND RANGES - Retrieves Key Worksheet References and Data Boundaries
'###################################################################################################
' ParentWorkbook - Returns the associated workbook, ensuring it is valid.
Public Property Get ParentWorkbook() As Workbook
    ValidateState State_Workbook, "MXG_Sheet::ParentWorkbook": Set ParentWorkbook = m_Workbook
End Property

' ParentWorksheet - Returns the associated worksheet, ensuring it is valid.
Public Property Get ParentWorksheet() As Worksheet
    ValidateState State_Worksheet, "MXG_Sheet::ParentWorksheet": Set ParentWorksheet = m_Worksheet:
End Property

' ParentTable - Returns the associated table (ListObject), ensuring it is valid.
Public Property Get ParentTable() As ListObject
    ValidateState State_ListObject, "MXG_Sheet::ParentTable": Set ParentTable = m_ListObject
End Property

' IsTable - Returns True if the worksheet contains a valid Table (ListObject).
Public Property Get isTable() As Boolean: isTable = Not m_ListObject Is Nothing: End Property

' IsSheetEmpty - Returns True if the worksheet contains no data.
Public Property Get IsSheetEmpty() As Boolean
    On Error Resume Next: Dim rng As Range: Set rng = m_Worksheet.UsedRange
    If rng.Cells.Count = 1 And IsEmpty(rng.Cells(1, 1)) Then IsSheetEmpty = True _
    Else IsSheetEmpty = Application.CountA(rng) = 0
    On Error GoTo 0
End Property

' IsDataBodyEmpty - Returns True if the worksheet has headers but no data below the header row.
Public Property Get IsDataBodyEmpty() As Boolean
    On Error Resume Next
    Dim dataRange As Range: Set dataRange = DataBodyRangeX(False)
    IsDataBodyEmpty = (dataRange Is Nothing Or dataRange.rows.Count = 1)
    On Error GoTo 0
End Property

' HeaderRowNumber - Returns the stored header row number for column mapping.
Public Property Get HeaderRowNumber() As Long: HeaderRowNumber = m_HdrRowNb: End Property

' HeaderRowRangeX - Returns the range of the header row used for column mapping.
Public Property Get HeaderRowRangeX() As Range
    Dim fncNm$: fncNm = "MXG_Sheet::HeaderRowRangeX": On Error GoTo ErrorHandler
    Set HeaderRowRangeX = m_Worksheet.Range(m_Worksheet.Cells(m_HdrRowNb, 1), _
                                             m_Worksheet.Cells(m_HdrRowNb, LastColumnNumber))
    Exit Property
    
ErrorHandler: ValidateState MXG_StateType.State_Worksheet, fncNm: RaiseError Err.Number, fncNm
End Property

' RangeX - Returns the full dataset range, combining headers and data.
Public Property Get RangeX() As Range
    Dim fncNm$: fncNm = "MXG_Sheet::RangeX": On Error GoTo ErrorHandler
    Dim dataRange As Range, area As Range
    Dim lastRow As Long

    ' Retrieve data range
    Set dataRange = DataBodyRangeX(False)

    ' Ensure the data range exists
    If Not dataRange Is Nothing Then
        With m_Worksheet
            Set area = dataRange.Areas(dataRange.Areas.Count)  ' Retrieve the last area
            lastRow = area.rows(area.rows.Count).row  ' Find the last row
            Set RangeX = .Range(.Cells(m_HdrRowNb, 1), .Cells(lastRow, dataRange.columns.Count))
        End With
    Else
        Set RangeX = HeaderRowRangeX  ' If no data, just return header row
    End If
    Exit Property

ErrorHandler: Set RangeX = Nothing: RaiseError Err.Number, fncNm
End Property

'---------------------------------------------------------------------------------------------------
' DataBodyRangeX - Returns the data range below the header row, either from a ListObject (table) or
'                  UsedRange, while excluding trailing empty rows (ghost rows) at the end of the sheet.
' Params:  RaiseSheetEmptyError (Boolean, Optional) - If True, raises an error if the sheet is empty.
' Returns: Range - The entire data range or Nothing if no data rows exist.
' Notes:   - Supports both ListObjects (tables) and standard ranges.
'          - Ensures the range contains the last data row unless Empty End Rows were previously deleted.
'          - If no data exists, DataBodyRangeX will return a single row beyond the header.
'---------------------------------------------------------------------------------------------------
Public Function DataBodyRangeX(Optional RaiseSheetEmptyError As Boolean = False) As Range
    Dim fncNm$: fncNm = "MXG_Sheet::DataBodyRangeX": On Error GoTo ErrorHandler
    Dim usedRng As Range, lastArea As Range, frstRow As Long, lastRow As Long, LastDataRow As Long, lastCol As Long
    
    With m_Worksheet
        ' Determine data range based on ListObject or UsedRange
        If Not m_ListObject Is Nothing Then
            ' Use ListObject's data body range if available
            Set usedRng = m_ListObject.databodyrange
            If usedRng Is Nothing Then
                frstRow = m_HdrRowNb + 1: lastRow = m_HdrRowNb + 1
                Set usedRng = .Range(.Cells(frstRow, 1), .Cells(lastRow, m_ListObject.Range.columns.Count))
            End If
        Else
            ' If no ListObject, use UsedRange to determine the data range
            Set usedRng = .UsedRange: If usedRng.Address = "$A$1" Then Set DataBodyRangeX = usedRng: Exit Function
            frstRow = m_HdrRowNb + 1: lastRow = Application.Max(usedRng.rows(usedRng.rows.Count).row, frstRow)
            lastCol = Application.Max(usedRng.columns.Count, 1)
            Set usedRng = .Range(.Cells(frstRow, 1), .Cells(lastRow, lastCol))
        End If
        
        ' Verify the last true data row, unless EmptyEndRowDetection was explicitly turned off for performance
        If m_EmptyEndRowDetection Then
            Set lastArea = usedRng.Areas(usedRng.Areas.Count)
            If Not lastArea Is Nothing Then
                 ' Identify the true last row with actual data by scanning backward
                lastRow = lastArea.rows(lastArea.rows.Count).row
                For LastDataRow = lastRow To m_HdrRowNb + 1 Step -1
                    ' Exit the loop when a row with data is found
                    If Application.CountA(usedRng.rows(LastDataRow - m_HdrRowNb)) > 0 Then Exit For
                Next LastDataRow
                LastDataRow = Application.Max(m_HdrRowNb + 1, LastDataRow)
                ' Adjust the data range to exclude empty ghost rows
                Set usedRng = .Range(.Cells(m_HdrRowNb + 1, 1), .Cells(LastDataRow, m_ColumnHdrDict.Count))
            End If
        End If
    End With

    ' Handle Empty End Rows (if enabled) to ensure only actual data is included in the range
    If RaiseSheetEmptyError And usedRng Is Nothing Then _
        RaiseError MXG_StateType.State_SheetData, fncNm, "MXG_Sheet::Unable to retrieve range."

    Set DataBodyRangeX = usedRng
    Exit Function

ErrorHandler: Set DataBodyRangeX = Nothing: RaiseError Err.Number, fncNm
End Function

' EmptyEndRowDetection (Get) - Returns whether empty trailing rows are excluded from the data range.
Public Property Get EmptyEndRowDetection() As Boolean: EmptyEndRowDetection = m_EmptyEndRowDetection: End Property

' EmptyEndRowDetection (Let) - Enables or disables detection of empty trailing rows.
Public Property Let EmptyEndRowDetection(ByVal Value As Boolean): m_EmptyEndRowDetection = Value: End Property

'---------------------------------------------------------------------------------------------------
' DeleteEmptyEndRows - Detects and removes empty trailing rows (ghost rows) from the worksheet.
' Params:  PromptUser (Optional Boolean) - If True (default), prompts user before deletion. If False,
'                                          skips prompt and deletes automatically if ghost rows exist.
' Returns: Boolean - True if Empty End Rows were successfully removed or skipped, False otherwise.
' Notes:   - If ghost rows are found and the user selects "Cancel", processing is halted via RaiseError.
'          - Updates UsedRange to reflect actual data boundaries.
'          - Turn off automatic detection: EmptyEndRowDetection = Not DeleteEmptyEndRows.
'---------------------------------------------------------------------------------------------------
Public Function DeleteEmptyEndRows(Optional PromptUser As Boolean = True) As Boolean
    Dim lastRow As Long, LastDataRow As Long, lastArea As Range, tmpRng As Range, usedRng As Range
    Dim isScreenUpdating As Boolean, prevEmptyEndRowDetection As Boolean
    
    ' Ensure the function does not attempt modifications on protected sheets
    On Error Resume Next
    If m_Worksheet.AutoFilterMode Then m_Worksheet.ShowAllData
    Dim fncNm$: fncNm = "MXG_Sheet::DeleteEmptyEndRows": On Error GoTo ErrorHandler
    
    ' Store previous Empty End Row detection setting and disable it for this operation
    prevEmptyEndRowDetection = EmptyEndRowDetection: EmptyEndRowDetection = False
    
     ' Retrieve the current data range. Exit function if empty
    Set usedRng = DataBodyRangeX: If usedRng Is Nothing Then Exit Function

    With usedRng.Worksheet
        ' Iterate backwards to find the actual last row with data
        Set lastArea = usedRng.Areas(usedRng.Areas.Count)
        If Not lastArea Is Nothing Then
            ' Identify last row with data
            lastRow = lastArea.rows(lastArea.rows.Count).row
            For LastDataRow = lastRow To m_HdrRowNb + 1 Step -1
                ' Exit the for loop if a row with actual data is found
                If Application.CountA(usedRng.rows(LastDataRow - m_HdrRowNb)) > 0 Then Exit For
            Next LastDataRow
            LastDataRow = Application.Max(m_HdrRowNb + 1, LastDataRow)

            ' Adjust UsedRange to remove Empty End Rows
            Set usedRng = .Range(.Cells(m_HdrRowNb + 1, 1), .Cells(LastDataRow, m_ColumnHdrDict.Count))

            ' If the UsedRange contains ghosts, then delete Empty End Rows and force recalculation
            If LastDataRow <> lastRow Then
                If PromptUser Then
                    Dim ghostRange As Range: Set ghostRange = .Range(.Cells(LastDataRow + 1, 1), .Cells(lastRow, 1))
                    Select Case MsgBox("Empty end rows (ghost rows) were detected below your data." & vbCrLf & vbCrLf & _
                                       "Rows: " & ghostRange.row & " to " & ghostRange.rows(ghostRange.rows.Count).row & vbCrLf & vbCrLf & _
                                       "Would you like to permanently delete these rows from the worksheet?" & vbCrLf & vbCrLf & _
                                       "Yes    - Delete the ghost rows now" & vbCrLf & _
                                       "No     - Keep the ghost rows and continue" & vbCrLf & _
                                       "Cancel - End all processing entirely", _
                                       vbYesNoCancel + vbQuestion, "Delete Empty End Rows?")
                        Case vbCancel: RaiseError State_SheetData, fncNm, _
                                                  "User canceled all processing due to ghost rows: " & ghostRange.Address
                        Case vbNo:     Exit Function     ' No = skip deletion
                        Case vbYes:                      ' Proceed with deletion
                    End Select
                End If
                
                ' Delete empty rows
                On Error Resume Next
                .Range(.Cells(LastDataRow + 1, 1), .Cells(lastRow, 1)).EntireRow.Delete
                On Error GoTo 0

                ' Force recalculation
                isScreenUpdating = Application.ScreenUpdating
                Application.ScreenUpdating = False
                .UsedRange.Calculate
                DoEvents
                
                ' Refresh the UsedRange after deletion and recalculation
                Set tmpRng = .UsedRange:  Set lastArea = tmpRng.Areas(tmpRng.Areas.Count)
                lastRow = lastArea.rows(lastArea.rows.Count).row
                Application.ScreenUpdating = isScreenUpdating
            End If
        End If
    End With

    ' Confirm deletion success
    DeleteEmptyEndRows = (LastDataRow = lastRow)
    EmptyEndRowDetection = prevEmptyEndRowDetection
    Exit Function

ErrorHandler: RaiseError Err.Number, fncNm
End Function

' LastRowNumber - Returns the last row containing data, excluding trailing empty rows.
Public Property Get LastRowNumber() As Long
    Dim fncNm$: fncNm = "MXG_Sheet::LastRowNumber": On Error GoTo ErrorHandler
    Dim rowRange As Range, lastRow As Long
    
    Set rowRange = DataBodyRangeX(False) ' Get the last row from the last area of DataBodyRangeX()
    If Not rowRange Is Nothing Then
        Dim lastArea As Range: Set lastArea = rowRange.Areas(rowRange.Areas.Count)
        lastRow = lastArea.rows(lastArea.rows.Count).row
        If lastRow = m_HdrRowNb + 1 Then
            If Application.CountA(m_Worksheet.rows(lastRow)) = 0 Then lastRow = m_HdrRowNb
        End If
    End If
    LastRowNumber = Application.Max(lastRow, m_HdrRowNb) ' Ensure lastRow is never less than the header row
    Exit Property
ErrorHandler: LastRowNumber = m_HdrRowNb: RaiseError Err.Number, fncNm
End Property

' LastColumnNumber - Returns the last populated column in the worksheet, excluding empty columns.
Public Property Get LastColumnNumber() As Long
    Dim fncNm$: fncNm = "MXG_Sheet::LastColumnNumber": On Error GoTo ErrorHandler
    Select Case True
        Case Not IsValidState(State_HasData): LastColumnNumber = 1
        Case IsValidState(State_ListObject):  LastColumnNumber = m_ListObject.Range.columns.Count
        Case Else: LastColumnNumber = m_Worksheet.Cells(m_HdrRowNb, _
                        m_Worksheet.columns.Count).End(xlToLeft).Column
    End Select
    Exit Property
ErrorHandler: RaiseError Err.Number, fncNm
End Property

' RowCount - Returns the number of rows containing data, excluding trailing empty rows.
Public Property Get RowCount() As Long
    Dim fncNm$: fncNm = "MXG_Sheet::RowCount": On Error GoTo ErrorHandler
    Dim lastArea As Range, dataRange As Range: Set dataRange = DataBodyRangeX()

    ' Determine row count
    If Not dataRange Is Nothing Then
        Set lastArea = dataRange.Areas(dataRange.Areas.Count)
        RowCount = lastArea.rows(lastArea.rows.Count).row - m_HdrRowNb
    Else
        RowCount = 0
    End If
    Exit Property
ErrorHandler: RaiseError Err.Number, fncNm
End Property

'###################################################################################################
' COLUMN METADATA AND RANGES
'###################################################################################################

' ColumnNames - Returns an array of column names (0-based index).
Public Property Get columnNames() As Variant: columnNames = m_ColumnHdrDict.keys(): End Property

'---------------------------------------------------------------------------------------------------
' GetColumnNumber - Resolves a column number from a header name (String), index (Long), or Range.
' Params:  ColIdxOrName (Variant) - Column identifier (String, Long, or Range).
' Returns: Long - The resolved column number.
' Notes:   - Raises an error for invalid column types or missing column names.
'---------------------------------------------------------------------------------------------------
Public Function GetColumnNumber(ColIdxOrNm As Variant) As Long
    Dim fncNm$: fncNm = "MXG_Sheet::GetColumnNumber": On Error GoTo ErrorHandler
    Dim colType As String: colType = TypeName(ColIdxOrNm)
    If colType = "String" And IsNumeric(ColIdxOrNm) Then ColIdxOrNm = CLng(ColIdxOrNm): colType = "Long"

    ' Resolve column number based on type
    Select Case colType
        Case "Long", "Integer": GetColumnNumber = ColIdxOrNm ' Directly return index
        Case "String", "Date":  If Left$(ColIdxOrNm, 1) = "'" Then ColIdxOrNm = Mid(ColIdxOrNm, 2)
                                ValidateState State_HeaderColumn, fncNm, ColIdxOrNm
                                GetColumnNumber = m_ColumnHdrDict(ColIdxOrNm) ' Lookup column name
        Case "Range":           GetColumnNumber = ColIdxOrNm.Column ' Resolve column from Range
        Case Else:              RaiseError State_HeaderColumn, fncNm, _
                                    "Invalid column parameter variant. Must be String, " & _
                                    "Date, Long, or Range. Provided: " & TypeName(ColIdxOrNm)
    End Select
    
    ' Handle missing column case
    If GetColumnNumber <= 0 Then _
       RaiseError State_HeaderColumn, fncNm, "Header column name is missing or invalid.", , ColIdxOrNm
    Exit Function

ErrorHandler: RaiseError Err.Number, fncNm, "Header column name is missing or invalid.", , ColIdxOrNm
End Function

'---------------------------------------------------------------------------------------------------
' GetColumnName - Resolves a column name from a header index (Long), name (String), or Range.
' Params:  ColIdxOrNm (Variant) - Column identifier (String, Long, or Range).
' Returns: String                 - The resolved column name.
' Notes:   - Raises an error for invalid column types or unresolved indexes.
'---------------------------------------------------------------------------------------------------
Public Function GetColumnName(ColIdxOrNm As Variant) As String
    Dim fncNm$: fncNm = "MXG_Sheet::GetColumnName": On Error GoTo ErrorHandler
    GetColumnName = m_ColumnHdrDict.keys()(GetColumnNumber(ColIdxOrNm) - 1)
    Exit Function
ErrorHandler: RaiseError State_Parameter, fncNm, , , ColIdxOrNm
End Function

' Return True if the specified column exists in the mapped header
Public Function ColumnExists(ColIdxOrNm As Variant)
    On Error Resume Next: ColumnExists = (GetColumnNumber(ColIdxOrNm) > 0): On Error GoTo 0
End Function

'---------------------------------------------------------------------------------------------------
' ColumnsX - Combines one or more specified columns into a single range, supporting both contiguous
'            and non-contiguous column selections. Rows within the range are always contiguous.
' Params:  ColIdxOrNms (ParamArray) - List of column indexes (Long) or names (Strings) to combine.
' Returns: Range - A single combined range for all specified columns.
' Notes:   - Raises an error if any column name is invalid or the range cannot be retrieved.
'---------------------------------------------------------------------------------------------------
Public Function ColumnsX(ParamArray ColIdxOrNms() As Variant) As Range
    Dim combinedRange As Range, columnRange As Range, databodyrange As Range, col, colIdx&, lastCol
    Dim fncNm$: fncNm = "MXG_Sheet::ColumnsX": On Error GoTo ErrorHandler

    ' Combine the specified columns into a single range, ensuring that non-contiguous columns are
    ' handled by using Union to merge them into a single range for processing.
    Set databodyrange = DataBodyRangeX
    For Each col In ColIdxOrNms
        lastCol = col
        colIdx = GetColumnNumber(col)
        If combinedRange Is Nothing Then
            Set combinedRange = Intersect(databodyrange, databodyrange.Parent.columns(colIdx))
        Else
            Set combinedRange = Union(combinedRange, Intersect(databodyrange, databodyrange.Parent.columns(colIdx)))
        End If
    Next col
    
    If combinedRange Is Nothing Then _
        RaiseError State_HeaderColumn, fncNm, "Header column name is missing or invalid.", , lastCol

    Set ColumnsX = combinedRange
    Exit Function

ErrorHandler: RaiseError Err.Number, fncNm, , , col
End Function

'---------------------------------------------------------------------------------------------------
' GetUniqueColumnArray - Returns an array of unique, non-blank values from the specified column.
' Params:  ColIdxOrName (Variant) - Column name, index, or range to extract values from.
' Returns: Variant - Array of unique column row values.
'---------------------------------------------------------------------------------------------------
Public Function GetUniqueColumnArray(ColIdxOrNm As Variant) As Variant
    Dim fncNm$: fncNm = "MXG_Sheet::GetUniqueColumnArray": On Error GoTo ErrorHandler
    Dim d As Object: Set d = CreateObject("Scripting.Dictionary"): d.CompareMode = vbTextCompare
    Dim a, i As Long, v As Variant: a = ColumnsX(ColIdxOrNm).Value
    For i = 1 To UBound(a, 1)
        v = Trim(CStr(a(i, 1))): If Len(v) > 0 Then d(v) = Empty
    Next
    Dim arr As Variant: arr = d.keys
    QSortArrays arr

    GetUniqueColumnArray = arr
    Exit Function
ErrorHandler: RaiseError Err.Number, fncNm, , , ColIdxOrNm
End Function

'---------------------------------------------------------------------------------------------------
' IsInUniqueColumnArray - Checks if a value exists in the array returned by GetUniqueColumnArray.
' Params:   SrchStr (Variant) - The value to search for.
'           Arr (Variant)     - A 1D array of unique values, typically from GetUniqueColumnArray.
' Returns:  Boolean - True if the value exists in the array; otherwise False.
' Notes:    - Performs case-insensitive match using vbTextCompare.
'           - Trims search string before lookup for consistency.
'           - Wraps IsInArray with error handling.
'---------------------------------------------------------------------------------------------------
Public Function IsInUniqueColumnArray(SrchStr As Variant, arr As Variant) As Boolean
    Dim fncNm$: fncNm = "MXG_Sheet::IsInUniqueColumnArray": On Error GoTo ErrorHandler
    IsInUniqueColumnArray = IsInArray(Trim(CStr(SrchStr)), arr): Exit Function
ErrorHandler: RaiseError Err.Number, fncNm
End Function

'---------------------------------------------------------------------------------------------------
' CaptureColumnFormatFormula - Captures number formats and R1C1 formulas from the first data row.
' Purpose:
'   - Stores column-level NumberFormat and inferred format type in m_ColumnFormatDict.
'   - Stores R1C1 formulas from row 1 of the DataBodyRange in m_ColumnFormulaDict.
'   - Used for visual restoration after SmartRowSet or manual value overwrites.
'---------------------------------------------------------------------------------------------------
Public Sub CaptureColumnFormatFormula()
    Dim fncNm$: fncNm = "MXG_Sheet::CaptureColumnFormatFormula": On Error GoTo ErrorHandler
    Dim colNm As Variant, cell As Range, fmt As String, typ As String
    m_ColumnFormatDict.RemoveAll: m_ColumnFormulaDict.RemoveAll

    For Each colNm In columnNames
        Set cell = SmartCells(HeaderRowNumber + 1, IIf(IsNumeric(colNm), "'" & colNm, colNm))
        If Not cell Is Nothing Then
            fmt = cell.NumberFormat
            If fmt <> "General" Then
                Select Case True
                    Case InStr(fmt, "%") > 0 And InStrAny(fmt, Array("0", "#")): typ = "Percent"
                    Case InStrAny(fmt, Array("0", "#", "(", ")", "-", ",")):     typ = "Numeric"
                    Case InStr(fmt, "m/") > 0 Or InStr(fmt, "d/") > 0:           typ = "Date"
                    Case Else:                                                   typ = "Text"
                End Select
                m_ColumnFormatDict(CStr(colNm)) = Array(fmt, typ)
            End If
            If cell.HasFormula Then m_ColumnFormulaDict(colNm) = cell.FormulaR1C1
        End If
    Next
    Exit Sub

ErrorHandler: RaiseError Err.Number, fncNm, , , colNm
End Sub

'---------------------------------------------------------------------------------------------------
' RestoreColumnFormatFormula - Reapplies previously captured number formats and formulas.
' Params:   columnNames (ParamArray) - Optional. One or more column names to restore.
'                                      If omitted, restores all columns stored in m_ColumnFormatDict.
' Returns:  None
' Notes:    - Reapplies NumberFormat from m_ColumnFormatDict.
'           - Reapplies R1C1-style formulas from m_ColumnFormulaDict.
'           - Safe to call after SmartRowSet or other bulk operations that overwrite values.
'           - Use after CaptureColumnFormatFormula to restore original display fidelity.
'---------------------------------------------------------------------------------------------------
Public Sub RestoreColumnFormatFormula(ParamArray columnNms() As Variant)
    Dim fncNm$: fncNm = "MXG_Sheet::RestoreColumnFormatFormula": On Error GoTo ErrorHandler
    Dim arr As Variant, colNm As Variant, fmtArr As Variant, rng As Range
    If UBound(columnNms) >= LBound(columnNms) Then arr = columnNms Else arr = m_ColumnFormatDict.keys
    
    For Each colNm In arr
        colNm = CStr(colNm)
        On Error Resume Next: Set rng = ColumnsX(colNm): On Error GoTo ErrorHandler
        If Not rng Is Nothing Then ' Handle missing or deleted columns gracefully
            If m_ColumnFormatDict.exists(colNm) Then fmtArr = m_ColumnFormatDict(colNm): rng.NumberFormat = fmtArr(0)
            If m_ColumnFormulaDict.exists(colNm) Then rng.FormulaR1C1 = m_ColumnFormulaDict(colNm)
        End If
    Next colNm
    Exit Sub

ErrorHandler: RaiseError Err.Number, fncNm, , , colNm
End Sub

'---------------------------------------------------------------------------------------------------
' SortX - Sorts rows in the worksheet by one or more columns in ascending or descending order.
' Params:  ColIdxOrNms (ParamArray) - Column names or indices to sort by.
' Returns: Boolean - True if sorting is successful, False otherwise.
' Notes:   - Prefix "<" for descending order, ">" for ascending order (default: ascending).
'          - Example: SortX(">Week", "<Revenue") sorts Week ascending and Revenue descending.
'---------------------------------------------------------------------------------------------------
Public Function SortX(ParamArray ColIdxOrNms() As Variant) As Boolean
    Dim fncNm$: fncNm = "MXG_Sheet::SortX": On Error GoTo ErrorHandler
    Dim ColIdxOrName As Variant, colNumber As Long: SortX = False

    ' Configure sorting
    With ParentWorksheet.Sort
        For Each ColIdxOrName In ColIdxOrNms
            ' Resolve column number using GetColumnNumber
            colNumber = GetColumnNumber(Replace(Replace(ColIdxOrName, "<", ""), ">", ""))
            If colNumber <= 0 Or colNumber > LastColumnNumber Then _
                RaiseError State_HeaderColumn, fncNm, "Header column name is missing or invalid.", , ColIdxOrName
            ' Add sort field
            .SortFields.Add key:=ColumnsX(colNumber), _
                            Order:=IIf(Left(ColIdxOrName, 1) = "<", xlDescending, xlAscending)
        Next ColIdxOrName
        .SetRange DataBodyRangeX: .Header = xlNo: .Apply: .SortFields.Clear
    End With
    SortX = True
    Exit Function

ErrorHandler: RaiseError Err.Number, fncNm, , , ColIdxOrName
End Function

'###################################################################################################
' SMART SUITE - Reduce code clutter and increase performance using Excel-like functions with 100K+
'               row sub-second performance and accuracy at Excel’s maximum sheet size
'               - WITHOUT PROTECTED SHEET AND ROW VISIBILITY CONSTRAINTS -
' --------------------------------------------------------------------------------------------------
'   SMARTCELLS   - Multi-column retrieval using row numbers, search strings, and wildcards.
'   SMARTFILTER  - AutoFilter-like filtering, but 60% faster and accurate at Excel’s max size.
'   SMARTLOOKUP  - Composite key lookups with wildcard matching, caching, and sort control.
'   SMARTROW     - Efficient row read/write using a dictionary for fast updates and automation.
'###################################################################################################

'###################################################################################################
' SMARTCELLS - SmartCells extends Cells functionality with 100K+ sub-second performance and flexible
'              row-level access using row numbers, search criteria (including wildcards), column
'              names, or ranges. Reduces variable clutter by retrieving row data from multi-columns
'              in a dictionary. Supports protected sheets and multi-column extraction in one call.
'###################################################################################################
'---------------------------------------------------------------------------------------------------
' SmartCells - Retrieves cell values from a row based on a search value or row number.
' Params:  RowIdxOrSrchStr (Variant)           - Row number, cell range, or search string.
'          ColIdxOrNm (Variant, Optional)  - Column name/index used for searching (if needed).
'          rtrnCol (Variant, Optional)     - Column name, index, or array of columns to return.
'          RaiseSearchError (Boolean)      - If True (default), raises error when no match is found.
' Returns: Variant - A Range (single cell), Dictionary (multi-column or "*" request), or Nothing on error.
' Notes:   - If RowIdxOrSrchStr is a Range, the search column is inferred from its position in the header row.
'          - Use "*" as rtrnCol to return all columns in a Dictionary.
'          - Matching is case-insensitive. Duplicate rtrnCol names will overwrite earlier values.
'          - A leading apostrophe (') in RowIdxOrSrchStr (denoting string, not row) is stripped before matching.
'---------------------------------------------------------------------------------------------------
' Example: mxgSh.SmartCells(25, 1) ' Retrieve Row 25, Col 1
'          mxgSh.SmartCells("Mark Watson", "Employee Name") ' Find Mark Watson
'          mxgSh.SmartCells("Mary Wilson", "Employee Name", "Email") ' Lookup Email for Mary Wilson
'          mxgSh.SmartCells("John", "Name", Array("Age", "City")) ' Returns a Dictionary with multiple columns
'          mxgSh.SmartCells("1001", "ID", "*") ' Returns all columns as a Dictionary
'---------------------------------------------------------------------------------------------------
Public Function SmartCells(RowIdxOrSrchStr As Variant, Optional ColIdxOrNm As Variant = vbNullString, _
                           Optional rtrnCol As Variant = vbNullString, _
                           Optional RaiseSearchError As Boolean = True) As Variant
    Dim fncNm$: fncNm = "MXG_Sheet::SmartCells": On Error GoTo ErrorHandler
    Dim srchColNbr As Long, rowNbr As Long, fndCell As Range, dict As Object, arrIdx As Long
    If Not SafeIsEmpty(ColIdxOrNm) Then srchColNbr = GetColumnNumber(ColIdxOrNm)

    rowNbr = ResolveRowNumber(RowIdxOrSrchStr, ColIdxOrNm, RaiseSearchError, "SmartCells")
    If rowNbr = 0 Then
        If SafeIsArray(rtrnCol) Or rtrnCol = "*" Or (SafeIsEmpty(ColIdxOrNm) And SafeIsEmpty(rtrnCol)) Then
            Set SmartCells = CreateObject("Scripting.Dictionary")
        Else
            Set SmartCells = Nothing
        End If
        Exit Function
    End If

    With m_Worksheet
        ' Handle default empty column request
        If Not SafeIsEmpty(ColIdxOrNm) And SafeIsEmpty(rtrnCol) Then Set SmartCells = .Cells(rowNbr, srchColNbr): Exit Function
        If SafeIsEmpty(ColIdxOrNm) And SafeIsEmpty(rtrnCol) Then rtrnCol = "*"

        ' Return multiple columns as dictionary
        If SafeIsArray(rtrnCol) Then
            If LBound(rtrnCol) > UBound(rtrnCol) Then Exit Function ' Extra Safe check
            Set dict = CreateObject("Scripting.Dictionary")
            ' Ensure ColIdxOrNm is included in results if specified
            If Not SafeIsEmpty(ColIdxOrNm) Then Set dict(columnNames(srchColNbr - 1)) = .Cells(rowNbr, srchColNbr)
            For arrIdx = LBound(rtrnCol) To UBound(rtrnCol)
                ' Note: Duplicate keys allowed — last one wins for performance and simplicity.
                Set dict(rtrnCol(arrIdx)) = .Cells(rowNbr, GetColumnNumber(rtrnCol(arrIdx))) ' rtnCol Array
            Next arrIdx
            Set SmartCells = dict: Exit Function
        End If

        If rtrnCol <> "*" Then Set SmartCells = .Cells(rowNbr, GetColumnNumber(rtrnCol)): Exit Function

        ' Wildcard(*) override - All Columns (returns Dictionary)
        Set dict = CreateObject("Scripting.Dictionary")
        For arrIdx = 1 To m_ColumnHdrDict.Count
            Set dict(m_ColumnHdrDict.keys()(arrIdx - 1)) = .Cells(rowNbr, arrIdx)
        Next arrIdx

        Set SmartCells = dict: Exit Function
    End With
    Exit Function

ErrorHandler:
    Set SmartCells = Nothing
    If Err.Number = State_Search Then RaiseError Err.Number, fncNm, "Search String Not Found: '" & RowIdxOrSrchStr & "'"
    RaiseError Err.Number, fncNm
End Function

'###################################################################################################
' SMARTFILTER - High-speed, in-memory filtering across 100K+ rows with support for complex criteria,
'               protected sheets, and wildcards. Outperforms AutoFilter with full control and reliability.
'###################################################################################################
'---------------------------------------------------------------------------------------------------
' SmartFilter - Filters rows using AND/OR logic with exact, wildcard, or operator criteria. Stores
'               results in memory for sub-second retrieval. Unlike AutoFilter, does not alter visible rows.
' Params:   ColIdxOrNm (Variant)       - Column name or index to apply the filter on.
'           Criteria1 (Variant)        - Value or array of values to match (OR logic if array).
'           Criteria2 (Variant, Opt)   - Optional secondary criteria for AND/OR logic.
'           CriteriaOperator (Opt)     - xlAnd (default) or xlOr for combining Criteria1 and 2.
'           RaiseSearchError (Boolean) - If True (default), raises an error when no matches found.
' Notes:    - Filters refine across columns (AND logic).
'           - Operators: =, <>, <, <=, >, >=, *, ?.
'           - Regex is used internally to extract operator and value from criteria strings.
'           - Use SmartFilterRows to retrieve. Use SmartFilterClear to reset.
'---------------------------------------------------------------------------------------------------
Public Sub SmartFilter(ColIdxOrNm As Variant, Criteria1 As Variant, _
                       Optional Criteria2 As Variant = "", _
                       Optional CriteriaOperator As XlAutoFilterOperator = xlAnd, _
                       Optional RaiseSearchError As Boolean = True)
    Dim fncNm$: fncNm = "MXG_Sheet::SmartFilter": On Error GoTo ErrorHandler

    Dim colRange As Range, rowNbr As Variant, iRow As Long, iCol As Long, arr As Variant
    Dim SrchVal1 As Variant, Op1 As String, SrchVal2 As Variant, Op2 As String

    ' Validate inputs
    If ColIdxOrNm = "" Then RaiseError State_Parameter, fncNm, "Search column was not defined."
    If CriteriaOperator <> xlAnd And CriteriaOperator <> xlOr Then _
        RaiseError State_Parameter, fncNm, "Invalid operator: " & CriteriaOperator
    If IsNull(Criteria1) Or IsNull(Criteria2) Then _
        RaiseError State_Parameter, fncNm, "Criteria cannot be null."

    ' Track the number of columns processed in this filter sequence
    ' This ensures that multiple column filters are applied progressively
    m_FindResultsColCtr = m_FindResultsColCtr + 1 ' Increment column counter

    ' Retrieve the column range and store values in an array for faster lookups
    If m_FindResultsLastRow = 0 Then
        Set colRange = ColumnsX(ColIdxOrNm)
        If colRange.rows.Count > 0 Then m_FindResultsLastRow = colRange.rows(colRange.rows.Count).row
    Else
        With m_Worksheet
            iCol = GetColumnNumber(ColIdxOrNm)
            Set colRange = .Range(.Cells(m_HdrRowNb + 1, iCol), .Cells(m_FindResultsLastRow, iCol))
        End With
    End If
    
    ' Get the column type so data can quickly be converted for comparisons.
    m_FindColumnType = VarType(colRange.Cells(1, 1).Value)
    If m_FindColumnType = vbEmpty Then m_FindColumnType = vbVariant
    
    arr = colRange.Value
    If SafeIsEmpty(arr) Then Set m_FindResultsColl = New Collection: Exit Sub

    ' Extract search values and operators from Criteria1 and Criteria2
    ' Wildcards (*, ?) default to "=" if no explicit operator is provided
    If SafeIsArray(Criteria1) Then
        If Not SafeIsEmpty(Criteria2) Then _
           RaiseError State_Parameter, fncNm, "If Criteria1 is an array then Criteria2 must be empty."
        SrchVal1 = Criteria1
    Else
        GetCriteriaParts Criteria1, SrchVal1, Op1
        If Criteria1 <> "" And Not IsInArray(Op1, Array("<>", "<=", ">=", "<", ">", "=")) Then _
            RaiseError State_Parameter, fncNm, "Invalid Criteria1 Operator: " & Op1
        If Criteria2 <> "" Then
            GetCriteriaParts Criteria2, SrchVal2, Op2
            If Not IsInArray(Op2, Array("<>", "<=", ">=", "<", ">", "=")) Then _
                RaiseError State_Parameter, fncNm, "Invalid Criteria2 Operator: " & Op2
        End If
        ' Convert values to correct type
        SrchVal1 = TypeConvert(m_FindColumnType, SrchVal1)
        If Criteria2 <> "" Then SrchVal2 = TypeConvert(VarType(SrchVal1), SrchVal2)
    End If

    ' Apply filtering: Each call further refines the stored row collection
    ' by removing rows that do not match the current column filter
    Set m_FindResultsColl = SmartFilterEvalRows(m_FindResultsColl, arr, SrchVal1, Op1, SrchVal2, Op2, CriteriaOperator)

    ' If no matches are found and RaiseSearchError is enabled then raise an error
    If m_FindResultsColl.Count = 0 And RaiseSearchError Then _
        RaiseError MXG_StateType.State_SheetData, fncNm, "No matching rows found."
    Exit Sub

ErrorHandler: RaiseError Err.Number, fncNm
End Sub

'---------------------------------------------------------------------------------------------------
' SmartFilterEvalRows - Progressively filters rows by evaluating values in a single column against
'                       specified Criteria1 and Criteria2. With each successive call, only rows from
'                       InputRowNbrs are evaluated, enabling high-performance multi-column filtering.
'
'                       Matching rows are collected in outputRowNbrs. After processing all rows,
'                       outputRowNbrs replaces InputRowNbrs as the new active filter state.
'                       This progressive narrowing of the row set with each call significantly boosts
'                       performance across large datasets.
' Params:   InputRowNbrs (Collection) - Collection of rows from a prior filter call.
'                                     - If empty, all rows in the column are evaluated (first filter pass).
'                                     - If not empty, only those rows are checked (subsequent passes).
'           ColumnValueArr (Variant)  - 2D array of values from the column being filtered.
'           SrchVal1 (Variant)        - First search value. Can be a single value, array, or dictionary.
'                                     - Arrays are automatically converted to dictionaries for OR logic.
'           Op1 (String)              - Operator for SrchVal1: >, <, >=, <=, =, <>.
'           SrchVal2 (Variant)        - Optional second search value (used only if CriteriaOperator = xlAnd).
'           Op2 (String)              - Optional operator for SrchVal2 (>, <, >=, <=, =, <>).
'           CriteriaOperator (XlAutoFilterOperator)
'                                     - Determines how Criteria1 and Criteria2 are combined.
'                                     - xlAnd (default): both criteria must match.
'                                     - xlOr: either condition may match.
' Returns:  Collection                - A new collection of row numbers that match the provided criteria.
' Notes:    - Utilizes two collections:
'               1) InputRowNbrs: rows from the previous filter pass.
'               2) outputRowNbrs: refined result set matching current criteria.
'           - For the first filter call, all rows in the column are evaluated.
'           - For all subsequent calls, only previously matched rows are evaluated.
'           - SrchVal1 arrays are converted to dictionaries for fast OR-based evaluation.
'           - This design dramatically improves performance on 100K+ row datasets.
'---------------------------------------------------------------------------------------------------
Private Function SmartFilterEvalRows(InputRowNbrs As Collection, ColumnValueArr As Variant, _
                                     SrchVal1 As Variant, Op1 As String, _
                                     Optional SrchVal2 As Variant = "", Optional Op2 As String = "", _
                                     Optional CriteriaOperator As XlAutoFilterOperator = xlAnd) As Collection
    Dim fncNm$: fncNm = "MXG_Sheet::SmartFilterEvalRows": On Error GoTo ErrorHandler
    Dim outputRowNbrs As New Collection, criteriaDict As Object, val As Variant
    Dim iRow As Long, rowNbr As Variant, CellValue As Variant
    Dim offset As Long, lb As Long, ub As Long ' NEW: cache header offset and bounds

    ' Convert SrchVal1 to a dictionary if it is an array
    ' - This allows for faster lookups when filtering for multiple values (OR logic).
    If SafeIsArray(SrchVal1) Then
        Set criteriaDict = CreateObject("Scripting.Dictionary"): criteriaDict.CompareMode = vbTextCompare
        For Each val In SrchVal1: criteriaDict(val) = "": Next val
        Set SrchVal1 = criteriaDict
    End If

    offset = m_HdrRowNb ' cache header offset

    ' Determine if this is the first column being filtered (m_FindResultsColCtr = 1)
    ' - If true, all rows are checked against criteria
    ' - If false, only rows in InputRowNbrs are checked (progressive filtering)
    If m_FindResultsColCtr = 1 Then
        ' First column search - process all rows in ColumnValueArr
        lb = LBound(ColumnValueArr, 1): ub = UBound(ColumnValueArr, 1) ' cache bounds
        For iRow = lb To ub
            rowNbr = iRow + offset ' Adjust to handle row header
            CellValue = ColumnValueArr(iRow, 1)

            ' Check if the row matches the criteria
            If EvalCriterias(CellValue, SrchVal1, Op1, SrchVal2, Op2, CriteriaOperator) Then
                outputRowNbrs.Add rowNbr
            End If
        Next iRow
    Else
        ' Subsequent column filtering - refine previous results.
        ' - Instead of adding new matches, rows that do not match are removed.
        For Each rowNbr In InputRowNbrs
            iRow = rowNbr - offset ' Adjust back to match correct indexing
            CellValue = ColumnValueArr(iRow, 1)

            ' Keep only rows that match the current filter criteria.
            ' - Filter is being rebuilt here
            If EvalCriterias(CellValue, SrchVal1, Op1, SrchVal2, Op2, CriteriaOperator) Then
                outputRowNbrs.Add rowNbr
            End If
        Next rowNbr
    End If

    ' Return the filtered collection
    Set SmartFilterEvalRows = outputRowNbrs
    Exit Function

ErrorHandler: RaiseError Err.Number, fncNm
End Function

'---------------------------------------------------------------------------------------------------
' EvalCriterias - Evaluates a cell value against one or two criteria using AND or OR logic.
'                 If SrchVal1 is an array, it’s treated as OR logic across all values.
'                 If both SrchVal1 and SrchVal2 are provided, logic is determined by CriteriaOperator.
' Params:  CellValue (Variant) - Value to evaluate.
'          SrchVal1 (Variant)  - First criteria (single value or Dictionary for OR logic).
'          Op1 (String)        - Operator for SrchVal1: >, <, >=, <=, =, <>.
'          SrchVal2 (Variant)  - Optional second value (used with xlAnd).
'          Op2 (String)        - Optional operator for SrchVal2.
'          CriteriaOperator (XlAutoFilterOperator) - xlAnd (default) or xlOr logic between values.
' Returns: Boolean - True if the cell meets the criteria.
' Notes:   - Supports wildcards (*, ?) for pattern-based matching.
'          - Arrays are converted to dictionaries for fast OR lookups.
'          - Applies each operator type-safe and handles mixed data gracefully.
'---------------------------------------------------------------------------------------------------
Private Function EvalCriterias(CellValue As Variant, SrchVal1 As Variant, Op1 As String, _
                               Optional SrchVal2 As Variant = "", Optional Op2 As String = "", _
                               Optional CriteriaOperator As XlAutoFilterOperator = xlAnd) As Boolean
    ' If SrchVal1 is a Dictionary, it means Criteria1 was an array
    ' - This enables fast lookups where OR logic is applied to multiple possible values
    If IsObject(SrchVal1) Then
        On Error Resume Next
        Dim testVal: testVal = SrchVal1(CellValue)
        If Err.Number = 0 Then EvalCriterias = True: Exit Function
        On Error GoTo 0
    End If

    ' Convert CellValue to uppercase if the search column is text-based
    If m_FindColumnType = vbString And Not IsNull(CellValue) Then CellValue = UCase$(CellValue)

    ' If only one criteria is provided, evaluate against SrchVal1 only
    If SrchVal2 = "" Then
        ' Check if CellValue matches SrchVal1 using the specified operator
        If EvalCriterion(CellValue, SrchVal1, Op1) Then EvalCriterias = True: Exit Function
    Else
        ' Apply the correct logic based on CriteriaOperator (xlAnd or xlOr)
        If CriteriaOperator = xlOr Then
            ' Match if either SrchVal1 OR SrchVal2 criteria is met
            If EvalCriterion(CellValue, SrchVal1, Op1) Then EvalCriterias = True: Exit Function
            If EvalCriterion(CellValue, SrchVal2, Op2) Then EvalCriterias = True: Exit Function
        Else
            ' Match only if BOTH SrchVal1 AND SrchVal2 criteria are met
            If Not EvalCriterion(CellValue, SrchVal1, Op1) Then Exit Function
            If EvalCriterion(CellValue, SrchVal2, Op2) Then EvalCriterias = True: Exit Function
        End If
    End If
    EvalCriterias = False
End Function

' EvalCriterion - Compares a value to a search term using standard or wildcard operators.
Private Function EvalCriterion(Value As Variant, SrchVal As Variant, Op As String) As Boolean
    Select Case Op ' Perform the comparison
        Case ">":   EvalCriterion = (Value > SrchVal)
        Case "<":   EvalCriterion = (Value < SrchVal)
        Case ">=":  EvalCriterion = (Value >= SrchVal)
        Case "<=":  EvalCriterion = (Value <= SrchVal)
        Case "=":   EvalCriterion = (Value = SrchVal)
        Case "<>":  EvalCriterion = (Value <> SrchVal)
        Case "*":   EvalCriterion = (Value Like SrchVal) ' Use "Like" for wildcard matching
        Case Else:  EvalCriterion = False                ' Handle unsupported operators gracefully
    End Select
End Function

' SmartFilterClear - Clears all SmartFilter results and resets internal state.
Public Sub SmartFilterClear()
    On Error Resume Next: m_FindResultsColCtr = 0: m_FindResultsLastRow = 0: On Error GoTo 0
End Sub

' IsSmartFilterActive - Returns True if SmartFilter results exist; False otherwise.
Public Property Get IsSmartFilterActive() As Boolean: IsSmartFilterActive = (m_FindResultsColl.Count > 0): End Property

'---------------------------------------------------------------------------------------------------
' SmartFilterSort - Sorts SmartFilter results in memory using composite keys from one or more columns.
' Params:   sortOrder (XlSortOrder)        - Sort direction: xlAscending or xlDescending.
'           ColIdxOrNms (ParamArray)       - Column names or indices used to build the sort key.
' Notes:    - Does not affect worksheet row order or visibility.
'---------------------------------------------------------------------------------------------------
Public Sub SmartFilterSort(SortOrder As XlSortOrder, ParamArray ColIdxOrNms() As Variant)
    Dim fncNm$: fncNm = "MXG_Sheet::SmartFilterSort": On Error GoTo ErrorHandler
    Dim colData() As Variant, colNameArg As String, colName As String, colFormat As String
    Dim cellValRaw As Variant, cellFormatted As String, isFormatting As Boolean
    Dim keyArr() As String, rowArr() As Long, rowNum As Variant, key As String
    Dim RowCount As Long, idx As Long, i As Long

    ' Exit if no sort columns provided or no rows to sort
    If m_FindResultsColl Is Nothing Then Exit Sub
    If m_FindResultsColl.Count = 0 Then Exit Sub
    If UBound(ColIdxOrNms) < 0 Then Exit Sub

    ' Load column data with optional formatting
    ReDim colData(1 To UBound(ColIdxOrNms) + 1)
    For i = 0 To UBound(ColIdxOrNms)
        colNameArg = ColIdxOrNms(i)
        colFormat = ""
        If InStr(1, colNameArg, ":Format=", vbTextCompare) > 0 Then
            colName = Trim(Split(colNameArg, ":")(0))
            colFormat = Mid(colNameArg, InStr(1, colNameArg, "Format=", vbTextCompare) + 7)
            isFormatting = True
        Else
            colName = colNameArg
        End If
        colData(i + 1) = Array(ColumnsX(colName).Value, colFormat)
    Next i

    ' Retrieve current filtered rows
    Set m_FindResultsColl = SmartFilterRows
    RowCount = m_FindResultsColl.Count
    ReDim keyArr(1 To RowCount)
    ReDim rowArr(1 To RowCount)

    ' Build composite sort keys
    idx = 1
    For Each rowNum In m_FindResultsColl
        key = ""
        For i = 1 To UBound(colData)
            cellValRaw = colData(i)(0)(rowNum - m_HdrRowNb, 1)
            
            If isFormatting And Len(colData(i)(1)) > 0 Then
                cellFormatted = Format(cellValRaw, colData(i)(1))
                cellFormatted = Trim(cellFormatted)
            Else
                cellFormatted = CStr(cellValRaw)
            End If
            If Len(cellFormatted) = 0 Then cellFormatted = Chr(0)
            key = key & "|" & cellFormatted
        Next i
        keyArr(idx) = key
        rowArr(idx) = rowNum
        idx = idx + 1
    Next rowNum

    ' Sort keys and associated row numbers
    QSortArrays keyArr, xlAscending, rowArr

    ' Rebuild results collection in sort order
    Set m_FindResultsColl = New Collection
    If SortOrder = xlDescending Then
        For i = RowCount To 1 Step -1: m_FindResultsColl.Add rowArr(i): Next i
    Else
        For i = 1 To RowCount: m_FindResultsColl.Add rowArr(i): Next i
    End If
    Exit Sub

ErrorHandler:
    RaiseError Err.Number, fncNm
End Sub

'---------------------------------------------------------------------------------------------------
' SmartFilterRows - Returns SmartFilter results as row numbers (default) or full row ranges.
' Params:   ReturnRowRanges (Boolean, Optional) - If True, returns row ranges. Otherwise, row numbers.
' Returns:  Collection - Filtered row numbers or row ranges based on mode.
'---------------------------------------------------------------------------------------------------
Public Function SmartFilterRows(Optional ReturnRowRanges As Boolean = False) As Collection
    Dim fncNm$: fncNm = "MXG_Sheet::SmartFilterRows": On Error GoTo ErrorHandler
    Dim rowNbr As Variant, rngColl As New Collection, lastCol As Long, iRow As Long
    
    ' No filters applied yet — return all row numbers from header+1 to LastRowNumber
    If m_FindResultsColCtr = 0 Then
        If m_FindResultsColl Is Nothing Then Set m_FindResultsColl = New Collection
        For iRow = m_HdrRowNb + 1 To LastRowNumber
            m_FindResultsColl.Add iRow
        Next iRow
        Set SmartFilterRows = m_FindResultsColl
        m_FindResultsColCtr = 1
        Exit Function
    End If

    ' Exit early if no active SmartFilter results
    ' - If no filtering has been applied, return an empty collection
    If m_FindResultsColCtr = 0 Then Set SmartFilterRows = New Collection: Exit Function
    
    ' If ReturnRowRanges is False, return stored row numbers from m_FindResultsColl
    If Not ReturnRowRanges Then Set SmartFilterRows = m_FindResultsColl: Exit Function
    
    ' Return row numbers that have been converted into full row ranges
    ' - Retrieves the last column index from the column header dictionary
    lastCol = m_ColumnHdrDict.Count
    For Each rowNbr In m_FindResultsColl
        rngColl.Add m_Worksheet.Range(m_Worksheet.Cells(rowNbr, 1), _
                                        m_Worksheet.Cells(rowNbr, lastCol))
    Next rowNbr
    Set SmartFilterRows = rngColl
    Exit Function
    
ErrorHandler: RaiseError Err.Number, fncNm
End Function

' GetCriteriaParts - Parses a filter string into operator and search value. Supports wildcards.
Private Function GetCriteriaParts(criteria As Variant, ByRef SrchVal As Variant, ByRef Op As String) As Boolean
    Dim match As Object
    SrchVal = "": Op = "" ' Reset outputs and create RegEx Object if not provided
    
    ' Use regex (created during the class constructor) to extract operator and search value
    Set match = m_FindCriteriaOpRegex.Execute(criteria)
    If Not match Is Nothing Then
        If match.Count > 0 Then
            ' Extract operator if found, otherwise default to "="
            Op = match(0).SubMatches(0): If Op = "" Then Op = "="
            ' Extract search value
            SrchVal = match(0).SubMatches(1)
            ' If criteria contains * or ?, override operator with "*" indicating wildcard match
            If InStr(criteria, "*") > 0 Or InStr(criteria, "?") Then Op = "*"
        End If
    End If
    
    ' Return True if a valid search value was found
    GetCriteriaParts = (SrchVal <> "")
End Function

' TypeConvert - Converts input to expected type for fast, accurate filtering; raises error on failure.
Private Function TypeConvert(typeData As VbVarType, convertData As Variant, _
                             Optional columnName As String = "") As Variant
    ' Attempt conversion based on expected type
    Dim fncNm$: fncNm = "TypeConvert": TypeConvert = Null
    Select Case typeData
        Case vbString:  TypeConvert = UCase(CStr(convertData))
        Case vbVariant: TypeConvert = convertData
        Case vbDate:    TypeConvert = CDate(convertData)
        Case vbDouble, vbSingle, vbInteger, vbLong, vbCurrency: TypeConvert = CDbl(convertData)
        Case vbBoolean: TypeConvert = CBool(convertData)
        Case vbEmpty:   TypeConvert = convertData
        Case Else:      RaiseError State_Parameter, fncNm, "Unexpected Column type.", , columnName
    End Select
End Function

' ResolveRowNumber - Resolves the row number from various input criteria (Long, Range, or String).
Private Function ResolveRowNumber(RowIdxOrSrchStr As Variant, Optional ColIdxOrNm As Variant = vbNullString, _
                                  Optional RaiseSearchError As Boolean = True, _
                                  Optional callerName As String = "SmartCells") As Long
    Dim fndCell As Range
    Select Case TypeName(RowIdxOrSrchStr)
        Case "Long", "Integer": ResolveRowNumber = RowIdxOrSrchStr
                        If ResolveRowNumber = 0 Then _
                            RaiseError State_Search, "MXG_Sheet::" & callerName, "Row not found", _
                                       SafeReference(RowIdxOrSrchStr), SafeReference(ColIdxOrNm)
                                
        Case "Range":  If RowIdxOrSrchStr.rows.Count > 1 Then RaiseError State_Parameter, "MXG_Sheet::" & callerName, "RowIdxOrSrchStr must be a single row", , ColIdxOrNm
                       Set fndCell = RowIdxOrSrchStr.Cells(1, 1): ResolveRowNumber = fndCell.row

        Case "String": If Left(RowIdxOrSrchStr, 1) = "'" Then RowIdxOrSrchStr = Mid$(RowIdxOrSrchStr, 2)
                       Dim srchRng As Range: Set srchRng = ColumnsX(ColIdxOrNm)
                       Set fndCell = srchRng.Find(What:=RowIdxOrSrchStr, LookIn:=xlValues, LookAt:=xlPart, _
                                                  After:=srchRng.Cells(srchRng.rows.Count, 1))
                       If fndCell Is Nothing Then
                          If RaiseSearchError Then
                             RaiseError State_Search, "MXG_Sheet::" & callerName, "Row not found", SafeReference(RowIdxOrSrchStr), SafeReference(ColIdxOrNm)
                          Else
                             ResolveRowNumber = 0: Exit Function
                          End If
                       End If
                       ResolveRowNumber = fndCell.row
        Case Else:     RaiseError State_Parameter, "MXG_Sheet::" & callerName, "Unsupported RowIdxOrSrchStr type: " & TypeName(RowIdxOrSrchStr), , ColIdxOrNm
    End Select
End Function

'###################################################################################################
' SMARTLOOKUP       - Sub-second lookup performance on 100K+ rows using composite keys built from any
'                     combination of columns, stored in cached memory for instant retrieval.
' SMARTLOOKUPROWS   - Supports exact and wildcard matching on composite keys. Returns matching row
'                     numbers for downstream extraction using SmartLookupValues or SmartCells.
' SMARTLOOKUPVALUES - Retrieves cached column value(s) from row results using SmartLookupRows.
'                     Supports single or multiple columns with dictionary output for bulk reads.
'###################################################################################################
'---------------------------------------------------------------------------------------------------
' SmartLookup - Builds a high-performance lookup map using composite keys across any column
'               combination, with optional sorting and value caching for downstream access.
'
' Params:
'   lookupMeta (Variant, ByRef) - Receives the reusable lookup object returned by this function.
'                                 Pass to SmartLookupRows or SmartLookupValues for downstream access.
'   SrchColIdxNmOrArray (Variant)   - One or more columns (index, name, or range) used to build composite keys.
'   CacheColIdxNmOrArray (Variant)  - Optional. One or more columns to cache for fast value retrieval.
'   SortColIdxNmOrArray (Variant)   - Optional. One or more columns to define the sort order of results.
'   SortOrder (XlSortOrder)     - Optional. Sort direction (default: xlAscending).
'
' Returns:
'   Boolean - True if the lookup structure was built successfully, False if no data or keys were found.
'
' Notes:
'   - Supports composite keys with optional formatting using ":Format=" suffix.
'   - All key, row, and cache data is stored in memory for sub-second access on 100K+ rows.
'   - Use SmartLookupRows to retrieve matching row numbers.
'   - Use SmartLookupValues to retrieve cached column values for each row.
'---------------------------------------------------------------------------------------------------
Public Function SmartLookup(ByRef lookupMeta As Variant, SrchColIdxNmOrArray As Variant, _
                            Optional CacheColIdxNmOrArray As Variant = Empty, _
                            Optional SortColIdxNmOrArray As Variant = Empty, _
                            Optional SortOrder As XlSortOrder = xlAscending) As Boolean
    Dim fncNm$: fncNm = "MXG_Sheet::SmartLookup": On Error GoTo ErrorHandler
    Dim keyColNms, sortColNms, CacheColNms, iCol As Long, RowCount As Long, r As Long
    Dim colData(), fmtCols(), keyArr As Variant, sortKeyArr() As String, rowNbrArr() As Long
    Dim colNm As String, colOrigNm As String, idx As Long, isFormatUsed As Boolean

    ' --- Normalize input ---
    keyColNms = SafeToArray(SrchColIdxNmOrArray)
    sortColNms = SafeToArray(SortColIdxNmOrArray)
    CacheColNms = SafeToArray(CacheColIdxNmOrArray)
    
    ' --- Build composite keys and row numbers, exit early if keyArr is empty ---
    keyArr = BuildCompositeKeyArr(keyColNms, rowNbrArr)
    If UBound(keyArr) < 0 Then SmartLookup = False: Exit Function
    
    ' --- Optional: sort using SortColIdxNmOrArray ---
    If UBound(sortColNms) < LBound(sortColNms) Then
        QSortArrays keyArr, SortOrder, rowNbrArr
    Else
        sortKeyArr = BuildCompositeKeyArr(sortColNms)
        QSortArrays sortKeyArr, SortOrder, keyArr, rowNbrArr
    End If

    ' --- Build composite key row collection dictionary ---
    Dim keyDict As Object: Set keyDict = CreateObject("Scripting.Dictionary")
    keyDict.CompareMode = vbTextCompare
    Dim lastKey As String: lastKey = vbNullString
    Dim coll As Collection: Set coll = New Collection
    For iCol = LBound(keyArr) To UBound(keyArr)
        If keyArr(iCol) <> lastKey Then
            If lastKey <> vbNullString Then Set keyDict(lastKey) = coll
            Set coll = New Collection: lastKey = keyArr(iCol)
        End If
        coll.Add rowNbrArr(iCol)
    Next
    If lastKey <> vbNullString Then Set keyDict(lastKey) = coll

    ' --- Initialize lookupMeta ---
    ReDim lookupMeta(0 To 4)
    lookupMeta(meta_KeyArr) = keyArr
    lookupMeta(meta_rowNbrs) = rowNbrArr
    Set lookupMeta(meta_keyDict) = keyDict
    lookupMeta(meta_CachedCols) = Empty
    Set lookupMeta(meta_CachedColNms) = CreateObject("Scripting.Dictionary")

    ' --- Cache columns if provided ---
    If UBound(CacheColNms) >= 0 Then
        Dim cachArr(): ReDim cacheArr(1 To UBound(CacheColNms) + 1)
        For iCol = 1 To UBound(CacheColNms) + 1
            colNm = CStr(CacheColNms(iCol - 1))
            cacheArr(iCol) = ColumnsX(colNm).Value
            lookupMeta(meta_CachedColNms)(colNm) = iCol
        Next

        lookupMeta(meta_CachedCols) = cacheArr
    End If

    SmartLookup = True: Exit Function
ErrorHandler: RaiseError Err.Number, fncNm
End Function

' Returns unique composite keys from SmartLookup metadata.
Public Function SmartLookupUniqueKeys(ByRef lookupMeta As Variant) As Collection
    Dim fncNm$: fncNm = "MXG_Sheet::SmartLookupUniqueKeys": On Error GoTo ErrorHandler
    Dim keysCol As New Collection, prevKey$, i&, keys: keys = lookupMeta(meta_KeyArr)
    If Not SafeIsArray(keys) Then Set SmartLookupUniqueKeys = keysCol: Exit Function
    For i = LBound(keys) To UBound(keys)
        If keys(i) <> prevKey Then keysCol.Add keys(i): prevKey = keys(i)
    Next i
    Set SmartLookupUniqueKeys = keysCol: Exit Function
ErrorHandler: RaiseError Err.Number, fncNm
End Function

'---------------------------------------------------------------------------------------------------
' SmartLookupRows - Retrieves row number(s) from SmartLookup using exact or wildcard key match.
' Params:  lookupMeta (Variant)  - Metadata returned by SmartLookup.
'          RowIdxCmptKeyOrArray (Variant)   - Composite key (array/string) or Empty to return all rows.
'          FirstOnly (Boolean)   - If True, returns only the first matching row (default: False).
' Returns: Variant - A single value if one column is requested; otherwise, a Dictionary of values.
'                    Returns Empty if no match and one column is requested; otherwise, Nothing.
' Notes:   - Supports * and ? wildcards for flexible search patterns.
'          - Optimized for 100K+ rows using sorted in-memory arrays.
'          - A leading apostrophe (') proceeding a number in the RowIdxCmptKeyOrArray denotes a string not row.
'---------------------------------------------------------------------------------------------------
Public Function SmartLookupRows(ByRef lookupMeta As Variant, Optional RowIdxCmptKeyOrArray As Variant = Empty, _
                                Optional FirstOnly As Boolean = False) As Variant
    Dim fncNm$: fncNm = "MXG_Sheet::SmartLookupRows": On Error GoTo ErrorHandler
    Dim i As Long, val As String, rowNum As Long, keyStr As String, rowArr, keyArr, matchRows As Collection, exists As Boolean

    ' No key: return all rows
    If IsMissing(RowIdxCmptKeyOrArray) Or IsEmpty(RowIdxCmptKeyOrArray) Then
        rowArr = lookupMeta(meta_rowNbrs): Set matchRows = New Collection
        For i = LBound(rowArr) To UBound(rowArr): matchRows.Add rowArr(i): Next
        Set SmartLookupRows = matchRows: Exit Function
    End If
    
    ' if numeric, simply return the row number; otherwise, construct the composite key
    If IsNumeric(RowIdxCmptKeyOrArray) Then SmartLookupRows = CLng(RowIdxCmptKeyOrArray): Exit Function
    keyStr = ConstructSmartLookupKey(RowIdxCmptKeyOrArray)

    ' Wildcard match
    If InStr(keyStr, "*") Or InStr(keyStr, "?") Then
        keyArr = lookupMeta(meta_KeyArr): rowArr = lookupMeta(meta_rowNbrs)
        Set matchRows = New Collection
        For i = LBound(keyArr) To UBound(keyArr)
            If keyArr(i) Like keyStr Then
                If FirstOnly Then SmartLookupRows = rowArr(i): Exit Function
                matchRows.Add rowArr(i)
            End If
        Next
        If FirstOnly Then SmartLookupRows = 0 Else Set SmartLookupRows = matchRows
        Exit Function
    End If

    ' If keyDict is not valid then gracefully exit (if FirstOnly then return 0; otherwise, an empty collection)
    On Error Resume Next
    Dim returnVal
    If FirstOnly Then
        SmartLookupRows = lookupMeta(meta_keyDict)(keyStr)(1)
        If Err.Number <> 0 Then Err.Clear: SmartLookupRows = 0
    Else
        Set SmartLookupRows = lookupMeta(meta_keyDict)(keyStr)
        If Err.Number <> 0 Then Err.Clear: Set SmartLookupRows = New Collection
    End If
    Exit Function

ErrorHandler: RaiseError Err.Number, fncNm
End Function

'---------------------------------------------------------------------------------------------------
' SmartLookupValues - Retrieves cached column value(s) for a given key or row number from SmartLookup.
' Params:  lookupMeta (Variant)   - Lookup metadata returned by SmartLookup.
'          RowIdxCmptKeyOrArray (Variant)    - Composite key (array/string) or row number.
'          CacheColNms (ParamArray) - Optional column name(s); returns all cached if omitted.
' Returns: Variant - Single value, dictionary, or Empty if key/row not found.
' Notes:   - Requires caching to be enabled during SmartLookup setup.
'          - Optimized for sub-second access across 100K+ rows using in-memory cache.
'          - A leading apostrophe (') in the RowIdxCmptKeyOrArray (denoting string, not row) is stripped before matching.
'---------------------------------------------------------------------------------------------------
Public Function SmartLookupValues(ByRef lookupMeta As Variant, RowIdxCmptKeyOrArray As Variant, _
                                  ParamArray CacheColNms() As Variant) As Variant
    ' Ensure cache is available
    Dim fncNm$: fncNm = "MXG_Sheet::SmartLookupValues": On Error GoTo ErrorHandler
    If IsEmpty(lookupMeta(meta_CachedCols)) Or lookupMeta(meta_CachedColNms) Is Nothing Then _
        RaiseError State_Parameter, fncNm, "Cached columns not found. Please enable caching in SmartLookup."

    ' Resolve row number from key or numeric
    Dim rowNum As Long, rowIndex As Long
    If IsNumeric(RowIdxCmptKeyOrArray) Then
        rowNum = CLng(RowIdxCmptKeyOrArray)
    Else
        rowNum = SmartLookupRows(lookupMeta, RowIdxCmptKeyOrArray, True)

        ' exit if no rows are found.
        If rowNum = 0 Then
            If UBound(CacheColNms) <> 0 Then
                Set SmartLookupValues = Nothing ' expected dictionary ? return Nothing
            Else
                SmartLookupValues = Empty       ' single value ? return Empty
            End If
            Exit Function
        End If
    End If
    rowIndex = rowNum - m_HdrRowNb: If rowIndex < 0 Then SmartLookupValues = Empty: Exit Function

    ' Resolve columns to return
    Dim isSingle As Boolean, colList As Variant, i As Long, colName As String
    Select Case True
        Case UBound(CacheColNms) < 0: colList = lookupMeta(meta_CachedColNms).keys
        Case UBound(CacheColNms) = 0: colList = Array(CacheColNms(0)): isSingle = True
        Case Else: colList = CacheColNms
    End Select

    ' Return result as single value or dictionary
    Dim colIndex As Long
    If isSingle Then
        colName = CStr(colList(0))
        colIndex = lookupMeta(meta_CachedColNms)(colName)
        If colIndex = 0 Then RaiseError State_Parameter, fncNm, _
            "Cached column not found: " & colName
        SmartLookupValues = lookupMeta(meta_CachedCols)(colIndex)(rowIndex, 1)
    Else
        Dim resultDict As Object: Set resultDict = CreateObject("Scripting.Dictionary")
        For i = 0 To UBound(colList)
            colName = CStr(colList(i))
            colIndex = lookupMeta(meta_CachedColNms)(colName)
            If colIndex = 0 Then RaiseError State_Parameter, fncNm, _
                "Cached column not found: " & colName
            resultDict(colName) = lookupMeta(meta_CachedCols)(colIndex)(rowIndex, 1)
        Next
        Set SmartLookupValues = resultDict
    End If
    Exit Function
ErrorHandler: RaiseError Err.Number, fncNm
End Function

' Constructs a normalized composite key string for SmartLookup based on input value or array.
Private Function ConstructSmartLookupKey(RowIdxCmptKeyOrArray As Variant) As Variant
    Dim keyStr As String, val As String, i As Long
    If SafeIsArray(RowIdxCmptKeyOrArray) Then
        For i = LBound(RowIdxCmptKeyOrArray) To UBound(RowIdxCmptKeyOrArray)
            val = CStr(RowIdxCmptKeyOrArray(i)): If Left$(val, 1) = "'" Then val = Mid$(val, 2)
            keyStr = keyStr & "|" & IIf(Len(val) = 0, Chr(0), val)
        Next i: ConstructSmartLookupKey = keyStr: Exit Function
    End If
    val = CStr(RowIdxCmptKeyOrArray): If Left$(val, 1) = "'" Then val = Mid$(val, 2)
    ConstructSmartLookupKey = IIf(Left$(val, 1) = "|", val, "|" & val)
End Function

' BuildCompositeKeyArr - Builds composite keys; optionally returns row numbers.
Private Function BuildCompositeKeyArr(colNames As Variant, Optional rowNbrArr As Variant) As Variant
    Dim fncNm$: fncNm = "MXG_Sheet::BuildCompositeKeyArr": On Error GoTo ErrorHandler
    Dim iCol As Long, r As Long, RowCount As Long, idx As Long
    Dim colNm As String, val As Variant, compositeKey As String
    Dim isFormatUsed As Boolean, colData() As Variant, fmtCols() As String, keyArr() As String
    Dim writeRowNbrs As Boolean: If Not IsArray(colNames) Then colNames = Array(colNames)

    ReDim colData(LBound(colNames) To UBound(colNames))
    ReDim fmtCols(LBound(colNames) To UBound(colNames))

    ' Load columns and extract optional formats
    For iCol = LBound(colNames) To UBound(colNames)
        colNm = CStr(colNames(iCol))
        idx = InStr(1, colNm, ":Format=", vbTextCompare)
        If idx > 0 Then fmtCols(iCol) = Mid(colNm, idx + 8): colNm = Left(colNm, idx - 1): isFormatUsed = True
        colData(iCol) = ColumnsX(colNm).Value
    Next
    
    If Not SafeIsArray(colData(0)) Then
        BuildCompositeKeyArr = Array()
        Exit Function
    End If

    RowCount = UBound(colData(0), 1)
    ReDim keyArr(1 To RowCount)
    writeRowNbrs = Not IsMissing(rowNbrArr) And IsArray(rowNbrArr)
    If writeRowNbrs Then ReDim rowNbrArr(1 To RowCount)

    ' Build composite keys
    For r = 1 To RowCount
        compositeKey = ""
        For iCol = LBound(colData) To UBound(colData)
            val = colData(iCol)(r, 1)
            If isFormatUsed And Len(fmtCols(iCol)) > 0 Then
                On Error Resume Next: val = Format(val, fmtCols(iCol))
                If Err.Number <> 0 Then val = Trim(CStr(val)): Err.Clear
                On Error GoTo ErrorHandler
            Else: val = Trim(CStr(val))
            End If
            compositeKey = compositeKey & "|" & IIf(Len(val) = 0, Chr(0), val)
        Next
        keyArr(r) = compositeKey
        If writeRowNbrs Then rowNbrArr(r) = r + m_HdrRowNb
    Next

    BuildCompositeKeyArr = keyArr
    Exit Function
ErrorHandler: RaiseError Err.Number, fncNm
End Function

'###################################################################################################
' SMARTROW - SmartRow delivers sub-second performance for efficient row-level retrieval and write-back,
'            supporting row numbers, search strings, ranges, and multi-column extraction in a dictionary.
'            SmartRowGet  - Stores an entire row's values in a dictionary for fast access.
'            SmartRowSet - Batch writes the dictionary to the row (unprotected columns required).
'###################################################################################################

'---------------------------------------------------------------------------------------------------
' SmartRowGet - Retrieves an entire row's values based on RowIdxOrSrchStr.
' Params:  RowIdxOrSrchStr (Variant)           - Row number, cell range, or search string.
'          ColIdxOrNm (Variant, Optional)  - Column name/index used for searching (if needed).
'          RaiseSearchError (Boolean)      - If True (default), raises error when no match is found.
' Returns: Variant - A Dictionary containing row data with column names as keys, or Nothing if no match.
' Notes:   - If RowIdxOrSrchStr is a Range, the search column is inferred from its position in the header row.
'          - Matching is case-insensitive. All columns are returned in the dictionary.
'---------------------------------------------------------------------------------------------------
' Example: mxgSh.SmartRowGet(25) ' Retrieve Row 25
'          mxgSh.SmartRowGet("Mary Wilson", "Employee Name") ' Retrieve row for Mary Wilson
'---------------------------------------------------------------------------------------------------
Public Function SmartRowGet(RowIdxOrSrchStr As Variant, _
                              Optional ColIdxOrNm As Variant = vbNullString, _
                              Optional RaiseSearchError As Boolean = True) As Object
    Dim fncNm$: fncNm = "MXG_Sheet::SmartRowGet": On Error GoTo ErrorHandler
    Dim rowNbr As Long, arr As Variant, dict As Object, arrIdx As Long
    Dim srchRng As Range, fndCell As Range, colNm, colIdx As Long

    ' Resolve the row number from criteria (Long, Range, or String) or exit if no rows found
    rowNbr = ResolveRowNumber(RowIdxOrSrchStr, ColIdxOrNm, RaiseSearchError, "SmartRowGet")
    If rowNbr = 0 Then Set SmartRowGet = Nothing: Exit Function

    ' Retrieve entire row as array
    arr = m_Worksheet.Range(m_Worksheet.Cells(rowNbr, 1), _
                            m_Worksheet.Cells(rowNbr, m_ColumnHdrDict.Count)).Value

    ' Build dictionary with column names and values
    Set dict = CreateObject("Scripting.Dictionary"): dict.CompareMode = vbTextCompare
    For arrIdx = 1 To m_ColumnHdrDict.Count
        dict(m_ColumnHdrDict.keys()(arrIdx - 1)) = arr(1, arrIdx)
    Next arrIdx
    
    dict("__Row") = rowNbr ' Store row number for round-trip writeback
    Set SmartRowGet = dict
    Exit Function

ErrorHandler:
    Set SmartRowGet = Nothing
    RaiseError Err.Number, fncNm, , RowIdxOrSrchStr, ColIdxOrNm
End Function

'---------------------------------------------------------------------------------------------------
' SmartRowSet - Writes the previously read row (via SmartRowGet), including any data updates
'                 (e.g., dict("ColumnName") = "NewValue"), back to the worksheet.
' Params:  RowDict (Object) - Dictionary containing column names as keys and corresponding values.
' Returns: None
' Notes:   - The dictionary must include the "__Row" key specifying the target row number.
'          - Writes the entire row in a single operation.
'---------------------------------------------------------------------------------------------------
' Example: Set dict = SmartRowGet(25)      ' Retrieve row as dictionary
'          dict("Revenue") = 500            ' Modify value
'          SmartRowSet dict               ' Write updated row back
'---------------------------------------------------------------------------------------------------
Public Sub SmartRowSet(rowDict As Object)
    Dim fncNm$: fncNm = "MXG_Sheet::SmartRowSet": On Error GoTo ErrorHandler
    Dim rowNbr As Long, colIdx As Long, colName As Variant, rng As Range
    Dim arr() As Variant: ReDim arr(1 To 1, 1 To m_ColumnHdrDict.Count)
    Dim val As Variant, formatString As String, formatType As String

    If Not rowDict.exists("__Row") Then _
        RaiseError State_Parameter, fncNm, "Missing '__Row' in dictionary."
    rowNbr = rowDict("__Row")

    ' Build 1xN array using header order (optimized)
    For colIdx = 1 To m_ColumnHdrDict.Count: arr(1, colIdx) = rowDict.Items()(colIdx - 1): Next colIdx

    ' Write entire row in a single call
    m_Worksheet.Range(m_Worksheet.Cells(rowNbr, 1), _
                      m_Worksheet.Cells(rowNbr, m_ColumnHdrDict.Count)).Value = arr
    Exit Sub

ErrorHandler: RaiseError Err.Number, fncNm, "Failed to set row.", rowNbr
End Sub

'###################################################################################################
' Helper Functions - Modular private functions used in this class
'###################################################################################################
' RemoveNonNumericChars - Strips all non-digit characters from a string and returns the numeric-only result.
Function RemoveNonNumericChars(ByVal val As String) As String
    Dim i As Long, c As String, output As String
    For i = 1 To Len(val)
        c = Mid$(val, i, 1)
        If c Like "#" Then output = output & c
    Next
    RemoveNonNumericChars = output
End Function

' IsInArray - Return True if a value exists in the array; otherwise, False.
Private Function IsInArray(SrchStr As Variant, arr As Variant, Optional idx As Long = -1) As Boolean
    On Error Resume Next: idx = -1: idx = Application.match(SrchStr, arr, 0): IsInArray = (idx > -1): On Error GoTo 0
End Function

' ResolveWorksheet - Resolves the specified worksheet by name, object, or table.
Private Function ResolveWorksheet(vTarget As Variant) As Worksheet
    Dim ws As Worksheet: Set ws = Nothing
    Dim fncNm$: fncNm = "MXG_Sheet::fncNm": On Error GoTo ErrorHandler
    Select Case TypeName(vTarget)
        Case "Worksheet": Set ws = vTarget ' Worksheet is already open
        Case "String": On Error Resume Next: Set ws = m_Workbook.Sheets(CStr(vTarget)): On Error GoTo ErrorHandler
    End Select
    Set ResolveWorksheet = ws
    Exit Function
ErrorHandler: RaiseError Err.Number, fncNm
End Function

' SafeReference - Converts a row or column reference (String, Long, or Range) into a safe string for RaiseError context.
Function SafeReference(val As Variant) As String
    Select Case TypeName(val)
        Case "Range":   SafeReference = "'" & val.Worksheet.Name & "'!" & val.Address(False, False)
        Case "String", "Long", "Integer": SafeReference = CStr(val)
        Case Else:      SafeReference = ""
    End Select
End Function

' SafeIsArray - Returns True if the variable is an array, suppressing errors.
Private Function SafeIsArray(arr As Variant) As Boolean
    On Error Resume Next: SafeIsArray = IsArray(arr): On Error GoTo 0
End Function

' SafeIsEmpty - Returns True if a variable is empty, an uninitialized array, or Nothing.
Private Function SafeIsEmpty(v As Variant) As Boolean
    On Error Resume Next: Select Case VarType(v)
        Case vbArray: SafeIsEmpty = (LBound(v) > UBound(v))
        Case vbObject: SafeIsEmpty = (v Is Nothing)
        Case Else: SafeIsEmpty = (IsEmpty(v) Or v = vbNullString)
    End Select: On Error GoTo 0
End Function

' SafeToArray - Ensures any input is returned as a 1D array.
Private Function SafeToArray(val As Variant) As Variant
    If IsEmpty(val) Then
        SafeToArray = Array()
    ElseIf SafeIsArray(val) Then
        SafeToArray = val
    Else
        SafeToArray = Array(val)
    End If
End Function

' InStrAny - Returns True if any character in the array exists within the input string.
Private Function InStrAny(str As String, chars As Variant) As Boolean
    Dim c As Variant
    For Each c In chars
        If InStr(str, c) > 0 Then InStrAny = True: Exit Function
    Next: InStrAny = False
End Function

' QuickSortDictionary - Sorts dictionary keys and values in-place (ascending or descending).
Private Function QuickSortDictionary(ByRef dict As Object, Optional SortOrder As XlSortOrder = xlAscending) As Object
    Dim fncNm$: fncNm = "MXG_Sheet::QuickSortDictionary": On Error GoTo ErrorHandler: If dict Is Nothing Then Exit Function
    Dim k, v, i&: k = dict.keys: v = dict.Items: QSortArrays k, SortOrder, v: dict.RemoveAll
    For i = LBound(k) To UBound(k): dict.Add k(i), v(i): Next: Set QuickSortDictionary = dict: Exit Function
ErrorHandler: RaiseError Err.Number, fncNm
End Function

' QSortArrays - Sorts an array with optional sync of up to two parallel arrays.
Private Sub QSortArrays(keyArr As Variant, _
                       Optional SortOrder As XlSortOrder = xlAscending, _
                       Optional syncArr1 As Variant = Empty, _
                       Optional syncArr2 As Variant = Empty, _
                       Optional first As Long = -1, Optional last As Long = -1)
    Dim fncNm$: fncNm = "MXG_Sheet::QSortArrays": On Error GoTo ErrorHandler
    If Not IsArray(keyArr) Then Exit Sub

    Dim i As Long, j As Long, pivot As String
    Dim tmpKey As String, tmpVal1 As Variant, tmpVal2 As Variant
    Dim hasSync1 As Boolean: hasSync1 = IsArray(syncArr1)
    Dim hasSync2 As Boolean: hasSync2 = IsArray(syncArr2)
    Dim isObj1 As Boolean, isObj2 As Boolean

    If hasSync1 Then isObj1 = IsObject(syncArr1(LBound(keyArr)))
    If hasSync2 Then isObj2 = IsObject(syncArr2(LBound(keyArr)))
    If first < 0 Then first = LBound(keyArr)
    If last < 0 Then last = UBound(keyArr)
    If first >= last Then Exit Sub

    ' --- Divide and QConquer: pick pivot point, initialize bounds, begin partition ---
    pivot = keyArr((first + last) \ 2)
    i = first: j = last

    Do While i <= j ' --- Partition around pivot ---
        If SortOrder = xlAscending Then
            Do While StrComp(keyArr(i), pivot, vbTextCompare) < 0: i = i + 1: Loop
            Do While StrComp(keyArr(j), pivot, vbTextCompare) > 0: j = j - 1: Loop
        Else
            Do While StrComp(keyArr(i), pivot, vbTextCompare) > 0: i = i + 1: Loop
            Do While StrComp(keyArr(j), pivot, vbTextCompare) < 0: j = j - 1: Loop
        End If

        If i <= j Then
            ' Swap key array
            tmpKey = keyArr(i): keyArr(i) = keyArr(j): keyArr(j) = tmpKey

            ' Sync optional arrays
            If hasSync1 Then If isObj1 Then Set tmpVal1 = syncArr1(i): Set syncArr1(i) = syncArr1(j): Set syncArr1(j) = tmpVal1 _
                            Else tmpVal1 = syncArr1(i): syncArr1(i) = syncArr1(j): syncArr1(j) = tmpVal1

            If hasSync2 Then If isObj2 Then Set tmpVal2 = syncArr2(i): Set syncArr2(i) = syncArr2(j): Set syncArr2(j) = tmpVal2 _
                            Else tmpVal2 = syncArr2(i): syncArr2(i) = syncArr2(j): syncArr2(j) = tmpVal2

            i = i + 1: j = j - 1
        End If
    Loop
    
    ' --- Recursive sort of each partition until only 1 or 0 elements remain ---
    If first < j Then QSortArrays keyArr, SortOrder, syncArr1, syncArr2, first, j
    If i < last Then QSortArrays keyArr, SortOrder, syncArr1, syncArr2, i, last
    Exit Sub
ErrorHandler: RaiseError Err.Number, fncNm
End Sub
