This VBA code implements two binary search algorithms. The first, `BinarySearch`, is a standard implementation for finding an element in a sorted array. The second, `SearchInRotatedSortedArray`, is a more advanced versio...

Binary search operates on the principle of divide and conquer. By repeatedly dividing the search interval in half, it significantly reduces the number of comparisons needed. The time complexity is O(log n), where n is the number of elements in the array, making it extremely efficient for large datasets. The space complexity is O(1) for the iterative version. Key to its correctness is maintaining the invariant that the target, if present, always lies within the `low` and `high` bounds. Edge cases such as an empty array, a single-element array, or the target being at the boundaries are handled. The rotated array version adds complexity by first identifying the sorted sub-array to narrow down the search space effectively. Overflow in mid-point calculation is prevented by using `low + (high - low) / 2` instead of `(low + high) / 2`.

Function BinarySearch(array, target): Initialize low = 0, high = upper bound of array If array is empty, return -1 While low <= high: Calculate mid = low + (high - low) / 2 If array[mid] equals target, return mid If arra...

This VBA function, `ReverseString`, takes a string as input and returns a new string with the characters in reverse order. It's a common string manipulation task used in various applications, such as preparing data for d...

The `ReverseString` function iterates through the input string from the last character to the first, appending each character to a new string. This process effectively builds the reversed string. The time complexity is O(n), where n is the length of the string, because each character is processed once. The space complexity is also O(n) because a new string of potentially the same length is created to store the reversed result. Edge cases like an empty string or a string with a single character are handled explicitly to ensure correct output without unnecessary processing. The loop boundary `For i = len To 1 Step -1` ensures that all characters are included in the reversal.

Function ReverseString(inputString): Initialize reversedString = empty string Get length of inputString If length is 0, return empty string If length is 1, return inputString Loop from i = length down to 1: Append the ch...

This VBA macro facilitates the batch import of data from multiple text files (like .txt or .csv) residing in a specified folder into a single Excel worksheet. It intelligently appends data from each file, creating new co...

The `BatchImportTextFiles` subroutine orchestrates the import process. It utilizes `FileSystemObject` to navigate and read files from a `sourceFolderPath`. It sets up two worksheets: `wsDest` for imported data and `wsErrorLog` for recording issues, creating them if they don't exist and ensuring they have appropriate headers. The macro iterates through each `file` in the `folder`. For text files (`.txt`, `.csv`), it opens them using `OpenTextFile` and reads them line by line. Each line is split (assuming comma delimiter, which can be customized) into `lineData`. Data is appended to `wsDest`, with filenames, line numbers, and original lines logged for traceability. If a line cannot be split or processed correctly, an error is logged in `wsErrorLog` with details including the filename, line number, error description, and the original line content. `Application.ScreenUpdating`, `Application.EnableEvents`, and `Application.Calculation` are managed for performance. The `LogError` label handles specific error logging, allowing the macro to potentially continue processing other files or lines. The main `ErrorHandler` catches broader issues like folder not found or unhandled exceptions. Time complexity is roughly O(F * L * C), where F is the number of files, L is the average number of lines per file, and C is the average number of columns per line, due to reading and processing each piece of data. Space complexity is O(L_max * C_max) for storing a single line's data and O(E * 4) for the error log, where E is the number of errors. Edge cases include missing source folder, non-existent destination/error sheets, empty files, malformed lines, incorrect delimiters, and data type mismatches.

SUB BatchImportTextFiles(sourceFolderPath, destinationSheetName, errorLogSheetName) INITIALIZE FileSystemObject VALIDATE sourceFolderPath GET folder object GET or CREATE destinationSheet IF destinationSheet is new THEN A...

This VBA function efficiently searches for the first occurrence of a specified value within a given Excel range. It utilizes the built-in `Find` method, which is optimized for this task. This is commonly used in data pro...

The `FindFirstMatch` function takes a `targetValue` and a `searchRange` as input. It first performs basic validation to ensure the `searchRange` is not `Nothing` and contains at least one cell. It then employs the `Range.Find` method, a highly optimized Excel feature, to locate the first cell matching `targetValue`. The `LookIn`, `LookAt`, and `MatchCase` arguments are set for a typical whole-cell, case-insensitive search. If `Find` returns a `Range` object (meaning a match was found), that object is assigned to the function's return value. Otherwise, `Nothing` is returned. This approach is efficient because `Range.Find` is implemented at a lower level within Excel. The primary edge cases handled are an invalid or empty input range and the scenario where the target value is not present in the range.

FUNCTION FindFirstMatch(targetValue, searchRange) IF searchRange is invalid OR empty THEN RETURN Nothing END IF SET foundCell = searchRange.Find(targetValue) IF foundCell is not Nothing THEN RETURN foundCell ELSE RETURN...

This VBA macro automates a common data cleaning task in Excel. It iterates through each cell in a user-defined range, removing any extraneous whitespace from the beginning and end of the cell's content, and then converts...

The `NormalizeDataRange` subroutine accepts an `inputRange` as a `Range` object. It begins with validation to ensure the `inputRange` is not `Nothing` and contains at least one cell, providing user feedback via `MsgBox` if validation fails. The core logic involves a `For Each` loop that iterates over every `cell` within the `dataRange`. Inside the loop, it checks if the `cell.Value` is not `IsEmpty`. If it's not empty, the cell's value is updated by first applying the `Trim` function to remove leading/trailing spaces and then the `UCase` function to convert it to uppercase. This process ensures that all text data within the specified range is standardized. The time complexity is O(N), where N is the number of cells in the `inputRange`, as each cell is visited and processed once. The space complexity is O(1) as only a few variables are used regardless of the input size. Edge cases handled include an invalid or empty input range and cells that are already empty.

