Initialization Function(s)

Initialize

Description

Prepares the AWE_Sheet object for workbook and worksheet operations. Validates inputs, initializes metadata for column mapping, and supports various targets (worksheets, sheet names, or ListObjects).

Parameters

Returns

Error Handling

Notes

• Automatically maps columns if the worksheet has a valid header row.
• Supports handling empty worksheets and deferred workbook resolution.
• Works seamlessly with ListObjects for advanced table operations.
• If a workbook with the same name is already open, that workbook will be used instead of reopening it.
• Remove ":x:/r/" from SharePoint file paths to avoid opening the workbook in read-only mode.

Usage Examples

Sub Initialize_Examples()
    On Error GoTo ErrorHandler
    Dim mySheet As New AWE_Sheet

    ' Example 1: Initialize with a sheet name
    mySheet.Initialize "Sheet1"
    Debug.Print "Example 1: Initialization with sheet name successful!"

    ' Example 2: Initialize with a worksheet object
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("DataSheet")
    mySheet.Initialize ws
    Debug.Print "Example 2: Initialization with worksheet object successful!"

    ' Example 3: Initialize with a ListObject (table)
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Sheets("DataSheet").ListObjects(1)
    mySheet.Initialize tbl
    Debug.Print "Example 3: Initialization with ListObject successful!"

    ' Example 4: Initialize with a custom header row
    mySheet.Initialize "DataSheet", 3
    Debug.Print "Example 4: Initialization with custom header row successful!"

    ' Example 5: Initialize with a workbook object
    Dim wb As Workbook
    Set wb = Workbooks("Workbook.xlsx")
    mySheet.Initialize "Sheet1", 1, wb
    Debug.Print "Example 5: Initialization with workbook object successful!"

    ' Example 6: Initialize in read-only mode with an external workbook
    mySheet.Initialize "Sheet1", 1, "C:\Files\Workbook.xlsx", True
    Debug.Print "Example 6: Initialization in read-only mode successful!"

    ' Example 7: Initialize with an external workbook (file path)
    mySheet.Initialize "DataSheet", 1, "C:\Files\Workbook.xlsx"
    Debug.Print "Example 7: Initialization with external workbook successful!"

    ' Example 8: Initialize with a SharePoint workbook
    mySheet.Initialize "Data", 1, "https://sharepoint.com/sites/mysite/Documents/Workbook.xlsx"
    Debug.Print "Example 8: Initialization with SharePoint workbook successful!"

    ' Exit before error handler
    Exit Sub

ErrorHandler:
    Debug.Print "Error during initialization: " & Err.Description
    Err.Clear
End Sub
    

Column and Range Functions

ColumnNbr

Description

Retrieves the column number for a specified column name.

Parameters

Returns

Notes

• Raises an error if the specified column name is not found in the header mapping.

Usage Examples

Sub ColumnNbr_Example()
    Dim mySheet As New AWE_Sheet
    ' Initialize with a worksheet
    mySheet.Initialize "Sheet1"
    ' Retrieve the column number for "Name"
    Debug.Print "Column number for 'Name': " & mySheet.ColumnNbr("Name")
End Sub

ColumnRng

Description

Combines one or more specified columns into a single range.

Parameters

Returns

Notes

• Raises an error if any of the column names are not found in the header mapping.

Usage Examples

Sub ColumnRng_Example()
    Dim mySheet As New AWE_Sheet
    ' Initialize with a worksheet
    mySheet.Initialize "Sheet1"
    ' Combine ranges for "Name" and "Age"
    Dim combinedRange As Range
    Set combinedRange = mySheet.ColumnRng("Name", "Age")
    Debug.Print combinedRange.Address
End Sub

DataBodyRange

Description

Retrieves the data range of the worksheet, excluding the header row.

Parameters

This function does not take any parameters.

Returns

Notes

• Returns the ListObject DataBodyRange if the worksheet contains a valid table.
• Dynamically constructs a range based on the header row, last row, and last column if no ListObject is found.

Usage Examples

