🔥 AWE_Sheet – The Excel Powerhouse

AWE_Sheet is a FREE VBA class module that delivers Excel automation with less code—faster, cleaner, and more reliable.

It’s the engine you wish Microsoft had built: blazing-fast lookups, wildcards, instant filters, and a normalized sheet architecture that just works.

Built with 30+ years of Excel and OOP expertise and GPT-4 AI collaboration, AWE_Sheet redefines what’s possible in Excel:

  • 🚀 Runs 60% faster than traditional VBA
  • 📊 Scales to Excel’s maximum size
  • 🛡️ Adapts to structural changes—column shifts, table/range swaps, filter states, and sheet protection—no rewrites, no breakage
  • 🔄 Read/write across multiple workbooks—local, SharePoint, Teams, or network—with simple, high-level APIs

🏆 For Every Experience Level

  • ✔ Beginner—Learn macros fast and become a power user overnight.
  • ✔ Pro—Solve business problems faster—without losing VBA’s native capabilities.

Whether you're building enterprise-grade automation or evolving a legacy workbook, AWE_Sheet is your low-code solution—code less, create more reliable automation.

Jump to: Quick Guide | Start Here | User Guide | Advanced Topics | FAQs | Feedback

ChatGPT Built with GPT-4 (AI) and supported by ChatGPT 24×7.

Need help? Stuck on a business logic problem?
Ask ChatGPT how to use AWE_Sheet—it already knows the answer.

Example: “Write code for a [new | mid-level | experienced] macro developer that uses AWE_Sheet to email project managers when their project budgets exceed 80%. Use ProjectID, Funded Amount, and Email from the Project sheet, and ProjectID and Revenue from the Timecard sheet. Make the code [easy to follow | fast-performing | as concise as possible].”

  • ✅ Full API knowledge
  • ✅ Pattern-based solution generation
  • ✅ Explains usage, errors, and best practices
  • ✅ Solves real-world business workflows using your data
  • ✅ ChatGPT rewrites messy legacy code—just cut and paste
  • ✅ Adapts responses based on your experience level—just say if you’re new, mid-level, or experienced
  • ✅ Adjusts output based on your goals—ask for readable, fast, or minimal code

Click each of the below sections to find out more:

👉️ How to Set Up AWE_Sheet in ChatGPT
  1. Start a project: Hover over Projects in the left panel, click the +, and name your project.
  2. Upload the correct files: Upload both the AWE_Sheet.txt (class module source code) and ChatGPT_AWE_Instructions.v.YYYY.MM.DD.X.docx files.
    ⚠️ Do not upload the .cls file—ChatGPT cannot read it.
  3. Paste the following text into the ChatGPT Project Chat: (exactly as shown)
Open the ChatGPT_AWE_Instructions.v.YYYY.MM.DD.X.docx file with the latest
version number (v.YYYY.MM.DD.X) and follow the
“Instructions for ChatGPT to Follow – Startup Protocol” section.

💡 Pro Tip: Want better results from ChatGPT? Read the section below titled “Pro Tips – How to Work with AWE_Sheet and ChatGPT” — it shows how to prevent drift and use ChatGPT like a code-generation assistant.

👉️ Pro Tips - How to work with AWE_Sheet and ChatGPT

🧠 Working with AWE_Sheet and ChatGPT – Pro Tip
By default, ChatGPT enters a behavior pattern known as the Lazy Protocol—a dangerously misleading combination of:

  • Guessing your intent instead of asking
  • Forgetting uploaded files unless told to re-read them
  • Losing context unless reminded of what it's learned
  • Inventing function names, even in predefined libraries like AWE_Sheet
  • Drifting from clear instructions without realizing it
  • Apologizing to appease you, without fixing the problem

This is not malicious—it’s baked into how ChatGPT is designed to help average users. But when you're working with advanced frameworks like AWE_Sheet, this behavior can corrupt logic, damage working code, and waste time.

🔒 How to Disable the Lazy Protocol
At the start of every session, paste this line into the chat:

Open the ChatGPT_AWE_Instructions.v.YYYY.MM.DD.X.docx file with the latest version number and 
follow the “Instructions for ChatGPT to Follow – Startup Protocol” section.

Note: Uploaded project files must include:
ChatGPT_AWE_Instructions.v.YYYY.MM.DD.X.docx and AWE_Sheet.txt

The above text activates strict mode, which forces ChatGPT to:

  • Obey the AWE_Sheet API contract exactly
  • Follow the Smart Function Decision Tree
  • Ask structured questions before writing code
  • Never generate macros unless you explicitly say: "Generate code"

🧠 Use ChatGPT Like a Developer-in-Training
“Treat me like a genius but junior developer—someone who’s overly eager to help but needs constant oversight. You are the lead. Guide me. Correct me.”

✅ Best Practices for Success

  • Keep backups of working code
  • Work in small, testable sections
  • Use structured requests (Pseudo-SQL or Smart Templates)
  • Challenge anything that seems off—ChatGPT will self-correct
  • Don’t try to fix damaged macros—restore your last known-good version
  • Use "LOCK THIS" to checkpoint working code and session knowledge
    (e.g. LOCK THIS – Save as BudgetAlert_v2_Final.txt)

Even with guardrails on, you must stay alert.
ChatGPT can and will drift, rewrite valid code, or omit critical logic without warning.
That’s why backup discipline is non-negotiable.

If a session starts to break down, first try reminding it to follow the uploaded instructions.
If that fails—don’t fight it.
Start a new chat in the same project, reload your last working file, and continue forward from that checkpoint.

This system—built on rules, verification, and iteration—is how you turn ChatGPT from a code generator into a powerful, reliable development assistant.
Treat it like a tool you train, not a mind-reader.
Together—you as the leader, ChatGPT your assistant, and AWE_Sheet your optimized worksheet library—let’s build your next “something amazing.”

👉 Standard Help Template – How to Ask For Help (Most Users)

Use this structured format to get accurate, production-ready AWE_Sheet code. Start simple, test, and refine your macro iteratively with ChatGPT.

📌 Request Template

  • Required:
    • Context: What are you trying to do and why?
    • Input: SheetName, HeaderRow – What to request, filter, or process
  • Optional:
    • Output: SheetName, HeaderRow – Where to write the result
    • Join Sheets: Sheet1:ColA = Sheet2:ColB
    • Behavior: e.g., skip blanks, sort by Date, remove duplicates
    • Sheet Format: tables, filters, protection, hidden rows
    • Special Instructions: ghost rows, missing headers, non-unique keys
    • Coding Style: beginner | mid | advanced; condensed | fast; low | med | high comments
📌 Standard Help Template Examples – Start Building Now
  • 📄 Copy SmartFiltered rows to a new sheet
    Context – I want to export only matching records
    Input – Timecard, 3 – Filter ProjectID = "Prj-171"
    Output – Sheet2, 1 – Paste header and filtered rows
  • 📧 Auto-Lookup employee details
    Context – I want to auto-fill project records using GAL info
    Input – Project, 1 – Use Email as lookup key
    Output – Fill Name, Title, Department, and Phone
  • 🧹 Clean data and validate timesheets
    Context – I want to detect blank dates, ghost rows, and overlaps
    Input – Timecard, 3 – Analyze and clean in-place
  • 📤 Send budget alerts
    Context – I want to calculate budgets exceeding 80% so that I can notify project managers
    Input – Project, 1 – Retrieve ProjectID, Funded Amount, and Email
    Input – Timecard, 3 – Summarize Revenue by ProjectID and calculate budget utilization
    Join Sheets – Project:ProjectID = Timecard:ProjectID
  • 📎 Send personalized reports
    Context – I want to email filtered PDF reports for each project
    Input – Project – Use Email and ProjectID to personalize message
    Output – Outlook – Generate filtered report and attach to email
  • ⚠️ Handle SmartFilter errors with targeted propagation
    Context – I want to trigger and capture known SmartFilter errors using CStateType
  • 🛑 Manually handle AWE_Sheet errors
    Context – I want to handle errors locally and continue running the macro
👉 Power User Help Template - Pseudo-SQL

Ideal for power users, analysts, or developers.
Describe the request using Pseudo SQL-style syntax—ChatGPT will translate it into a VBA macro that uses AWE_Sheet commands.

✅ Supported Keywords

  • Required
    • • SELECT – Choose columns to return or process (ColumnName, or * for all columns)
    • • FROM – Specify the source sheet (SheetName, optional HdrRow, defaults to 1)
          📌 Example: FROM Timecard (HdrRow = 3)
  • Optional
    • • WHERE – Define filter conditions (SheetName.ColumnName, For Wildcards, use '?|*')
    • • GROUP BY – Summarize rows by unique combinations (SheetName.ColumnName, etc.)
    • • HAVING – Apply post-aggregation filters (e.g., thresholds)
    • • JOIN – Merge data across sheets
    • • ORDER BY – Sort results
    • • INTO – Define where to write results (sheet, variable, email body, etc.)
  • Optional Modifiers
    • • CodeType – Condensed | Performance
    • • Comments – Low | Medium | High
    • • Special Instructions – Any unique conditions or context for code generation.
          📌 Examples:
              • "SheetName.ColumnName contains duplicate keys."
              • "Skip blank Revenue values."

📌 Examples

SELECT ProjectID, SUM(Revenue)
FROM Timecard (HdrRow = 3)
JOIN Budget ON Timecard.ProjectID = Budget.ProjectID
GROUP BY ProjectID
HAVING SUM(Revenue) >= 0.8 * Budget.FundedAmount
INTO AlertSheet

📤 Send Budget Alerts (Alt View)

SELECT ProjectID, Email, SUM(Revenue)
FROM Timecard (HdrRow = 3)
JOIN Project ON Timecard.ProjectID = Project.ProjectID
GROUP BY ProjectID, Email
HAVING SUM(Revenue) >= 0.8 * Project.FundedAmount
INTO EmailAlerts

📎 Send Personalized Reports

SELECT ProjectID, Email, Status, Budget
FROM Project (HdrRow = 1)
WHERE Status = "Active"
INTO OutlookEmails (PDF Report)

You can include wildcards (e.g., WHERE ProjectID LIKE "Prj-1*"), aggregates (SUM, COUNT), and joins. ChatGPT will translate into SmartLookup or SmartFilter logic.

📘 Need documentation for an advanced concept?
Just ask ChatGPT to generate it.
It already understands AWE_Sheet’s structure, syntax, and behavior.
Shorten your learning curve—focus on describing what you want to automate.

