VBA

AreArraysEquals

Compares two arrays to check if they are equal, meaning they have the same size and identical elements in the same order.

Syntax

AreArraysEqual( _
    Array1 As Variant, _
    Array2 As Variant _
) As Boolean

Parameters

• Array1: First array to compare
• Array2: Second array to compare

Return Value

Returns True if both arrays are equal, False otherwise.

Remarks

• Arrays must have the same upper and lower bounds
• Arrays must have identical elements in the same positions
• The function performs an element-by-element comparison
• Returns False if arrays have different sizes
• Can compare arrays of any type since parameters are declared as Variant

Example

Dim arr1 As Variant: arr1 = Array(1, 2, 3)
Dim arr2 As Variant: arr2 = Array(1, 2, 3)

If AreArraysEqual(arr1, arr2) Then
    Debug.Print "Arrays are equal"
Else
    Debug.Print "Arrays are different"
End If

AutoFillFormulas

Automatically fills formulas across a range using a reference cell's formula. The reference cell can be either the first or last cell containing a formula in the range.

Syntax

AutoFillFormulas( _
    rng As Range, _
    Optional UseLastCellAsRef As Boolean = False _
)

Parameters

• rng: The range where formulas will be filled
• UseLastCellAsRef: (optional) Boolean flag to determine which cell to use as reference
  • False (Default): Uses the first cell with formula as reference
  • True: Uses the last cell with formula as reference

Remarks

• Does nothing if the range is empty (Nothing) or contains only one cell
• Only works if the range contains at least one formula
• Uses R1C1 formula notation to ensure proper relative references when filling
• Only fills formulas in cells that are part of the specified range
• Requires the helper function RangeHasAnyFormula to check for formulas in the range

Example

Dim rng As Range
Set rng = Range("A1:A10")
AutoFillFormulas rng 'Uses first formula cell as reference

'Or using the last cell as reference:
AutoFillFormulas rng, True

CleanString

Cleans a string by removing or replacing special and control characters with spaces.

Syntax

CleanString( _
    myString As String, _
    Optional ReplaceBySpace As Boolean = True, _
    Optional ConvertNonBreakingSpace As Boolean = True _
) As String

Parameters

• myString: The input string to be cleaned
• ReplaceBySpace: (optional) Boolean flag that determines if special characters should be replaced by spaces
  • True (Default): Replaces special characters with spaces
  • False: Removes special characters without replacement
• ConvertNonBreakingSpace: (optional) Boolean flag to handle non-breaking spaces
  • True (Default): Converts non-breaking spaces (ASCII 160) to regular spaces
  • False: Leaves non-breaking spaces unchanged

Return Value

Returns the cleaned string with special characters either removed or replaced by spaces.

Remarks

• Removes ASCII control characters (0-31)
• Handles special characters like ASCII 127, 129, 141, 143, 144, and 157
• Converts non-breaking spaces to regular spaces (when enabled)
• Trims leading and trailing spaces from the final result
• Preserves all other printable characters

Example

Dim cleanedStr As String

' Replace special characters with spaces
cleanedStr = CleanString("Hello" & Chr(0) & "World")
Debug.Print cleanedStr ' Result: "Hello World"

' Remove special characters
cleanedStr = CleanString("Hello" & Chr(0) & "World", False)
Debug.Print cleanedStr ' Result: "HelloWorld"

' Keep non-breaking spaces
cleanedStr = CleanString("Hello" & Chr(160) & "World", True, False)
Debug.Print cleanedStr ' Result: Original string unchanged