Creating and refining your first VBA macros with Copilot

- Let's jump right in, shall we? Go ahead and open the exercise file named first-macro.xlsm to follow along. Throughout this course, we'll handle all our exercises in the same easy to follow way. For each video, you'll get a starter file to dive right in, a completed solution file with a fully working macro for backup, and a list of the exact Copilot prompts I used in the end of chapter document. I'll typically use Copilot live right along with you, but remember we've always got these reliable, fully tested macros ready in case things get tricky. Think of it like your safety net. Back to the task at hand. Remember, this is a macro-enabled workbook and I'll assume you're comfortable enabling the developer tab and switching back and forth between the VBA editor and Excel itself. Inside this workbook, we've got a simple dataset related to our sales staff and we're going to test drive Excel Copilot to automate tasks and create useful VBA macros. First off, you'll notice the worksheet has some mysterious blank rows that keep appearing in the workbooks we receive. I'll launch Copilot and ask it to generate a macro that removes all blank rows from our worksheet. So we're in Copilot here. One thing that I'll be doing throughout this course is, if you head to the end of the ribbon, there's an option to full screen, and I like to do that. It just gives us a little bit more space to use Copilot. The other thing is with our prompts, they are all saved in some documents in the exercise file. So generally I will just copy and paste from that prompt file, and you are welcome to do the same. Like I said, this is our first one, Generate a macro that removes all blank rows from the worksheet. So we will run that. You should see the code pop up in the chat box, but here's the catch. Unlike other Copilot generated features such as calculated columns or conditional formatting, this VBA macro won't execute itself. You'll need to copy this snippet and then manually paste it into the VBA editor, which we'll do right now. So when I take this code, I will highlight it and then you will need to do a control and see, right click and copy isn't going to work. We will head over to the VBA developer here under the Developer tab. We'll go to the editor and come here. And this is our first macro. I'm going to go to my module, and it looks like there's already something in here that we used before. I'm going to go ahead and clear out of that so we're starting from scratch. We can copy that, okay, and then we will save it and let's head back to Excel. We'll go back to developer and we will run our remove blank rows macro. So fortunately our macro was able to run. Unfortunately we're not able to have Copilot do that on its own. But one nice silver lining here is that we can refine our macros. We can actually have Copilot even kind of look at the data in the workbook. So let me give you some examples of that. The first thing I'll do, just to clean up this macro a bit is we'll ask for some exploratory comments to this macro so it's a little bit more clear as to what this macro is actually doing. And you will see here that some comments have been added to the macro. If you wanted to redo that macro in the editor and add the comments, you could do that. I'm going to go ahead and move to something a little more ambitious here, which is referencing and using the data within our worksheet. This macro here is pretty generic and agnostic to what's actually in the worksheet, but let's ask for something a little more specific. I'll take my next prompt here. We're going to ask for a macro. We want a separate worksheet for each distinct value in the region column. So we're referring to things in this specific worksheet. Like I said, we want to name each workbook according to its corresponding region. So we'll run that and ideally Copilot will give us a tailored macro for this workbook. Okay, so we are getting a more complex macro this time. I'm going to go ahead and copy that and we can head back to the VBA editor. If you want to do ALT and F11, that will take you there on the keyboard. You may need to click kind of outside of Copilot into the main grid, ALT-F11, and we'll create a new macro here. We'll keep this remove blank rows. When I paste that there are going to be some some red lines here. So anytime you see rad here in the VBA editor, that is indicating there's some kind of problem. Generally these problems are going to have to do with the way that the code gets returned in Copilot. It doesn't handle things like less than or greater than well, and it's actually using these HTML entities here, which we really don't want. We just want regular text and we don't want these HTML entities. So in cases like this, what we can do is go to copilot.microsoft.com. We are going to use the prompt there to fix this code for us. So I'm going to go ahead and move this back to my clipboard. We will go to Microsoft 365 Copilot and we will ask for some help there. So if you've not used 365 Copilot here, you'll see it. It looks a little bit more like what you may be used to in a generative AI tool. We've got a prompt box here and some options are you can actually refer to individual files if you wished. You could use this as a tool, upload specific files and and get help. But for what we need to do right here, just pasting our code will probably work. Okay, so I'm going to ask to remove the HTML entities. Okay? And if I paste that in, we should get some code. You'll notice that this does come back as a more traditional kind of a code editor. There will be a button here to copy this, and we'll go back to Excel and paste this. And I will go back to the VBA editor and let's go ahead and paste that in. So I'm not seeing any errors this time. I'm going to go ahead and save it. And now let's go ahead and close that and we can run it. And like I said, our goal is to save off some workbooks for each region here. So let's run that, export workbooks by region. And that seems to be running as intended. And we get a little success message here at the end indicating exactly where those workbooks were saved. Throughout this course, we'll typically start by seeking quick assistance directly within Excel since it's faster and more convenient. However, for significant troubleshooting or fine tuning, we will leverage 365 Copilot. A helpful middle ground is copying code generated in Excel Copilot into Microsoft 365 Copilot, saving you the step of uploading the entire file. That gives us a versatile set of tools we can use depending on our needs. Now full disclosure, if you're already a seasoned VBA developer, you might roll your eyes at some of the code quality here. Generative AI tends to produce verbose and sometimes redundant code, and this is no exception. We are definitely vibe coding here, meaning we're writing code that quickly solves the problem at hand, but may contain significant flaws and be difficult to reuse. At some point, you'll need to decide if you want to keep using Copilot for your existing solutions, or if investing additional time and effort into building something more robust makes more sense for your solution. And yes, some of the code, even in these solution files could absolutely be improved. But remember, we are just taking our first steps here. Ultimately, if you're looking for highly scalable or performance-oriented code, deeper VPA expertise will still be necessary. But for many analysts needing quick ad hoc automation, Copilot is going to be a genuine game changer.