VBA Function to Sum the Squares of Numbers in a Range

This VBA function takes a cell range as input and outputs the sum of the squares of all numbers in that range. The function includes necessary input validation, error handling, and detailed documentation.

Function Definition

VBA Code

' Function to sum the squares of numbers in a specified range
Function SumOfSquares(rng As Range) As Double
    '----------------------------------------------------------------------
    ' Summary: Calculates the sum of the squares of numbers in a given range
    ' Parameters:
    '   rng (Range): Excel cell range that contains the numbers to be squared and summed
    ' Returns: Double - The sum of the squares of numbers in the range
    ' Raises: None
    '----------------------------------------------------------------------
    
    Dim cell As Range
    Dim result As Double

    ' Initialize result to zero
    result = 0

    ' Validate input to ensure it's a Range
    If rng Is Nothing Then
        MsgBox "Invalid input: Please provide a valid range.", vbExclamation
        Exit Function
    End If

    ' Loop through each cell in the range
    For Each cell In rng
        ' Check if the cell contains a number
        If IsNumeric(cell.Value) Then
            ' Add the square of the cell's value to the result
            result = result + (cell.Value) ^ 2
        End If
    Next cell

    ' Return the calculated sum of squares
    SumOfSquares = result
End Function

Commentary and Explanation

• Function Declaration: SumOfSquares(rng As Range) As Double defines the function and specifies the input parameter rng as a Range and the return type as Double.
• Input Validation: The function checks if the provided input rng is a valid range.
• Loop Through Cells: The function iterates through each cell in the specified range (rng). For each cell, it checks if the cell contains a numeric value and, if so, adds the square of that value to the result.
• Return Value: The function returns the sum of the squares of all numeric values in the specified range.

Usage Example

To use this function in Excel, follow these steps:

1. Press ALT + F11 to open the VBA editor.
2. Insert a new module by clicking Insert > Module.
3. Copy and paste the VBA code into the module.
4. Press CTRL + S to save your work and close the VBA editor.

Now you can use the SumOfSquares function as a custom formula in Excel.

Example Usage in Excel

1. Assume you have a range of numbers in cells A1:A5.

2. In any empty cell, enter the following formula:

   =SumOfSquares(A1:A5)

This formula will return the sum of the squares of the numbers in the range A1:A5.

Conclusion

This VBA function provides a robust and efficient way to calculate the sum of the squares of numbers within a specified range in Excel. For more advanced Excel and VBA training, consider exploring courses offered by Enterprise DNA Platform.