How to Create An Excel Macro with AI

Do you want to program an Excel Macro in less than 5 minutes, for free and without any previous programming experience?
Thanks to Artificial Intelligence, now it is possible

Keep reading, this post and with the help of chatGPT you will learn how to create an Excel Macro with AI (Artificial Intelligence)

The purpose of this post is simple, by the moment you finish reading this post, you will be able to program for free your own Excel Macro in less than 5 minutes with the help of Artificial Intelligence.

It sounds too good to be real.

Before jumping into the process of creating the Excel macro itself with AI,
let’s deep dive into what Artificial Intelligence (AI) is about and how it will change the world as we know it nowadays.

What is the Artificial Intelligence about

To put it very simply,
Artificial Intelligence is the combination of different programs, and the code of each of these programs emulates the way the human brain works.

In other words, Artificial Intelligence simulates the way we reason and think as humans.
At the moment of writing these lines (Feb 2023), without any doubts, the Ais can program better than you, are able to draw better than you and are even able to translate texts better than you.

At the same time,
the AIs are making mistakes with the simplest things, that you could ever imagine, this is normally behind a poor prompt provided to the AI.

Let’s see below what the prompts are, and how they will make a difference in the inputs you will receive.

How to give the proper prompt to the AI

The way Artificial Intelligence works is that you give input to your AI,
and the AI will generate an output based on your input.

The output that will be generated, will be only as good as the input you give it, what we call the prompt.

Meaning the input is the same as what we call in the AI world a prompt

The people, who will manage to get the most out of the AIs, are the ones who will learn how to properly interact with the AIs.
What it is being called prompt engineers.

Example of a good prompt to interact with AI:

Prompt:
You will behave as an experience programmer in VBA. I want the VBA code for placing a filter in the row 1, selecting the cell C1 and sorting the content of the column C from largest to smallest. Add the comment for each of the lines of code explaining what the code is doing.

Breaking down the prompt in a few parts

First, we tell the AI the behaviour we expect:
You will behave as an experience programmer in VBA.

Later we tell the AI the specifics of what we want:
I want the VBA code for placing a filter in the row 1, selecting the cell C1 and sorting the content of the column C from largest to smallest.

Finally, we ask for some details that we want to see in the code:
Add the comment for each of the lines of code explaining what the code is doing.

As a result of this prompt,
the AI will generate the following output ready to be used into your Excel Macro.

Sub SortColumnCDescending()

    'Place filter in row 1

    ActiveSheet.Range("1:1").AutoFilter

    'Select cell C1

    Range("C1").Select

    'Sort the content of column C from largest to smallest

    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range("C:C"), _

        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

    With ActiveWorkbook.ActiveSheet.Sort

        .SetRange Range("A:C")

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

End Sub

Now that you know some basics, let’s see step by step how to create your first Excel Macro using Artificial Intelligence, in this case we will use ChatGPT.

Do you know the best thing of ChatGPT? It if for free, gratis! .

Step by step on how to create an Excel Macro with chatGPT

Go to the chatGPT website and click on Try ChatGPT

Register on the platform (it is for free). You can use your Gmail account, or any other email account linked to you.

Once you are registered,
the following window will appear and you can start interacting with chatGPT.

It all starts with the prompt (input) you are giving to the program.

Imagine you want to program a Macro that allows you to:

• Place a filter in row 1
• Select the cell C1
• Sort from largest to smallest the content of the column C.

For this code I want to generate, I will give following prompt to the AI:

You will behave as an experience programmer in VBA. I want the VBA code for placing a filter in the row 1, selecting the cell C1 and sorting the content of the column C from largest to smallest. Add the comment for each of the lines of code explaining what the code is doing.

Include your prompt on the dialogue box at the bottom and click send:

The AI will start to generate the response.
Note: At the bottom there is the option of Stopping the AI of generating, in case you notice the information you are getting is not what you need.

Once the AI finished generating what you asked for, you will see the output including the information that you requested in your prompt.

Note – on the top right side corner, there is a Copy code functionality.

Sub FilterAndSort()
    ' Place a filter in row 1
    Rows("1:1").AutoFilter
    
    ' Select cell C1
    Range("C1").Select
    
    ' Sort the content of column C from largest to smallest
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("C1"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

This is very helpful, as with one click you will be copying the code, ready to be used in your Excel Macro.

Include that code generated by the Artificial Intelligence into your Excel Macro.
How to do it?: Developer/ Visual Basic / Insert Module

If you are not sure where to paste this code in your Excel Macro, discover here how to create your first Macro in Excel

Once you pasted the code into your Excel Macro.

Create a button and link it to the Macro you have just created.

After clicking on the button,
the prices in the column C will be sorted from largest to smallest

And that’s it,
in less than 5 minutes you will have a Macro fully functional and created with Artificial Intelligence.

If after this, you are not convinced about the benefits of using AI to create your own Excel Macros,
I bring a few reasons why you should start using AI to program your Excel Macro tools.

Benefits of using Artificial Intelligence to program Excel Macros

The five main reasons why to use AI when you are planning to program an Excel Macro:

Efficiency:
AI can generate code much faster than a human programmer, leading to increased efficiency and productivity.

Accuracy:
AI algorithms can identify and correct errors in code, reducing the likelihood of bugs and improving the accuracy of the code.

Customization:
AI can generate code that is specifically tailored to the user's needs, including custom functions and procedures that may be difficult or time-consuming for a human to create.

Reduced Learning Curve:
AI-generated code can simplify the process of learning to code, reducing the time and effort required to become proficient in Excel macro programming.

Scalability:
AI can easily generate code for large and complex Excel macro projects, allowing users to create more sophisticated tools and processes without requiring significant additional time or resources.

Conclusion: How to create an Excel Macro with AI

Creating Excel Macros with AI is very simple.

You need to register in the AI platform (in this case for chatGPT - you can use your email), remember it is free of charge.

Once you have access to the platform, define a precise prompt covering in detail what you expect as output from the IA.

And that’s it, in a few clicks and a very limited amount of time you will have a ready VBA code to be used in your Excel Macros.

FAQ How to create an Excel Macro with AI

Some related questions regarding creating Excel Macros with AI

Can I use AI for Excel?

Yes, of course, AI can be used for Excel and many other languages where you would need to generate some programming language such as: C++, Phyton, HTML… AI is programmed to provide outputs in various programming languages.

How long does it take to create a Macro for Excel using AI?

It depends on the size of the macro and how good the prompt it is. Assuming a good prompt and a very simple macro, in less than 5 minutes you can have a fully functional code ready to be used in your Macros.

How do I auto-create Macros in Excel?

A very simple way to auto-create Macros in Excel, is using Artificial Intelligence, for example, ChatGPT. You only need to register, give a prompt to the Artificial Intelligence, and copy/paste the code generated by the AI into your Macro.

Is it safe to use AI for creating Macros in Excel?

It all depends on good it is the prompt you are providing to the AI. It is always safe to validate the code provided by AI before using it in your business. First, validate and later run it. If you want to learn more about VBA macros here.  

Can I ask the AI to comment on the code?

Of course, you can ask the AI to comment on the code. It depends on how well you are typing your prompt to the Artificial Intelligence. Not only the AI can comment on the code, as well can provide improved code versus the original code that you provide. The opportunities for AI are endless.