ANALYSE THE BELOW SCRIPT AND TELL ME WHAT IT IS DOING AND WHAT KIND OF LOGIC IT IS USING IN GREAT DETAIL:-


Option Explicit

' =============================================================================
' Author:        Alicia Technologies LLP
' Created On:    06-12-2024 03:21 PM
' Description:   Processes multiple tables within an Excel worksheet by splitting
'                component positions into separate rows in a new processed sheet.
'                Enhances data normalization for Bill of Materials (BOM).
' =============================================================================

' =============================================================================
' Main Subroutine: SplitComponentsMultipleTablesCorrected
' Purpose        : Orchestrates the entire process of identifying tables, processing
'                  their data, and outputting the normalized data to a new worksheet.
' =============================================================================
Sub A2_SEPARATE()
    On Error GoTo ErrorHandler  ' Global error handler for runtime errors.
    
    ' =============================================================================
    ' Variable Declarations
    ' =============================================================================
    Dim sourceWorksheet As Worksheet
    Dim processedWorksheet As Worksheet
    Dim processedSheetName As String
    Dim totalRows As Long
    Dim currentRow As Long
    Dim targetRow As Long
    Dim partRefHeader As String
    Dim qtyHeader As String
    Dim colMapping As Object
    Dim headerRow As Long
    
    ' =============================================================================
    ' Performance Optimization: Disable screen updating and automatic calculations
    ' =============================================================================
    Call OptimizePerformance(False)
    
    ' Set the name for the processed sheet
    processedSheetName = "A2_ProcessedBillOfMaterial"
    
    ' =============================================================================
    ' Select the source worksheet
    ' =============================================================================
    Set sourceWorksheet = SelectSheetByIndex()
    
    ' Validate the selected worksheet
    If sourceWorksheet Is Nothing Then
        MsgBox "No worksheet selected. Operation canceled.", vbExclamation
        GoTo Cleanup
    End If
    
    ' =============================================================================
    ' Prompt user for header names
    ' =============================================================================
    partRefHeader = GetUserInput("Enter the exact header name of the 'Part Reference' or 'Designator' column:", _
                                 "Part Reference Header", "Reference Headers")
    If Trim(partRefHeader) = "" Then
        MsgBox "You must specify a valid Part Reference header.", vbExclamation
        GoTo Cleanup
    End If
    
    qtyHeader = GetUserInput("Enter the exact header name of the 'Quantity' column (leave blank if none):", _
                            "Quantity Header", "Quantity")
    
    ' =============================================================================
    ' Validate data in the source worksheet
    ' =============================================================================
    If IsWorksheetEmpty(sourceWorksheet) Then
        MsgBox "The selected worksheet contains no data.", vbExclamation
        GoTo Cleanup
    End If
    
    totalRows = sourceWorksheet.UsedRange.Rows.Count
    currentRow = 1
    targetRow = 1  ' Start from row 1 in the processed sheet
    
    ' =============================================================================
    ' Setup the processed worksheet
    ' =============================================================================
    Set processedWorksheet = SetupProcessedSheet(processedSheetName)
    If processedWorksheet Is Nothing Then
        MsgBox "Could not create or access the processed sheet.", vbExclamation
        GoTo Cleanup
    End If
    
    ' =============================================================================
    ' Loop through all rows to find and process each table
    ' =============================================================================
    Do While currentRow <= totalRows
        Set colMapping = FindNextTable(sourceWorksheet, currentRow, totalRows, headerRow, partRefHeader)
        
        If Not colMapping Is Nothing Then
            ' Process the identified table
            Call ProcessTable(sourceWorksheet, processedWorksheet, currentRow, totalRows, _
                             headerRow, colMapping, targetRow, partRefHeader, qtyHeader)
        Else
            ' No more tables found
            Exit Do
        End If
    Loop
    
    ' =============================================================================
    ' Finalize the processed worksheet
    ' =============================================================================
    Call FinalizeProcessedSheet(processedWorksheet)
    
    ' Notify the user of successful completion
    MsgBox "Component positions have been split into separate rows on the '" & processedSheetName & "' sheet.", _
           vbInformation, "Process Complete"

Cleanup:
    ' Re-enable settings changed for performance optimization
    Call OptimizePerformance(True)
    Exit Sub

ErrorHandler:
    ' Global Error Handler
    MsgBox "An unexpected error occurred: " & Err.Description, vbCritical, "Error"
    Resume Cleanup
