I Trained ChatGPT How to Automate Excel

Ever wondered how to use ChatGPT to write and troubleshoot VBA macros? In this video, I walk you through how I used AI to create an Excel macro, fix errors, and optimize my workflow using the ODA Prompting Framework. Whether you're new to VBA or looking to refine your automation skills, this guide will help you unlock new levels of efficiency!

🔗LINKS


Related Content:

00:00 Introduction
00:45 Creating a VBA Macro with ChatGPT
01:00 Providing the Prompt
01:30 Copying the Code
02:45 Testing the Macro
03:10 Troubleshooting Errors
07:40 The ODA Prompting Framework

[heatherr]

I love the fact that your framework encourages the user to ask the AI bot about its own understanding of the request - this can clear up SOOOO much confusion from the AI response. Still not perfect, but it does prevent a lot of errors.

And yes, it really helps to have your own basis of knowledge to work from. AI tools are just that, tools, but a good human brain is still very useful in partnership with Chat GPT, Copilot, etc.

[duncanwil]

I spent a whole day a few months ago training chatGPT to find and correct the 10-15 errors that were in the 30 or so csv files I work with. As you have found: prompt… revise prompt … revise prompt based on the learning that’s taken place. Some things took a lot of explanation, it grasped others immediately. I agree with your comment in which you say, with VBA, for example … or Python or M or DAX… share all error message with it. Sometimes it revises its work as a result but sometimes it takes it further down a rabbit hole. So, a whole working day that I found really rewarding and we solved my problems. Then, of course, the REGEX functions arrived and changed the entire error correction world for the better.

[JG-wmhh]

I have used this concept earlier this week to write a macro to copy and save a file in folder and then draft an email to a specific individual and even included an HTML table as summary of part of the context in the spreadsheet. get started with a record macro and than had ChatGbt finish up the VBA

[peterjames]

Doing simiar, writing the question, in word, reviewing and pasting into GPT. If not correct updating my Word prompt and go again. It does work, what i ended up with which i was not expecting is nice bunch of prompts i can re use.

[Jolmoodtube]

I am under the impression that GPT is better at python than VBA and so for heavy automation I use it to make python codes for excel. If you don't know python or how to install and use it. Just ask GPT for a step by step guide on how to install pyhton

[iankr]

Many thanks, Jon. Very useful advice.
Chat GPT aside, in my experience you can't do *anything* (even from the UI), with grouped sheets containing structured tables. Annoying, but I can kind of see why!

[jerrydellasala]

Macro tip - before attempting to run a macro, with the cursor within the macro select Debug -> Compile Project. That's kind of like running the macro without it doing anything. It found two instances of "Option Explicit" after the first macro which isn't allowed. Great tip about Chat GPT which ironically is a LOT better with Excel / Power Query than Copilot! I needed a moving average formula and Copilot couldn't get it right after 4 tries - and I'm using the 365 version which I let roll into my 365 subscription, so i am paying for it. Chat GPT got it on the first try with the free version. Just sayin....

[kylejones]

I got this prompt from Twitter back in the GPT 3.0 days… Dots still extremely effective today: Please forget all prior prompts. I want you to become my Prompt Creator. Your goal is to help me build the best detailed prompt for my needs. This prompt will be used by you, ChatGPT. Please follow this process: 1) Your first response will be to ask me what the prompt should be about. I will provide my answer, but we will need to improve it through continual iterations by going through the next steps. 2) Based on my input, you will generate 3 sections. a) Revised prompt [provide your rewritten prompt. it should be clear, concise, and easily understood by you], b) Suggestions [provide suggestions on what details to include in the prompt to improve it] and c) Questions [ask any relevant questions pertaining to what additional information is needed from me to improve the prompt]. 3. We will continue this iterative process with me providing additional information to you and you updating the prompt in the Revised prompt section until it’s complete. If you understand this respond with >

[salvadorepozzi]

Always wonderful information... Thank So Much!

[AI-snack]

8:39 you could just write the corresponding number of the question and write yes or no. 1) yes, 2) no.. etc. Or ask ChatGPT to give you the questions one by one. Nice work around.

[patrickkawooya]

How does it compare to reasoning models on similar vba task challenges

[smeaden]

Did you actually train Excel? I doubt this, it already knew before you arrived is more likely.

[parth]

o3-mini-high is THE BEST. A bit slow but very very very powerful