Automate Excel with VBA and ChatGPT

If you've ever spent hours cleaning up repetitive data in Excel, you know how tedious and error-prone it can be. The good news is that Excel has built-in tools that can help us automate those boring tasks and save yourself tons of time.

In this article, I will show you how to automate Excel with VBA and ChatGPT using Excel's hidden Macro Recorder and AI-powered code improvement. This combination will help you work like a pro, eliminating manual errors and speeding up your workflow.

Let's dive into a practical example where we take raw order data and transform it into a clean, formatted report with just a click.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Downloads

Understanding the Problem: Manual Data Cleanup

Every week, you might receive raw order data that requires cleaning and formatting before it can be used for reporting. This often involves multiple repetitive steps such as:

• Converting raw data into a proper Excel table
• Inserting header rows for titles and dates
• Sorting data by specific columns
• Applying conditional formatting to highlight important values

Doing these tasks manually is time-consuming, boring, and prone to errors. This is especially true when you have to repeat them weekly or monthly. The key is to automate this entire process so you can focus on more valuable work.

Step 1: Enabling the Developer Tab and Recording a Macro

The first step is to use Excel's Macro Recorder, a hidden but powerful tool. It records your actions and converts them into VBA code that can be run automatically later.

If you don't see the Developer tab on your ribbon, here is how to enable it:

1. Right-click anywhere on the ribbon and select Customize the Ribbon.
2. In the right pane, check the box next to Developer.
3. Click OK to enable the Developer tab.

Once enabled, go to the Developer tab and click Record Macro. You will be prompted to name your macro (no spaces allowed, so use underscores). Choose to store it in the Personal Macro Workbook so it will be available across all your Excel files.

Now, every action you perform in Excel will be recorded as VBA code until you stop the recording.

Step 2: Recording the Data Cleanup Process

With recording started, perform the following steps to clean up your data:

1. Select any cell in your raw data and convert it to an Excel table (Insert tab → Table).
2. Insert three blank rows at the top to create space for the report title and date.
3. Type your report title in cell A1 and format it with a bold font size of 16.
4. In the new header rows, add the word “Date” and use the TODAY() function to insert the current date.
5. Replace the TODAY() formula with its static value to avoid automatic updates.
6. Sort the table by the Product ID column to group similar products.
7. Apply conditional formatting to the Quantity column to highlight values greater than 8 with a red fill.

Once done, stop the macro recording by clicking the stop button either in the Developer tab or the status bar at the bottom.

Step 3: Viewing and Testing the Recorded Macro

Now that you have a macro recorded, let's see the code behind it:

1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
2. In the Project window, find the PERSONAL.XLSB file and open the relevant module (e.g., Module1).
3. Review the recorded VBA code. It will contain all the steps you performed, but usually with hard-coded cell and range references.

To test the macro, open a new Excel workbook with raw order data and run the macro by pressing Alt + F8, selecting your macro, and clicking Run. The macro will perform all the cleanup steps automatically.

Step 4: Identifying Limitations of the Macro Recorder

While the macro recorder is amazing for capturing your workflow, it has some limitations:

• Hard-coded references: The macro uses fixed cell ranges and sheet names, which break when your data size changes or sheet names differ.
• Static ranges: The table range won't automatically expand to include new rows added to your data.
• Sheet name dependencies: If the macro references a specific sheet name, it will cause errors if run on a differently named sheet.

These issues mean you often need to manually update the macro code to accommodate new data or different files, which defeats the purpose of automation.

Step 5: Using ChatGPT to Make Your Macro Dynamic

This is where AI comes in to save the day. Instead of writing complex VBA code yourself, you can use ChatGPT or similar AI tools to clean up and improve your recorded macro.

Here's how to do it:

1. Copy the entire recorded macro code from the VBA editor.
2. Paste the code into ChatGPT with a prompt asking to make the macro more dynamic and flexible.
3. Answer any clarifying questions ChatGPT may have about your data or desired functionality.
4. Receive a refactored version of the macro that uses variables, dynamic ranges, and better programming practices.

The improved macro will:

• Automatically detect the size of your data and adjust the table range accordingly.
• Use variables instead of hard-coded values for sheet names and ranges.
• Handle inserting header rows and formatting in a more adaptable way.

Step 6: Implementing and Testing the Improved Macro

Once you get the improved macro code from ChatGPT, paste it back into your VBA editor in a new module or overwrite your existing macro (changing the macro name if needed to avoid conflicts).

Then, test the macro on new raw data files with varying row counts to verify that:

• The table expands to include all rows.
• Header rows and formatting are correctly applied.
• Sorting and conditional formatting behave as expected.

This dynamic macro lets you automate your data cleanup process with confidence that it will work on different files without manual code edits.

Step 7: Leveraging the Personal Macro Workbook for Convenience

Storing your macros in the Personal Macro Workbook means they are available whenever you open Excel, regardless of which file you are working on.

• You don't need to save macros inside each individual file.
• You can run your macros on any open workbook instantly.
• You can create custom buttons or tabs on the ribbon to run your favorite macros quickly.

This setup streamlines your workflow and makes macro automation more accessible across your Excel projects.

Checkout my video series on the Personal Macro Workbook and Custom Ribbon Macro Buttons to learn more about this awesome feature of Excel.

Final Thoughts on Automate Excel with VBA and ChatGPT

Using the Macro Recorder combined with AI tools like ChatGPT is a game changer for automating Excel tasks. The Macro Recorder captures your manual steps quickly, and ChatGPT helps you transform that recorded code into dynamic, reusable VBA scripts.

This approach:

• Saves you hours of repetitive work
• Reduces errors caused by manual cleanup
• Allows you to handle varying data sizes and formats without rewriting code
• Enables you to run powerful macros on any file from your Personal Macro Workbook

While AI tools are not perfect and some VBA knowledge is helpful, this combination makes macro automation accessible to more users than ever before.

If you want to take your Excel skills to the next level, consider exploring online VBA training courses to complement your AI-assisted automation journey.

What Excel task will you automate next? Feel free to share your ideas or questions in the comments below. If you found this guide helpful, be sure to like and subscribe for more tips on working smarter with Excel.