The Problem: Converting Daily Monitoring Data
Working with Repetitive Tasks and VBA
While working at a company handling repetitive tasks, I frequently used VBA to automate my workflow. Having already tested my Python and LISP coding skills with ChatGPT, I was eager to try it with VBA. The challenge I faced was to convert the daily monitoring data of a solar power plant from an automatically generated Excel format to another format.
Company Security Policy and Python Limitations
Ideally, I could gather data for a month and process it using Python. However, due to the company’s security policy, the Excel files were automatically password-protected, preventing this approach. My goal was to reduce a task that would take me an hour manually to just 10 minutes by automating it using VBA in Excel.
The Solution: ChatGPT and VBA Integration
Detailed Instructions and Algorithm Requirements
As I had already practiced making requests to ChatGPT in Python and LISP, this challenge was a breeze. I provided a detailed explanation and laid out the requirements as if I were writing an algorithm. It’s interesting to think that while ChatGPT can generate code in various programming languages, ultimately, skilled developers still need to provide clear instructions for the best results. The algorithm I requested was as follows.
Write a VBA that automatically edits existing data in Excel. The algorithm is as follows.
- I don’t know where from column B, but the columns are filled with data until there is a space. For example, B1 is a name, and B4 to B27 are numeric data. Let’s call the current tab with data “Database”.
- Create a new worksheet in the same file and name it “Result”.
- A1 is called “Date”, and from there, column A can be filled in by loading the numbers in the file name.
- Write the title as “Name” for B1, “Time” for C1, and “Data” for D1.
- Copy the names from B2 to B1 of “Database” to B2 to B25 of “Result”.
- Fill in the numbers 1 through 24 in order from C2 to C25.
- In D2~D25, copy the data of B4~B27 of “Database” as it is.
- Copy the names from B26 to C1 of “Database” to B26 to B49 of “Result”.
- Fill C26~C49 with numbers 1 through 24 in order.
- In D26~D49, copy the data of C4~C27 of “Database” as it is.
- In this way, repeat steps 8 through 10 until the column becomes blank and attach the data of “Database” to the bottom of the “Result” tab.
- The program ends while selecting A1 in the “Result” tab.
The Outcome: Improved Efficiency and Time Savings
Before and After Running the Program
Two or three supplementary requests were faithfully fulfilled, and the VBA coding content that ChatGPT eventually created is as follows:
Here’s what the program looked like before and after running:
ChatGPT’s Impressive Coding Capabilities
One of the remarkable aspects of ChatGPT is its coding capabilities. Whether a developer or not, anyone can input coding commands at the prompt, and ChatGPT performs the task efficiently without getting tired or complaining. Although it might still struggle with complex or challenging code, its ability to handle simpler coding tasks can significantly reduce our workload.
The Future of Coding and Work with ChatGPT
In the future, I believe it will be hard to imagine coding and working without ChatGPT.