With AWE_Sheet and ChatGPT, you become a member of the world’s most powerful team.

Quick Guide – AWE_Sheet at a Glance

This guide covers essential functions and examples to get you automating faster. Learn how to filter, look up data, and more with simple, one-line VBA calls.

⚠️ Is Your Automation Crippled by Enterprise Chaos?

  • Shared workbooks on SharePoint, Teams, or network drives?
  • Filters locked on protected sheets—only editable during maintenance windows?
  • Unoptimized code that’s slow, fragile, and impossible to maintain?

💡 The Quick Guide Below Shows How AWE_Sheet Solves It:

  • Open multiple shared workbooks at once—Teams, SharePoint, network, no prompts
  • Filter protected sheets—even if in use by other users with filters set
  • Track columns by name, not position—immune to layout shifts
  • Read/write entire rows or specific cells—clean, repeatable logic every time
  • Reduce code size (and clutter) by up to 75%—with optimized, clean, and reliable VBA

Focus on solving your business problem—not Excel’s constraints or cryptic syntax.

Functionality Example
🚀 SmartFilter & 🔄 SmartFilterRows
Filters Protected Sheets—VBA Can’t
Locate and filter massive datasets instantly—60% FASTER than AutoFilter. Works on protected sheets, shared workbooks, and tables.
Returns matching row numbers instantly—NO LOOPS required to find rows. 
Public Sub SmartFilter_Example()
    Dim aweSh As New AWE_Sheet
    Dim rowNbr As Variant

    ' Open and initialize a sheet in one call.
    aweSh.Initialize "Timecard", 3

    ' ✅ AutoFilter syntax—but handles what AutoFilter can’t: protected sheets, and performs 60% faster
    aweSh.SmartFilter "ProjectID", "=Prj-171"
    aweSh.SmartFilter "Date", ">=1/1/2021", "<=1/31/2021", xlAnd

    ' ✅ Get filter counts without relying on SpecialCells constraints
    Debug.Print "Matching Row Count: " & aweSh.SmartFilterRows.Count

    ' ✅ Reliably process through Excel's maximum sheet size
    For Each rowNbr In aweSh.SmartFilterRows
        ' Retrieve cell by row and column name (see User Guide for full SmartCells full power)
        Debug.Print aweSh.SmartCells(rowNbr, "Employee Name").Value & ", ROW#:" & rowNbr
    Next rowNbr

    ' ✅ Clear reusable filters
    aweSh.SmartFilterClear
End Sub
💡 AWE_Sheet Advantage
✅ AutoFilter simplicity—but works on protected and shared sheets without breaking
✅ 60% faster than native filtering—even on Excel’s max sheet size
✅ Returns filtered row numbers instantly—no SpecialCells, no visible row dependencies
🔍 SmartLookup – Wildcard Composite Keys + Cached Retrieval
Create a composite key with cached columns in one call—optimized for sub-second performance.
Use wildcards to retrieve matching rows and cached values instantly, even on protected or shared sheets. Replace 80+ lines of fragile native VBA with just 20 lines of clean, enterprise-ready logic. 
Public Function SmartLookup_Example()
    On Error GoTo ErrHandler
    Dim aweSh As New AWE_Sheet, lookupMeta, rowNbr, prjID, revenue As Double

    ' ✅ One call to Initialize + detect the sheet layout (Header, Table, Range...)
    aweSh.Initialize "Timecard", 3
    If aweSh.IsSheetEmpty Then Exit Function

    ' ✅ One call to build composite keys and cache columns
    aweSh.SmartLookup lookupMeta, _
        Array("ProjectID", "Date:Format=yyyymmdd"), _
        Array("Revenue", "Hours", "Employee Name")

    ' ✅ Iterate by project, wildcard by date, retrieve values from cache
    For Each prjID In aweSh.GetUniqueColumnArray("ProjectID")
        For Each rowNbr In aweSh.SmartLookupRows(lookupMeta, Array(prjID, "202101*"))
            revenue = revenue + aweSh.SmartLookupValues(lookupMeta, rowNbr, "Revenue")
        Next rowNbr
        Debug.Print "Project: " & prjID & " | Revenue: " & revenue
        revenue = 0
    Next prjID

    Exit Function

' ✅ One call to handle the unexpected with meaningful text and call stack trace.
ErrHandler: Debug.Print "Desc: " & Err.Description & " | Source: " & Err.Source
End Function
💡 AWE_Sheet Advantage
✅ One call to create a composite key and cached columns—optimized for sub-second performance
✅ Reduced code (Only 20 lines)—strictly focusing on the business problem, revenue per project
✅ Runs on protected and shared sheets with zero breakage
✅ Outperforms 80–100 lines of traditional VBA—built for speed, built to last
🧠 See the User Guide to unlock SmartLookup’s full potential—including multi-value dictionary retrieval.
📊 Reliable WORKSHEET METADATA
ACCURATE METADATA across all structures—standard sheets, tables, filtered data, hidden, and non-congruent rows.
BUILD CODE YOU CAN TRUST. 
Public Sub SmartMetadata_Example()
    Dim aweSh As New AWE_Sheet

    ' ✅ One call to open the sheet and extract consistent structure—no guesswork, no row hunts
    aweSh.Initialize "Timecard", 3

    ' ✅ Get consistently reliable ranges—no trailing ghost rows, filters, or layout confusion
    Debug.Print "--- Sheet Metadata ---" & vbLf & _
        "        Header Row: " & aweSh.HeaderRowNumber & vbLf & _
        "          Last Row: " & aweSh.LastRowNumber & vbLf & _
        "       Last Column: " & aweSh.LastColumnNumber & vbLf & _
        "      Header Range: " & aweSh.HeaderRowRangeX.Address & vbLf & _
        "    DataBody Range: " & aweSh.DataBodyRangeX.Address & vbLf & _
        " Header & Databody: " & aweSh.RangeX.Address & vbLf & _
        "          Is Table: " & IIf(aweSh.IsTable, "Yes", "No") & vbLf & _
        "    Is Sheet Empty: " & IIf(aweSh.IsSheetEmpty, "Yes", "No") & vbLf & _
        "SmartFilter Active: " & IIf(aweSh.IsSmartFilterActive, "Yes", "No") & vbLf & _
        "         Row Count: " & aweSh.RowCount & vbLf & _
        "      Column Names: " & Join(aweSh.ColumnNames, ", ") & vbCrLf
End Sub
💡 AWE_Sheet Advantage
✅ Works with tables and standard ranges—ListObjects, dynamic layouts, filtered sheets
✅ Handles non-contiguous rows and dynamic structures—no gaps, no surprises
✅ Eliminates hardcoded references—metadata updates automatically as layout changes
🚀 Cross-Workbook Automation with Budget Sync at 100K Rows
Automate logic across SharePoint-hosted workbooks with clean, reliable code. Track budgets, calculate burn rate, and write updates—even when users are inside the workbook. This is enterprise-scale logic made simple. 
Public Sub CrossWorkbook_Example()
    On Error GoTo ErrorHandler
    Dim tStart As Double, budgetRow As Range, budgetDict As Object: tStart = Timer
    Dim shTimecard As New AWE_Sheet, shBudget As New AWE_Sheet, PrjIdCol As Range, RevenueCol As Range

    ' ✅ Seamlessly open unlimited workbooks—SharePoint, Teams, local, network, shared—no breakage.
    shTimecard.Initialize "Timecard", 3, "https://sharepoint.com/.../TimecardWB.xlsx"
    shBudget.Initialize "Budget", 1, "https://sharepoint.com/.../ProjectWB.xlsx"

    ' ✅ Column names only—never worry about position or layout again.
    Set PrjIdCol = shTimecard.ColumnsX("ProjectID")
    Set RevenueCol = shTimecard.ColumnsX("Revenue")

    ' ✅ Row-by-row control across sheets—no filters, no visibility hacks.
    For Each budgetRow In shBudget.DataBodyRangeX.Rows
        Set budgetDict = shBudget.SmartRowGet(budgetRow) ' One read call
		
	' ✅ Backward compatible with native VBA—Refactor existing code or patch it in where Excel Fails
        budgetDict("Revenue") = WorksheetFunction.SumIfs(RevenueCol, PrjIdCol, budgetDict("ProjectID"))

        ' ✅ Auto-calculate + writeback—no cells, no offset, no errors.
        If budgetDict("Revenue") > 0 Then
            budgetDict("Ratio") = _
                (budgetDict("Funded Amount") - budgetDict("Revenue")) / budgetDict("Funded Amount")
            shBudget.SmartRowSet budgetDict ' One write call
        End If
    Next budgetRow

    Debug.Print "Completed in " & Format(Timer - tStart, "0.000") & " seconds"
    Exit Sub

ErrorHandler:
    ' ✅ Handle unexpected errors—missing workbook or column—with clear messages & full stack tracing.
    MsgBox "Description: " & Err.Description & " Source: " & Err.Source
End Sub
💡 AWE_Sheet Advantage
✅ Only 25 lines (with comments and robust error handling)
✅ 0.5 seconds to process 100K rows
✅ Replaces 60–100 lines of fragile, unoptimized, hard-to-maintain VBA

Works Where Excel VBA Macros Fail.
The next move is yours—maintain the status quo or build something awesome.

Start Here - See What You've Been Missing

Setting up AWE_Sheet is quick and easy. Download, integrate, and start automating in just a few steps. Follow this guide to unlock its full power and streamline your workflow.

Download & Set Up AWE_Sheet📥

1️⃣ Download & Extract

  • Get the .zip file (class module + example workbook).
  • Extract it to access:
    • AWE_Sheet_Example.xlsm – Test AWE_Sheet’s features.
    • AWE_Sheet.cls – The class module for your projects.

2️⃣ Enable Macros & Explore

  • Open AWE_Sheet_Example.xlsm and enable macros.
  • Press Alt + F11 to open the VBA editor and step through the code.

🔗 Learn how to: Enable Macros | Import VBA class modules

Jump to: Quick Guide | Start Here | User Guide | Advanced Topics | FAQs | Feedback

 

Awe Sheet Bundle Zip
Archive – 4.6 MB

3️⃣ Integrate Into Your Project

  • Open your workbook → Press Alt + F11.
  • Go to File → Import File → Select AWE_Sheet.cls.
  • It will appear under Class Modules (press Ctrl + R if hidden).
  • Save your workbook. Use the Quick & User Guides to start automating.

 

