by Excel automation is one of the fastest places to get value from AI because the problems are usually concrete: clean this sheet, split this column, compare two lists, create a report, or repeat these clicks every week.
The right way to use ChatGPT for VBA
Do not ask AI to magically automate an entire workbook in one prompt. Describe the workbook structure, the exact input columns, the expected output, and the rules that must not be violated.
Practical toolchain
- ChatGPT or Copilot to draft VBA, formulas, and Power Query ideas.
- Excel VBA editor for testing and stepping through the macro.
- Power Query for repeatable data cleanup where code is not needed.
- Power Automate Desktop when Excel is only one part of a larger desktop workflow.
Prompt structure that works
I have an Excel workbook with a sheet named Input.
Columns are: Date, Customer, Invoice, Amount, Status.
Create a VBA macro that filters Status = Pending, copies rows to a sheet named Pending_Report, formats Amount as currency, and does not modify the Input sheet.
Add comments and basic error handling.
Testing checklist
- Save a copy of the workbook before running generated VBA.
- Run the macro on 10 sample rows first.
- Check whether formulas, hidden rows, filters, and merged cells affect the output.
- Ask AI to explain the macro before you trust it.
- Keep the final macro version in a text note or versioned folder.
When not to use VBA
If the task is only data import, filtering, joining, or reshaping, Power Query may be cleaner. Use VBA when you need workbook actions, custom formatting, buttons, or repeatable multi-step behavior that Power Query does not handle well.
Related reading
- Introduction to Windows Power Automate Desktop
- Power Automate Desktop worker machine setup
- Exploring ChatGPT basics
