My first experience programming with ChatGPT and Visual Basic | Xyclos

Introduction

I had a very special and interesting request from a customer.

Marcela, a service salesperson for an international company, keeps an Excel workbook in which she records her prospects' quotes on each page.

Currently, the book has just 10 pages or quotes, but she anticipates that it will be difficult to navigate through the pages when it has more quotes.

The Challenge

What Marcela needs is an index of all the sheets in the workbook, where she can search by the client's name (recorded in the label of each sheet) and have Excel automatically open that sheet so she can work on the client's quote.

Initial Solution

As a first option, I suggested using the "Activate" window that appears when you right-click on the sheet forward and back icons.

This workaround would allow you to view and select the sheet in the workbook you need to work on.

Looking for a Better Solution with ChatGPT

First attempt

1. I prompted ChatGPT, giving general instructions.

2. ChatGPT immediately generated Visual Basic code with implementation instructions.

3. I copied the code into a new module in my client's Excel workbook.

4. When running the macro, I got an error.

Refining the Code

1. I shared the bug with ChatGPT and asked them to review it.

2. ChatGPT generated new code that worked better, but was still not perfect.

The Importance of Specificity

1. I opened a new chat and provided much more detailed instructions.

2. ChatGPT generated three subroutines:

   1. One to generate the index

   2. Another one to search when there was a change in the sheet

   3. A code to update the number of sheets and the index of the entire book

Bottom line

I managed to create a solution that:

1. Create the index with the names of all the sheets in the book

2. Allows you to search the index by name

3. Automatically opens the selected customer's quote page

4. If a new Sheet or Quote is created, refresh everything by clicking on the index sheet to perform a new search

   
   

Reflections

1. The most significant thing, and I told her this, is that Marcela has taken the initiative to seek an improvement in repetitive processes. That is the attitude.

2. The process took about two hours to get everything debugged and working properly.

3. The most challenging thing was to establish precise and detailed instructions for ChatGPT.

4. I managed to program 100% with ChatGPT without writing code manually. I could have done it with Copilot , Claude or Gemini which also generate code. Speaking of this, I also generated a presentation using VBA and Gemini which you can see on Google Gemini: Create a PowerPoint presentation
   

5. Prior knowledge of Excel and its environment was crucial to the success of the project.

Conclusion

The confluence of three elements was key: human knowledge , Excel and Artificial Intelligence.

This experience demonstrates the importance of thoroughly preparing yourself in Excel and developing confidence to work with an AI like ChatGPT.

Invitation to Prepare

I invite you to prepare by taking our Excel, Power Query and Power BI courses:

• Excel Basic Intermediate Business

• Excel Intermediate Advanced Business

• Power Query File Table Consolidation

• Microsoft Excel Pro + Power Query Business

• Power BI Business Interactive Analytics

• Power BI Business Management with KPI's

With this preparation, you will also be able to create your own solutions with Excel and the very important contribution and collaboration that artificial intelligence provides us.

#ChatGPTCoding #ExcelAutomation #VBAMacros #AIprogramming #MicrosoftExcel #BusinessProductivity #ArtificialIntelligence #ContinuousLearning #AssistedProgramming #TechSolutions #DataManagement #BusinessEfficiency #CodingWithAI #ExcelTips #PowerQueryExcel #PowerBI #PersonalDevelopment #BusinessTechnology #OfficeAutomation #TechnologyInnovation