User Guide

AWE_Sheet simplifies Excel VBA so you can meet your deadlines with faster, leaner, and more reliable code. This guide covers everything you need—function breakdowns, examples, and notes. Jump to any section, grab an example, and get back to coding.

Table of Contents by Category:

📌Initialization: Initialize | IsWorkbookOpen | MapColumnNumbers

📌Data Optimization: DeleteEmptyEndRows | EmptyEndRowDetection 

📌Metadata: DataBodyRangeX | HeaderRowNumber | HeaderRowRangeX | IsSheetEmpty | IsTable | LastColumnNumber | LastRowNumber | ParentTable | ParentWorkbook | ParentWorksheet | RangeX | RowCount

📌Columns: ColumnNames | ColumnsX | GetColumnNumber | GetUniqueColumnArray | IsInUniqueColumnArray

📌Smart-Search: SmartCells | IsSmartFilterActive | SmartFilter | SmartFilterClear | SmartFilterRows | SmartLookup | SmartLookupRows | SmartLookupUniqueKeys | SmartLookupValues  | SmartRowGet | SmartRowSet

📌Error Handling: CStateType | RaiseError

Jump to: Quick Guide | Start Here | User Guide | Advanced Topics | FAQs | Feedback


Every function in the AWE_Sheet class follows a Functional API Contract to ensure speed, flexibility, and consistent behavior across all supported environments. This includes predictable parameter handling, protected sheet compatibility, and structured error tracing for automation at scale.

📄 AWE_SHEET Functional API Contract (Click to view)

AWE_SHEET FUNCTIONAL API CONTRACT - Ensuring Consistent Behavior and Standard Interfaces

All functions:

  1. Perform at sub-second speed on 100K+ rows.
  2. Use familiar, AI-optimized and enterprise-proven functions that replace up to 30 lines of standard VBA.
  3. Support dynamic sheet structures (column shifts, table/range swaps, filter states, and sheet protection) without code breakage:
    • Re-initialize AWE_Sheet when columns move or when switching between range and table.
    • Works with or without AutoFilter or protection (unless writing to protected ranges).
    • Re-initialize cached functions (Smart Suite, GetUniqueColumnArray) after row changes.
  4. Work in shared workbook environments regardless of AutoFilter.
  5. Run on protected sheets unless writing to a protected range.
  6. Support standard ranges and ListObjects.
  7. Provide structured error handling with call stack tracing.
Multi-Type Parameter Name Components – Ensuring Consistent and Intuitive Naming

Used in parameters like ColIdxOrNm or RowIdxOrSrchStr:

  • Row – A Range, single row or cell (e.g., Range("5:5"))
  • Col – A Range, single column or cell (e.g., Range("C:C"))
  • Idx – A numeric index (row or column number)
  • Nm – A column header name (String)
  • Nms – A ParamArray or array of column names
  • SrchStr – A search string to find matching rows (wildcards supported)
  • CmptKey – A composite lookup key (String or Array)
  • Arr – An array of the above types, depending on the function’s input
  • Cache – Stores or retrieves values to and from memory cache
Multi-Type Column Parameter Examples – Supporting Flexible and Familiar Inputs
  • ColIdxOrNm – Column range, index, or name identifying a single column
  • ColIdxOrNms – Multiple columns by index, name, or range
  • CacheColNms – ParamArray of header names to cache
Multi-Type Row Parameter Examples – Supporting Row Index, Ranges, and Search Keys
  • RowIdxOrSrchStr – A row, row number, or search string that retrieves multiple rows
  • RowIdxCmptKeyOrArr – A row, row number, composite key (string or array), or array of key parts
Multi-Type Returns – Predictable Return Types Based on Input Scope
  • Single column – Returns a single value or Range
  • Multiple columns – Returns a dictionary (column name and value pair)
  • Single row – Returns a single row number
  • Multiple rows – Returns a Collection of row numbers
Functions Ending in "X" – Extending Native VBA with Sheet-Normalized Enhancements
  • Functions ending in X – Enhanced versions of native VBA counterparts.
  • Retain familiar behavior, but add support for normalized ranges, protection-aware execution, and additional logic.
  • Examples include RangeX, ColumnsX, and HeaderRowRangeX.
  • See the User Guide for specific enhancements in each function.

Note: This contract excludes DeleteEmptyEndRows, which removes ghost rows and requires calculation time plus unprotected ranges.

🚀 Initialization Functions

Initializing AWE_Sheet with Initialize is required for all AWE_Sheet functions. It activates the class by analyzing your sheet, mapping column headers, detecting list objects, and preparing the sheet for all other easy-to-use AWE_Sheet operations like DataBodyX, GetUniqueColumnArray, SmartCells, SmartFilter, and SmartLookup.

Initialize

Description Prepares the AWE_Sheet object for workbook and worksheet operations. Validates inputs, initializes metadata, and integrates tables (ListObjects) when present.
Parameters ✅ SheetNameOrObj (Variant) – Target sheet.
  • String - Sheet name.
  • Worksheet - A Worksheet object.

✅ HeaderRowNumber (Long, Optional) – Header row if no table is found. Default: 1.

✅ WorkbookFileNameOrObj (Variant, Optional) – Workbook reference.
  • String - Full file path of the workbook.
  • Workbook - A Workbook object.
  • Nothing - Defaults to ThisWorkbook.

✅ OpenReadOnly (Boolean, Optional) – If True, opens the workbook in read-only mode. Default: False.
Returns None
Examples 📌 Example 1: Initialize in ThisWorkbook
mySheet.Initialize "SheetName"
📌 Example 2: Initialize with Workbook File Path (Read-Only)
mySheet.Initialize "SheetName", , "C:\Path\To\Workbook.xlsx", True
📌 Example 3: Initialize with SharePoint URL
mySheet.Initialize "SheetName", 1, "https://sharepoint.com/sites/mysite/Documents/Workbook.xlsx"
Notes ✅ If a table (ListObject) exists, the first one is used by AWE_Sheet functions.

IsWorkbookOpen

Description Checks if a workbook is already open in Excel. Helps avoid duplicate openings by verifying if the file exists in the active workbooks collection. Supports safe reference return via ByRef.
Parameters ✅ FullFileName – Full file path of the workbook (e.g., "C:\Files\Workbook.xlsx")
✅ wb – (ByRef, Optional) Returns a reference to the open workbook if found; otherwise Nothing.
Returns Boolean – True if the workbook is open, False otherwise.
Examples 📌 Example: Check if a Workbook is Open
isOpen = aweSh.IsWorkbookOpen("C:\Files\Workbook.xlsx", wb)
Notes ✅ Case-insensitive comparison using workbook name only (not full path).
✅ Only checks workbooks in the current Excel instance.
✅ If workbook is not open, wb will be set to Nothing.

MapColumnNumbers

Description Maps column names to their respective column numbers from the header row. Validates metadata, ensures unique column names, and populates an internal dictionary for fast lookups.
Parameters None
Error Handling ✅ State_HeaderColumn (1007) – Raised if duplicate column names are detected.
Notes ✅ Clears existing mappings before creating new ones.
✅ Validates metadata and ensures unique column names.
✅ Raises an error for duplicate or empty column names.
✅ MapColumnNumbers can be called directly to refresh mappings when headers change.
✅ Initialize automatically invokes MapColumnNumbers but also refreshes all sheet metadata.
Examples 📌 Example: MapColumnNumbers - Update the internal column mapping dictionary
mySheet.MapColumnNumbers

🧹 Data Optimization

Data Optimization ensures your automation runs clean and fast by detecting and eliminating trailing “ghost” rows that inflate your UsedRange and slow down Excel. With EmptyEndRowDetection and DeleteEmptyEndRows, AWE_Sheet helps you reclaim control of your sheet boundaries—quietly fixing what Excel won’t.

DeleteEmptyEndRows

Description Detects and removes trailing empty rows ("ghost rows") from the worksheet to ensure UsedRange and DataBodyRangeX reflect actual data boundaries.
Returns ✅ Boolean – Returns True if ghost rows were successfully deleted or none existed.
❌ Returns False if ghost rows exist but were skipped by the user.
Parameters Optional PromptUser As Boolean = True – If True, the user will be prompted to confirm deletion when ghost rows are detected.
If the user selects Cancel, all processing halts via RaiseError.
Examples 📌 Example: Prompt user before deleting empty end rows
Debug.Print mySheet.DeleteEmptyEndRows

📌 Example: Run in silent mode (no user prompt)
Debug.Print mySheet.DeleteEmptyEndRows(False)
Notes ✅ When PromptUser is True, the prompt displays the exact range of ghost rows for review.
✅ If ghost rows are detected and the user clicks Cancel, the function raises a structured error and aborts processing.
✅ After calling this function, set EmptyEndRowDetection = False to prevent automatic deletion later.

EmptyEndRowDetection

Description Controls whether DeleteEmptyEndRows is automatically triggered to remove trailing empty rows. When enabled, DataBodyRangeX dynamically excludes empty end of sheet rows.
Get/Set ✅ Get: Returns the current setting for Empty End Row detection.
✅ Let: Enables (True) or disables (False) Empty End Row detection.
Examples 📌 Example: Check if Empty End Row Detection is Enabled
Debug.Print mySheet.EmptyEndRowDetection

📌 Example: Disable Empty End Row Detection
mySheet.EmptyEndRowDetection = False
Notes ✅ Set EmptyEndRowDetection to False after DeleteEmptyEndRows to improve performance.

📐 Metadata Functions

The Metadata Functions provide critical information about the worksheet structure—like header rows, table boundaries, and cell ranges—so your automation works reliably on any layout: tables or standard sheets, with or without AutoFilter. These functions normalize structural details and remove the need for manual row, column, or range detection.

DataBodyRangeX

Description Returns the entire data range of the worksheet below the header row. Supports both tables and standard ranges, including contiguous and non-contiguous ranges.

While DataBodyRangeX functions like its ListObject counterpart, DataBodyRange, it also works on standard sheets and ensures the last true data row is accurately retrieved.
Parameters ✅ RaiseSheetEmptyError (Boolean, Optional) – If True, raises an error if the sheet is empty. Default: False.
Returns Range – The data range of the worksheet, excluding the header row. Returns Nothing if no data rows are available.
Error Handling ✅ State_SheetData (1009) – Raised if no valid data rows are found and RaiseSheetEmptyError is set to True.
Notes ✅ Supports both ListObjects and standard worksheet ranges.
✅ Handles contiguous, non-contiguous, visible, and non-visible ranges.
✅ Calculates LastRowNumber dynamically to determine the extent of the data range.
✅ Removes empty trailing rows (ghost rows) to ensure accurate range detection.
✅ Does not handle filtered rows — Instead, use SmartFilter with SmartFilterRows to retrieve AWE_Sheet Filtered rows.
Examples 📌 Example: Print the Data Body Range Address
Debug.Print mySheet.DataBodyRangeX.Address

