Create Org Chart with Excel VBA

To create an organizational chart (Org Chart) in Excel using VBA, you can automate the process by generating shapes and connectors to visually represent the hierarchy. Here’s a detailed example of how to create an Org Chart with VBA:

Step-by-Step Explanation:

1. Data Structure: We assume the data for the Org Chart is stored in a simple table format in Excel, where each row represents an employee, and columns contain information like Employee Name, Manager, and Position.

Example:

2. VBA Code Structure: The code will loop through the data, create shapes for each employee, and draw lines to represent the hierarchical structure.

VBA Code Example:

Sub CreateOrgChart()
    Dim ws As Worksheet
    Dim employeeRange As Range
    Dim employee As Range
    Dim empName As String
    Dim empManager As String
    Dim empPosition As String
    Dim shapesDict As Object
    Set shapesDict = CreateObject("Scripting.Dictionary") ' To store shapes by name
    ' Define the worksheet containing data
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Define the range of data (starting from A2 to C last row)
    Set employeeRange = ws.Range("A2:C" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
    ' Clear any existing shapes in the worksheet
    ws.Shapes.SelectAll
    Selection.Delete
    ' Loop through each employee row
    For Each employee In employeeRange.Rows
        empName = employee.Cells(1, 1).Value
        empManager = employee.Cells(1, 2).Value
        empPosition = employee.Cells(1, 3).Value
        ' Create shape for employee
        Dim empShape As Shape
        Set empShape = ws.Shapes.AddShape(msoShapeRectangle, 0, 0, 120, 60) ' Shape properties
        empShape.TextFrame.Characters.Text = empName & vbCrLf & empPosition       
        ' Assign unique name to the shape
        empShape.Name = empName
        shapesDict.Add empName, empShape       
        ' Position the employee shapes based on some basic logic
        ' Example: simple position grid
        If shapesDict.Count = 1 Then
            empShape.Left = 100
            empShape.Top = 100
        Else
            empShape.Left = 100 + (shapesDict.Count * 150)
            empShape.Top = 100 + (Int((shapesDict.Count - 1) / 3) * 100)
        End If       
        ' Create a connector if the employee has a manager
        If empManager <> "" Then
            Dim managerShape As Shape
            Set managerShape = shapesDict(empManager)
            ' Create a connector line between manager and employee
            Dim conn As Shape
            Set conn = ws.Shapes.AddConnector(msoConnectorStraight, managerShape.Left + managerShape.Width / 2, managerShape.Top + managerShape.Height, _
                                               empShape.Left + empShape.Width / 2, empShape.Top)
            conn.Line.EndArrowheadStyle = msoArrowheadTriangle
        End If
    Next employee
    ' Optionally, adjust the zoom level to fit the org chart
    ws.PageSetup.Zoom = False
    ws.PageSetup.FitToPagesWide = 1
    ws.PageSetup.FitToPagesTall = 1
End Sub

Code Explanation:

1. Setup Worksheet and Range:
   • The code begins by setting the worksheet (ws) and the range (employeeRange) to the table that contains employee data.
   • The range is defined from A2 to the last used row in column C (C being the « Position » column).
2. Clearing Existing Shapes:
   • It deletes any existing shapes before creating new ones to ensure a clean workspace.
3. Looping Through Employee Data:
   • The For Each loop processes each employee row, where the empName, empManager, and empPosition values are extracted.
4. Creating Employee Shapes:
   • A rectangle shape is created for each employee using ws.Shapes.AddShape, with their name and position added as text inside the shape.
   • The employee’s name is used to uniquely identify the shape in the dictionary (shapesDict), which allows you to easily reference it later when connecting shapes.
5. Positioning Shapes:
   • A basic positioning strategy is applied to ensure that the shapes don’t overlap. This simple logic places shapes in a grid, with each new employee being placed 150 pixels to the right and 100 pixels down as the count increases.
6. Connecting Employees to Managers:
   • If an employee has a manager (i.e., empManager is not empty), a connector (line) is drawn from the manager’s shape to the employee’s shape.
   • This is done using ws.Shapes.AddConnector, with properties like arrowhead styles set for better visualization.
7. Fit the Org Chart to Page:
   • Finally, the code adjusts the print settings so that the entire Org Chart fits within one page when printed.

Customization:

• Shape Styling: You can change the shape dimensions, color, font, and alignment to make the Org Chart look better.
• Positioning Logic: You may want to customize the position of shapes to improve the layout, especially for larger organizations. You could also use more advanced layout algorithms.
• Multiple Levels: If you need to handle multiple levels of hierarchy more visually (e.g., placing managers at a higher level and their subordinates below), you can introduce more sophisticated positioning rules based on depth in the hierarchy.