Employee Data Reporting Function in Excel

This document describes how to create a custom Excel Function using Visual Basic for Applications (VBA) to generate a report on employee data. The function will analyze employee information and summarize key statistics.

Function Overview

Purpose

To create a summary report of employee data, including total number of employees, average salary, and department-wise count.

Parameters

• rng (Range): A range of cells containing employee data where the first row is headers.
• summaryType (String): Specifies the type of statistic to return ('total', 'average', or 'countByDept').

Returns

• Variant: Returns the summary statistic based on the specified type.

Exceptions

• Raises an error if the input range is invalid or summaryType is unrecognized.

Code Implementation

Required Imports

No external libraries are needed as this function will use standard Excel VBA.

Function Code

Function EmployeeReport(rng As Range, summaryType As String) As Variant
    ' This function summarizes employee data based on the provided summary type.
    ' Parameters:
    '   rng: A Range object containing employee data.
    '   summaryType: A string indicating the type of report ('total', 'average', or 'countByDept').
    ' Returns:
    '   A variant containing the required summary information.

    Dim totalEmployees As Long
    Dim totalSalary As Double
    Dim deptDict As Object
    Dim i As Long
    Dim dept As String
    Dim output As Variant
    
    ' Input validation
    If rng Is Nothing Or rng.Rows.Count < 2 Then
        EmployeeReport = "Invalid data range"
        Exit Function
    End If

    ' Initialize variables
    totalEmployees = rng.Rows.Count - 1 ' Excluding header
    totalSalary = 0
    Set deptDict = CreateObject("Scripting.Dictionary")

    ' Loop through each employee row
    For i = 2 To totalEmployees + 1 ' Start from 2 to skip headers
        totalSalary = totalSalary + rng.Cells(i, 3).Value ' Assuming Salary is in the 3rd column
        dept = rng.Cells(i, 2).Value ' Assuming Department is in the 2nd column

        ' Count employees by department
        If deptDict.Exists(dept) Then
            deptDict(dept) = deptDict(dept) + 1
        Else
            deptDict.Add dept, 1
        End If
    Next i
    
    ' Return results based on summary type
    Select Case LCase(summaryType)
        Case "total"
            EmployeeReport = totalEmployees
        Case "average"
            If totalEmployees > 0 Then
                EmployeeReport = totalSalary / totalEmployees
            Else
                EmployeeReport = 0
            End If
        Case "countbydept"
            Dim deptSummary As String
            deptSummary = ""
            For Each dept In deptDict.Keys
                deptSummary = deptSummary & dept & ": " & deptDict(dept) & vbCrLf
            Next dept
            EmployeeReport = deptSummary
        Case Else
            EmployeeReport = "Unrecognized summary type"
    End Select
End Function

Code Explanation

• Input Validation: The function checks if the provided range is valid and contains data.
• Looping through Data: The function iterates through each row, calculating the total salary and counting employees by department.
• Select Case Statement: Depending on the summaryType, the function returns total employees, average salary, or a department-wise count.

Example Usage

To use the EmployeeReport function in Excel:

1. Open Excel and press ALT + F11 to open the VBA editor.
2. Insert a new module and paste the above code.
3. Go back to Excel and use the function in a cell like this:

=EmployeeReport(A1:C100, "total")
=EmployeeReport(A1:C100, "average")
=EmployeeReport(A1:C100, "countByDept")

Where A1:C100 is the range containing employee data (with headers).

Conclusion

This VBA function provides an efficient and robust way to generate reports from employee data. It can be expanded or modified to include additional summary types or more complex statistics. For additional training and resources, consider exploring courses available on the Enterprise DNA Platform.