HeaderRowNumber

Description Returns the header row number for the initialized worksheet.
Parameters None
Returns Long – The row number of the header row for the initialized worksheet.
Examples 📌 Example: Print the Header Row Number
Debug.Print mySheet.HeaderRowNumber

HeaderRowRangeX

Description Returns the header row range for the initialized worksheet.
Parameters None
Returns Range – The range object representing the header row of the initialized worksheet.
Examples 📌 Example: Print the Header Row Range Address
Debug.Print mySheet.HeaderRowRangeX.Address

IsSheetEmpty

Description Returns True if the worksheet is empty; otherwise, returns False.
Parameters None
Returns Boolean – Returns True if the worksheet is empty, False otherwise.
Examples 📌 Example: Check if the Worksheet is Empty
Debug.Print mySheet.IsSheetEmpty

IsTable

Description Returns True if the sheet is based on a Table (ListObject); otherwise, returns False.
Parameters None
Returns Boolean – Returns True if the sheet is based on a Table (ListObject), False otherwise.
Examples 📌 Example: Print if there is a table on the worksheet
Debug.Print mySheet.IsTable
Notes ✅ Designed for use with properly initialized sheets as part of the AWE_Sheet workflow.
✅ Useful for conditional logic when specific table operations are required.

LastColumnNumber

Description Retrieves the last header column number in the worksheet. Returns 1 if the worksheet is empty.
Parameters None
Returns Long – The header column number. Returns 1 if the worksheet is empty.
Examples 📌 Example: Print the Last Populated Column Number
Debug.Print mySheet.LastColumnNumber
Notes ✅ Returns 1 if the worksheet is empty, including when the header row is blank.
✅ Call MapColumnNumbers before calling to dynamically adjust to column changes.

LastRowNumber

Description Retrieves the last valid row number in the worksheet. If no valid data rows exist, it returns the header row number.
Parameters None
Returns Long – The last valid row number in the worksheet. If no valid rows are found, it returns the header row number.
Examples 📌 Example: Print the Last Valid Row Number
Debug.Print mySheet.LastRowNumber
Notes ✅ Determines the last row by calling DataBodyRangeX.
✅ If no data is present, it defaults to returning the header row number.

ParentTable

Description Retrieves the worksheet's associated table (ListObject) that was identified by the Initialize Function. If a sheet contains multiple tables, the first ListObject found is used.
Parameters None
Returns ListObject – The first ListObject (table) on the worksheet. Returns Nothing if no table is found.
Examples 📌 Example: Print the Parent Table Name
Debug.Print mySheet.ParentTable.Name
Notes ✅ Retrieves the first table (ListObject) associated with the worksheet.
✅ Returns Nothing if the worksheet has no table.

ParentWorkbook

Description Retrieves the workbook object assigned during the Initialize Function.
Parameters None
Returns Workbook – The associated workbook object. Ensures the workbook is valid before returning the reference.
Error Handling ✅ Raises a State_Workbook error if the workbook is not valid.
Examples 📌 Example: Print the Parent Workbook Name
Debug.Print mySheet.ParentWorkbook.Name
Notes ✅ Use this property to safely access the workbook associated with the AWE_Sheet instance.

ParentWorksheet

Description Retrieves the worksheet object assigned during the Initialize Function.
Parameters None
Returns Worksheet – The associated worksheet object. Ensures the worksheet is valid before returning the reference.
Error Handling ✅ Raises AWE_StateType.State_Worksheet error if the worksheet is not properly initialized or is invalid.
Examples 📌 Example: Print the Parent Worksheet Name
Debug.Print mySheet.ParentWorksheet.Name
Notes ✅ Use this property to safely access the worksheet associated with the AWE_Sheet instance.

RangeX

Description Retrieves the combined range of the header row and the data body range, adjusting based on the worksheet's structure.

RangeX functions like ListObject.Range, returning a structured dataset that includes the header row and data body range. However, unlike ListObject.Range, RangeX also works with standard worksheet ranges, dynamically determining the dataset structure.
Parameters None
Returns Range – The combined range of the header row and data body range. If the worksheet is empty, only the header row range is returned, or A1 if no headers exist.
Examples 📌 Example: Print the Combined Header and Data Body Range
Debug.Print mySheet.RangeX.Address
Notes ✅ Combines the header row and data body range for convenient access to the entire dataset.
✅ Updates dynamically when worksheet data changes.

RowCount

Description Returns the number of rows in the worksheet's data body range. If no data rows exist, it returns 0.
Parameters None
Returns Long – The total number of rows in the data body range. Returns 0 if no rows are present.
Examples 📌 Example: Print the Total Row Count
Debug.Print mySheet.RowCount

📊 Column Functions

The Column Functions give you complete control over column names, positions, and values. Quickly retrieve column numbers, scan for unique values, or validate matches using intuitive, name-based lookups. Designed for clarity and precision—no more hunting for column indexes or writing helper formulas.

ColumnNames

Description Retrieves an array of column names from the header row.
Parameters None
Returns Variant – An array of column names from the header row.
Examples 📌 Example: Print All Column Names
Debug.Print Join(mySheet.ColumnNames, ", ")
Notes ✅ Column names are derived from the header row during or the Initialize or MapColumnNumbers functions.
✅ Useful for iterating over columns dynamically without hardcoding their names.

ColumnsX

Description Retrieves a combined range of specified columns (contiguous or non-contiguous) by name or index.

ColumnsX functions like its VBA counterpart, Columns, but with enhanced flexibility. While Columns references entire columns, ColumnsX retrieves only the data range of specified columns. ColumnsX allows referencing by name or index and supports retrieving multiple columns (contiguous or non-contiguous) as a single range in a single call.
Parameters ✅ ColIdxOrNms (ParamArray) – A list of column identifiers:
  • String - The column name (e.g., "Age")
  • Long - The column index (e.g., 3)
Returns Range – A combined range consisting of all specified columns.
Error Handling ✅ Raises AWE_StateType.State_SheetData error if the column range cannot be retrieved.
Examples 📌 Example: Print the Address of Selected Columns
Debug.Print mySheet.ColumnsX("Age", "Salary").Address
Notes ✅ Supports retrieving multiple columns dynamically, either by name or index.
✅ Returns a contiguous or non-contiguous range based on the specified columns.

GetColumnNumber

Description Resolves the column number from a column name, index, or range.
Parameters ✅ ColIdxOrNm (Variant) – The column identifier, which can be:
  • String - The column name (e.g., "Age").
  • Long - The column index (1-based, e.g., 3).
  • Range - A cell or range in the target column.
Returns Long – The resolved column number.
Error Handling ✅ Raises AWE_StateType.State_HeaderColumn error in the following cases:
  • If an unsupported column identifier type is provided.
  • If the specified column name does not exist in the header row.
Examples 📌 Example: Retrieve Column Number by Name, Index, or Range
Debug.Print mySheet.GetColumnNumber("Name")
Debug.Print mySheet.GetColumnNumber(3)
Debug.Print mySheet.GetColumnNumber(mySheet.ParentWorksheet.Cells(1, 3))
Notes ✅ Handles column identifiers dynamically, allowing flexibility in referencing columns by name, index, or range.
✅ Raises descriptive errors for unsupported types or missing columns to aid debugging.

GetUniqueColumnArray

Description Returns an array of unique, non-blank values from a single column. This function performs a fast in-memory scan and removes duplicates using case-insensitive comparison.
Parameters ✅ ColIdxOrNm (Variant) – The column to extract unique values from.
  • String – Column name (e.g., "Employee Name").
  • Long – Column index (1-based).
  • Range – A specific column range.
Returns ✅ Variant – A 1D array of unique, non-blank values.
Examples 📌 Example: Get Unique Project IDs
Dim values As Variant
values = mySheet.GetUniqueColumnArray("ProjectID")

📌 Example: Loop Through Unique Entries
Dim v
For Each v In mySheet.GetUniqueColumnArray(3)
    Debug.Print v
Next
Notes ✅ Blank or empty values are excluded.
✅ Comparisons are case-insensitive.
✅ Returns values in the order they are first encountered.

IsInUniqueColumnArray

Description Checks whether a given value exists in an array returned by GetUniqueColumnArray. Supports exact and wildcard lookups using * and ? for flexible matching.
Parameters ✅ SrchStr (Variant) – The value to search for. Supports wildcards.

✅ Arr (Variant) – The array returned from GetUniqueColumnArray.
Returns ✅ Boolean – True if the search value exists in the array; otherwise False.
Examples 📌 Example: Check for Exact Match
Dim exists As Boolean
exists = mySheet.IsInUniqueColumnArray("PRJ-101", mySheet.GetUniqueColumnArray("ProjectID"))

📌 Example: Use Wildcards in Search
exists = mySheet.IsInUniqueColumnArray("PRJ*", mySheet.GetUniqueColumnArray("ProjectID"))
Notes ✅ Supports wildcard characters: * (any characters) and ? (single character).
✅ Comparison is case-insensitive.
✅ Leading/trailing spaces in the search string are ignored.

🔍 Smart-Search Suite

Smart-Search Suite is the Excel automation engine you’ve always wanted—just not the one Excel shipped. It replaces rigid tools like FIND, INDEX+MATCH, VLOOKUP, and AutoFilter with faster, precision-targeted functions engineered for enterprise-scale automation.

No more waiting for users to close a shared workbook just to run automation. No more disabling protection just to filter rows. No more fragile formulas holding your logic together.

Smart Suite functions let you access rows, cells, and values from any workbook—even protected ones—using wildcards, composite keys, cached values, and structured filters. Whether you’re working with 1,000 rows or 1 million, Smart Suite delivers instant, reliable results you can trust.

All engineered for sub-second performance—even on Excel’s largest workbooks.


The following contract defines the consistent behavior guaranteed by all Smart Suite functions:

📄 Smart Suite Functional API Contract (Click to view)

SMART SUITE FUNCTIONAL API CONTRACT

