Creating a Project Updates Tracker in Excel Using VBA
Three simple steps to track project updates in Excel and log them
Oftentimes, we are involved in multiple projects in our jobs. Each project involves several tasks or sub-tasks within it. It is a good practice to track the status of these tasks and projects for project management. These project tasks or updates can be used for our knowledge as well as for sharing information during project meetings. There are different free or commercial project management tools available in the market, which serve similar purpose. However, I wanted to create a simple Excel-based tool for my use using Visual Basic Applications (VBA).
The functionality of VBA is very broad. It can be used to automate data processing, data analysis, and data visualization. This makes working and handling large datasets within Excel very convenient. One of the facts about VBA is that the VBA codebase is not updated regularly similar to different packages in Python. It can be regarded as both a merit and demerit in different contexts. However, one of the merits is that once you learn VBA, you can use the same knowledge in the future. You don’t need to be updated with new versions or new features in VBA now and then, because there aren’t any (unless Microsoft decides to introduce new features).
In one of my previous posts, I used VBA for time series resampling.
In this post, I am going to share how I created a simple project updates tracker tool for myself using Visual Basic Applications (VBA) in Excel in three simple steps. Let’s get started.
Aim
I aimed to create an application for tracking project updates and logging them. I wanted to have one sheet, where I entered each new update for a task within a project. I also wanted to track the time I logged this information in my file. With the click of a button, I wanted to move each new update along with the time it was logged to a separate sheet for logging the information.
For this purpose, I created an Excel file with two different sheets. The first sheet was called ProjectTasksTracker, and the second sheet was called Logbook. Both these sheets comprised of same header row containing six columns: DateTime, Project, Tasks, Responsible Staff, Status, and Updates.
I used the =NOW() function in Excel in the DateTime column to get the real time. I allowed three options in the dropdown menu in the Status column: Started, In Progress, and Complete. I also created a button named Update Logbook to log all the information automatically without duplication in the Logbook sheet. The ProjectTasksTracker sheet looked like shown below:
Coding Steps
I started with creating a subroutine inside a module in VBA.
- The first step involved defining the workbook object
wbfor the file and two worksheet objects,ws1andws2for ProjectTasksTracker sheet and Logbook sheet respectively. The code is given in the snippet below:
'Define workbook and two worksheets.
Dim wb As Workbook
Dim ws1 As Worksheet 'Project tracker worksheet
Dim ws2 As Worksheet 'Logbook worksheet
Set wb = ThisWorkbook
Set ws1 = ThisWorkbook.Sheets("ProjectTasksTracker")
Set ws2 = ThisWorkbook.Sheets("Logbook")2. The second step involved writing code to count the number of rows and columns in the two sheets. This can also be done manually. However, since the number of rows can change while entering project updates, this process is updated. The number of columns is kept fixed (6) to keep the structure of the two sheets consistent. However, it is also coded for demonstration purposes.
In the code snippet below, lr1 counts the number of rows in the worksheet ws1 based on column A. lc1 counts the number of columns in the same worksheet based on row 1.
'Count the number of rows and columns in ProjectTasksTracker sheet
Dim lr1, lc1 As Integer
lr1 = ws1.Cells(Rows.Count, “A”).End(xlUp).Row
lc1 = ws1.Cells(1, Columns.Count).End(xlToLeft).Column Note: It is possible to use the reference to a specific cell while working with macros. This is useful while working with datasets with the possibility of changes. For example, I created a named range Updates to refer to cell F1 in the ProjectTasksTracker sheet. In case, one column is added before it, Updates will then refer to cell G1.
Updates named range is referred to in the code with update_cell as shown below. The column number it belongs to is referred to with update_column and the column number in terms of alphabet is given by update_col.
Dim update_cell As Range
Set update_cell = ws1.Range(“Updates”)
Dim update_column As Integer
update_column = update_cell.Column
Dim update_col As String
update_col = Chr(update_column + 64)
MsgBox "Update column belongs to: Column " & update_col
In the code in the next step, we are going to refer to the Updates column with column number 6 directly for convenience.
3. The third step is the most important in this process. In this step, I looped through each row (except the header row and Datetime column) in the ProjectTasksTracker sheet and performed the following operations given as three sub-steps:
a. For each row in the ProjectTasksTracker sheet, I checked whether the Updates column is empty or not for each task. If a particular row in ProjectTasksTracker had updates, then I counted the number of rows in the Logbook sheet and assigned the count as an integer called lr2. Furthermore, I declared a boolean datatype called valuesMatch and assigned it as False by default.
b. Next, I created a nested loop to loop through each row in the Logbook sheet and checked whether the content of each column of the row in the ProjectTasksTracker sheet (defined as range rg1) matched with the content of each column of any row in the Logbook sheet (defined as range rg2). If there are no matches between rg1 and any value of rg2, then it would mean that the update in a particular row in the ProjectTasksTracker sheet had not been logged into the Logbook sheet before. The valuesMatch would remain False. If the content of a row in the ProjectTasksTracker sheet matched with any row in the Logbook sheet, then it meant that the row had already been logged before. In that case, the value of the valuesMatch would be changed to True.
c. If the valuesMatch was True at the end of both for-loops, then there would be no further processes. If the valuesMatch was False at the end of two for-loops, then the row from the ProjectTasksTracker sheet (including the Datetime column) would be copied and pasted to the Logbook sheet.
The steps 3a, b, and c have been coded in the gist below:
Demonstration
The plot below shows the updates in the ProjectTasksTracker sheet as of 8/20/2023 23:32.
These updates have already been logged into the Logbook sheet as shown below on 8/20/2023 itself.
Next, on 8/29/2023 23:38, I made some changes in the ProjectTasksTracker sheet as highlighted by the red color (made changes in the first two rows and added the last row). Then I clicked on the Update Logbook button, to which the macro described in the Coding Steps section above is assigned.
These new changes are then logged into the Logbook sheet. The rows on the bottom highlighted in red color are the changes made on 8/29/2023. Other updates that were logged earlier stay the same.
Conclusion
In this post, I described some coding steps to create a simple tracker in Excel to enter project task updates and log them. If some changes or additions are made in the ProjectTasksTracker sheet and the macro is run, this would copy and paste those updates into the Logbook sheet. However, if there are no changes in the ProjectTasksTracker, the updates will stay the same in both sheets after clicking the button.
It is also possible to create additional functionalities such as sorting the rows in the Logbook sheet in a particular order at the end. And it is also possible to create a new file to log the project updates instead of logging them in a separate sheet in the same Excel file. In that case, the workbook and worksheet destinations need to be redefined in the code. These steps are not included in this post to keep it simple. The code and macro-enabled Excel file used in this post are available in this GitHub repository. Thank you for reading!
