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.

1. 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”.
2. Create a new worksheet in the same file and name it “Result”.
3. A1 is called “Date”, and from there, column A can be filled in by loading the numbers in the file name.
4. Write the title as “Name” for B1, “Time” for C1, and “Data” for D1.
5. Copy the names from B2 to B1 of “Database” to B2 to B25 of “Result”.
6. Fill in the numbers 1 through 24 in order from C2 to C25.
7. In D2~D25, copy the data of B4~B27 of “Database” as it is.
8. Copy the names from B26 to C1 of “Database” to B26 to B49 of “Result”.
9. Fill C26~C49 with numbers 1 through 24 in order.
10. In D26~D49, copy the data of C4~C27 of “Database” as it is.
11. 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.
12. 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:

Sub processData()

    Dim Database As Worksheet
    Dim Result As Worksheet
    Dim row As Long, col As Long
    Dim dataStart As Long
    Dim fileName As String, dateString As String, formattedDate As Date

    ActiveSheet.Name = "Database"
    Set Database = ThisWorkbook.Worksheets("Database")
    Set Result = ThisWorkbook.Worksheets.Add
    Result.Name = "Result"
    fileName = ThisWorkbook.Name
    dateString = Mid(fileName, InStr(fileName, "_")   1, 8)
    formattedDate = DateSerial(Left(dateString, 4), Mid(dateString, 5, 2), Right(dateString, 2))

    With Result
        .Range("A1") = "Date"
        .Range("C1") = "Name"
        .Range("D1") = "Time"
        .Range("H1") = "Data"
    End With

    row = 2
    col = 2

    Do While Not IsEmpty(Database.Cells(1, col))
        dataStart = 4
        For i = 1 To 24
            Result.Cells(row, "A").Value = formattedDate
            Result.Cells(row, "A").NumberFormat = "yyyy-mm-dd"
            Result.Cells(row, "C").Value = Database.Cells(1, col)
            Result.Cells(row, "D").Value = i
            Result.Cells(row, "H").Value = Database.Cells(dataStart   i - 1, col)
            row = row   1
        Next i

    col = col   1
    Loop

    Result.Cells(1, "A").Select
End Sub

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.

Reference

WIKIPEDIA: Visual Basic for Applications (VBA)

Other Stories