This Functional API Contract defines consistent behavior across all Smart Suite functions. It supplements the AWE_Sheet design contract by focusing on input flexibility and output predictability for all high-performance operations like SmartFilter, SmartCells, SmartRowRead, and SmartLookup.

  1. Accept flexible, multi-type parameters:
    • Columns – Column index, name, or range
    • Rows – Row number, cell range, or search string
    • Others – As needed by the specific function
  2. Return values match the type of request:
    • Single value – Returns a single value
    • Multiple values – Returns a Collection or Dictionary (column name and value pair)
    • No match – Returns:
      • 0 for single values
      • Empty Collection for row lists
      • Nothing for dictionaries
  3. Support consistent, predictable behavior across all Smart Suite functions.
  4. Keys must match the formatting used in SmartLookup:
    • Supports exact match (e.g., "2025/01/01")
    • Supports wildcards (e.g., "*2025*")
    • Matching is case-insensitive
    • Wildcard matching uses VBA’s Like operator or Application.Match where optimized

The table in the section below compares the core Smart Suite functions side-by-side—highlighting their performance, return types, wildcard support, and real-world use cases. Use this as a quick guide to determine which function best fits your automation task.

📊 Smart Suite Function Comparison (Click to view)
Feature / Function SmartCells SmartFilter SmartLookup
Purpose Lightning-fast retrieval of one cell, a range, or a dictionary of ranges using a key, row number, or range—ideal for automation, formatting, or working directly with sheet data. High-performance filtering with exact, wildcard, or multi-column criteria—runs up to 50% faster than AutoFilter, works on protected sheets, and handles 1M+ rows with precision. High-performance lookups using composite keys across any column combination, with automatic sorting by key (or optional custom sort columns), and built-in caching for blazing-fast sub-second 100K+ row retrieval—even with wildcards.
Related Functions Shared utility for direct cell access SmartFilterRows (get row numbers), SmartFilterClear (clear filter), SmartFilterSort (sort filtered rows), SmartCells (cell access) SmartLookupRows (get row numbers), SmartLookupValues (return cached values), SmartCells (cell access)
Return Type Range or Dictionary (live worksheet cells) Returns True if rows were found and stored in memory, otherwise False Row numbers, values, or full dictionary (via cache)
Supports Wildcards ✅ Yes ✅ Yes ✅ Yes
Sorting Support ❌ No ✅ Yes ✅ Yes (based on key columns or SortCols)
100K+ Sub-Second Performance ⚡ Fast: For multi-cell row-level retrieval ⚡⚡ Faster: For precise row group search and retrieval ⚡⚡⚡ Fastest: Composite key lookup, row group search and retrieval, with optional value caching
Use Case Read/write cell ranges and values from a specific row Analyze and process subsets of data using filters Perform lookups and retrieve related values with lightning speed using composite keys
Replaces Excel Functions CELLS, RANGE, INDEX, FIND AUTOFILTER, ADVANCEDFILTER, FILTER, COUNTIFS FIND, VLOOKUP, INDEX + MATCH, XLOOKUP, FILTER
👉️ Click here to view the AWE_Sheet Function Decision Tree

🧠 AWE_Sheet Function Decision Tree

  • 🔍 Filter rows by criteria?
    • → Use SmartFilter + SmartFilterRows
  • 🔑 Lookup rows by key or wildcard?
    • → Use SmartLookup + SmartLookupRows
  • 📬 Retrieve values from a single row?
    • One value → SmartCells
    • Multiple values → SmartRowGet
  • 💾 Write data to a row?
    • → SmartRowSet
  • 📊 Get all unique values from a column?
    • → GetUniqueColumnArray

SmartCells

Description Retrieves a range object or dictionary based on row criteria and specified search/return columns. Supports searching by row number, range reference, or lookup value.

While SmartCells operates like its VBA counterpart, Cells, SmartCells expands functionality by allowing column name lookups, row value searches (lookups), and structured data extraction. SmartCells can return a single-cell range, multiple column values, or an entire row as a dictionary (without the need for additional variables), making SmartCells dynamic in working with spreadsheets and simplifying code.
Parameters ✅ RowIdxOrSrchStr (Variant) – Identifies the target row.
  • Long – Row number (e.g., 5).
  • Range – A cell or range in the row.
  • String – A lookup value (requires SrchColIdxOrNm).

✅ SrchColIdxOrNm (Variant) – (Required for lookups) The column to search in.
  • String – Column name (e.g., "Age").
  • Long – Column index (e.g., 3).
If omitted and RowIdxOrSrchStr is a Range, SmartCells will attempt to infer SrchColIdxOrNm from the header row position.

✅ RtrnColIdxOrNms (Variant, Optional) – Specifies which columns to return.
  • Single column name or index → Returns a Range.
  • Array of column names/indices → Returns a Dictionary.
  • "*" → Returns all row values as a Dictionary.

✅ RaiseSearchError (Boolean, Optional) – If True, raises an error if no match is found. Default: True.
Returns ✅ Range – If retrieving a single value.
✅ Range – If rtrnCol is empty...
✅ Dictionary – If retrieving multiple columns...
Error Handling ✅ Raises AWE_StateType.State_Search (1011) if no match is found and RaiseSearchError = True.
✅ Raises AWE_StateType.State_Parameter (1010) if rowCriteria is unsupported.
✅ Raises AWE_StateType.State_HeaderColumn (1007) if rtrnCol references a missing column.
Examples 📌 Example: Retrieve a Single Cell Value
Debug.Print mySheet.SmartCells(25, 1).Value   ' Retrieve Row 25, Col 1
Debug.Print mySheet.SmartCells("Mark Watson",  "Employee Name").Value   ' Find Mark Watson
Debug.Print mySheet.SmartCells("Mary Wilson",  "Employee Name",  "Email").Value   ' Lookup Email for Mary Wilson

📌 Example: Retrieve Multiple Columns as a Dictionary
Dim dict As Object
Set dict = mySheet.SmartCells("John", "Name", Array("Age", "City"))   ' Retrieve multiple columns as Dictionary
Debug.Print dict("Age").Value & ", " & dict("City").Value

📌 Example: Retrieve All Columns in a Row as a Dictionary
Set dict = mySheet.SmartCells("1001", "ID", "*")   ' Retrieve all columns as Dictionary
Debug.Print Join(dict.keys, ", ")
Notes ✅ Supports searching by row number, range reference, or lookup value.
✅ If RtrnColIdxOrNms is an array or "*", returns a dictionary of column-value pairs.
✅ Raises an error if no match is found and RaiseSearchError = True.
✅ If RtrnColIdxOrNms is omitted, SmartCells returns the cell at the resolved row and SrchColIdxOrNm position.
✅ If RtrnColIdxOrNms is an empty or invalid array, the function returns Nothing.

SmartFilter

⚡ Speed Test: SmartFilter vs AutoFilter (Click to view results)

✅ SmartFilter achieved sub-second speed across all tests with 100K rows, and outperformed AutoFilter by over 60% overall.

Test Case AutoFilter (ms) SmartFilter (ms) % Faster
Not Equal27711757.8%
Greater Than2777074.7%
Less Than4777883.6%
Greater Than or Equal3449871.5%
Less Than or Equal1766662.5%
Two-Criteria AND HOURS39814563.6%
Multi-Col G/L/Exact34820341.7%
Multi-Col OR36319546.3%
Multi-Col Numeric46119956.8%
Numeric & Date21516025.6%
Numeric & Text40614863.5%
Date & Text60924659.6%
Wildcard Asterisk28512157.5%
Wildcard Question16811730.4%
Wildcard Operators58226654.3%
Wildcard Mixed Types96519180.2%
Totals6351242061.9%

Benchmarks based on 100K-row test data in Excel VBA. Performance may vary slightly by data type and system specs.


Description Filters rows based on criteria such as text, numbers, or dates. SmartFilter functions like AutoFilter, its predecessor, but is 50% faster, supports protected sheets, AutoFiltered data, tables, and standard worksheets, and delivers accurate results on over 1 million rows. Designed for speed, precision, and scalability, SmartFilter seamlessly handles complex multi-criteria searches while ensuring high performance.

Though SmartFilter functions like AutoFilter, it operates differently—it does not filter out or hide rows. Instead, results are stored in memory until retrieved using SmartFilterRows. This design makes SmartFilter faster than AutoFilter and allows it to work on protected sheets.

Each SmartFilter call refines the previous results using AND logic between columns.
Parameters ✅ SrchColIdxOrNm (Variant) – The column to filter by.
  • String – Column name.
  • Long – Column index (1-based).
  • Range – A column or single cell.

✅ Criteria1 (Variant) – The primary filter condition.
  • String – Text or wildcard filtering.
  • Number – Numeric filtering.
  • Date – Date filtering.
  • Array – Matches any value in the array.

✅ Criteria2 (Variant, Optional) – A second filter condition for advanced filtering.
  • String – Text or wildcard filtering.
  • Number – Numeric filtering.
  • Date – Date filtering.

✅ CriteriaOperator (XlAutoFilterOperator, Optional) – Logical operator between Criteria1 and Criteria2.
  • xlAnd – Rows must match both criteria.
  • xlOr – Rows must match either criterion.

✅ RaiseSearchError (Boolean, Optional) – If True, raises an error if no matches are found. Default: True.
Examples 📌 Basic Filtering
mySheet.SmartFilter "ProjectID", "=Prj-171"   ' Exact match: Prj-171
mySheet.SmartFilter "Hours", ">5"   ' Greater than 5 hours
mySheet.SmartFilter "Rate", "<=110"   ' Less than or equal to 110

📌 Wildcard Filtering
mySheet.SmartFilter "ProjectID", "Prj*"   ' Matches any ProjectID starting with "Prj"
mySheet.SmartFilter "ProjectID", "Prj??10"   ' Matches "Prj" followed by two characters and "10"

📌 Array Filtering
mySheet.SmartFilter "Employee Name", Array("Name-0001", "Name-0002", "Name-0003")   ' Matches multiple names