End Sub

' =============================================================================
' Subroutine: OptimizePerformance
' Purpose     : Enables or disables performance optimizations such as screen updating
'               and automatic calculations to enhance macro performance.
' Parameters  :
'   - enableOptimization As Boolean : True to re-enable settings, False to disable.
' =============================================================================
Sub OptimizePerformance(enableOptimization As Boolean)
    If enableOptimization Then
        ' Re-enable settings
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    Else
        ' Disable settings for performance
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
    End If
End Sub

' =============================================================================
' Function: GetUserInput
' Purpose : Prompts the user with an input box and retrieves the input value.
' Parameters:
'   - prompt As String       : The message displayed to the user.
'   - title As String        : The title of the input box.
'   - defaultResponse As String : The default value in the input box.
' Returns:
'   - userInput As String    : The trimmed user input or an empty string if canceled.
' =============================================================================
Function GetUserInput(prompt As String, title As String, defaultResponse As String) As String
    Dim userInput As Variant
    userInput = Application.InputBox(prompt:=prompt, title:=title, Default:=defaultResponse, Type:=2) ' Type:=2 for string input
    
    ' Check if the user canceled the input
    If userInput = False Then
        GetUserInput = ""
    Else
        GetUserInput = Trim(CStr(userInput))
    End If
End Function

' =============================================================================
' Function: IsWorksheetEmpty
' Purpose : Determines whether the provided worksheet contains any data.
' Parameters:
'   - ws As Worksheet : The worksheet to check.
' Returns:
'   - isEmpty As Boolean : True if the worksheet is empty, False otherwise.
' =============================================================================
Function IsWorksheetEmpty(ws As Worksheet) As Boolean
    On Error Resume Next
    IsWorksheetEmpty = (Application.WorksheetFunction.CountA(ws.Cells) = 0)
    On Error GoTo 0
End Function

' =============================================================================
' Function: SetupProcessedSheet
' Purpose : Creates or replaces the processed worksheet where normalized data will
'           be stored.
' Parameters:
'   - processedSheetName As String : The name for the processed sheet.
' Returns:
'   - processedWs As Worksheet       : The newly created or existing processed worksheet.
' =============================================================================
Function SetupProcessedSheet(processedSheetName As String) As Worksheet
    On Error GoTo SetupErrorHandler
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    
    ' =============================================================================
    ' Delete existing processed sheet if it exists
    ' =============================================================================
    Application.DisplayAlerts = False  ' Suppress confirmation dialogs
    On Error Resume Next
    wb.Sheets(processedSheetName).Delete
    On Error GoTo 0
    Application.DisplayAlerts = True  ' Re-enable alerts
    
    ' =============================================================================
    ' Create a new processed sheet
    ' =============================================================================
    Set SetupProcessedSheet = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
    SetupProcessedSheet.Name = processedSheetName
    Exit Function

SetupErrorHandler:
    MsgBox "Error setting up the processed sheet: " & Err.Description, vbExclamation, "Setup Error"
    Set SetupProcessedSheet = Nothing
End Function

' =============================================================================
' Function: FindNextTable
' Purpose : Identifies the next table's header row starting from the current row
'           and returns a mapping of column headers to their respective indices.
' Parameters:
'   - sourceWs As Worksheet      : The worksheet to search for tables.
'   - ByRef currentRow As Long   : The current row index to start searching from.
'   - totalRows As Long          : The total number of rows in the worksheet.
'   - ByRef headerRow As Long    : The row index where the header is found.
'   - partRefHeader As String    : The header name for "Part Reference" to identify table headers.
' Returns:
'   - colMapping As Object       : A dictionary mapping header names to column indices.
'                                 Returns Nothing if no header row is found.
' =============================================================================
Function FindNextTable(sourceWs As Worksheet, ByRef currentRow As Long, _
                       totalRows As Long, ByRef headerRow As Long, _
                       partRefHeader As String) As Object
    On Error GoTo FindNextTableError
    Dim i As Long
    Dim colMapping As Object
    
    ' Iterate from the current row to the total number of rows
    For i = currentRow To totalRows
        Set colMapping = IsHeaderRow(sourceWs, i, partRefHeader)
        If Not colMapping Is Nothing Then
            headerRow = i
            currentRow = i + 1  ' Move to the row after the header for next iteration
            Set FindNextTable = colMapping
            Exit Function
        End If
    Next i
    
    ' If no more tables are found
    Set FindNextTable = Nothing
    Exit Function