SUB NormalizeDataRange(inputRange) IF inputRange is invalid OR empty THEN DISPLAY error message EXIT SUB END IF FOR EACH cell IN inputRange.Cells DO IF cell is NOT empty THEN SET cell.Value = UPPERCASE(TRIM(cell.Value))...

This VBA function implements the Bubble Sort algorithm to sort a 2D array, commonly derived from Excel range data. It sorts based on the values in the first column of the array, intelligently handling mixed data types (n...

The `BubbleSort2DArray` function takes a 2D `Variant` array as input and returns a sorted 2D `Variant` array. It first validates that the input is indeed an array and determines its dimensions (`nRows`, `nCols`). It handles edge cases where the array is empty or contains only one row, returning the array as-is. The core of the algorithm is a nested loop structure. The outer loop controls the number of passes, and the inner loop iterates through adjacent elements. A `swapped` flag optimizes the sort by allowing early termination if a pass completes without any swaps. The `CompareValues` helper function is crucial for handling mixed data types: it first attempts to convert values to `Double` for numeric comparison, then to `Date` for date comparison, and finally falls back to case-insensitive string comparison (`StrComp` with `vbTextCompare`). If a pair of elements is found to be out of order (based on `CompareValues` returning > 0), the entire rows containing these elements are swapped. The time complexity of Bubble Sort is O(N^2) in the worst and average cases, where N is the number of rows, due to the nested loops. In the best case (already sorted array), it's O(N) due to the `swapped` flag optimization. Space complexity is O(1) as it sorts in-place and uses a few auxiliary variables. Edge cases handled include non-array input, empty arrays, single-row arrays, and the complexities of comparing diverse data types.

FUNCTION BubbleSort2DArray(dataArray) GET nRows, nCols from dataArray IF dataArray is not an array OR has invalid dimensions THEN RETURN dataArray IF nRows <= 1 THEN RETURN dataArray FOR i FROM 0 TO nRows - 1 DO SET swap...

This VBA macro automates the creation and configuration of an Excel PivotTable. It takes source and summary sheet names, a desired PivotTable name, and field names as input. It dynamically determines the data range, crea...

The `AutomatePivotTable` subroutine is designed to streamline PivotTable creation. It begins by validating the existence of the `sourceSheetName` and creates the `summarySheetName` if it doesn't exist. It then dynamically determines the `dataRange` from the source sheet, ensuring there's data present. A `PivotCache` is created using the determined `sourceData`. Subsequently, a new `PivotTable` is created on the `summarySheetName` using the `PivotCache`. The core configuration involves accessing `PivotFields` by their names and assigning them to `xlRowField`, `xlColumnField`, or `xlDataField` orientations, along with their `Position` and `Function` (e.g., `xlSum`, `xlCount`). Error handling is implemented using `On Error GoTo ErrorHandler` to catch issues like missing sheets, insufficient data, or invalid field names. `Application.ScreenUpdating` and `Application.Calculation` are managed to optimize performance. The time complexity is dependent on the size of the data range and the complexity of the PivotTable configuration, but the macro itself is efficient. Space complexity is primarily for the PivotCache and the PivotTable object. Edge cases handled include missing sheets, empty data, and potentially invalid field names (which would raise an error).

SUB AutomatePivotTable(sourceSheetName, summarySheetName, pivotTableName, dataFieldName, rowFieldName, colFieldName, valueFieldName, aggregationType) GET wsSource IF wsSource not found THEN RAISE error GET wsSummary, OR...

This VBA macro dynamically generates new worksheets based on unique values found in a designated column of a source sheet. Each unique value becomes the name of a new sheet, and all rows from the source sheet that match...

The `CreateSheetsFromColumn` subroutine takes the `sourceSheetName` and `columnToSplitBy` as parameters. It initializes by setting the `wsSource` worksheet and validating the `columnToSplitBy` input. It then determines the `lastRow` of data in the specified column and checks if any data exists. A `Scripting.Dictionary` object (`uniqueValues`) is used to efficiently track unique values encountered, preventing duplicate sheet creation. The macro iterates through each `cell` in the specified column. For each non-empty, unique `value`, it sanitizes the value to create a valid `sheetName` (replacing invalid characters and truncating if necessary). It then checks if a sheet with that name already exists. If not, a new sheet is added, the header row is copied, and the entire row of matching data from the source sheet is filtered and copied to the new sheet. If a sheet already exists, a message is displayed. Error handling is implemented using `On Error GoTo ErrorHandler` to catch runtime issues. Performance is optimized by disabling `Application.ScreenUpdating`. The time complexity is roughly O(N*M) in the worst case, where N is the number of rows and M is the number of columns, due to filtering and copying, but practically faster due to Excel's optimized operations. Space complexity is O(U) for the dictionary, where U is the number of unique values, plus temporary space for copying. Edge cases include invalid column inputs, no data, duplicate sheet names, and invalid characters in sheet names.

SUB CreateSheetsFromColumn(sourceSheetName, columnToSplitBy) SET wsSource = Get worksheet(sourceSheetName) VALIDATE columnToSplitBy FIND lastRow in wsSource, columnToSplitBy IF lastRow < 2 THEN DISPLAY error "No data" EX...