Sub DataBodyRange_Example()
    Dim mySheet As New AWE_Sheet
    ' Initialize with a worksheet
    mySheet.Initialize "Sheet1"
    ' Retrieve the data body range
    Debug.Print mySheet.DataBodyRange.Address
End Sub

HeaderRowNbr

Description

Retrieves the header row number for the initialized worksheet.

Parameters

This function does not take any parameters.

Returns

Usage Examples

Sub HeaderRowNbr_Example()
    Dim mySheet As New AWE_Sheet
    ' Initialize with a worksheet
    mySheet.Initialize "Sheet1"
    ' Get the header row number
    Debug.Print mySheet.HeaderRowNbr
End Sub

HeaderRowRng

Description

Retrieves the range of the header row for the initialized worksheet.

Parameters

This function does not take any parameters.

Returns

Usage Examples

Sub HeaderRowRng_Example()
    Dim mySheet As New AWE_Sheet
    ' Initialize with a worksheet
    mySheet.Initialize "Sheet1"
    ' Get the header row range
    Debug.Print mySheet.HeaderRowRng.Address
End Sub

LastColumnNbr

Description

Retrieves the last populated column in the worksheet.

Parameters

This function does not take any parameters.

Returns

Usage Examples

Sub LastColumnNbr_Example()
    Dim mySheet As New AWE_Sheet
    ' Initialize with a worksheet
    mySheet.Initialize "Sheet1"
    ' Get the last column number
    Debug.Print mySheet.LastColumnNbr
End Sub

LastRowNbr

Description

Retrieves the last populated row in the worksheet, considering data and header rows.

Parameters

This function does not take any parameters.

Returns

Usage Examples

Sub LastRowNbr_Example()
    Dim mySheet As New AWE_Sheet
    ' Initialize with a worksheet
    mySheet.Initialize "Sheet1"
    ' Get the last row number
    Debug.Print mySheet.mySheet.LastRowNbr
End Sub

Search and Sort Functions

FilterSheetBy

Description

Filters the worksheet by a specified column and value, hiding rows that do not match the criteria.

Parameters

Returns

Error Handling

Usage Examples

Sub FilterSheetBy_Example()
    Dim mySheet As New AWE_Sheet
    mySheet.Initialize "Sheet1"
    mySheet.FilterSheetBy "Status", "Completed"
End Sub

GetCellRng

Description

Retrieves the cell range (as a Range object) based on a row number and column header name.

Parameters

Returns

Error Handling

Usage Examples

Sub GetCellRng_Example()
    Dim mySheet As New AWE_Sheet
    mySheet.Initialize "Sheet1"
    ' Retrieve the cell in row 2, column "Status"
    Dim cell As Range
    Set cell = mySheet.GetCellRng(2, "Status")
    Debug.Print "Cell Value: " & cell.Value
End Sub

LookupColumnNbr

Description

Retrieves the column number associated with a specific header name. This function is case-insensitive and searches for an exact match.

Parameters

Returns

Error Handling

Usage Examples

Sub LookupColumnNbr_Example()
    Dim mySheet As New AWE_Sheet
    mySheet.Initialize "Sheet1"
    ' Retrieve the column number for the "Status" header
    Dim colNum As Long
    colNum = mySheet.LookupColumnNbr("Status")
    Debug.Print "Column Number: " & colNum
End Sub

LookupRowNbrs

Description

Finds all row numbers where a specified column matches a given value.

Parameters

Returns

Error Handling

Usage Examples

Sub LookupRowNbrs_Example()
    Dim mySheet As New AWE_Sheet
    mySheet.Initialize "Sheet1"
    ' Find all rows where "Status" is "Completed"
    Dim rowNumbers As Collection
    Set rowNumbers = mySheet.LookupRowNbrs("Status", "Completed")
    ' Iterate through found rows
    Dim row As Variant
    For Each row In rowNumbers
        Debug.Print "Found row: " & row
    Next row
End Sub

Sub SortSheet_Example()
    Dim mySheet As New AWE_Sheet
    mySheet.Initialize "Sheet1"
    mySheet.SortSheet Array("Status", "Priority"), Array("asc", "desc")
End Sub

