Practice Code Typing – SkillsTyping

Choose your language:

ℹ️ Select 'Choose Exercise', or randomize 'Next Random Exercise' in selected language.

Choose Exercise:

Timer 00:00

WPM --

Score --

Acc --

Correct chars --

Exercise:

VBA: Batch Import from Text Files with Error Handling

Language:

VBA (Visual Basic for Applications)

Difficulty:

Goal -- WPM

Ready

Exercise Algorithm Area

Correct typos to continue.

1Sub BatchImportTextFiles(sourceFolderPath As String, destinationSheetName As String, errorLogSheetName As String)
2' Imports data from multiple text files in a folder to a destination sheet.
3' Appends data and logs errors to a separate sheet.
4' Handles file access, format, and data type errors.
5
6Dim fso As Object ' FileSystemObject
7Dim folder As Object
8Dim file As Object
9Dim ts As Object ' TextStream
10Dim wsDest As Worksheet
11Dim wsErrorLog As Worksheet
12Dim lastDestRow As Long
13Dim lastErrorRow As Long
14Dim lineNum As Long
15Dim fileNum As Long
16Dim lineData() As String
17Dim importError As Boolean
18Dim errorDescription As String
19Dim headerRow As String
20Dim fileCounter As Integer
21
22' --- Initialization and Setup ---
23On Error GoTo ErrorHandler
24
25' Create FileSystemObject
26Set fso = CreateObject("Scripting.FileSystemObject")
27
28' Validate source folder
29If Not fso.FolderExists(sourceFolderPath) Then
30Err.Raise vbObjectError + 2001, "BatchImportTextFiles", "Source folder not found: " & sourceFolderPath
31End If
32Set folder = fso.GetFolder(sourceFolderPath)
33
34' Get destination worksheet, create if it doesn't exist
35On Error Resume Next
36Set wsDest = ThisWorkbook.Sheets(destinationSheetName)
37On Error GoTo ErrorHandler
38If wsDest Is Nothing Then
39Set wsDest = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
40wsDest.Name = destinationSheetName
41' Add header row if new sheet
42wsDest.Cells(1, 1).Value = "FileName"
43wsDest.Cells(1, 2).Value = "LineNumber"
44wsDest.Cells(1, 3).Value = "ErrorDescription"
45wsDest.Cells(1, 4).Value = "OriginalLine"
46lastDestRow = 1 ' Start appending from row 2
47Else
48lastDestRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row
49If lastDestRow = 1 And IsEmpty(wsDest.Cells(1, 1).Value) Then ' Handle case where sheet exists but is empty
50wsDest.Cells(1, 1).Value = "FileName"
51wsDest.Cells(1, 2).Value = "LineNumber"
52wsDest.Cells(1, 3).Value = "ErrorDescription"
53wsDest.Cells(1, 4).Value = "OriginalLine"
54lastDestRow = 1
55End If
56End If
57
58' Get error log worksheet, create if it doesn't exist
59On Error Resume Next
60Set wsErrorLog = ThisWorkbook.Sheets(errorLogSheetName)
61On Error GoTo ErrorHandler
62If wsErrorLog Is Nothing Then
63Set wsErrorLog = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
64wsErrorLog.Name = errorLogSheetName
65' Add header row for error log
66wsErrorLog.Cells(1, 1).Value = "FileName"
67wsErrorLog.Cells(1, 2).Value = "LineNumber"
68wsErrorLog.Cells(1, 3).Value = "ErrorDescription"
69wsErrorLog.Cells(1, 4).Value = "OriginalLine"
70lastErrorRow = 1
71Else
72lastErrorRow = wsErrorLog.Cells(wsErrorLog.Rows.Count, "A").End(xlUp).Row
73If lastErrorRow = 1 And IsEmpty(wsErrorLog.Cells(1, 1).Value) Then ' Handle case where sheet exists but is empty
74wsErrorLog.Cells(1, 1).Value = "FileName"
75wsErrorLog.Cells(1, 2).Value = "LineNumber"
76wsErrorLog.Cells(1, 3).Value = "ErrorDescription"
77wsErrorLog.Cells(1, 4).Value = "OriginalLine"
78lastErrorRow = 1
79End If
80End If
81
82' --- File Processing Loop ---
83Application.ScreenUpdating = False
84Application.EnableEvents = False ' Prevent worksheet events
85Application.Calculation = xlCalculationManual
86
87fileCounter = 0
88For Each file In folder.Files
89' Process only text files (e.g., .txt, .csv)
90If LCase(fso.GetExtensionName(file.Name)) = "txt" Or LCase(fso.GetExtensionName(file.Name)) = "csv" Then
91fileCounter = fileCounter + 1
92lineNum = 0
93importError = False
94errorDescription = ""
95
96' Get a free file number
97fileNum = FreeFile
98
99On Error Resume Next ' Handle file open/read errors
100Set ts = fso.OpenTextFile(file.Path, 1) ' 1 for reading
101If Err.Number <> 0 Then
102importError = True
103errorDescription = "Error opening file: " & Err.Description
104GoTo LogError
105End If
106On Error GoTo ErrorHandler ' Reset to main error handler
107
108' Read file line by line
109Do While Not ts.AtEndOfStream
110lineNum = lineNum + 1
111Dim originalLine As String
112originalLine = ts.ReadLine
113
114' Attempt to parse and import the line
115On Error Resume Next ' Handle parsing/data type errors
116lineData = Split(originalLine, ",") ' Assuming comma-separated values
117If Err.Number <> 0 Then
118importError = True
119errorDescription = "Error splitting line: " & Err.Description
120GoTo LogError
121End If
122
123' Basic check for expected number of columns (adjust delimiter if needed)
124' This is a simplified check; more robust validation might be needed
125If UBound(lineData) < 0 Then ' Empty line or parsing issue
126If Trim(originalLine) <> "" Then ' If not just an empty line
127importError = True
128errorDescription = "Line appears malformed or empty after split."
129GoTo LogError
130End If
131Else
132' Append data to destination sheet
133lastDestRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1
134wsDest.Cells(lastDestRow, 1).Value = file.Name ' Store filename
135wsDest.Cells(lastDestRow, 2).Value = lineNum ' Store line number
136wsDest.Cells(lastDestRow, 3).Value = "Success"
137wsDest.Cells(lastDestRow, 4).Value = originalLine ' Store original line for reference
138
139' Populate actual data columns
140Dim colIndex As Integer
141For colIndex = 0 To UBound(lineData)
142' Ensure destination column exists, expand if necessary
143If wsDest.Cells(1, 5 + colIndex).Value = "" Then
144wsDest.Cells(1, 5 + colIndex).Value = "Col " & (colIndex + 1) ' Auto-generate header
145End If
146wsDest.Cells(lastDestRow, 5 + colIndex).Value = Trim(lineData(colIndex))
147Next colIndex
148End If
149On Error GoTo ErrorHandler ' Reset to main error handler
150
151Loop
152ts.Close
153End If
154Next file
155
156' --- Finalization ---
157Application.ScreenUpdating = True
158Application.EnableEvents = True
159Application.Calculation = xlCalculationAutomatic
160
161If fileCounter = 0 Then
162MsgBox "No text files (.txt or .csv) found in the specified folder.", vbExclamation
163ElseIf lastErrorRow > 1 Then ' Check if any errors were logged
164MsgBox "Batch import completed with errors. Please check the '" & errorLogSheetName & "' sheet.", vbExclamation
165Else
166MsgBox "Batch import completed successfully.", vbInformation
167End If
168
169Exit Sub
170
171LogError:
172' Log the error to the error log sheet
173lastErrorRow = wsErrorLog.Cells(wsErrorLog.Rows.Count, "A").End(xlUp).Row + 1
174wsErrorLog.Cells(lastErrorRow, 1).Value = file.Name
175wsErrorLog.Cells(lastErrorRow, 2).Value = lineNum
176wsErrorLog.Cells(lastErrorRow, 3).Value = errorDescription
177wsErrorLog.Cells(lastErrorRow, 4).Value = originalLine
178
179' If error occurred during file open, mark it
180If ts Is Nothing Then ' Error opening file
181' No 'originalLine' to log in this specific case, but we have file name and error desc
182Else
183ts.Close ' Ensure stream is closed if opened
184End If
185importError = True ' Flag that an error occurred for this file/line
186GoTo ContinueProcessing ' Continue to next file/line if possible
187
188ContinueProcessing:
189If Not ts Is Nothing Then
190If Not ts.AtEndOfStream Then ' If not end of stream, try to continue reading
191Resume Next ' Attempt to continue reading from the current file
192Else
193ts.Close
194End If
195End If
196GoTo SkipMainErrorHandler ' Prevent main handler from firing if we handled it
197
198ErrorHandler:
199Application.ScreenUpdating = True
200Application.EnableEvents = True
201Application.Calculation = xlCalculationAutomatic
202
203If Not ts Is Nothing Then ts.Close ' Ensure file is closed
204
205If Err.Number = vbObjectError + 2001 Then ' Specific folder not found error
206MsgBox Err.Description, vbCritical
207ElseIf importError Then ' Error logged in LogError section
208' Message already displayed or handled by LogError logic
209' If we reached here due to an unhandled error after LogError, show it
210If Err.Number <> 0 Then
211MsgBox "An unhandled error occurred during processing: " & Err.Description & " (Error " & Err.Number & ")", vbCritical
212End If
213Else ' General unexpected error
214MsgBox "An unexpected error occurred: " & Err.Description & " (Error " & Err.Number & ")", vbCritical
215End If
216On Error GoTo 0 ' Reset error handling
217
218SkipMainErrorHandler:
219
220End Sub