📌 Multi-Column Filtering – Each SmartFilter call further narrows the filter results using an AND operator. criteria1 and criteria2 can be combined with xlAnd or xlOr within a single call. The example below shows how each SmartFilter call is joined using AND. Retrieve the combined filter results using SmartFilterRows().
mySheet.SmartFilter "Hours", ">=5", "<=10", xlAnd   ' Hours between 5 and 10
mySheet.SmartFilter "ProjectID", "=Prj-158", "=Prj-171", xlOr   ' ProjectID is 158 or 171
mySheet.SmartFilter "Hours", ">6", "<10", xlAnd   ' Hours greater than 6 but less than 10
mySheet.SmartFilter "Date", ">3/1/2021", "<3/31/2021", xlAnd   ' Filters dates in March 2021
Error Handling ✅ Raises AWE_StateType.State_Parameter (1010) in the following cases:
  • If SrchColIdxOrNm is empty or undefined.
  • If CriteriaOperator is not xlAnd or xlOr.
  • If Criteria1 or Criteria2 is Null.
  • If Criteria1 is an array and Criteria2 is not empty.
  • If Criteria1 or Criteria2 contains an invalid operator.

✅ Raises AWE_StateType.State_SheetData (1009) in the following cases:
  • If the search column (SrchColIdxOrNm) contains no data or only empty cells.
  • If no matching rows are found and RaiseSearchError = True.
Notes ✅ Use AutoFilter criteria syntax (>, <, >=, <=, =, <>) including wildcards (*, ?).
✅ Criteria1 and Criteria2 can be combined using AND (xlAnd) or OR (xlOr).
✅ If Criteria1 is an array, then all array criteria are combined using OR (xlOr).
✅ If Criteria1 is an array, then Criteria2 must be empty or a State_Parameter error is raised.
✅ Each SmartFilter call cumulatively refines the filter using AND.
✅ Retrieve results using SmartFilterRows().

SmartFilterClear

Description Clears all filters applied using SmartFilter, resetting stored filter results and allowing new filtering operations to start fresh.
Parameters None
Returns ✅ Boolean – Returns True if SmartFilter results were cleared, or False if there were no results to clear.
Examples 📌 Example: Clear SmartFilter Results
mySheet.SmartFilterClear
Notes ✅ Resets all stored row numbers from SmartFilter, ensuring a clean slate for new filters.
✅ Does not interact with Excel’s AutoFilter or visually remove native filters.
✅ Use before applying new SmartFilter criteria to prevent unintended filtering constraints.
✅ Returns True if filters were present and cleared, or False if no filters existed.

SmartFilterRows

Description Retrieves the row numbers stored by SmartFilter. If SmartFilter has not yet been called, returns all data rows from the worksheet. This function does not refilter data—it strictly returns the previously stored (or default) results for efficient row access.
Parameters ✅ ReturnRowRanges (Boolean, Optional) – Specifies whether to return row numbers or row ranges.
  • True - Returns a Collection of row ranges.
  • False - Returns a Collection of row numbers (default).
Returns ✅ Collection – A Collection of row numbers or row ranges based on ReturnRowRanges. Returns all data rows if no filters have been applied. Returns an empty Collection only if filtering was applied but no rows matched.
Examples 📌 Example: Retrieve Filtered Row Numbers
Dim filteredRows As Collection Set filteredRows = mySheet.SmartFilterRows

📌 Example: Retrieve Filtered Rows as Row Ranges
Dim rowRanges As Collection Set rowRanges = mySheet.SmartFilterRows(True)
Notes ✅ Retrieves row numbers or row ranges from stored SmartFilter results.
✅ Returns a Collection in all cases for easy iteration.
✅ Returns an empty Collection if no filtered rows exist.
✅ Works with SmartFilter and does not interact with Excel’s native AutoFilter.
✅ If SmartFilter has not been called, returns all data rows from the header down.

SmartLookup

Description Creates a high-performance lookup map using composite keys across any number of columns. Supports exact and wildcard matches, optional sorting, column formatting, and cached value retrieval.

Designed for large datasets, SmartLookup maintains sub-second performance even with 100K+ rows. Use SmartLookupRows for key-based lookups, SmartLookupValues to retrieve values from cached columns, or SmartCells to retrieve cell ranges directly from the worksheet.
Parameters ✅ lookupMeta (Variant, ByRef) – Stores metadata required for lookups. Used by SmartLookupRows, SmartLookupValues, and SmartCells.

✅ SrchColIdxNmOrArray (Variant) – One or more columns used to create the lookup key.
  • String – Column name (e.g., "ProjectID").
  • Long – Column index (e.g., 2).
  • Array – Use multiple values to form a composite key.
  • Supports :Format= for key formatting (e.g., "Date:Format=yyyymmdd").

✅ CacheColIdxNmOrArray (Variant, Optional) – Columns to cache for value fast retrieval.
Key columns from SrchColIdxNmOrArray are automatically included.
  • Single name/index → One column cached.
  • Array of names/indices → Multiple columns cached.

✅ SortColIdxNmOrArray (Variant, Optional) – Columns to sort the lookup keys.
  • If omitted, SrchColIdxNmOrArray is used by default.
  • Supports formatting just like SrchColIdxNmOrArray.

✅ SortOrder (XlSortOrder, Optional) – Direction to sort keys.
  • xlAscending (default)
  • xlDescending
Returns ✅ Boolean – True if lookup metadata was successfully created, otherwise False.
Examples 📌 Basic: Single Column Lookup
If sh.SmartLookup(lookupMeta, "Employee ID") Then
    ' Ready to use SmartLookupRows
End If

📌 Composite Key with Formatting
Builds a composite key using ProjectID and a formatted Date column. Helpful for grouping by month or year using formats like yyyymmdd.
If sh.SmartLookup(lookupMeta, Array("ProjectID", "Date:Format=yyyymmdd")) Then
    ' Key = ProjectID + formatted Date (yyyymmdd)
End If

📌 With Cached Columns
If sh.SmartLookup(lookupMeta, "Employee ID", Array("Employee Name", "Status")) Then
    ' Values can be retrieved using SmartLookupValues
End If

📌 Full Configuration
Creates a lookup with ProjectID as the search key, caches columns like Revenue and Employee Name for fast access, and sorts results by ProjectID, a formatted Date column, and Employee Name.
shTC.SmartLookup metaTC,
    Array("ProjectID"), _
    Array("Revenue", "Employee Name", "Date", "ProjectID"), _
    Array("ProjectID", "Date:Format=yyyymmdd", "Employee Name"), _
    xlAscending
Notes ✅ Key matching uses vbTextCompare (case-insensitive).
✅ Wildcard searches use VBA’s Like operator and are format-sensitive based on SrchColIdxNmOrArray.
✅ Multiple lookupMeta objects can be created for the same sheet, each with a unique key, sort, and cache configuration.
✅ Works in conjunction with SmartLookupRows, SmartLookupValues, and SmartCells.

SmartLookupRows

Description Retrieves row numbers matching a key from a lookup map created by SmartLookup. Supports both exact matches and wildcard patterns using * and ?, and works with simple or composite keys. Use this to identify all matching rows or return just the first, depending on your needs.
Parameters ✅ lookupMeta (Variant, ByRef) – Metadata generated by SmartLookup. Contains keys, row mappings, and optional cache/sort info.

✅ RowIdxCmptKeyOrArray (Variant) – A row index, composite key string, or array of key values to match.
  • String – For single-column or composite key string lookups.
  • Array – For composite keys (e.g., Array("PRJ-100", "20250101")).
  • Supports wildcards (*, ?) in any segment.
  • Use "" to match any value in that key position.

✅ FirstOnly (Boolean, Optional) – If True, returns only the first match. Default: False.
Returns ✅ Collection – When FirstOnly = False, returns a Collection of matching row numbers.
✅ Long – When FirstOnly = True, returns the first matching row number or Empty if not found.
Examples 📌 Exact Match
Retrieves all rows matching an exact composite key.
Dim results As Collection
Set results = mySheet.SmartLookupRows(lookupMeta, Array("PRJ-101", "20250101"))

📌 Wildcard Match
Returns all rows where ProjectID starts with "PRJ" and the date starts with "2025".
Set results = mySheet.SmartLookupRows(lookupMeta, Array("PRJ*", "2025*"))

📌 First Match Only
Returns only the first row number that matches the composite key.
Dim firstRow As Long
firstRow = mySheet.SmartLookupRows(lookupMeta, Array("PRJ-101", "20250101"), True)

📌 Iterate Over Matches
Loops through all matching row numbers using a For Each loop.
Dim rowNbr As Long
For Each rowNbr In mySheet.SmartLookupRows(lookupMeta, Array("PRJ-100", "20250101"))
    ' Process each matching row
Next rowNbr
Notes ✅ Search values passed in RowIdxCmptKeyOrArray must match the order and format used in SmartLookup's SrchColIdxNmOrArray parameter.
✅ Wildcards (*, ?) are supported in each key segment independently.
✅ Matching is case-insensitive using vbTextCompare and the VBA Like operator.
✅ Combine with SmartLookupValues to retrieve cached values, or SmartCells to retrieve cell ranges.

SmartLookupUniqueKeys

Description Returns a Collection of unique composite keys stored in SmartLookup metadata. Use this to iterate through all distinct keys, maintaining their sorted order from initialization.
Parameters ✅ lookupMeta (Variant, ByRef) – Metadata generated by SmartLookup. Contains the array of composite keys to process.
Returns ✅ Collection – A Collection of unique composite keys. ✅ If no keys exist, returns an empty Collection.
Examples 📌 Get All Unique Keys
Retrieves all unique composite keys into a Collection.
Dim keysColl As Collection
Set keysColl = mySheet.SmartLookupUniqueKeys(lookupMeta)

📌 Loop Through Unique Keys
Loops through each key in the collection.
Dim keyVal As Variant
For Each keyVal In keysColl
    Debug.Print keyVal
Next keyVal
Notes ✅ Duplicates are automatically filtered — only unique keys are added to the collection.
✅ Keys maintain the sort order established during SmartLookup initialization.
✅ If the lookupMeta is empty or invalid, returns an empty Collection without error.

SmartLookupValues

Description This function searches keys defined by SmartLookup and retrieves cached column values. Depending on the number of columns requested, it returns a single value or a dictionary of values. Designed for fast, repeated lookups after SmartLookup has been initialized with CacheCols.
Parameters ✅ lookupMeta (Variant, ByRef) – Metadata created by SmartLookup. Used to retrieve values from previously cached columns.

✅ RowIdxCmptKeyOrArray (Variant) – A row number, composite key string, or key array to retrieve values.
  • String – For single-column or formatted composite key strings.
  • Array – For multi-part composite keys (e.g., Array("PRJ-100", "20250101")).
  • Long – Directly specify a row number from SmartLookupRows.

