Excel How To Write VBA with ChatGPT

There are many built-in Excel features and functions. Which is awesome because they enable us to do so many different things. But … what happens when we want to do something for which there is no built-in feature? Are we stuck? No … we can typically get help from a macro.

Macros are written in a language called VBA. VBA can be used to automate Excel tasks. For example, you can use it to print each worksheet in a workbook to a separate PDF file. Manually, this would be a time-consuming task, especially if you have a large number of worksheets. But with VBA, you can easily automate this process.

But, what if we don’t know VBA? While it does indeed take a while to learn VBA, a free service called ChatGPT can absolutely accelerate your learning curve. It can even help you write VBA code. Although the code it generates won’t always be perfect, it can certainly be a helpful assistant. In this blog post, we’ll explore how to use ChatGPT to write the VBA code needed to print each worksheet in a workbook to separate PDF file.

Video

Step-by-step guide

Our workbook has three worksheets, each with one report.

The Report A worksheet includes a report that looks something like this:

Report B looks like this:

And Report C looks like this:

We would like to write a VBA macro that will automate the task of printing each worksheet to its own PDF file. We aren’t exactly sure how to write such a macro, so we ask ChatGPT for a hand.

ChatGPT Prompt and Response

We open a new chat at the free chat.openai.com website. We enter the following prompt: “write an excel macro that will print each worksheet in the workbook to pdf.”

ChatGPT responds with:

In addition to the code block above, ChatGPT also provides instructions for how to use the macro. Wow:

So, we follow the instructions. We hit Alt + F11, insert a new module, and paste the VBA code:

We modify the filepath, and run the macro as instructed. And bam … the macro runs and creates three PDFs for us, just as we hoped:

We open each and confirm everything worked as expected. Wow!

Conclusion

Depending on what you’re working on, the code generated by ChatGPT may not be perfect. But it’s a great place to start. It can help you get started and save a bunch of time.

If I can provide any additional information, or you have any ChatGPT suggestions or experiences, please share by posting a comment below … thanks!

Sample File