Understanding VBA code created by macros - Microsoft Excel Tutorial

- If you know the basics of macro recording, then you know just about everything you point or click in Excel can be recorded into a procedure. Visual basic for Applications, or VBA, is the language behind Microsoft Office. Each program, like Word, Excel, PowerPoint, and Access can all be further automated with this language. Every time you click on a ribbon button, what's actually happening behind the scenes is it's executing VBA code. The good news is you don't have to be a programmer to use it. We're just using macros to record things we do on a routine basis. I do think it's a good idea to know how to navigate and work with VBA. All right, let's get started with a developer window because when you record macros, they go into modules. We'll go to Developer, we'll go to Visual Basic. Now that we're in VBA, let's take a look at our module here. You see this module? This is what is created when we start to record macros. I can also rename this module anything I want to in the properties. It's worth noting that when we close this file and record macros, it's going to actually create a new module for every time we have a session of macros that we record. I think it's also important to know how to jump around these procedures 'cause they can get really lengthy if you have a lot of steps and scrolling up and down can be ineffective. Let's take a look at the top right hand side. This is the Procedures dropdown. This allows me to jump between procedures and because these are meaningfully named, I know that this sets the format and this adds the headers. This can be an easy way to look and explore the macros that have been recorded in your workbook. Also, there's some different features that we can set for the window. Let's take a look at the word sub. Okay, I just highlighted it there for you. Do you notice how it's sort of navy blue and false and true are also navy blue? These are key words. Do you notice our comments here are in green and then our regular text, our code here, is all black. You can also set your preferences for fonts and size and even colors for the editor. We do this by going to Tools and Options in the Editor format. Do you notice the word sub is navy and this procedure name is Add Headers, it's written in black? That's the difference between normal text, which is the black text and keyword text. That Navy may be hard for you to distinguish based on your vision. So if you want to change that color, like maybe to a brighter blue or even change the size, you can. These are actual machine settings so you can set them at your preference, what's readable for you? Because when I send this file to anyone else, they're not going to get these settings. This is only for my machine. Okay, I don't want to change anything here, so I'm going to choose Cancel. The other thing I find myself doing a lot is toggling between my code window and my Excel window. So if you notice up top, there's a little Excel icon in the first position of the toolbar, it works as a toggle, so I'm going to choose that and it takes me back to my spreadsheet. I'm on the Developer tab so I can choose Visual Basic, and then here I am again, I'm back to my code. Okay, so again, this button toggles between Excel and Visual Basic. Last but not least, especially if you want to learn more code, you may want to actually watch the code being recorded. All right, let me go ahead and split my window. Okay, and I'm going to choose to record a new macro and I'm just going to call this Test, click Okay. Oh, and notice how I got module two. All right, let me double click on that. All right, now I'm recording, so watch what happens when I select A2 through A14. You see how it codes range A2 through A14 select. Okay, and then I'm going to tell it to make it bold. Okay, and then I'm going to go put a thick outside border. Now notice it put a ton of code in there. It put every option. I don't really need all that, but it also doesn't hurt my feelings for it to be in there. But let's say I don't really want that. I can undo right now and it actually recorded me correcting myself. Now sometimes that can produce a lot of extra code you want to get rid of. Just be aware it is sensitive enough to undo the steps as it records it. All right, let me go ahead and go back to my Developer tab and stop my recording. Okay, now, macro was successfully recorded, but it's also available as an Office script and is asking me do I want to save this script or discard the script? I don't work with Office Script right now. I'm working solely with VBA, so I'm just going to close this here and we'll explore that on another day. Okay, also, something that's happening here worth noting is I've highlighted these names in the process and notice I have a Copilot icon. Okay, so Copilot shows up and it will allow me to ask questions, explain formulas. Just wanted to point that out. We'll work with it a little bit later in the course. Remember, this is just the beginning of diving in. You do not have to work with VBA at all if you don't want to, but I do believe it's important to know what's happening behind the scenes, especially if you want to go further with VBA than what you can record. And remember, you do not have to know every single detail to gain the benefits of automation with VBA.