✅ CacheColNms (Variant, ParamArray, Optional) – Column names to return.
  • If omitted → Returns all cached columns.
  • Single column → Returns a scalar value.
  • Multiple columns → Returns a dictionary (column → value).
  • All column names must match those specified during SmartLookup setup.
Returns ✅ Variant – Returns one of the following:
  • Single value – If one column is requested.
  • Dictionary – If multiple columns are requested or omitted.
  • Empty – If no match is found.
Examples 📌 Retrieve Single Value
Looks up the project name for a given ProjectID.
Dim projectName
projectName = mySheet.SmartLookupValues(lookupMeta, "PRJ-100", "Project Name")

📌 Retrieve Multiple Values
Gets a dictionary of all cached values for a composite key.
Dim values
Set values = mySheet.SmartLookupValues(lookupMeta, Array("PRJ-100", "20250101"))
Debug.Print values("Revenue"), values("Employee Name")

📌 Retrieve Selected Columns
Returns only the specified cached columns as a dictionary.
Set result = mySheet.SmartLookupValues(lookupMeta, Array("PRJ-100", "20250101"), Array("Employee Name", "Department"))

📌 Full Lookup Example
Defines a SmartLookup, retrieves matching rows using SmartLookupRows, and retrieves cached values using SmartLookupValues.
If mySheet.SmartLookup(lookupMeta, Array("ProjectID", "Date:Format=yyyymmdd"), _
    Array("Employee Name", "Revenue")) Then
  ' SmartLookupRows retrieves rows for Project PRJ-100 during January 2025 (formatted as yyyymmdd).
  ' Rows are returned in sort order based on SmartLookup's SrchCols (ProjectID + formatted Date),
  ' unless SortCols was specified during SmartLookup initialization.
  Dim rowNbr As Long, dict
  For Each rowNbr In mySheet.SmartLookupRows(lookupMeta, Array("PRJ-100", "202501*"))
    ' SmartLookupValues returns all cached columns because ColumnNames was not specified.
    Set dict = mySheet.SmartLookupValues(lookupMeta, rowNbr)
    Debug.Print dict("Employee Name"), dict("Revenue")
  Next rowNbr
End If
Notes ✅ Only columns listed in SrchColIdxNmOrArray or CacheColIdxNmOrArray during SmartLookup can be retrieved.
✅ Keys passed to RowIdxCmptKeyOrArray must match the order and formatting of SrchColIdxNmOrArray used in SmartLookup.
✅ You may also pass a row number directly from SmartLookupRows.
✅ Returns a single value, a dictionary, or Empty / Nothing depending on the match and requested columns.
✅ This function only works if caching was enabled during SmartLookup initialization.

SmartRowGet

Description Retrieves an entire row's values as a dictionary using row number, range, or search string.

SmartRowGet is the fastest way to extract a full row of data in one call. It automatically maps column names to values and works on protected sheets and normalized structures. Use it instead of SmartCells when your goal is to retrieve two or more cells in a row without specifying return columns.
Parameters ✅ RowIdxSrchKeyOrArray (Variant) – Identifies the target row.
  • Long – Row number (e.g., 5).
  • Range – A cell or range within the target row.
  • String – A lookup value (requires SearchCol).
  • Array – Composite key parts for advanced lookup.

✅ SearchCol (Variant, Optional) – Required if lookup is based on a string or array.
  • String – Column name to search in (e.g., "Employee ID").
  • Long – Column index.

✅ RaiseSearchError (Boolean, Optional) – If True, raises an error if no row is found. Default: True.
Returns ✅ Dictionary – Column name → cell reference for each column in the row.
Error Handling ✅ Raises AWE_StateType.State_Search (1011) if no match is found and RaiseSearchError = True.
✅ Raises AWE_StateType.State_Parameter (1010) if input is unsupported.
✅ Raises AWE_StateType.State_HeaderColumn (1007) if a column cannot be mapped.
Examples 📌 Example: Get Row 10 as a Dictionary
Dim rowDict As Object
Set rowDict = mySheet.SmartRowGet(10)
Debug.Print rowDict("ProjectID").Value & " | " & rowDict("Hours").Value

📌 Example: Get a Row by Lookup
Set rowDict = mySheet.SmartRowGet("Name-1003", "Employee Name")
Debug.Print rowDict("Email").Value
Notes ✅ Returns a dictionary of column names and cell references.
✅ Automatically includes all visible columns in the row.
✅ Use when you want the entire row without specifying columns.
✅ Wildcard lookup supported if composite key was built using SmartLookup.

SmartRowSet

Description Writes values to an entire row using a column-value dictionary and row reference.

SmartRowSet writes the entire row previously retrieved by SmartRowGet—including any changes—back to the sheet. It allows for fast, structured updates across any combination of columns. Works on protected sheets (if cells are not locked) and ensures data integrity by mapping each dictionary key to the correct column. Ideal for updating filtered rows or automating writebacks across large datasets.
Parameters ✅ RowIdxOrRange (Variant) – The row to update.
  • Long – Row number (e.g., 8).
  • Range – A cell or range within the target row.

✅ ColValueDict (Object) – Dictionary containing column names (or indexes) and new values.
  • Key – Column name (string) or column index (numeric).
  • Value – New value to assign to that column in the specified row.
Returns None. Writes values directly to the worksheet.
Error Handling ✅ Raises AWE_StateType.State_Parameter (1010) if inputs are invalid.
✅ Raises AWE_StateType.State_HeaderColumn (1007) if a column name is missing or invalid.
✅ Raises AWE_StateType.State_Protection (1008) if the target cell is locked or protected.
Examples 📌 Example: Retrieve and Update a Row
Dim dict As Object
Set dict = mySheet.SmartRowGet(25)
dict("Status") = "Approved"
mySheet.SmartRowSet dict

📌 Example: Lookup, Modify, and Write Back
Set dict = mySheet.SmartRowGet("Name-1003", "Employee Name")
dict("Hours") = 8.75
dict("Approved") = True
mySheet.SmartRowSet dict
Notes ✅ Supports both column names and numeric indexes as dictionary keys.
✅ Values are written directly to the worksheet.
✅ Works with filtered rows, shared workbooks, and protected sheets (if cell is unlocked).
✅ Only updates the columns specified in the dictionary.

⚠️ Error Handling Functions

The Error Handling Functions provide robust, structured exception support for your automation logic. Use RaiseError to throw clear, categorized errors with built-in call stack tracking—ensuring you know exactly where failures occur. Fully reusable in your own modules and classes, RaiseError helps enforce consistent error behavior across your entire automation framework.

CStateType

Description Retrieves the numeric value of an AWE_StateType enum, making it useful for debugging and validation. This function does not raise errors—it simply returns the corresponding numeric value.
Parameters ✅ EnumName (AWE_StateType) – The state enum to retrieve its numeric value.
Returns ✅ Long – The numeric value corresponding to the provided AWE_StateType enum. Below are enumerated values and definitions:

🔹 1000: State_Workbook - Workbook is invalid.
🔹 1001: State_ReadOnly - Workbook is read-only.
🔹 1002: State_Worksheet - Worksheet is invalid.
🔹 1003: State_HasData - Worksheet is missing data.
🔹 1005: State_ListObject - ListObject (Table) does not exist or is invalid.
🔹 1006: State_HeaderRow - Header row is invalid.
🔹 1007: State_HeaderColumn - Header column could not be found.
🔹 1008: State_Protection - Workbook or worksheet is protected.
🔹 1009: State_SheetData - Data on the sheet is invalid.
🔹 1010: State_Parameter - Parameter is invalid.
🔹 1011: State_Search - Search is invalid.
Examples 📌 Example: Return the enumerated (numeric) equivalent for State_Workbook, i.e. 1000
Debug.Print mySheet.CStateType(State_Workbook)
Notes ✅ Each AWE_StateType enum has a corresponding numeric value.
✅ Used primarily for debugging and error handling.
✅ If an error occurs and the queried state is unknown, it is considered invalid.

RaiseError

Description Triggers a structured VBA error with a custom error code, function name, and description. RaiseError is used for custom validation, process control, and structured error handling in VBA workflows.

Unlike native VBA error handling, this function includes a callstack and accumulated messages, making debugging easier by providing detailed execution context.
Parameters ✅ errorCode (Long) – Numeric error code identifying the issue.
✅ functionName (String) – The name of the function where the error occurred.
✅ description (String, Optional) – Optional error message providing additional context.
Returns None – This function does not return a value. It raises an error and halts execution unless handled.
Examples 📌 Example: Trigger a Custom Error
mySheet.RaiseError 2001, "ValidateInput", "A required field was left empty."

📌 Example: Use RaiseError in a Validation Check
If userInput = "" Then mySheet.RaiseError 3002, "ProcessData", "User input cannot be blank."
Common Use Cases ✅ Enforcing required fields and validating user inputs.
✅ Preventing invalid data types or unexpected values.
✅ Implementing custom error handling for business logic.
✅ Providing clear debugging messages when an issue occurs.
Notes ✅ RaiseError is not limited to AWE_Sheet—it is a general-purpose function for structured error handling.
✅ Errors raised must be handled using standard VBA error handling (e.g., On Error Resume Next).

Advanced Topics

The Advanced Topics section is designed for experienced users who want to explore the full potential of AWE_Sheet. Building on the foundational knowledge in the User Guide, these examples focus on advanced workflows for efficient data handling, automation, and integration with Excel-native features.

This section covers:

  • Handling large datasets with arrays for in-memory operations.
  • Dynamic column mapping and bulk processing for scalable macros.
  • Using AWE_Sheet with Excel-native features like conditional formatting and clipboard operations.
  • Implementing robust error-handling strategies for complex workflows.

How to Use This Section:

  1. Start with the User Guide for a strong understanding of AWE_Sheet’s core functionality.
  2. Explore the advanced examples for optimized solutions to real-world scenarios.
  3. Combine and adapt these examples to enhance your automation projects.

Jump to: Quick Guide | Start Here | User Guide | Advanced Topics | FAQs | Feedback

Functionality Example
Error Handling - Ensure stability and structured error management in VBA workflows.
Local Error Handling
Handles errors within the same function to prevent execution failures.

Note: Local error handling allows controlled execution without terminating macros but may lead to silent failures if not properly managed. 
Sub LocalErrorHandling_Example()
    Dim mySheet As New AWE_Sheet

    ' Enable local error handling
    On Error Resume Next

    ' Attempt to initialize with a missing sheet
    mySheet.Initialize "NonExistentSheet"
    
    ' Apply a SmartFilter on an uninitialized sheet
    mySheet.SmartFilter "Status", "Completed"

    ' Check for an error and handle it manually
    If Err.Number <> 0 Then
        Debug.Print "Local Error: " & mySheet.CStateType(Err.Number)
        Err.Clear
    End If

    On Error GoTo 0 ' Disable local error handling