FindNextTableError:
    MsgBox "Error finding next table: " & Err.Description, vbExclamation, "Find Table Error"
    Set FindNextTable = Nothing
End Function

' =============================================================================
' Subroutine: ProcessTable
' Purpose     : Processes a single table by copying and splitting data rows
' Parameters  :
'   - sourceWs As Worksheet      : The source worksheet containing the table.
'   - processedWs As Worksheet   : The processed worksheet to output data.
'   - ByRef currentRow As Long   : The current row index in the source worksheet.
'   - totalRows As Long          : The total number of rows in the source worksheet.
'   - headerRow As Long          : The row index of the table's header.
'   - colMapping As Object       : Dictionary mapping headers to column indices.
'   - ByRef targetRow As Long    : The current row index in the processed worksheet.
'   - partRefHeader As String    : The header name for "Part Reference".
'   - qtyHeader As String        : The header name for "Quantity".
' =============================================================================
Sub ProcessTable(sourceWs As Worksheet, processedWs As Worksheet, _
                 ByRef currentRow As Long, totalRows As Long, _
                 headerRow As Long, colMapping As Object, _
                 ByRef targetRow As Long, partRefHeader As String, _
                 qtyHeader As String)
    On Error GoTo TableErrorHandler
    Dim initialColumn As Long
    Dim lastNonEmptyColumn As Long
    Dim headers As Variant
    Dim partRefCol As Long
    Dim qtyCol As Long
    
    ' Determine table columns based on header row
    initialColumn = 1  ' Assuming tables start from column A
    lastNonEmptyColumn = sourceWs.Cells(headerRow, sourceWs.Columns.Count).End(xlToLeft).Column
    
    ' Read headers from the header row
    headers = sourceWs.Range(sourceWs.Cells(headerRow, initialColumn), _
                             sourceWs.Cells(headerRow, lastNonEmptyColumn)).value
    
    ' Identify "Part Reference" column
    partRefCol = GetColumnIndex(colMapping, partRefHeader)
    If partRefCol = 0 Then
        MsgBox "The specified Part Reference header '" & partRefHeader & _
               "' was not found in the header row at " & headerRow & ".", vbCritical, "Header Not Found"
        Exit Sub
    End If
    
    ' Identify "Quantity" column if provided
    If Trim(qtyHeader) <> "" Then
        qtyCol = GetColumnIndex(colMapping, qtyHeader)
        If qtyCol = 0 Then
            MsgBox "The specified Quantity header '" & qtyHeader & "' was not found. Proceeding without Quantity.", _
                   vbExclamation, "Quantity Header Not Found"
        End If
    Else
        qtyCol = 0  ' Indicates that Quantity is not used
    End If
    
    ' Write headers to processed sheet if processing the first table
    If targetRow = 1 Then
        Call WriteHeaders(processedWs, headers, initialColumn, lastNonEmptyColumn, targetRow)
    End If
    
    ' Process each data row within the table
    Do While currentRow <= totalRows
        ' Exit if the current row is empty
        If IsEmptyRow(sourceWs, currentRow) Then
            currentRow = currentRow + 1
            Exit Do
        End If
        
        ' Exit if the current row is a new header row (indicating a new table)
        If Not IsHeaderRow(sourceWs, currentRow, partRefHeader) Is Nothing Then
            Exit Do
        End If
        
        ' Process the data row
        Call ProcessDataRow(sourceWs, processedWs, currentRow, targetRow, _
                           initialColumn, lastNonEmptyColumn, partRefCol, qtyCol)
        
        currentRow = currentRow + 1  ' Move to the next row
    Loop
    
    Exit Sub

TableErrorHandler:
    MsgBox "Error processing table starting at header row " & headerRow & ": " & Err.Description, _
           vbExclamation, "Table Processing Error"
End Sub

' =============================================================================
' Function: GetColumnIndex
' Purpose : Retrieves the column index for a given header name from the mapping.
' Parameters:
'   - colMapping As Object : Dictionary mapping headers to column indices.
'   - headerName As String  : The header name to find (case-insensitive).
' Returns:
'   - colIndex As Long      : The column index if found; 0 otherwise.
' =============================================================================
Function GetColumnIndex(colMapping As Object, headerName As String) As Long
    Dim key As Variant
    key = LCase(Trim(headerName))
    If colMapping.Exists(key) Then
        GetColumnIndex = CLng(colMapping(key))
    Else
        GetColumnIndex = 0
    End If