Algorithm description viewbox

VBA: Batch Import from Text Files with Error Handling

Algorithm description:

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 columns as needed, and meticulously logs any import errors (file access, formatting, data type issues) to a dedicated error log sheet. This is crucial for processing large volumes of structured data from external sources reliably.

Algorithm explanation:

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.

Pseudocode:

SUB BatchImportTextFiles(sourceFolderPath, destinationSheetName, errorLogSheetName)
  INITIALIZE FileSystemObject
  VALIDATE sourceFolderPath
  GET folder object

  GET or CREATE destinationSheet
  IF destinationSheet is new THEN ADD headers (FileName, LineNumber, ErrorDesc, OriginalLine)
  GET lastDestRow

  GET or CREATE errorLogSheet
  IF errorLogSheet is new THEN ADD headers (FileName, LineNumber, ErrorDesc, OriginalLine)
  GET lastErrorRow

  DISABLE screen updating, events, set calculation to manual

  FOR EACH file IN folder.Files DO
    IF file is a text file (.txt or .csv) THEN
      SET lineNum = 0, importError = FALSE, errorDescription = ""
      GET free file number
      TRY to OPEN file for reading
      IF error opening file THEN
        importError = TRUE
        errorDescription = "Error opening file: " & Err.Description
        GOTO LogError
      END IF

      WHILE NOT end of file stream DO
        lineNum = lineNum + 1
        READ originalLine from file

        TRY to SPLIT originalLine by delimiter (e.g., comma)
        IF error splitting THEN
          importError = TRUE
          errorDescription = "Error splitting line: " & Err.Description
          GOTO LogError
        END IF

        IF line is valid (e.g., not empty after split) THEN
          INCREMENT lastDestRow
          WRITE file.Name to wsDest(lastDestRow, 1)
          WRITE lineNum to wsDest(lastDestRow, 2)
          WRITE "Success" to wsDest(lastDestRow, 3)
          WRITE originalLine to wsDest(lastDestRow, 4)
          FOR EACH data element in split line DO
            IF destination column header doesn't exist THEN CREATE it
            WRITE data element to wsDest(lastDestRow, corresponding column)
          END FOR
        ELSE IF originalLine is not empty THEN
          importError = TRUE
          errorDescription = "Malformed or empty line after split."
          GOTO LogError
        END IF
      END WHILE
      CLOSE file stream
    END IF
  END FOR

  ENABLE screen updating, events, set calculation to automatic

  IF no text files found THEN DISPLAY message
  ELSE IF errors were logged THEN DISPLAY error summary message
  ELSE DISPLAY success message

  EXIT SUB

