How to use Excel macros in construction planning and scheduling

One of our students in the PPE Course (Professional Planning Engineer) asked about Excel macros. In the PPE Course, we teach planning engineers how to automate most of their work by using Excel macros In Construction Planning. This article will explain the macros, why we need them, and how to create and modify them easily.

What is an Excel macro?

An Excel macro is a code or instructions that uses Microsoft Excel to automate processes. Macros are created using Visual Basic for Apps (VBA), a programming language used by Excel and other Office apps.

Why might we need to use macros?

Excel macros can be handy for various reasons, especially when dealing with huge databases, repeated operations, or complex workflows. Here are some of the reasons you may need to use Excel macros:

1. Automation of Repetitive Tasks:

Automate repetitive processes like cell formatting, data copying, and report generation to save time and effort.

Macros reduce the risk of human mistakes by ensuring consistent task performance.

For Example: copying data from one sheet to another, modifying activity ID, adding BOQ reference

2. Handling Large Databases:

Manual processing and analyzing huge databases can be time-consuming. Processes can be sped up by using macros, which can sort data, apply filters, and execute calculations quickly.

Macros enable complicated data transformations and calculations that are difficult or impossible to perform manually.

For Example: If you want to add a certain column in 25 sheets.

3. Custom Workflows:

Macros enable the creation of personalized workflows based on certain requirements or procedures in your company. This might be merging various databases, automating intricate action sequences, or creating personalized reports.

Enhanced capability: You may create solutions that are unique to your needs by extending Excel’s capability beyond its built-in capabilities with the help of VBA.

For Example: Colorize the file with WBS colors, sort data, calculate the earned value, and then tell which activity is delayed

4. Reporting and Analysis:

Macros could automatically create and structure reports by incorporating data from many sources, using standardized formatting, and, if required, sending the results via email.

Macros automate data analysis tasks, such as applying statistical models, producing pivot tables, and displaying data using charts and graphs.

For Example: Convert Primavera XER to an Excel Sheet with the WBS coloring tool.

RECOMMENDED COURSE

Data Management and Reporting

Data Management is the key success of construction projects reporting. This self-study course will teach you using a step by step case…

• 1,674 students
• WhatsApp support group
• Certificate

View course

5. Timesaving:

• Reduce Manual Work: By automating mundane tasks, you can focus on your work’s more strategic or analytical aspects.
• Batch Processing: Macros can perform tasks on multiple files or datasets in one go, which is much faster than manually.

6. Error Reduction:

Macros improve accuracy by automating procedures with several steps or complex calculations, reducing the possibility of errors compared to human tasks.

Macros ensure constant process execution, especially for precision tasks.

7. Learning and Skill Development:

Writing and utilizing macros can enhance your comprehension of VBA (Visual Basic for Applications), a crucial skill for Excel professionals.

Creating macros promotes logical thinking and problem-solving by designing an automated solution for your desired activity.

How to enable it

To enable macros in Excel, follow these steps:

1. Open Excel: Launch Excel and open the workbook that contains the macro.
2. Go to the “File” Tab: Click on the “File” tab in the top-left corner.
3. Access Options:

·         Click on “Options” at the bottom of the left-hand menu.

·         In the “Excel Options” window, select “Trust Center” from the left-hand menu.

4. Trust Center Settings:

·         Click on the “Trust Center Settings” button.

·         In the “Trust Center” window, select “Macro Settings.”

5. Enable Macros:

·         Choose the option “Enable all macros” (not recommended; potentially dangerous code can run) or “Disable all macros with notification” (this will prompt you whenever a macro is present and allow you to enable it manually).

·         Click “OK” to save your settings.

Detailed tutorial on how macros can help you

For example, here is a video showing the power of the macro in planning and how to use it.

In this video, we have a huge project BOQ sheet not organized but by Power of Macros, the sheet will get well organized by adding reference number columns well organized in a few minutes.

For More helpful macros, Tips, And Tricks to handle huge data and projects you can sign in to the PPE Course (Professional Planning Engineer), the course also includes practical applications of planning techniques through a project management development plan. This plan will involve creating a project from scratch aimed at improving the project management abilities of planning engineers.

Download the Resources of this Article

How to use Excel macros in construction planning and scheduling 487.97 KB 1257 downloads

This File is included in the following course(s)

Intermediate, Professional Planning

---

Keep in Touch

Join our Email Newsletters and receive FREE weekly useful content!

Your Name

Email

---

Related posts:

Block work Method Statement WBS Dictionary Template Complete Guide to Using Store Period Performance in P6 Principles of Schedule Impact Analysis Text book Primavera P6 Calendar 10 Tips and Tricks