End Function

' =============================================================================
' Subroutine: WriteHeaders
' Purpose     : Writes the header row to the processed worksheet.
' Parameters  :
'   - processedWs As Worksheet  : The worksheet to write headers to.
'   - headers As Variant        : Array containing header values.
'   - initialColumn As Long     : The starting column index.
'   - lastNonEmptyColumn As Long: The last column index with data.
'   - ByRef targetRow As Long   : The current row index in the processed worksheet.
' =============================================================================
Sub WriteHeaders(processedWs As Worksheet, headers As Variant, _
                 initialColumn As Long, lastNonEmptyColumn As Long, _
                 ByRef targetRow As Long)
    On Error GoTo WriteHeadersError
    ' Write the headers to the specified range in the processed worksheet
    processedWs.Range(processedWs.Cells(targetRow, initialColumn), _
                     processedWs.Cells(targetRow, lastNonEmptyColumn)).value = headers
    
    targetRow = targetRow + 1  ' Increment the target row for data entries
    Exit Sub

WriteHeadersError:
    MsgBox "Error writing headers: " & Err.Description, vbExclamation, "Header Writing Error"
End Sub

' =============================================================================
' Subroutine: ProcessDataRow
' Purpose     : Processes a single data row by splitting part references and
'               copying the data to the processed worksheet.
' Parameters  :
'   - sourceWs As Worksheet      : The source worksheet containing the data row.
'   - processedWs As Worksheet   : The processed worksheet to output data.
'   - currentRow As Long         : The current row index in the source worksheet.
'   - ByRef targetRow As Long    : The current row index in the processed worksheet.
'   - initialColumn As Long      : The starting column index of the table.
'   - lastNonEmptyColumn As Long : The last column index of the table.
'   - partRefCol As Long         : The column index for "Part Reference".
'   - qtyCol As Long             : The column index for "Quantity" (0 if not used).
' =============================================================================
Sub ProcessDataRow(sourceWs As Worksheet, processedWs As Worksheet, _
                   currentRow As Long, ByRef targetRow As Long, _
                   initialColumn As Long, lastNonEmptyColumn As Long, _
                   partRefCol As Long, qtyCol As Long)
    On Error GoTo DataRowError
    Dim partReferences As String
    Dim trimmedRefs() As String
    Dim idx As Long
    Dim isFirstRef As Boolean
    
    ' Retrieve and clean the "Part Reference" value from the current row
    partReferences = Trim(Application.Clean(sourceWs.Cells(currentRow, partRefCol).value))
    
    ' Proceed only if "Part Reference" is not empty
    If partReferences <> "" Then
        ' Split the "Part Reference" into individual references
        trimmedRefs = SplitAndTrimPartReferences(partReferences)
        
        ' Check if any references were found after splitting
        If UBound(trimmedRefs) >= LBound(trimmedRefs) Then
            ' Iterate through each part reference and copy data accordingly
            For idx = LBound(trimmedRefs) To UBound(trimmedRefs)
                isFirstRef = (idx = LBound(trimmedRefs))
                
                ' Copy data to the processed sheet with updated "Part Reference"
                Call CopyDataToProcessedSheet(sourceWs, processedWs, currentRow, targetRow, _
                                             initialColumn, lastNonEmptyColumn, trimmedRefs(idx), _
                                             partRefCol, qtyCol, isFirstRef)
                                             
                targetRow = targetRow + 1  ' Increment target row after copying
            Next idx
        Else
            ' No valid references found after splitting
            Debug.Print "Row " & currentRow & ": No valid references found after splitting."
        End If
    Else
        ' "Part Reference" is empty or invalid
        Debug.Print "Row " & currentRow & ": 'Part Reference' is empty or invalid."
    End If
    
    Exit Sub

DataRowError:
    MsgBox "Error processing data row " & currentRow & ": " & Err.Description, vbExclamation, "Data Row Error"
End Sub