LogError:
  INCREMENT lastErrorRow
  WRITE file.Name to wsErrorLog(lastErrorRow, 1)
  WRITE lineNum to wsErrorLog(lastErrorRow, 2)
  WRITE errorDescription to wsErrorLog(lastErrorRow, 3)
  WRITE originalLine to wsErrorLog(lastErrorRow, 4)
  IF file stream is open THEN CLOSE it
  SET importError = TRUE
  GOTO ContinueProcessing

ContinueProcessing:
  IF file stream is not at end THEN Resume Next to continue reading current file
  ELSE CLOSE file stream
  GOTO SkipMainErrorHandler

ErrorHandler:
  ENABLE screen updating, events, set calculation to automatic
  IF file stream is open THEN CLOSE it
  DISPLAY appropriate error message based on Err.Number
  RESET error handling

SkipMainErrorHandler:
END SUB

Library

Playable exercises

Choose Exercise

Filter, sort, and preview algorithm before you choose one to play.

Total 8 published items

Pages 1 pages available

Language

Sort by Direction

Browse published coding scenarios with filters, sorting, and pagination.

Published

VBA (Visual Basic for Applications) Scripting (vba)

VBA Binary Search Implementation

Difficulty: writing: 7; length: 7
Popularity: 0
Created: 2026-01-26 14:06 UTC
Published: 2026-01-26 16:39 UTC
Author: Damian

