Boring Tasks, Begone! ChatGPT and VBA to the Rescue
Use ChatGPT to write VBA code and put some excitement back into your life!
Want to get the most out of this newsletter? Download the sample data so that you can follow along!
Are you tired of doing the same thing over and over again in Excel? Do you wish you could push a button and have all your boring tasks done for you? Well, get ready for an adventure! Today I'm going to show you how ChatGPT can help you write VBA macros.
With ChatGPT, you don't need to be a programming genius to write VBA macros. Tell ChatGPT what you want your macro to do and it'll generate the code for you! It's like having a personal Excel genie - but without all the annoying rules and restrictions.
Below I lay out the 5 steps to your freedom from boring. Make sure you download the sample data so that you can follow along.
Step 1: Enable the Macros in Excel
Before you can start writing VBA macros in Excel, you need to enable Macros to be used in your sheets.
To do this click on “File” in the Excel Ribbon, and then “Options”.
Go “Trust Center” and then “Trust Center Settings”.
Finally select “Macro Settings”, then “Enable VBA macros”.
One more thing - we want to Enable the Developer tab in the Excel Ribbon.
To do this, click on "File" in the Excel ribbon, then click on "Options", again.
In the "Excel Options" dialog box, click on "Customize Ribbon" in the left-hand pane.
Check the box next to "Developer" in the right-hand pane. Click "OK" to save the changes.
Note: you will have to perform a “Save As” on the sheet in order to use the macros. It will have to be saves as a “.xlsm” file.
Step 2: Review the task at hand
Here is a snapshot of our data set:
In this example we have 5, yes 5, tasks we would like to accomplish. There are almost 200 lines of data and we don’t have much time!
Step 3: Ask Chat GPT to Write your VBA code
Normally to write a VBA macro, you need to know the VBA syntax and commands. The VBA language is similar to other programming languages, such as C++ and Java, but with some differences.
With ChatGPT you can skip this step. Instead you can ask it to write you a code by simply describing what you want in plain English. The trick here is knowing how to describe what you want. Here is the prompt we will use:
Next we will open ChatGPT and enter our prompt. Click HERE to access the home page.
NOTE: I used ChatGPT 4.0 for this example. Other ChatGPT versions may not provide similar results.
Once it is done creating the script click on the “copy code” located in the top right hand corner.
Step 4: Open the Visual Basic Editor & Paste the macro into the Editor
You can open the Visual Basic Editor by clicking on "Developer" in the Excel ribbon, and then clicking on "Visual Basic". The Visual Basic Editor is where you write and edit your VBA code.
Click on Insert → Module. Then paste the code you copied from ChatGPT:
Step 5: Run your Macro!
This is the fun part! Go to the top menu and click on Developer → Then Macros. Select your Macro and then press Run.
Check the final results to see if you got the intended outcome. If you didn’t, try again. It will take a little bit of practice to nail it every time. Also, try stepping out each operation and have chat write out code step by step. This is actually a really great way to learn how to code in VBA.
One more thing, ChatGPT 4.0 is far better at writing consistent code than the legacy versions. For best results use version 4.0.
This was a bit of an advanced Excel assignment, but you can follow along and practice! I’ve made this super easy for you by including the data set I used in the example. You can find it at the very start of this newsletter. Once you start using VBA to up your Excel game you will join a very elite group of Excel users!
I hope you enjoyed this newsletter. There are a few other ways I can help you master Excel:
Want to learn more Excel Shortcuts? I have created something special (and free) for you: The BEST DAMN Excel Shortcut Sheet. PERIOD.
Do you have any burning questions about Excel that you want answered? Send me your question here. I will address it in a future newsletter.
Do you or your organization need a little spreadsheet help? Book a 1 on 1 with me here.
Til next time ✌️
-Ajay
The Excel Ninja 🥷
PS - If you like these tips & tricks please share this newsletter with a friend or two...