When there are many projects, it becomes difficult to keep track of how many each person is handling.
To solve this, we will create a macro that automatically counts how many times each person appears in the list and calculates the number of projects assigned to each person.
Creating a Macro
From the Developer tab, click Visual Basic.
Then, choose Module from the Insert menu, and this is where you will write your code.
First, we define a macro named CountPersons_SelectOutputCell.
Defining Variables
Next, we define seven variables using Dim.
① Dim ws As Worksheet
Here, we define the worksheet we will work on, and name it ws.
②Dim dict As Object
I declare a variable named dict with Dim dict As Object.
Later in the code, this variable is assigned a Dictionary object.
The dictionary stores data in a key-value structure, where the key is the staff member’s name and the value is the number of cases they handle.
In this example, we aggregated the number of cases for each staff member listed in column D, but there are many possible extensions, such as allowing the user to select the column to be aggregated by dragging.
The more cases you have, the more convenient this feature will feel.
変数を定義して、という所から始まるのはプログラミングと同じです。