#binary search #algorithms #VBA #searching #data structures #efficiency

goal: Implement and understand binary search variations. input: A sorted (or rotated sorted) array of integers and a target integer. output: The index of the target integer if found, otherwise -1.

Canonical Algorithm Text

Function BinarySearch(arr() As Integer, target As Integer) As Integer ' Performs a binary search on a sorted array of integers. ' Returns the index of the target element if found, otherwise returns -1. Dim low As Integer Dim high As Integer Dim mid As Integer low = 0 high = UBoun...

Description Algorithm

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...

Explanation

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`.

Pseudocode

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...

Published

VBA (Visual Basic for Applications) Scripting (vba)

VBA String Reversal

Difficulty: writing: 4; length: 2
Popularity: 0
Created: 2026-01-26 14:06 UTC
Published: 2026-01-26 16:42 UTC
Author: Damian

#string manipulation #VBA #algorithms #reversal #loops

goal: Reverse a given string. input: A string. output: A new string with characters in reverse order.

Canonical Algorithm Text

Function ReverseString(inputString As String) As String ' Reverses a given string. ' Handles empty strings and single-character strings gracefully. Dim reversedString As String Dim i As Integer Dim len As Integer len = Len(inputString) reversedString = "" ' Handle edge case: empt...

Description Algorithm

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...

Explanation

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.

Pseudocode

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...

Published

VBA (Visual Basic for Applications) Scripting (vba)

VBA: Batch Import from Text Files with Error Handling

Difficulty: writing: 8; length: 10
Popularity: 0
Created: 2026-01-26 13:16 UTC
Published: 2026-01-26 13:41 UTC
Author: Admin

#vba #batch import #file handling #error handling #macros #text processing

goal: Batch import data from text files into Excel. input: sourceFolderPath (String), destinationSheetName (String), errorLogSheetName (String) output: None (modifies workbook by adding/populating sheets).

Canonical Algorithm Text

Sub BatchImportTextFiles(sourceFolderPath As String, destinationSheetName As String, errorLogSheetName As String) ' Imports data from multiple text files in a folder to a destination sheet. ' Appends data and logs errors to a separate sheet. ' Handles file access, format, and dat...

Description Algorithm

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...

Explanation

Pseudocode

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

Published

VBA (Visual Basic for Applications) Scripting (vba)

VBA: Find First Matching Cell in Range

Difficulty: writing: 9; length: 2
Popularity: 0
Created: 2026-01-26 13:16 UTC
Published: 2026-01-26 13:41 UTC
Author: Admin

#vba #excel #range search #find method #macros

goal: Find the first cell in a range matching a value. input: targetValue (Variant), searchRange (Range) output: Range object of the first matching cell, or Nothing.

Canonical Algorithm Text

Function FindFirstMatch(targetValue As Variant, searchRange As Range) As Range ' Finds the first cell in searchRange that matches targetValue. ' Returns Nothing if no match is found or if the range is invalid. Dim foundCell As Range ' Basic validation for the input range If searc...

Description Algorithm

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...

Explanation

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.

Pseudocode

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...

Published

VBA (Visual Basic for Applications) Scripting (vba)

VBA: Basic Data Normalization Macro

Difficulty: writing: 1; length: 3
Popularity: 0
Created: 2026-01-26 13:16 UTC
Published: 2026-01-26 13:41 UTC
Author: Admin

#vba #excel #normalization #data cleaning #macros #text manipulation

goal: Clean and standardize data in an Excel range. input: inputRange (Range) output: None (modifies cells in place).

Canonical Algorithm Text

Sub NormalizeDataRange(inputRange As Range) ' Cleans and normalizes data within a specified Excel range. ' Removes leading/trailing spaces and converts text to uppercase. ' Handles empty cells and invalid input ranges. Dim cell As Range Dim dataRange As Range ' Validate input ran...

Description Algorithm

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...

Explanation

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.

Pseudocode

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))...

Published

VBA (Visual Basic for Applications) Scripting (vba)

VBA: Custom Sorting Algorithm (Bubble Sort)

Difficulty: writing: 7; length: 9
Popularity: 0
Created: 2026-01-26 13:16 UTC
Published: 2026-01-26 16:38 UTC
Author: Admin

#vba #sorting #bubble sort #algorithms #arrays #data types

goal: Sort a 2D array using Bubble Sort. input: dataArray (Variant - a 2D array) output: Variant (a sorted 2D array).

Canonical Algorithm Text

Function BubbleSort2DArray(dataArray As Variant) As Variant ' Sorts a 2D array (e.g., from a range) using the Bubble Sort algorithm. ' Handles mixed data types by attempting numeric comparison first, then string. ' Sorts based on the first column (index 0). ' Includes checks for...

Description Algorithm

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...

Explanation

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.

Pseudocode

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...

Published

VBA (Visual Basic for Applications) Scripting (vba)

VBA: Pivot Table Automation from Multiple Sources

Difficulty: writing: 7; length: 10
Popularity: 0
Created: 2026-01-26 13:16 UTC
Published: 2026-01-26 16:38 UTC
Author: Admin

#vba #excel #pivot automation #reporting #macros #data analysis

goal: Automate PivotTable creation and configuration. input: sourceSheetName (String), summarySheetName (String), pivotTableName (String), dataFieldName (String - column for data range determination), rowFieldName (String), colFieldName (String), valueFieldName (String), aggregationType (XlConsolidationFunction enum) output: None (modifies workbook by adding/updating PivotTable).

Canonical Algorithm Text

Sub AutomatePivotTable(sourceSheetName As String, summarySheetName As String, pivotTableName As String, dataFieldName As String, rowFieldName As String, colFieldName As String, valueFieldName As String, aggregationType As XlConsolidationFunction) ' Automates the creation and conf...

Description Algorithm

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...

Explanation

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).

Pseudocode

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

Published

VBA (Visual Basic for Applications) Scripting (vba)

VBA: Dynamic Sheet Creation and Population

Difficulty: writing: 3; length: 10
Popularity: 0
Created: 2026-01-26 13:16 UTC
Published: 2026-01-26 16:38 UTC
Author: Admin

#vba #excel #dynamic sheets #data segmentation #macros #automation

goal: Create new sheets from unique column values and populate them. input: sourceSheetName (String), columnToSplitBy (String or Integer) output: None (modifies workbook by adding sheets).

Canonical Algorithm Text

Sub CreateSheetsFromColumn(sourceSheetName As String, columnToSplitBy As String) ' Creates new worksheets based on unique values in a specified column. ' Populates each new sheet with rows corresponding to the unique value. ' Handles potential errors like duplicate sheet names an...

Description Algorithm

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...

Explanation

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.

Pseudocode

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...

Language (required)

Paste your code (required)

Max 20,000 characters. (Dangerous HTML/script fragments will be blocked)

Normalized preview:

I agree to Publish this, after admin approval (queued for review). (Not required) I confirm this submission is anonymous and does not contain personal data. I grant SkillsTyping a free, non-exclusive, worldwide, and irrevocable license to store, modify, adapt, and publish this content after review. I understand that publication is not guaranteed, that I retain no ownership or usage claims over published versions, and that no compensation will be provided. See Privacy Policy

Publish details

Shown only if you opt to submit for review. Required when consent is checked.

Title (required with consent)

Short description / summary (required with consent)

Algorithm explanation (required with consent)

Pseudocode (required with consent)