' =============================================================================
' Function: SplitAndTrimPartReferences
' Purpose : Splits the "Part Reference" string by a delimiter and trims each reference.
' Parameters:
'   - partReferences As String : The concatenated part references.
' Returns:
'   - trimmedRefs As Variant    : An array of individual trimmed part references.
' =============================================================================
Function SplitAndTrimPartReferences(partReferences As String) As Variant
    On Error Resume Next
    Dim refArray() As String
    Dim trimmedRefs() As String
    Dim idx As Long
    
    ' Split the part references by comma (adjust delimiter if necessary)
    refArray = Split(partReferences, ",")
    
    ' Handle cases where splitting results in no elements
    If UBound(refArray) < LBound(refArray) Then
        ReDim trimmedRefs(0 To 0)
        trimmedRefs(0) = partReferences
        SplitAndTrimPartReferences = trimmedRefs
        Exit Function
    End If
    
    ' Initialize the trimmed references array
    ReDim trimmedRefs(LBound(refArray) To UBound(refArray))
    For idx = LBound(refArray) To UBound(refArray)
        trimmedRefs(idx) = Trim(refArray(idx))
    Next idx
    
    SplitAndTrimPartReferences = trimmedRefs
End Function

' =============================================================================
' Subroutine: CopyDataToProcessedSheet
' Purpose     : Copies data from the source worksheet to the processed worksheet,
'               updating the "Part Reference" and optionally handling the "Quantity".
' Parameters  :
'   - sourceWs As Worksheet      : The source worksheet containing the data.
'   - processedWs As Worksheet   : The processed worksheet to output data.
'   - currentRow As Long         : The current row index in the source worksheet.
'   - targetRow As Long          : The target row index in the processed worksheet.
'   - initialColumn As Long      : The starting column index of the table.
'   - lastNonEmptyColumn As Long : The last column index of the table.
'   - trimmedRef As String       : The individual part reference to assign.
'   - partRefCol As Long         : The column index for "Part Reference".
'   - qtyCol As Long             : The column index for "Quantity" (0 if not used).
'   - isFirstRef As Boolean      : Indicates if this is the first reference in the split list.
' =============================================================================
Sub CopyDataToProcessedSheet(sourceWs As Worksheet, processedWs As Worksheet, _
                             currentRow As Long, targetRow As Long, _
                             initialColumn As Long, lastNonEmptyColumn As Long, _
                             trimmedRef As String, partRefCol As Long, _
                             qtyCol As Long, isFirstRef As Boolean)
    On Error GoTo CopyError
    ' Copy the entire row's data to the processed sheet
    processedWs.Range(processedWs.Cells(targetRow, initialColumn), _
                      processedWs.Cells(targetRow, lastNonEmptyColumn)).value = _
                      sourceWs.Range(sourceWs.Cells(currentRow, initialColumn), _
                                     sourceWs.Cells(currentRow, lastNonEmptyColumn)).value
                     
    ' Update the "Part Reference" column with the individual reference
    processedWs.Cells(targetRow, partRefCol).value = trimmedRef
    
    ' Optionally blank out the "Quantity" column for subsequent references
    If Not isFirstRef And qtyCol > 0 Then
        processedWs.Cells(targetRow, qtyCol).value = ""  ' Blank out quantity
    End If
    
    Exit Sub

CopyError:
    MsgBox "Error copying data to processed sheet at row " & targetRow & ": " & Err.Description, _
           vbExclamation, "Copy Error"
End Sub

' =============================================================================
' Function: IsHeaderRow
' Purpose : Determines if a given row is a header row by checking for the presence
'           of the specified "Part Reference" header. Returns a mapping of headers
'           to their column indices if true; otherwise, returns Nothing.
' Parameters:
'   - sourceWs As Worksheet : The worksheet to check.
'   - rowNum As Long        : The row number to evaluate as a potential header.
'   - partRefHeader As String : The header name for "Part Reference" to identify table headers.
' Returns:
'   - colMapping As Object  : A dictionary mapping headers to column indices.
'                              Returns Nothing if the row is not a header row.
' =============================================================================
Function IsHeaderRow(sourceWs As Worksheet, rowNum As Long, _
                    partRefHeader As String) As Object
    On Error GoTo HeaderError
    Dim colIndex As Long
    Dim headerText As String
    Dim headerFound As Boolean
    Dim colMapping As Object
    
    headerFound = False
    Set colMapping = CreateObject("Scripting.Dictionary")
    
    ' Iterate through each column in the row to identify headers
    For colIndex = 1 To sourceWs.UsedRange.Columns.Count
        headerText = LCase(Trim(CStr(sourceWs.Cells(rowNum, colIndex).value)))
        If headerText <> "" Then
            colMapping(headerText) = colIndex  ' Map header text to column index
            If LCase(partRefHeader) = headerText Then
                headerFound = True
            End If
        End If
    Next colIndex
    
    ' Determine if the current row is a header row based on the presence of the
    ' specified "Part Reference" header
    If headerFound Then
        Set IsHeaderRow = colMapping  ' Return the column mapping dictionary
    Else
        Set IsHeaderRow = Nothing  ' Not a header row
    End If
    
    Exit Function

