VBA Function to Randomly Assign Customers to Staff

This document provides a VBA function that randomly assigns a total of 4655 customers to 10 staff members. The results will be displayed in a specified column of another worksheet. The function also accounts for shortages among staff and displays the results in column J.

Code Overview

Necessary Imports

VBA does not require explicit imports, but you should ensure that the relevant worksheet names are correctly referenced.

Function Implementation

Below is the VBA code that accomplishes the task:

Sub AssignCustomersToStaff()
    ' Define constants
    Const TOTAL_CUSTOMERS As Long = 4655
    Const TOTAL_STAFF As Long = 10
    Dim staffAssignments(1 To TOTAL_STAFF) As Long
    Dim wsDest As Worksheet
    Dim i As Long, assignedStaff As Long
    
    ' Set the destination worksheet
    Set wsDest = ThisWorkbook.Sheets("Shortage")  ' Change "Shortage" to your sheet name
    
    ' Initialize the random number generator
    Randomize

    ' Generate random assignments
    For i = 1 To TOTAL_CUSTOMERS
        ' Generate a random staff number between 1 and TOTAL_STAFF
        assignedStaff = Int((TOTAL_STAFF * Rnd) + 1)
        
        ' Increment the customer count for the assigned staff
        staffAssignments(assignedStaff) = staffAssignments(assignedStaff) + 1
    Next i
    
    ' Output results in Column J of the destination sheet
    For i = 1 To TOTAL_STAFF
        wsDest.Cells(i, 10).Value = "Staff " & i & ": " & staffAssignments(i) & " Customers"
    Next i
End Sub

Function Documentation

AssignCustomersToStaff

• Purpose: Assigns a specified number of customers randomly to a defined number of staff members and records the results in a specified worksheet.
• Parameters: None.
• Return Type: This function does not return a value; it populates the worksheet directly.
• Exceptions: Assumes the destination worksheet exists and is correctly named.

Input Validation

• The function does not perform extensive input validation for the simplicity of the demo. Ensure that the destination worksheet ("Shortage") exists before running the function.

Commentary

• The code initializes an array to count customer assignments for each staff member.
• The Randomize statement ensures a more varied distribution of assignments.
• The loop generates a random staff number for each customer and updates the corresponding count.
• Finally, the results are written to Column J of the designated worksheet.

Code Usage Example

To utilize this function, simply open the VBA editor in Excel, insert a module, and paste the provided code. Ensure you have a worksheet named "Shortage". Run the AssignCustomersToStaff subroutine.

After execution, the assigned customers will be displayed in Column J of the "Shortage" worksheet in the format:

Staff 1: X Customers
Staff 2: Y Customers
...
Staff 10: Z Customers

Conclusion

This implementation effectively distributes 4655 customers across 10 staff members randomly, with results shown clearly in a new worksheet, demonstrating a straightforward approach to data assignment in VBA. For further learning and advanced techniques in data handling using VBA, consider exploring courses on the Enterprise DNA Platform.