Once these basics are covered, you'll be ready to move on. Learning about the different types of procedures, creating procedures, learning the scope of procedures and working with methods. You'll also learn how to get VBA context specific help from within the visual basic editor. If you're enjoying these videos, please like, and subscribe. If you want to earn certificates and digital badges, please become a member of our Patreon. The link is in our video description. If you have any questions you want answered by one of our instructors,.
Please join our offsite community. The link is in the description as well as always. If this course has exercise files, you'll find them in the video description. In our first lesson getting started, you're gonna be learning about an overview of VBA. That includes what type of language it is, why you would want to use visual basic for applications versus recording macros and advantages that.
It can give you in terms of efficiency. We'll move into recording a macro and running it in Excel. And then we'll go into the visual basic editor environment, and you'll get an overview of the environment. Before we start editing a macro in V VBA, you'll learn how to save a macro enabled workbook and the importance of doing so you'll learn a little bit about macro security. Now in this lesson, we're gonna be using two files that.
Are in the video description. They're both Excel files. One is named vehicles and the other one vehicles two, we will be creating a vehicle's macro enabled workbook during this lesson. And the name of that workbook is shown on the slide. You will not find it in the video description at this time. So what you would wanna do is grab those two Excel files, vehicles, and vehicles to from the video description and put them somewhere on your system.
Since then, it evolved to encompass. The applications that are mentioned in this slide, VBA is known as an object oriented programming language. O this means that everything within an application is an object, including the application itself objects have their own set of features and uses known as properties and methods. Respectively. Here's an example of object oriented programming. So the example using Excel would be that Excel is an object that contains other objects..
For example, sell worksheets charts, pivot tables, shapes, et cetera. Each object has its own properties. For example, a worksheet has a. A workbook can be opened and closed. And these are a couple of a workbooks methods. So methods are like actions. You will work with objects and manipulate them via their properties and methods throughout this course. So why VBA? It can give you the ability to run macros automatically create.
User defined functions, which can be used in the Excel application. You can use VVA to control other office applications and mostly to automate recurring and repetitive tasks. Don't get me wrong. The power of macros is excellent in Excel, but VBA does have some advantages. So for example, there are no limitations when you're recording macros, you are.
Limited to those tasks that you can perform in the Excel interface with VBA, you can attach code to events so that it runs automatically when the event occurs. For example, activating a worksheet would be an event VBA allows for decision making and it has several decision making structures. Ensuring code only runs when certain conditions are met. The looping structures in VBA ensure code runs multiple times based on a condition..
And then there's forms and boxes. You can use VBA to create user input forms, dialogue, boxes, and message boxes in Excel. So we're gonna get started. I have the vehicles, Excel file open from the video description, and we are going to be recording a macro in this file. Now, the thing is we wanna prepare Excel for recording a macro. There are several ways that you can start a macro recording, but we wanna add the developer tab to the ribbon as that is one of the ways that.
Posts Related:
You can record your macros, review your macros so on and so forth.
So the first thing we're going to do is right, click on any ribbon tab, home insert, page layout, whatever. And when you right click on a ribbon tab, you're gonna choose customize the ribbon in the customized, the ribbon options box on the right side. Everything that has a check. Mark is a tab that's showing on your ribbon..We wanna check the box in front of develop. and then at the bottom you're gonna click. Okay. And now you have the developer tab on your ribbon and let's navigate to that tab. So for parts of this course, we're gonna be using this make believe vehicle information. So we have make believe in numbers. We have year, we have make model classification, color dealer cost and manufacturer suggested retail price columns in here..
And we're gonna record a macro that is going to apply the column headings and also a little bit of formatting. So on the developer tab of the ribbon in the first group, the code group, you're gonna click on the record macro button and don't worry. It's not gonna be recording until after we clear the dialogue box. So when you click that button, the record macro dialogue box opens, and you have to give your macro a name. Well, you don't want your macros to have generic names..
So make them as descriptive as possible. Now, of course, there are rules to naming macros. Macro names must begin with a letter can be alpha numeric and can contain the underscore character, no spaces. So we're gonna name this macro capital, a add capital F formatting, all mush together. So it follows the naming convention. We're not gonna assign a shortcut key to the macro to make it run later..
We don't need to do that here. And you have choices as to where the macro is going to be stored. So by default, it's gonna just be in this workbook, the file that it's going to be recorded in this workbook. Your other choices are a new workbook or your personal macro workbook. If you store it in the personal macro workbook, that workbook opens in the background, every time you open Excel. And so the macro is available to any and all Excel files, we're.
Gonna leave it on this workbook. And there's another way that you can access it from another file that you'll see a little bit later. I like to add a description to a macro so that if anyone comes behind me and they need information about what the macro is going to do, the description will cover that. So we're gonna type in a description box, add column headers, bold and centered. Period apply accounting format to column G period auto fit columns a through H..
So that is the intent of the macro that we're going to record. So at this point, once we click, okay, everything we do until we stop the recording is going to be recorded. And I like to say this while you're recording the macro. If you make a mistake and you correct the mistake, you don't have to start all over again because it's recording both the mistake and the correction. So go ahead and click. Okay..
You'll notice on the developer tab now where it used to say record macro, we now have our stop recording icon there, and we'll use that when we're completely done going through all the steps that we are saving in our macro. So for right now, the first thing that we want to do here is right click on row, heading one and choose insert. So we get a blank row, a new row one, and that's the row that we're gonna.
Be adding our column headers in. And so we're gonna click in, sell a one and we're gonna type then V I N, and I'm doing it in all capital letters for vehicle identification number. I'm gonna press my tab key to get over to B one, and I'm gonna type year tab, make tab, model tab again, classification. The next one is color. The next one is dealer cost to separate words..
And lastly, in all caps, S R P for manufacturers suggested retail price.
And after that one, I'm gonna just press enter. So we have our headers in where they need to be, and then we're gonna select row heading one again. And we're gonna go to the home tab of the ribbon in the font group. We're gonna make it bold. And in the paragraph group, we're gonna use the center alignment button. So the headers are centered within. Their cells..Now we are also gonna add in adjusting column within here. So don't worry about that right now. The next thing we're gonna do is we're gonna select column G by clicking on its column heading. So the entire column is selected and on the home tab, in the number group, we're gonna click on the dollar sign for the accounting number format. And then the last thing we're gonna do is we are gonna select column headings a.
Through H and I just clicked on a, and we want to auto fit these columns with right. So that's what we wanna do. We wanna auto fit. And we do that by going to the cells group over to the right on the home tab of the ribbon and in the cells group, you're gonna select the format dropdown. And from that dropdown, you're gonna select auto fit column width. So now every column is wide enough to display everything in the column..
And if we were to make entries and they were wider than what they are now, it would automatically adjust the column with now, typically when I'm done recording my macro, before I click stop recording, I just like to click on any blank cell, just to make sure that nothing is selected. We're gonna go back to the developer tab and choose stop recording in the code group..
Typically, you cannot undo the effects of a macro. So we just recorded this macro while we were recording it. We were doing the steps. Now, what I'd like you to do is I'd like you to press control Z, which is undo. And the only thing it undid was the column auto width, everything else, our headings are there. They're bold and centered. We have our accounting format and column G and it just undid the column auto fit. So we wanna redo that step. So we're gonna do stew control Y which is redo..
And we end up again with the result of our macro. You will learn some workarounds for undoing the effects of a macro later on. But for right now, we wanna test this macro on a different sheet. So we're on the inventory sheet tab in this file. We have two other sheets. Let's go to sheet two sheet two has the same data that was on sheet one. Initially, when we came in here and this is a good sheet to test our macro on..
So we can go on the developer tab in the code group. This is where you can use the macros button to access any macros that you may have in this file. So we're gonna click on macros and we only have one add formatting it's already selected. And on the right side, you're gonna click the run button. So at the end of the day, you should have those column headings. They should be centered and bolded. And the auto width, the column auto width is in effect and you have the accounting number format in column G..
So while we were recording our macro, it was generating visual basic code in the background, visual basic for applications code in the background. Now on the developer tab, we can get to visual basic. You will learn other ways of starting macro recording without using the developer tab. You'll learn shortcuts throughout the course on how to switch back and forth between visual basic for applications and the Excel interface. But for right now on a developer tab, the first button in the code group is visual basic..
Let's go ahead and click on it. Visual basic for application opens in its own separate window. And so you literally have this window and your Excel window open at the same time, and we're just gonna focus on the VBA window right now. And so before we get started in here, we're ultimately gonna edit the code behind the macro we just recorded. But I just want you to get acclimated to this environment. This is known as the visual basic editor or VBE just so you know, um,.
It doesn't have a ribbon interface. It never updated. It still has the old school menu bar. Where you have file edit view and everything. And then underneath it, it has some toolbar that we used. This was like pre ribbon days, how Excel itself and all the other office applications used to be on the left side of your screen. You may have two different panels open. You have a project Explorer window at the top and a property's window at the bottom..
If you don't have both of those pains showing you can go up to the view menu and you can click on project Explorer and then come back to view and click on properties window. And you can see the shortcut keys for both of them. Or you could do control R to bring up project Explorer and F four to bring up the properties window. Let's talk about the project Explorer window first for every Excel file that you have open, you will have a separate project. So right now I only have one Excel file open. It's the vehicles dot Excel SX file..
So it creates its own VBA project. And it has the name of the file afterwards. Now I may have a few things here because I have some add-ins that create projects as well. Um, if you don't have them, you're fine, but this is a cautionary tale here because there have been times when I've had like five Excel files open and I find myself doing some coding. And then I look and see that I'm doing it in the wrong files project. So I can always cut and paste it to the right project. But you do want to be aware of what project you're working in..
When you click on VBA project vehicles dot Excel, SX, it expands, and you can look up at the title bar and it lets you know, that it is in that project now. So just kind of get in the habit of making sure you're in the right project. It has two folders in that project, Microsoft Excel objects, those are your sheet tabs. So we had the inventory sheet, we had a sheet two and a sheet three, and then the entire workbook is an object as well. So it lists that then you have another folder called modules..
That folder was created when we started recording our macro, actually, when we finished recording our macro, it created that modules folder. Your code is stored in modules. So expand the modules folder and it created a default module. It names it module one. And if we were to go back over to Excel now and record another macro, it would also put it in module one. If we were to close Excel, saving it properly and then reopen it.
And start recording a macro, it would create module two. It would always give it the next number. Module two, module three. You can also rename modules in here, which you'll see in a little bit. So if you click on module one, you will actually see the code window. And that is everything that it created while we were recording our macro. So it converted our steps into VBA code. And we're gonna edit this in just a little bit, but I want you to have.
Your editor window be as comfortable an environment for you to be working in. So one of the things we're gonna do is we're gonna go up to the tools, menu and click on options. We will revisit some somebody's settings later in the course, but when you go into options, you have four tabs editor, editor, format, general, and docking. One thing, one setting that I want to make sure that you have checked well, like make sure all of the code settings have a check mark in front of them. And as we work in the course, I'll explain what these settings mean. Your window settings..
You can actually have all of those checked as well, go to the editor format tab. And this is where you can set your font size. So I have my font set to 14 point, which is comfortable for me. I can bump it up to 16 and then at the bottom I'm gonna click, okay. So font size. And if you wanna go back in and change the font, I'm cool with the code looking font, cur font, um, that works for me. I'm just so comfortable with it. It doesn't bother me..
So I just wanted you to know that you can change some of those settings in the environment for yourself and for the ones that we are gonna be utilizing in the course. We'll go back in there and talk about what those settings are actually doing. The other thing we want to customize before we edit our code is we wanna add three icons to the toolbar that will be using frequently throughout this course. So what I'm gonna do is I'm gonna just right click in a blank area of any toolbar and go to customize..
And the customize dialogue box opens. You wanna make sure you're on the commands tab at the top. And then you'll see a list of categories that mimic the menus that are up there. File edit view insert. So basically you're gonna have to tell. Where it resides on the menu in order to add it to your toolbar. So under categories we're gonna select, edit, and then all the commands from the edit menu show on the right side of your screen. And on the right side of your screen, you're gonna scroll down. We're looking for two commands..
The first one is comment block, and the second is uncommon block. So what I'm gonna do when I find comment block, I'm gonna click and hold on it and I'm gonna drag it right after the question mark, on my toolbar. And then I'm gonna grab uncommon block and drag it right after comment block. Now we have one more command that we're gonna add up there under categories. We're gonna click on debug and the command at the top of the list is compile project. I'm gonna grab that and drag it right after uncommon block..
So I have comment, block, uncommon block and compile project. When we get ready to use those commands, I will explain what they're gonna do for us. They're gonna be kind of helpers for you. Let's examine the code that was generated while we were recording our macro. First of all, let's look at the information starting from the top. We'll talk about the option explicit statement a little bit later in the course. Don't worry about that right now. What I want you to focus on is the name of our code..
Add formatting. It comes after the word sub, and you'll notice that both option explicit and sub are in blue. That means those are visual basic for application keywords. They mean a specific thing in this code and should only be used for their intended purpose. It creates what's called a sub procedure to contain our code. So it starts with the sub statement. And if you scroll down at the very bottom, it concludes with an nub statement..
You have to have both. If you have a sub and not an nub, you're gonna get an error message, but it did this automatically. While we were recording our macro, it gave it the name that we named it add formatting. And then after it, it put a set of parentheses. Some procedures have parameters that need to be defined, and that would happen inside the parentheses. We're not there yet, but that's why they're there and they need to be there..
Then you'll notice several lines that are in green, beginning with apostrophes. Those are comment lines. So when we were filling out the record macro dialogue box, right, we named it ad formatting, and we put a description in add column headers, bold and centered apply accounting format, blah, blah. Comments are meant to describe your code. It's a good idea to comment your code liberally I've written code.
Before, and didn't comment it. And six months later, I had to figure out what the intent of the code that I wrote was so comments are really good. And so when you put a description in a record macro box, it comes in as comments they're informational. When you run your code or execute your code, the comments do not execute. And we can do a little bit of cleanup there. Um, we don't need that blank apostrophe at the top, so I'm gonna just delete it. And then I'm gonna delete the two underneath because they're not necessary either..
And then it starts our code. Well, the first thing we did is we right, clicked on row one and inserted a new row. So that's row one, colon, one.select, and then selection dot insert. Right? And so it moved everything. It shifted everything down and gave us the blank row at the top. And then we clicked and sell a one and we typed it in. So that's range, even though it's a cell range, a one.select notice the quote, the double quotes inside the parentheses there surrounding a one and.
An active cell, which is now cell a one. Don't worry about formula R one C1 right now, but active cell dot formula R one C1 equals whatever we typed in net cell. Then B one C one D one E one F one G one H one same thing. And then we selected row one again. So at this point, we're all the way down here in the code. When we selected row one again, and once we selected it, we made it bold. So before that selection dot font dot bold would equal false..
When we made it bold, it changed the force to true. And then you have, what's known as a width end with block, starts with the word with, and if you go down several rows, it ends with an end with statement. And this is an interesting thing. You'll learn more. It's known as a construct, right? You'll learn more about this construct as we go in the course, but this allows you. Anything that you do in the paragraph group on the home tab is included in this with block..
Bold is not in the paragraph group on the Excel ribbon, right? But your alignments are the ability to wrap, text your orientation, whether you're gonna indent merging cells. All of that is included in the paragraph group on the home tab in Excel. So all of these settings here are those things that mimic the paragraph group. And what we did is we centered those headings..