HeaderError:
    MsgBox "Error checking if row " & rowNum & " is a header row: " & Err.Description, _
           vbExclamation, "Header Row Error"
    Set IsHeaderRow = Nothing
End Function

' =============================================================================
' Function: IsEmptyRow
' Purpose : Checks if a given row in the worksheet is empty (no data).
' Parameters:
'   - sourceWs As Worksheet : The worksheet to check.
'   - rowNum As Long        : The row number to evaluate.
' Returns:
'   - isEmpty As Boolean    : True if the row is empty; False otherwise.
' =============================================================================
Function IsEmptyRow(sourceWs As Worksheet, rowNum As Long) As Boolean
    On Error Resume Next
    IsEmptyRow = (Application.WorksheetFunction.CountA(sourceWs.Rows(rowNum)) = 0)
End Function

' =============================================================================
' Subroutine: FinalizeProcessedSheet
' Purpose     : Finalizes the processed worksheet by adjusting column widths.
' Parameters  :
'   - processedWs As Worksheet : The worksheet to finalize.
' =============================================================================
Sub FinalizeProcessedSheet(processedWs As Worksheet)
    On Error Resume Next
    processedWs.Columns.AutoFit  ' Adjust column widths to fit content
    On Error GoTo 0
End Sub

' =============================================================================
' Function: SelectSheetByIndex
' Purpose : Prompts the user to select a worksheet by its index number from the
'           active workbook. Returns the selected worksheet or Nothing if canceled.
' Parameters:
'   - None
' Returns:
'   - selectedSheet As Worksheet : The worksheet selected by the user.
'                                  Returns Nothing if the user cancels.
' =============================================================================
Function SelectSheetByIndex() As Worksheet
    On Error GoTo SelectErrorHandler
    Dim wb As Workbook
    Dim i As Integer
    Dim sheetList As String
    Dim sheetIndex As Variant
    Dim isValidIndex As Boolean

    Set wb = ActiveWorkbook
    
    ' Validate that an active workbook exists
    If wb Is Nothing Then
        MsgBox "No active workbook found.", vbExclamation, "Workbook Not Found"
        Set SelectSheetByIndex = Nothing
        Exit Function
    End If

    ' Build a list of available sheets for user selection
    sheetList = "Available Sheets:" & vbCrLf
    For i = 1 To wb.Sheets.Count
        sheetList = sheetList & i & ": " & wb.Sheets(i).Name & vbCrLf
    Next i

    ' Highlight the currently active sheet
    If Not ActiveSheet Is Nothing Then
        sheetList = sheetList & vbCrLf & "Currently active sheet: " & ActiveSheet.Name
    End If

    ' Prompt the user to enter the index number of the desired sheet
    Do
        sheetIndex = Application.InputBox( _
            prompt:=sheetList & vbCrLf & "Enter the index number of the sheet to select:", _
            title:="Select Sheet", Type:=1)  ' Type:=1 ensures numeric input

        ' Check if the user canceled the input
        If sheetIndex = False Then
            Set SelectSheetByIndex = Nothing
            Exit Function
        End If

        ' Validate the user input
        isValidIndex = (IsNumeric(sheetIndex) And sheetIndex >= 1 And sheetIndex <= wb.Sheets.Count)
        If isValidIndex Then Exit Do  ' Valid index entered

        ' Inform the user of invalid input and prompt again
        MsgBox "Invalid input. Please enter a valid index number corresponding to a sheet.", _
               vbExclamation, "Invalid Input"
    Loop

    ' Return the selected worksheet based on the validated index
    Set SelectSheetByIndex = wb.Sheets(CLng(sheetIndex))
    Exit Function

SelectErrorHandler:
    MsgBox "Error selecting sheet by index: " & Err.Description, vbExclamation, "Selection Error"
    Set SelectSheetByIndex = Nothing
End Function