Automate Your Excel in 2025 with ChatGPT
Last updated on November 8, 2024 By Victor Chan
Visual Basic for Applications (VBA) can be a lifesaver if you often repeat tasks in Excel or other Microsoft apps.
It’s an incredibly powerful tool that makes working with spreadsheets faster and easier.
But if you’re new to writing VBA code, it can feel overwhelming.
This is where ChatGPT comes in. It can help you create VBA code super faster, so you can automate your Excel tasks quickly and easily.
In this article, I'll show you how to use ChatGPT to build and run VBA scripts, without having to write every line of code yourself.
And you can watch my 10 minute tutorial on YouTube by clicking here.
Download FREE Excel Workbook
Step 1: Sign up for free Click Here
Step 2: Log in for access Click Here
Step 3: Download file ChatGPT-VBA-Coding-Pictures.zip
Are you new to Excel VBA Macros?
• Find out how to enable and disable macros in Excel with this tutorial
• Save time and effort by copying VBA macros from one workbook to another with these instructions
1. Why Use ChatGPT to Generate VBA Code?
For many Excel users, learning to write VBA can feel overwhelming. Even if you're familiar with Excel, understanding VBA syntax is another story entirely.
ChatGPT provides a powerful shortcut—you can simply describe the task you want to automate, and ChatGPT will write the appropriate VBA code for you.
Instead of spending hours manually coding a solution, or copy-pasting bits and pieces of code from various sources, ChatGPT allows you to:
- Automate repetitive tasks in a few clicks.
- Reduce manual errors that occur when writing code by hand.
- Write useful VBA code even if you're new to programming.
2. Getting Started with ChatGPT
The first thing you need to do is sign up for ChatGPT. Head over to chatgpt.com (or as of Nov 7, 2024 you can go straight to chat.com), where you can create an account and access the tool for free.
There's a free tier and a Plus subscription—either option works for generating VBA code, but I use the Plus subscription to get higher quality responses with access to the latest models and features.
In November 2024, these advanced features include:
- Advanced Voice Mode where you can have a “real time” conversation with ChatGPT, even including the ability to interrupt it.
- GPT4o with Canvas (Beta) with which you can edit text and code by selecting specific parts to work on inside a special “canvas” that contains your latest version.
- Creating custom GPTs to fine-tune the chatbot to specific tasks.
If you prefer using ChatGPT as a standalone app, you can download it for desktop or mobile. I find the desktop version very convenient as I often switch between Excel and ChatGPT on my computer and don’t want to be distracted by browser tabs.
3. VBA Code 1: Moving Pictures
Once you have access to ChatGPT, you can use it to generate VBA code.
Here’s an example of a task that I wanted to automate. I have a workbook with multiple worksheets each with an image, and I want to reposition all of these images to the same spot on each worksheet—without manually dragging them on every page.
Match Position of Selected Picture
I opened the Windows ChatGPT app and typed in my request which was basically: “Create VBA code to reposition all images in a workbook to the same position on every sheet.”
ChatGPT quickly generated a subroutine that did exactly what I needed.
Here are some best practices for getting good VBA code using prompts:
- Be Clear and Specific: Describe exactly what you want the code to do, including any details or rules.
- Provide Context: Mention relevant details about your workbook, like sheet names, data structure, or existing macros.
- State Inputs and Outputs: Clearly explain what data the code should handle and what results you expect.
- Include Error Handling: Let it know if you want any specific actions for handling errors, like showing messages.
- Ask for Comments: Request comments in the code to explain how it works, making it easier to understand.
- Refine with Feedback: Review the code and ask for changes or improvements as needed.
The full prompt I used is here:
Write a VBA macro that takes the currently selected picture in the active workbook and resizes all other pictures in every worksheet of the active workbook to match the exact size and position of the selected picture. If no picture is selected, display a message box prompting the user to select a picture first and then exit the subroutine.
I then copied the generated code and pasted it into the Visual Basic Editor in Excel. Here’s how:
- 1. Activate the Developer Tab — Go to the Excel ribbon, right-click on any of the existing tabs, and select Customize the Ribbon. Check the box next to Developer and click OK.
- 2. Open the Visual Basic Editor — Click on the Developer tab, and then click Visual Basic. Or use the keyboard shortcut ALT + F11.
- 3. Insert a Module — In the Visual Basic Editor, right-click on Microsoft Excel Objects, hover over Insert, and click on Module. Paste your VBA code here.
Testing the VBA Code
After pasting the code, it was time to see if it worked. I selected one of the images and ran the subroutine from the Visual Basic Editor. ChatGPT had also thoughtfully included a check to make sure an image was selected before running—a nice touch that prevented errors.
When I ran the macro, it repositioned all images across the workbook to match the size and position of the selected image, saving me the hassle of doing it manually.
And this is just a taste of what VBA can do in Excel and Office. You could use it to create reports automatically, create user forms to guide users through a process, and streamline chart creation.
Here is the VBA code for reference:
[VBA Code Box]
Sub Pic_Match_Selected()
Dim selectedShape As Shape
Dim ws As Worksheet
Dim shp As Shape
Dim shapeFound As Boolean
' Check if a shape (picture) is selected
On Error Resume Next
Set selectedShape = Selection.ShapeRange(1)
On Error GoTo 0
If selectedShape Is Nothing Then
MsgBox "Please select a picture first and then run the macro.", vbExclamation, "No Picture Selected"
Exit Sub
End If
' Ensure the selected shape is a picture
If selectedShape.Type <> msoPicture Then
MsgBox "The selected object is not a picture. Please select a picture and try again.", vbExclamation, "Invalid Selection"
Exit Sub
End If
' Loop through all worksheets and resize all pictures
For Each ws In ActiveWorkbook.Worksheets
For Each shp In ws.Shapes
If shp.Type = msoPicture And Not shp Is selectedShape Then
shp.LockAspectRatio = msoFalse
shp.Width = selectedShape.Width
shp.Height = selectedShape.Height
shp.Top = selectedShape.Top
shp.Left = selectedShape.Left
End If
Next shp
Next ws
MsgBox "All pictures have been resized and positioned to match the selected picture.", vbInformation, "Operation Complete"
End Sub
4. VBA Code 2: Randomizing Positions
The next thing I did was experiment with repositioning the images randomly on each sheet. I asked ChatGPT to generate a subroutine called Pic_Random.
Here’s the full prompt I used:
Create a VBA subroutine called “Pic_Random”
Loop through all worksheets in the active workbook.
On each worksheet, reposition all the pictures randomly so that they fit between cells A1 and V29.
Make sure that the right edge of each picture does not go beyond column V and the bottom of the picture does not go below row 29.
In order to make them fit you will have to resize them. So make sure this happens properly.
It created a loop that randomly positioned images across each worksheet, constrained within the bounds of cells A1 to V29, which is how many cells were visible on my screen.
I copied the code, pasted it into the same module, and ran it. Sure enough, the images were randomly repositioned across all sheets in the specified range of cells.
Here is the VBA code for reference:
[VBA Code Box]
Sub Pic_Random()
Dim ws As Worksheet
Dim pic As Picture
Dim maxWidth As Double, maxHeight As Double
Dim newLeft As Double, newTop As Double
Dim scaleFactor As Double
' Set the boundaries for pictures to fit within cells A1 to V29
maxWidth = Range("A1:V1").Width
maxHeight = Range("A1:A29").Height
' Loop through each worksheet in the active workbook
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
' Loop through each picture in the worksheet
For Each pic In ws.Pictures
' Randomly position the picture
newLeft = Rnd() * (maxWidth - pic.Width)
newTop = Rnd() * (maxHeight - pic.Height)
' Ensure the picture fits within the boundaries by scaling if needed
If pic.Width > maxWidth Then
scaleFactor = maxWidth / pic.Width
pic.Width = maxWidth
pic.Height = pic.Height * scaleFactor
End If
If pic.Height > maxHeight Then
scaleFactor = maxHeight / pic.Height
pic.Height = maxHeight
pic.Width = pic.Width * scaleFactor
End If
' Reposition the picture
pic.Left = newLeft
pic.Top = newTop
Next pic
Next ws
MsgBox "Pictures repositioned and resized successfully."
End Sub
5. VBA Code 3: Resetting Positions
As someone who loves Excel, I also like having stuff organized neatly, and my insides squirmed at the thought of leaving the pictures with the random placements from the previous code!
So I went back to ChatGPT and asked it for a new subroutine called Pic_Reset.
This is the prompt I gave ChatGPT:
Create a VBA subroutine called "Pic_Reset".
Loop through all worksheets in the active workbook.
On each worksheet, move all pictures to cell A1.
Resize each picture to fit the width of columns A to L.
Ensure the pictures maintain their aspect ratio when resizing.
Update the screen after moving and resizing the final picture so the change is visible before displaying the message box.
At the end, show a message box confirming that the process is complete.
This routine was meant to reset all images to a uniform position with the top left corner in cell A1, while adjusting their sizes to fit between columns A to L.
However, the result wasn’t what I expected.
The VBA moved and resized the pictures to fit inside cell A1, so their height was only 1 row tall. I didn’t want this! And the images also lost their sharpness after being squashed into cell A1. So they got all blurry!
I realized my prompt was unclear, so ChatGPT generated VBA code that didn’t do what I wanted.
Lesson 1: Before blaming ChatGPT for mistakes, double-check that your prompt accurately reflects what you need. It can’t read your mind (yet!)
Luckily, I had a previously saved version of the spreadsheet to revert to.
Lesson 2: Always keep a backup!
Then I personally reviewed the code and rewrote it to make it do what I wanted.
If I didn't know VBA, I could have asked ChatGPT for more adjustments, but doing it myself was faster and more efficient in this case.
Here is the final corrected VBA code. It places images with their top-left corners in cell A1 and adjusts their width to fit columns A through L:
[VBA Code Box]
Sub Pic_Reset()
Dim ws As Worksheet
Dim pic As Picture
Dim maxWidth As Double, maxHeight As Double
Dim aspectRatio As Double
' Set the maximum width and height based on columns A to L and row 1
maxWidth = Range("A1:L1").Width
' Loop through each worksheet in the active workbook
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
' Loop through each picture in the worksheet
For Each pic In ws.Pictures
' Calculate the aspect ratio of the picture
aspectRatio = pic.Width / pic.Height
' Resize the picture while maintaining aspect ratio
pic.Width = maxWidth
pic.Height = maxWidth / aspectRatio
' Move the picture to cell A1
pic.Left = Range("A1").Left
pic.Top = Range("A1").Top
Next pic
Next ws
' Refresh the screen to show the changes
Application.ScreenUpdating = True
' Display a message box confirming completion
MsgBox "All pictures have been moved and resized successfully."
End Sub
6. Customizing the Ribbon with Buttons
I wanted an easy way to run these macros without always opening the Visual Basic Editor. To do this, I added custom buttons to the Excel ribbon:
- 1. Create a Personal Macro Workbook — Think of this as a storage vault for all your favorite macros. If you don’t have one yet, start by recording a quick macro and saving it to the Personal Macro Workbook. Then stop recording. This will create one for you.
- 2. Customize the Ribbon — Right-click the ribbon, select Customize the Ribbon, and add a new group inside the Developer tab. Rename this group to something relevant, like Pictures.
- 3. Add Macros as Buttons — Add the macros you generated with ChatGPT (Pic_Match_Selected, Pic_Random and Pic_Reset) to the new group, giving each button a clear name and icon.
Once everything was set up, I tested the new buttons.
I clicked on Match to align all images to the same position, then tried Random to scatter them, and finally used Reset to neatly align them back to A1.
It was satisfying to see all of this happen instantly with just a few clicks!
And it was so fast to use ChatGPT to write the VBA code for this simple use case.
It got me more than 95% of the way there, and I only had to make a few small changes to the third VBA sub procedure to get it to work the way I wanted.
7. What Next?
I hope you can see that ChatGPT makes generating Excel VBA code super easy. And it can cut out a lot of frustrating tasks that consume your time.
But to use AI to its fullest, it's helpful to know the basics of VBA. When you know what ChatGPT is generating, you can successfully modify the code and get even more value from it.
If you're just starting with VBA or have struggled to learn it in the past, consider watching detailed video tutorials.
Good videos make all the difference in gaining the confidence to write your own VBA scripts. I’ve taught over 500 professionals how to use VBA to automate their work, so I know this to be the case.
To learn Excel VBA check out my Excel VBA training program—it's designed to help you build a solid foundation and start automating your business workflows in no time.
Hi, I'm Victor!
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.