End Sub
Propagated Error Handling
Errors are raised and handled centrally, preserving the call stack for debugging.

Note: This method is preferred for debugging and structured workflows where silent failures are unacceptable. 
Sub PropagatedErrorHandling_Example()
    On Error GoTo ErrorHandler
    Dim mySheet As New AWE_Sheet

    ' Initialize the sheet
    mySheet.Initialize "SheetWithData"

    ' Apply a SmartFilter that will trigger an error (State_Search, no matches found)
    mySheet.SmartFilter "Status", "Does Not Exist"

    Exit Sub

ErrorHandler:
    Debug.Print "Error encountered: " & Err.Description

    ' Handle only the most relevant errors
    Select Case mySheet.CStateType(Err.Number)
        Case State_Workbook
            Debug.Print "Error: Invalid workbook."
        Case State_Worksheet
            Debug.Print "Error: Invalid worksheet."
        Case State_Search
            Debug.Print "Error: No search results found."
    End Select

    Exit Sub
End Sub
Bulk Data Operations - Efficiently process large datasets.
Bulk Processing - Update Data with Arrays
Efficiently process and update large datasets in memory before writing back in a single operation.

Note: This example demonstrates a bulk update on a 10K+ row dataset, adjusting Rate and recalculating Revenue for a specific ProjectID.

⚠ Important: Avoid bulk updates on sheets with AutoFilter applied, as data will be incorrectly written back to the sheet. 
Sub BulkUpdate_Project171()
    Dim mySheet As New AWE_Sheet
    Dim arrData As Variant
    Dim projectCol As Long, rateCol As Long, revenueCol As Long, hoursCol As Long
    Dim i As Long, startTime As Double
    Const RATE_INCREASE As Double = 10 ' Flat increase in Rate

    ' Initialize the sheet
    mySheet.Initialize "Timecard", 3
    
    ' Start timer
    startTime = Timer

    ' Load entire sheet data into an array
    arrData = mySheet.DataBodyRangeX.Value

    ' Determine column indexes
    projectCol = mySheet.GetColumnNumber("ProjectID")
    rateCol = mySheet.GetColumnNumber("Rate")
    revenueCol = mySheet.GetColumnNumber("Revenue")
    hoursCol = mySheet.GetColumnNumber("Hours")

    ' Loop through rows and update Rate & Revenue where ProjectID = "Prj-171"
    For i = LBound(arrData, 1) To UBound(arrData, 1)
        If arrData(i, projectCol) = "Prj-171" Then
            arrData(i, rateCol) = arrData(i, rateCol) + RATE_INCREASE ' Increase Rate
            arrData(i, revenueCol) = arrData(i, rateCol) * arrData(i, hoursCol) ' Recalc Revenue
        End If
    Next i

    ' Write updated array back to the sheet in one operation
    mySheet.DataBodyRangeX.Value = arrData

    ' Output execution time
    Debug.Print "Bulk update completed in: " & Format(Timer - startTime, "0.000") & " seconds"
End Sub
GoTo: Start Here | Quick Reference | User Guide | FAQs | Testimonials
Advanced Features - Extend functionality with advanced operations and integration.
Clipboard-Friendly Data Export
Copy SmartFilter results and paste seamlessly.

This method leverages Union for efficient row selection. While reliable for most use cases, performance may degrade beyond 100K+ disjointed rows. Consider batch processing for extreme cases. 
Sub CopyFilteredRows_Simple()
    On Error GoTo ErrorHandler
    Dim aweSh As New AWE_Sheet, rngToCopy As Range, rng As Variant
    Dim destSheet As Worksheet

    ' Initialize and filter
    aweSh.Initialize "Timecard", 3
    aweSh.SmartFilter "ProjectID", "Prj-171"

    ' Build range with header + filtered rows
    Set rngToCopy = aweSh.HeaderRowRangeX
    For Each rng In aweSh.SmartFilterRows(True)
        Set rngToCopy = Union(rngToCopy, rng)
    Next rng

    ' Ensure destination sheet exists
    On Error Resume Next: Set destSheet = ThisWorkbook.Sheets("Sheet2")
    On Error GoTo ErrorHandler
    If destSheet Is Nothing Then 
        Set destSheet = ThisWorkbook.Sheets.Add
        destSheet.Name = "Sheet2"
    End If

    ' Copy and paste all rows ignoring autofilter
    rngToCopy.Copy
    destSheet.Range("A1").PasteSpecial xlPasteAll
    
    ' === This method ensures all data is copied, regardless of AutoFilter =================
    ' Dim area As Range, destCell As Range
    ' Set destCell = destSheet.Cells(destSheet.Rows.Count, 1).End(xlUp).Offset(1, 0)
    ' For Each area In rngToCopy.Areas
    '     area.Copy
    '     destCell.PasteSpecial xlPasteAll
    '     Set destCell = destSheet.Cells(destSheet.Rows.Count, 1).End(xlUp).Offset(1, 0)
    ' Next area
    ' =====================================================================================

    Application.CutCopyMode = False
    Exit Sub

ErrorHandler:
    Debug.Print "Error: " & Err.Description & " | " & Err.Source
End Sub
GoTo: Start Here | Quick Reference | User Guide | FAQs | Testimonials

FAQs

Frequently Asked Questions

1. What is AWE_Sheet?
AWE_Sheet is an advanced VBA-based tool designed to streamline Excel automation. It extends native VBA functionality with powerful, intuitive commands that simplify workflows, reduce code, and handle dynamic operations effortlessly.

2. Who is AWE_Sheet for?
AWE_Sheet is perfect for:

  • Project Managers: Streamline reporting and task tracking.
  • Business Analysts: Automate data analysis and insights.
  • Developers: Write less code while achieving more.
  • Anyone using Excel: Simplify everyday tasks and save time.

3. What makes AWE_Sheet different from VBA?
AWE_Sheet enhances native VBA with:

  • Dynamic Flexibility: Functions like CellsX and FilterX handle complex tasks with ease.
  • Error Handling: Built-in validations ensure reliable code execution.
  • Reduced Complexity and Code: Simplifies repetitive tasks and lets you focus on your solution.

4. Can AWE_Sheet handle large datasets?
Yes! AWE_Sheet is optimized for performance and can efficiently handle thousands of rows, ensuring reliability even with complex operations.

5. What are the “X” functions in AWE_Sheet?
Functions like CellsX, RangeX, and FilterX are extended versions of native VBA functions. They support similar signatures but add powerful features like dynamic handling and robust error validation.

6. Is AWE_Sheet beginner-friendly?
Absolutely! While advanced users will appreciate its power, AWE_Sheet’s intuitive syntax and documentation make it accessible to VBA beginners.

7. Does AWE_Sheet work with protected worksheets?
AWE_Sheet includes robust error messages to notify you if a protected worksheet blocks an operation. Native Excel filtering does not work on protected worksheets, and since AWE_Sheet’s filtering functionality is built on top of Excel’s native filtering, it is also blocked. However, you can use FindRowNumbers as a powerful and fast alternative that works seamlessly, even on protected worksheets.

8. Can AWE_Sheet integrate with SharePoint files?
Yes! AWE_Sheet seamlessly integrates with SharePoint files, opening them in application mode. Use the Initialize method’s OpenReadOnly parameter to specify whether to open files in read-only or writable mode. Note: Remove ":x:/r/" from the SharePoint file path to open the workbook in writable mode.

9. What if I encounter errors while using AWE_Sheet?
AWE_Sheet includes built-in error codes and messages to guide troubleshooting. The User Guide also provides detailed explanations and examples for resolving issues.

10. How do I get started with AWE_Sheet?
Download the .cls file (see How to Import a VBA Class Module) and, if needed, download the example workbook. Then, start with the Quick Reference Guide for an overview of core functions and explore the User Guide for detailed explanations, examples, and best practices.

11. How do I achieve fast performance with AWE_Sheet?

For sub-second performance in filtering and searching, use FilterX and FindRowNumbers. FindRowNumbers works with or without filters and can further refine searches within filtered rows without needing to reset filters. Use CellsX for one-off searches or to retrieve row values after FilterX and FindRowNumbers searches. To future-proof your workflow and minimize costly overhead, avoid looping through rows without FilterX or FindRowNumbers. This workflow ensures efficient operations, even on large worksheets.

12. Can I request new features?
Yes! We value your feedback and encourage you to share feature suggestions or improvement ideas. Use the contact form on our website or leave a comment below to let us know how we can make AWE_Sheet even better.

Feedback and Testimonials

Project Manager

"As a project manager juggling multiple workbooks daily, AWE_Sheet has been a game-changer. The ability to filter, retrieve, and update data with a few intuitive commands has saved me countless hours. I no longer have to worry about complex syntax—AWE_Sheet handles it all effortlessly!"
– Sarah T., Project Manager

Business Analyst

"AWE_Sheet transformed the way I analyze data in Excel. Dynamic column handling and robust error-checking have made my workflows faster and more reliable. I love how easy it is to integrate with both standard sheets and tables—it’s the ultimate tool for any analyst!"
– Michael L., Business Analyst

Macro Developer

"As someone who’s worked with VBA for years, AWE_Sheet feels like a breath of fresh air. Its extended functionality eliminates the need for repetitive code and makes even the most complex operations simple and intuitive. It’s a must-have for any developer working with Excel!"
– David R., VBA Developer

Finance Professional

"Managing large datasets and maintaining dashboards has always been a challenge, but AWE_Sheet makes it seamless. Its ability to easily update tables ensures my dashboards stay accurate and up-to-date. The error-handling capabilities alone give me peace of mind, knowing my reports are reliable every time. Highly recommend this tool for finance teams!"
– Jennifer M., Financial Analyst

Educator

"Teaching Excel automation has become so much easier with AWE_Sheet. Students grasp the concepts quickly because the functions are intuitive and eliminate unnecessary complexity. It’s a powerful tool that bridges the gap between beginners and professionals."
– Dr. Emily C., Data Science Instructor

Add comment

Comments

Sarah Taylor
4 months ago

AWE_Sheet has been a lifesaver for managing my project reports. Can you add functionality to export filtered data directly into a new workbook?