Sub ErrorHandling_Examples()
    On Error GoTo ErrorHandler
    Dim rng As Range

    ' Example 1: Initialize AWE_Sheet with a worksheet
    Dim mySheet As New AWE_Sheet
    mySheet.Initialize "SheetWithData"

    ' Example 2: Apply a filter and process rows where "Name" is "John"
    mySheet.ClearSheetFilters
    mySheet.FilterSheetBy "Name", "John"
    For Each rng In mySheet.DataBodyRange.SpecialCells(xlCellTypeVisible)
        Debug.Print mySheet.GetCellRng(rng, "Name").Value
        Debug.Print mySheet.GetCellRng(rng, "Age").Value
    Next rng
    mySheet.ClearSheetFilters

    ' Example 3: Retrieve and print data using lookups where "Name" is "John"
    Dim iRow As Variant
    For Each iRow In mySheet.LookupRowNbrs("Name", "John")
        Debug.Print mySheet.GetCellRng(iRow, "Name").Value
        Debug.Print mySheet.GetCellRng(iRow, "Age").Value
    Next iRow

    ' Example 4: Initialize AWE_Sheet with a ListObject. Apply filter and process rows where "Name" is "John" (table)
    Dim myTableSheet As New AWE_Sheet
    myTableSheet.Initialize ThisWorkbook.Worksheets("SheetWithTable").ListObjects(1)
    myTableSheet.FilterSheetBy "Name", "John"
    For Each rng In myTableSheet.DataBodyRange.SpecialCells(xlCellTypeVisible)
        Debug.Print myTableSheet.GetCellRng(rng, "Name").Value
        Debug.Print myTableSheet.GetCellRng(rng, "Age").Value
    Next rng
    myTableSheet.ClearSheetFilters

    Exit Sub
	
' ErrorHandler demonstrates two strategies for managing errors:
' 1. Propagate errors to the parent function for debugging using Err.Raise.
' 2. Handle errors locally within this function using Select Case and AWE_SheetError constants.


ErrorHandler:

    ' Strategy 1: Propagate the error to the parent function
    ' Uncomment the next line to raise the error and stop execution here
    ' Err.Raise Err.Number, "ErrorHandling_Examples -> " & Err.Source, Err.Description

    ' Strategy 2: Handle errors within this function
    Select Case Err.Number
        ' Initialization Errors
        Case AWE_SheetError.WorkbookNotFound
            Debug.Print "Error during initialization: Workbook not found. Check the file path or ensure it is open."
        Case AWE_SheetError.InvalidWorksheet
            Debug.Print "Error during initialization: Worksheet is missing or invalid. Verify the sheet name."
        Case AWE_SheetError.HeaderRowOutOfRange
            Debug.Print "Error during initialization: The specified header row is out of range. Check the input row number."
        Case AWE_SheetError.SheetEmpty
            Debug.Print Warning: "The worksheet is empty or contains no usable data. Continuing operations."
            Resume Next
        Case AWE_SheetError.InvalidTable
            Debug.Print "Error during table initialization: ListObject (table) is invalid or not found. Ensure the table exists."

        ' Lookup and Column Errors
        Case AWE_SheetError.ColumnNotFound
            Debug.Print "Error: Column not found. Verify the column name in the header row."
        Case AWE_SheetError.NoMatch
            Debug.Print "Error: No matching rows found for the given criteria."
        Case AWE_SheetError.InvalidCriteria
            Debug.Print "Error: Invalid criteria provided. Check the search value or data type."
        Case AWE_SheetError.MetadataInitializationError
            Debug.Print "Error: Column mapping or metadata is incomplete. Ensure proper initialization."

        ' Filter and Sort Errors
        Case AWE_SheetError.ProtectedWorksheet
            Debug.Print "Error: Worksheet is protected. Unprotect the sheet to perform this operation."
        Case AWE_SheetError.FilterCriteriaMismatch
            Debug.Print Warning: "Filter criteria do not match any data. Execution will continue."
            Resume Next

        ' Default: Unexpected Errors
        Case Else
            Debug.Print "Unexpected Error: " & Err.Description & " (Error Code: " & Err.Number & ")"
    End Select

    ' Clear the error state
    Err.Clear
End Sub