Excel 2019 VBA Beginner Tutorial

Hi everyone i'm trish connor cato welcome to the excel 2019 visual basic for applications video course this course is for beginning users looking to automate repetitive and recurring tasks in microsoft excel vba is microsoft's programming language and it's built into the office applications our focus during this course is on excel specifically you'll be equipped with the basics to start writing your own vba code modify the code behind macros you've already recorded and have an understanding of how vba lends itself to creating efficiency in your daily tasks we'll start with the basics where you'll learn the

types of things you can do with vba versus recording macros in excel as well as some key terminology which will help in your vba journey and you will get to edit vba code you'll learn the importance of macro enabled workbooks and how to save them as well as how to modify some security settings we'll move on to understanding the excel object model collections and how to reference objects in vba code 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 off-site 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 below in our first lesson getting started you're going

to 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 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 going to be using two files that are in the video description there 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 want to do is grab those two excel files vehicles and vehicles too from the video description and put them somewhere on your system where you have easy access to

them before we get hands-on let's go ahead and get an overview of vba so as mentioned in the introductions vba is a microsoft programming language that is currently built into the excel word powerpoint outlook and access applications it's one of the many programming languages that evolved from the basic programming language which was developed in the 1960s it was first released in excel 5 and that was in the office 1995 suite since then it evolved to encompass The applications that are mentioned in this slide vba is known as an object-oriented programming language oop 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 cells worksheets charts pivot tables shapes etc each object has its own properties for example a worksheet has a name a workbook can be opened and closed and these are a couple of a workbook's 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 vba 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 dialog boxes and message boxes in excel so we're going to 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 want to prepare excel for recording a macro there are several ways that you can start a macro recording but we want to add the developer tab to the ribbon as that is one of the ways that 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 going to choose customize the ribbon in the customize the ribbon

options box on the right side everything that has a check mark is a tab that's showing on your ribbon we want to check the box in front of developer and then at the bottom you're going to click ok 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 going to be using this make vehicle information so we have make-believe then numbers we have year we have make model classification color dealer cost and manufacturer suggested retail price columns in here and we're going to

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 going to click on the record macro button and don't worry it's not going to be recording until after we clear the dialog box so when you click that button the record macro dialog 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 alphanumeric and can contain the underscore character no spaces so we're going to name this macro capital a add capital f formatting almost together so it follows the naming convention we're not going to 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 going to 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 going to 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 going to type in a description box add column headers comma bold and centered period apply accounting format to column g period autofit 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 ok 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're saving in our macro so for right now the first thing that we want to do here is right click on row heading 1 and choose insert so we get a blank row a new row one and that's the row that we're going to be adding our column

headers in and so we're going to click in cell a1 and we're going to type then v i n and i'm doing it in all Capital letters so vehicle identification number i'm going to press my tab key to get over to b1 and i'm going to type year tab make tab model tab again classification the next one is color the next one is dealer cost two separate words and lastly in all caps msrp for manufacturers suggested retail price after that one i'm going to just press enter so we have our headers in where they need

to be and then we're going to select row heading 1 again and we're going to go to the home tab of the ribbon in the font group we're going to make it bold and in the paragraph group we're going to use the center alignment button so the headers are centered within their cells now we're also going to add in adjusting column width in here so don't worry about that right now the next thing we're going to do is we're going to 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 going to click on the dollar sign for the accounting number format and then the last thing we're going to do is we're going to select column headings a through h and i just clicked on a and dragged across and We want to auto fit these columns with right so that's what we want to do we want to 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 going

to select the format drop down and from that drop down you're going to 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 width 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 going to 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 ctrl z which is undo and the only thing it undid was the column auto with 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 autofit so we want to redo that step so we're going to just do 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 want to 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 going to click on macros and we only have one ad formatting it's already selected and on the right side you're going to 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 the 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 going to focus on the vba window right now and so before we get started in here we're ultimately going to 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 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 tool

bars that we use 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 properties window at the bottom if you don't have both of those panes 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 ctrl r to bring up project explorer and f4 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 xlsx 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 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.xlsx 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 2 and a sheet 3 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 1 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 going to 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 going to do is we're going to go up to the tools menu and click on options we will revisit some of these 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 windows 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 going to click ok so font size and If

you want to go back in and change the font i'm cool with the code looking font courier 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 going to 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 want to add three

icons to the toolbar that we'll be using frequently throughout this course so what i'm going to do is i'm going to just right click in a blank area of any toolbar and go to customize and the customize dialog box opens you want to 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 it 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 uncomment block so what i'm going to do when i find comment block i'm going to click and hold on it and i'm going to drag it right after the question mark on my toolbar and then i'm going to grab uncomment block and drag it right after comment block now we

have one more command that we're going to add up there under categories we're going to click on debug and the command at the top of the list is compile project i'm going to grab that and drag it right after uncomment block so i have comment block uncomment block and compile project when we get ready to use those commands i will explain what they're going to do for us they're going to 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 ad 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 end sub statement you have to have both if you have a sub and not an n sub you're going to get an error message but it did this automatically while we were recording our macro it gave it the name that we named it ad 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 dialog box right we named it ad formatting and we put a description in add column headers bold and centered apply accounting format blah 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 the 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 clean up there um we don't need that blank apostrophe at the top so i'm going to just delete it and then i'm going to 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 rows one colon one dot 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 in cell a1 and we typed it in so that's range even though it's a cell range a1 dot select notice the quote the double quotes inside the parentheses there surrounding a1 and an active cell which is now cell a1 don't worry about formula r1c1 right now But activecell.formular1c1 equals whatever we typed in that cell then b1 c1 d1 e1 f1

g1 h1 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 false to true and then you have what's known as a with 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 width 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 going to 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 so what happened is when we did that the dot horizontal alignment equals excel center it was changed from excel left because typically things are left aligned in a cell so all of these other settings are the default settings the only thing that changed was excel center one of the things you you want in your code is for it to be as concise as possible we don't need everything that's in that with block we we really would just keep the horizontal and vertical alignment but we didn't do

any wrap text orientation indenting any of that kind of stuff so we're going to select the lines from wrap text all the way down to the dot merge cells equals false statement we need to leave that end with statement underneath that so once you have dot wrap text through dot merge cells line selected go ahead and press delete and then i'm a little ocd so i'm gonna shift tab to get the end with statement back in line with the with statement so if i have to troubleshoot code i want to make sure that i can

see with and end with at the same margin if that makes sense And when we start putting our code together i'll be walking you through how it should be formulated so when we were recording our macro we formatted column g as currency column g is the dealer cost column and we should have formatted the manufacturer's suggested retail price column column h well it says currency in here but it's actually accounting okay so we want both of those columns to be formatted in accounting underneath your end with statements you'll see columns and then in parentheses in

double quotes g colon g dot select the second g you're going to change to an h so now we're telling it when this macro runs select both of those columns and apply the format on both of those columns and that's what we want to do and then we have where we selected columns a through h and we did our columns autofit and then i said at the end before we stop recording click on any blank cell whatever cell you clicked on it will say range and then in parentheses and double quotes that cell dot select

we're going to change whatever that cell is that cell reference we want it to be a one and so we just modified the code that was generated for our macros now we're just going to Modify our comment our second comment line here to make it accurate so apply a counting format to columns we're going to make that plural columns g and then and h and when you move away from that line it will turn green again indicating that it's a comment now that we've edited our code and we'll test it shortly um we want to

change the name of module one you want your module names to be as descriptive as possible um it's kind of like folders that you're organizing your code in so we're gonna have several modules throughout this course and we don't want them to be module one module two module three module four so we're gonna use the properties window to make some changes to properties so we're going to start with renaming module 1. in your project explorer window click on module 1 and then if you look down at your properties window it says properties for module one

the object that we have selected and the only property a module has is the name property so if you double click on name it highlights module one for you and we're going to talk about a prefix that we're going to use here To identify it in a list as a module we're going to use lowercase mod mod to indicate that it is a module and then we're going to give it its name and that's going to be capital f and the rest of the word first so mod first if we're looking at a bunch of

stuff in a list we'll know that it's a module because it has that three character prefix of mod and then when you press enter you'll notice that it updated the name of the module in the project explorer window you can change the names of sheet tabs in here as well so in your project explorer you have that microsoft excel objects folder where you're seeing your three sheet tabs we have one and it's called sheet1 and in parentheses inventory sheet1 is how excel knows that sheet inventory is what it was renamed to but the system identifies

it as sheet1 and then we have sheet2 and we have sheet3 we're gonna click on sheet3 and in the properties window you'll notice that there are two name properties the one at the top is indicative of the system name right so what excel knows is that name is the name property At the top my arrow got a little skewed there and then you have a name property lower down in your properties window and the one that's lowered down is whatever you decide to name that sheet so the second name property the one that is not

in parentheses you can double click right on name and it selects sheet three and we're going to type new vehicles two separate words just like you would do it in excel if you were renaming the sheet tab and press enter so notice in your project explorer now you have your sheet1 which had already been renamed to inventory sheet 3 which we just named new vehicles we added the compile project icon to our toolbar and what that does is it kind of goes through your code for you and it checks to make sure that everything is

okay if there's a problem it will let you know by highlighting that problem so what we're going to do is make sure you're clicked anywhere between your sub and in sub statements you can be anywhere between those two statements and go up to your toolbar and find your compile vba project icon that we added there and just give it a click and it dims out and you didn't get any notifications of anything wrong so your code is Good good habit to get into good icon to have on your toolbar so now that we've modified our

code we want to save this file and if you look up at the top you know your title bar is vehicles.xlsx the standard four character excel workbook extension if we don't save this file in the right way it's as if we didn't record a macro and we wouldn't have any vba code in it so you have to save it as a macro enabled workbook as a matter of fact let's check this out on the toolbar you have a save button and notice when you hover over it it wants to save the file go ahead and

click on that save icon and you get this pop-up message that says the following features cannot be saved in macro free workbooks and then it says vb project that's your visual basic project to save a file with your code in it click no and then choose a macro enabled file type to continue saving as a macro free workbook click yes we are going to click no when you click no it gives you the save as dialog box now we're going to leave the same name vehicles but we need to change the type right underneath the

file name where it Says save as type click on excel workbook and we want the second choice excel macro enabled workbook again if we just save this as a regular excel file it's not going to keep your code so we need it to be macro enabled and part of that is for your security which i'll explain in just a few moments and after we have it set as excel macro enabled workbook we're going to go ahead and click save so now if you look up at the title bar it has a different extension dot xlsm

that's your macro enabled excel workbook extension and so it will retain our code and now that we've saved it as macro enabled we're going to switch over to excel to test our edited code there's a couple of ways that you can switch over to excel back and forth between excel and visual basic first of all and i'm not going to use this way but the first button on the toolbar is excel so i can use that to switch back over to excel i'm a real shortcut key person so alt f11 will also switch me back

and forth between excel and the visual basic editor so i can just keep doing alt f11 and you see that It switches back and forth between the two separate application windows that are open when i am back in excel i'm going to do control o to get the open dialog box to display so this is where we're going to open the vehicles 2 file and by the way if you look at my screen we have two files we have vehicles and vehicles too right we have two versions of vehicles i'm pointing to the macro enabled

worksheet and then we have our original one which is just a regular excel file and if you look closely the icons are slightly different the macro enabled worksheet icon has an extra little thing in its lower right hand corner than the other regular excel icons so we want to open the vehicles to excel workbook and so we have two excel files open we have our macro enabled vehicles and we have vehicles too earlier when we went to record our macro i said if you save your macro in the personal macro workbook it opens behind the

scenes every time you open any file in excel this is a workaround we save that macro in the vehicles file but we can use it in vehicles too as long as the vehicles dot Xlsm file is open i don't typically save in the personal macro workbook there are occasions that i do that if i'm going to use it across a wide range of files but typically my files are structured so differently that macros from one won't work well with others so what we're going to do here is we want to use the macro that we

recorded the add formatting macro we want to use it in this file another way of getting to your macros instead of going to the developer tab is you can go to the view tab of the ribbon and all the way to the right the last button is macros so check this out if i do the drop down arrow under macros i can view or record them um and i'm going to just go to view macros notice that it gives the file name exclamation point and then the name of the macro that vehicles.xlsm file has to

be open and it's showing you macros in all open workbooks if we did the drop down and we choose this workbook right it's showing there as well because the other one's open so you can access it but it really resides in that other file vehicles the macro enabled vehicles file and so we're going to just click on vehicles xlsm exclamation point ad formatting and choose Run so you'll notice that it gave us our headings they're centered and bolded notice one of the changes we made is we had it do the accounting format on the msrp

column right so that worked we also cleaned up some comments and stuff in there as well so you will see that if you have the file that contains the macro open you can access it from other excel files that you open also notice that the active cell is cell a1 because we changed that as well we're going to close this vehicles 2 file without saving any changes to it so i'm going to just close that file when it prompts me i'm going to say don't save the changes and i still have my macro enabled vehicles

file open so in our macro enabled vehicles file first take a note look at your um sheet tabs so we renamed sheet 3 new vehicles and that's what shows on the sheet down there we did that in the properties window in the visual basic editor so another change that we made in there that you're seeing in here and i mentioned earlier and we saw that you can't really undo the effects of a macro When we tried to do it it didn't really undo it it only undid the column autofit right so we saw that but

i said that there is a workaround like let's say on the sheet 2 right that we wanted to test the macro and it's already been run so we certainly don't want it to put another row in here right at the top so on sheet 2 what i'm going to have you do is just delete row one so i'm just right clicking on the row header and choosing delete and now i'm gonna go to the view tab drop down view my macros and i'm going to rerun this macro so it puts the headings back in right

we see that we have the currency format on or accounting format on the columns g and h and it put us in cell a1 so you would have to kind of manually delete the effects of a macro to rerun it on a sheet like that we're going to go ahead and right click on the sheet2 sheet tab and we want to delete sheet 2 and confirm the deletion so now we have two sheets in this macro enabled file we have our inventory sheet and we have our blank for right now new vehicle sheet let's go

ahead and save And close this vehicle's file now we're going to explore what happens when you open a file that is macro enabled so what i'm going to do is i'm going to just relaunch excel and go to open and navigate to wherever you saved your macro enabled vehicles file and when you're there you can go ahead and select the vehicles macro enabled file i'm going to just double click on it here and open it now because it's macro enabled you're going to get the yellow band underneath the ribbon that says security warning macros have

been disabled and then there's an enable content button microsoft does this by default that's the default security settings for a file that contains macros that has been saved as macro enabled and it's to kind of protect you so if you receive a file from an unknown source you probably do not want to enable the content it's not the typical way of delivering viruses to computers anymore but it used to be delivered via macros that were infected via viruses so if it's from a trusted source you can go ahead and enable the content which we're gonna

do now that can be annoying if you're Working with a lot of macro enabled files and you know the people that are creating them maybe your colleagues yourself you all work in teams or something like that so we're gonna modify a truss center setting and you'll see how this works so what we're going to do is we're going to go to the file tab and all the way at the bottom on the left you're going to go to options on the left side of excel options at the bottom you're going to click on trust center

and then on the right you're going to click on trust center settings so the first thing i want you to see here is on the left side click on macro settings so the default setting is to disable vba macros with notification that's what the yellow band that we got and i wouldn't suggest changing that what you can do to make it a little bit more convenient and you don't get that yellow band when you're you know using files back and forth amongst your teammates or colleagues is we can set either trusted documents on the left

or trusted locations i always do trusted locations it's more global than trusted documents so click on trusted locations and i'll tell you um how this works right so i set up a folder on my desktop it's called files for video description And it's where i've stored all the files that we're going to be using during this course and i want to set that as a trusted location so any of the macro enabled file formats that we're going to be using during this course that folder and its any subfolders will be a trusted location so what

i'm going to do underneath here is i'm going to click on add new location toward the bottom right and then i have to navigate to that location so i'm going to just do that i know it's on my desktop so you're navigating to wherever you put the files that you're going to be using so the folder name for me is files for video description and i'm going to click ok and then i'm going to say subfolders of this location are also trusted so any folders within that folder would also be trusted and then i'm going

to click ok and once i do that it shows up in this list of trusted locations so it could be a shared network drive if you're all of your files that you and your team are working together on are in a specific shared network drive you can use that as a trusted location so that you don't get the yellow warning and have to enable content at the bottom we're going to click ok And then let's close and reopen this vehicle's macro enabled file so when i open it it's in a trusted location now so i

don't get the yellow banner under the ribbon i don't have to enable content and i didn't disable the default trust center setting so that's still in place but this file is now in a trusted location so i'm able to open it with no problems just to recap what we covered in this introductory lesson one on the getting started section we reviewed what you can do with visual basic for applications you learn that it is an object oriented programming language that is part of the microsoft office suite you learned that it's the code that is produced

in the background when you record a macro you also learn the advantages of visual basic for applications over recording macros we moved into customizing the ribbon to add the developer tab and we recorded a macro in excel that added and formatted column headings on a worksheet we then toured the visual basic editor environment and were able to edit the vba code produced by our recorded macro we also changed some vbe settings Including font size and we saved a macro enabled file you tested your edited macro and lastly modified some trust center security settings by adding

a trusted location you also understand the importance of saving a macro enabled workbook or else all of your work would be lost before we get hands on again in lesson three we need to go over some background information and i should mention at this point that this slide deck is also in the video description and you can use it for your future reference no one is expecting you to memorize all of these things so we need to talk about the excel object model you need to understand collections and how to reference objects and also how

to simplify object references you learned that vba is an object-oriented programming language and we discussed that that means that excel is an object that contains objects objects that have attributes known as properties can be manipulated through vba and objects also have methods which are actions that can be performed on them via vba so this is a foundational lesson here and again you'll be able to reference the slide deck So the excel object model hierarchy is a hierarchical structure of excel and all its objects with excel the application residing at the top of the hierarchy so

you can see on this slide that we have the application at the top of the hierarchy here on the left and then underneath the application you would have add in objects window objects workbook objects and then we expand on the workbook object a workbook object has a name a visual basic project a workbook also contains worksheets worksheets can contain comments hyperlinks names ranges and pivot tables for example so a simplistic example is over on the right where you have excel the application which is an object at the top of the hierarchy then you have a

workbook a worksheet is an object of a workbook and a range is an object of a worksheet and you're going to be seeing this as we're working in this video course the next thing you need to understand are collections now collections are key in vba programming a collection is simply a group of objects of the same type a collection is also an object collections are useful when you Need to work with not just one worksheet but with a couple of them or all of them you'll learn later that your vba code can loop through all

members of a collection and do something to each one we have a table that shows the collections the most commonly used collections in visual basic so you have the workbooks collection and it would be all currently open workbook objects so all currently open cell files are part of the workbooks collection you have a worksheets collection and it's a collection of all worksheets contained in a particular workbook the charts collection is a collection of all chart objects so these are excel worksheets that only have charts on them a collection of all chart objects contained in a

particular workbook object a chart sheet is not part of the worksheets collection as it is not considered a worksheet it is in fact a worksheet but it only has a chart on it and then you have the sheets collection plural a collection of all worksheet objects contained in a particular workbook object so you have a worksheets collection and then you have a sheets collection what is the difference The sheets collection contains all worksheets and all chart sheets whereas the worksheets collection is just all of the worksheets and then the charts collection is just all of

the chart sheets the sheets collection combines them all so you can reference these things sometimes across different collections as you will see when we resume with our hands-on stuff in order to access the objects that you want to reference in your code you're going gonna have to learn how to reference them so you can work with an entire collection quite easily but it's most often the case that you'll need to work with a particular object in a collection for example if you need to work with a particular worksheet you'll need to know how to reference

it when it comes to referring to an object in a collection you can either use its name or its index number to reference a single object from a collection you put the item's name or index number in parentheses after the name of the collection so you have two examples on this slide that refer to the exact same object the first worksheet in the workbook so worksheets and then in Parentheses and double quotes sheet1 by name or worksheets and in parentheses 1 by index number when you refer to an object by its name the name must

be enclosed in quotes when referring to an object by its index number you need to know that the first object in the collection has the index number of one using a workbook with three worksheets as an example sheet one has an index number of one sheet two would be two and sheet three's index number would be three and index numbers are not enclosed in quotes we're going to spend a few slides on referencing objects so each worksheet in the worksheet's collection is an object in two collections which we mentioned already worksheets and sheets each chart

sheet is an object in two collections charts and sheets so there's another example of how to refer to sheet1 you could use the sheets collection instead of the worksheets collection like a typical office application you get several ways of referring to the same object you know there's multiple ways of getting the same thing done in excel and along the way you'll find a way that works best for you although i do have some suggestions so all excel objects are under the application object in the hierarchy remember excel The application is at the top of the

hierarchy you access these objects by moving down the hierarchy and connecting each object with the dot operator you can even access the value of a particular cell on the worksheet by using the range object and you saw that when we reviewed our code there is no cell object in vba really it would be the range object even with one element and here are additional examples on this slide of object references so referring to a specific workbook application dot workbooks and then in parentheses and double quotes the name of the workbook including its extension and then

a specific worksheet in the workbook if you're using the entire hierarchy application.workbooks the name of the workbook and then dot worksheets 1 the first sheet referring to the value of a cell your application.workbooksname.worksheets1 dot range a1 dot value the last example is known as a fully qualified reference it specifically refers to all the objects in the hierarchy from application down to range so again you have this power point in your video description for future reference now i'm going to tell you About simplifying object references you want it to be as concise as possible and i

i mentioned that earlier it's easier to write and easier to troubleshoot should there be any issues so visual basic for applications can assume some of the object references for you for example it assumes that the workbook's collection is within the application object and that means that you don't have to reference that object in most cases another example here is that if you only have one workbook open one excel file open it is the only one in the collection so vba assumes it is the active workbook and you don't have to reference the workbooks collection if

the first worksheet is the active sheet in the workbook you don't have to reference the worksheets collection as vba will assume it so gonna go over these examples the code that refers to the value of a cell so starting with the application application.workbooks the name of the workbook in parentheses and double quotes dot worksheets 1 by index number dot range a1 dot value if vba is assuming the application object it Would just start with workbooks in the name of the workbook so on and so forth if it's the only open excel file it will assume

the workbooks object so you can just start with worksheets and if the first sheet is the active sheet all you would have to reference is range a1 dot value and so the last example is the most concise and therefore preferable you don't want to have to type all of this line unless you just like to type like that my arrow drawing is getting horrible here but you don't have to type all of that why would you want to type all of that maybe fat finger to keyboard whatever set of circumstances could happen when all you

would have to type would be this and also if someone is coming behind you or if you have to troubleshoot the code this is a lot more difficult to troubleshoot because it has more on this line than the last and preferable example now that we've gained an understanding of the excel object model hierarchy collections and how to reference objects we're ready to dive into lesson three which is working with procedures and functions previously you were introduced to modules and sub procedures when we edited the vba code that was generated by the macro we recorded you're

going to get more In-depth knowledge on those two topics during this lesson so we're going to start by doing a deeper dive into modules and then procedures then we are going to move on to creating a standard module as well as creating a sub procedure you'll learn different methods of executing procedures or running them including how to call procedures in this lesson then we're going to create a function procedure and we're going to use the immediate window within the visual basic editor to run it you're going to learn how to get context sensitive help within

the visual basic editor which is really cool we're going to use the vehicles macro enable file that we created as well as another file in the video description and it's another excel file named received vehicles those two files need to be in the same directory for this lesson you saw earlier that when we recorded a macro vba created a module to store the visual basic code in and so we discussed how a module is a container of sorts that stores your vba code well there are two different types of modules there are class modules Which

allow you to create your own objects and we'll begin using class modules when we get to lesson seven and then there's standard modules one of which you already saw and it uses the application objects we also discussed that our code was put in a procedure specifically in our case it was a sub procedure began with the sub statement and end it with the n sub statement so a procedure tells the application what to do as it defines a specific task specifically it's a named group of statements that are run as a unit for example a

block of code a sub procedure defines specific tasks like our ad formatting sub procedure it told the application what to do so insert a new row and then we typed in the headers that kind of thing when responding to events you'll learn about these later they can be known as event procedures there are also function procedures and you'll be introduced to creating function procedures during this lesson a function procedure actually returns a value property procedures are outside of the scope of this course but they exist and they're are series of statements that allow a programmer

to create and manipulate custom properties for example read-only properties for forms Before we dive in we're going to go over the naming rules and conventions for procedures now the rules cannot be broken conventions are suggestions but just so you're aware of them you need to know what they are and i use conventions as well as the rules so if you inherit an excel macro enabled workbook and you're looking at somebody else's code they may be using the conventions so anyway let's go over this and then we'll get hands on and learn more about procedures and

modules by recording another macro so when we're naming procedures the name may be up to 255 characters it can be alphanumeric the first character must be a letter you can't use any spaces or any of the special characters listed on the slide and the names must be unique within a module so you can't have to add formatting sub procedures within the same module it will disallow it you just can't do it and then the conventions begin the names with a verb use proper case for each word within the name when two or more procedures are

related place the words that vary at the end and there are some examples so you could name a procedure close workbook get new inventory get selected date begin and get Selected date end so if you're looking at these in a list the ones that have the words that vary at the end would still be all together in the list so you know that they're related procedures i'm back in the vehicles macro enabled file that we created earlier and i'm going to switch to the new vehicles sheet tab now we're going to record a macro again

to get this lesson started and along the way and going forward beyond this lesson whether we're recording macros or we're writing vba code from scratch we're also going to be using properties and we're going to be using methods now you saw properties we use properties to change the name of a module we use properties to change the name of this particular sheet tab and a method is an action that is taken on an object so you'll learn how to access methods along the way as well and i want to show you another way of starting

a macro recording without accessing the ribbon so i'd like to direct your attention to your status bar and it's at the very bottom underneath your sheet tabs of your excel screen so the area all the way at the bottom now it starts with ready You may or may not have num lock and some of the other stuff that i have on my status bar but in particular i'm interested in making sure that you have this icon i don't know it looks like a spreadsheet with a little camera on it in a way and if you

don't have it i'm going to show you how to get it down there you can right-click in a blank area of the status bar and you get the customize status bar menu and notice that a lot of the things on my customize menu are already checked which means they will show in my status bar as appropriate so for example caps lock and num lock my num lock is on and that's why it's showing down in the status bar my caps lock is currently not being held down i'm not enabling caps lock on my keyboard i

should say so that one is not showing there but where i to enable it it would show in my status bar the setting that we're interested in having you have down there is macro recording that's that icon so if it doesn't have a check mark in front of it and you click on it it will check it if it does and you click on it it unchecks it like it just did on mine so i'll click on it again And then you just click on any blank cell you want to make sure you're in cell

a1 before we start recording this macro and what we're going to do is we're going to go down to that icon in our status bar that looks like a spreadsheet with a looks like a camera lens to me but if you hover over it it says no macros are currently recording click to begin recording a new macro and we're going to do just that and it gives us our record macro dialog box so you could start this process from the developer tab or you can start it from that icon on your status bar now for

this macro we want to name it using our rules and conventions so we're going to start with a verb capital g get capital n new capital v vehicles because remember you can't have spaces you could use the underscore character but i prefer to just use this convention we're not going to use the shortcut key we're going to store it in this workbook and we're going to click in the description box and we're going to type copy and paste data from the received vehicles file and again it once we click ok it's ready to follow our

instructions so go ahead and click ok notice that the icon on your status bar now looks different and it looks like a stop button and if you hover over it it says click here or a macro is currently Recording click to stop the recording so we can start and stop it from the status bar as well so what we want to do is we want to open the received vehicles file that is in the same directory as this file and i'm going to use the shortcut combination control o to bring up my open dialog box

i'm already in the proper directory for myself and i'm going to just double click that received vehicles excel file to open it and once it's opened you'll see that it has a limited amount of data there's some then numbers and dealer costs and so what we're going to do is we are going to click anywhere on the spreadsheet it's only on sheet 1 and we're going to hold down our control key and type the letter a and the letter c so a selects everything and c is the copy command and now we want to switch

back to our vehicles macro enabled file so one way to do that is we can go to our view tab on the ribbon the next to the last button is switch windows and i'm going to use i'm going to just click on vehicles to switch back to that file we're already in cell a1 in the vehicles file on the new vehicles tab i'm going to Just do control v as in victor to paste in the information we have one more step we're going to take before we stop this macro recording i'm going to switch back

to the received vehicles file and i'm going to just close it and now i can go down to my status bar and stop the recording before we switch over to the visual basic editor to look at the code that we just generated just wanted to give you some examples of methods so i've said that a method is an action that is taken on an object so clear contents is a method of the range object it would empty the cells delete everything that's in the cells clear all of everything that's in the cells in the range

add is a method of the workbook's collection object and it's used to create a new workbook and then you have activate and that's a method of the worksheet object it's used to activate a worksheet you know if i click on the inventory sheet tab i'm activating that sheet so examples of how you access the methods you start with the object so range a1 colon b11 in parentheses and double quotes you use the dot notation and then you can access the method clear Contents workbooks dot add worksheets you have to give it the name or the

index number of the sheet dot activate so we're seeing the dot notation used to call methods as well and when you're in the editor and you do the dot it sometimes gives you and usually gives you built-in help that it will list the methods that are available for that particular object i'm going to just delete this text box off of my spreadsheet then i'm going to alt f11 to switch over to the visual basic editor the first thing i want to point out is the project explorer window the upper left corner and if you look

in your modules folder now we renamed what used to be called module 1 mod first so because we had closed the file and reopened it when we recorded this macro it created a new module since module 1 wasn't in use anymore it used that and so we would have to go there to find the code that we just recorded the macro for so i'm going to switch to module one and it created a sub procedure we named it get new vehicles we put stuff in the description explaining what it is and you know we can

get rid of those extra green lines that are preceded By apostrophes that don't need to be there just to kind of clean up the code of course you want it as concise as possible when we did control o the open command it did this activewindow.close and then workbooks.open right so open is a method of the workbooks object close is a method of the active window object so we did workbooks open we had to give it the name of the file and these characters here so it says file name followed by a colon equal then there's

a space and an underscore the space and the underscore means the rest of this syntax the rest of what is needed here is on the next line it's called the line continuation character space and underscore combined like that in visual basic is the line continuation character and that's what we have after file name colon equal if we didn't have that there we would get an error message because it's saying hey you gotta give me the path to the file name including the file name so it just goes to where i have my on my hard

drive users trish desktop files for video description and then it's in that received vehicles the name of the file is receive vehicles.xlsx And that line starting with c begins with an open double quote and ends with a closing double quote and then i scrolled a little bit when i got over to that other file receive the vehicles and it took note of that that's this active window small scroll down and then we did control a to select everything which is a 1 through b11 dot select select is a method of the range object and then

we have range a2 activate and then we did copy so we actually just did control ac and it created those three lines of code and then after we copied it we switched back over to the vehicles macro enabled file we click in cell a1 and then we pasted what was on the clipboard onto the active sheet starting in cell a1 and then we went back over to received vehicles file calling the activate method and then we said close that window and then of course with every sub statement you have your n sub statement at the

bottom now that we've reviewed this code that was generated from us recording that macro there's a few things we want to do we want to rename module one so it's more specific and we want to create a new sub procedure from scratch in the newly named module so let's start In our project explorer window i'm gonna just click on module one go down the properties window double-click name and we're gonna call this mod remember that three character prefix which is a convention not a rule mod new vehicles and i'm using the proper casing all mushed

together and then i'm going to press enter so we have mod first and mod new vehicles in mod new vehicles click at the end of your in sub statement and press enter we're going to go to the insert menu and we're going to choose procedure and this is a microsoft product so this is just one way of doing this in the add procedure dialog box we're going to start work our way from top to bottom right in the name box we're going to name this procedure move cells so capital m capital c mushed together procedure

name move cells the type defaults to sub for a sub procedure and we're going to talk about the scope in a little while but we're going to leave it on public and we're going to just click ok so a couple of things happened it automatically put a dividing line between your get new vehicle sub procedure and this new sub Procedure and it has the keyword public because we did it from within the dialog box and again you'll learn about scope in a little while and that's what public speaks to it gave us our sub and

our n sub statements and of course the open and closing parentheses at the end of the first line so what i'm going to have you do is i'm going to have you click outside of the closing parentheses public sub move cells press enter twice and then press the tab key on your keyboard i talked about code being concise but also you need to know this you're going to probably end up spending more time troubleshooting code than actually writing code it's just the way it goes and there are certain things in terms of how you format

you your code that makes it easier to review when you're troubleshooting and indentation is one of those things i like to look at my code and see the public sub and in sub statements at the same margin and that's what we're doing by indenting here so now you're going to type a few lines of code four lines to be exact and you can take them off of my screen the first line is going to be columns Plural open paren open double quote b close your double quote close your paren so columns b so we're referencing

column b you're going to use the dot notation and start typing select and if i don't type it with a capital s when i press enter at the end it will automatically capitalize it for me and now we're going to type selection dot cut enter i'll explain it all when we get it in and then we're going to type columns open paren open double quote g close the quote close the paren dot select active sheet dot paste and typically i like to have a blank line after public sub and a blank line before end sub

so my previous block my get new vehicle sub procedure i'm going to just get that blank line above the n sub statement so basically it's going to select everything in column b and it's going to cut it put it on the clipboard and then it's going to select column g and it's going to paste what was in column b into column g congratulations you just wrote four lines of vba code from scratch now earlier we added some buttons to our toolbar and we used one of these earlier Be anywhere in between your public sub and

your in sub statements and go on and click that compile vba project that we put up on the toolbar earlier and again if there were any errors in your code you would be notified of it it's kind of line checking your code you accessed the select method of the columns object and once you have something selected it is known as the selection object so you access the cut method of the selection object and the paste method of the active sheet object in that code block so let's go ahead and save is save on the toolbar

or you could do control s and then we're going to do alt f11 to switch back over to excel so we can test this code there is another way that you can access your macros in here so earlier we accessed our macros from the developer tab by using the second button macros we also accessed it from the view tab of the ribbon by going to the last button which is macros well there's another way of doing it and it's a shortcut key so you can use alt f8 to open the macro dialog box and so

in here you'll see your ad formatting macro your Get new vehicles macro both of those were based off of macro recordings i should say and then move cells is listed in here even though we created that sub procedure from scratch so this is just another way alt f8 that you can open the macro dialog box and we're going to double click the move cells procedure so you could click it once and then choose run or you could just double click it to run it and it did exactly what we asked it to do just very

quickly selected everything in column b cut it and then pasted it into column g so the next part we're going to do is going to further our automation what we want to do is we want to edit the add formatting sub procedure so that it works on this sheet the new vehicle sheet that we're currently on and before we do that we're going to delete everything on this sheet new vehicles we're going to do control a to select everything and then we're going to just press delete and then just click in cell a1 now we

want to go back over to the visual basic editor alt f11 will get you there and in the project explorer we're going to double click mod first Which is where our ad formatting sub procedure resides now notice the first two lines in our code block that's when we right clicked on row one and we inserted a new row well we don't want to do that again we want to keep the row headings that we already have so we don't want it to insert a blank row above row one which now contains the headings so we

need to delete just those two lines of code rows one colon one dot select and selection dot insert shift blah blah blah the first two lines so i already have them selected and i'm going to delete those two lines and then what i'm going to do is in your project explorer window go ahead and double click mod new vehicles again to get back into that module where we have our get new vehicles which is the result of a macro and notice when you do a macro it gives you in green that it's based off of

a macro you didn't create this code and then whatever description you put in right so we should probably add comments just so i can be a good example here to our move cells sub procedure i'm going to click at the end of the public sub line and press enter twice And then i'm going to type an apostrophe now when you type an apostrophe it's like saying the visual basic hey heads up the next thing i'm going to type is going to be a comment which means you're not going to execute it when the code executes

it's just there for informational purposes and we're going to say that this is going to select and cut contents of column b and paste into column g and then when i press enter it will turn that line green because we started with an apostrophe before we do the next bit i just want to go over that there are different ways to execute your procedures or run them within the application or in vba so we've used some of these already um we've used the macro dialog box the ones that are highlighted on in yellow on this

slide we will use during this course so you'll later on we'll use the run sub user form button on the vbe toolbar we've already used the macro dialog box we're going to call the procedure from another sub procedure which is the one the example we're going to do next later on we're going to add a button to the quick access toolbar in excel you can also attach the procedure to an event which You will learn later and also you can run it from what's known as the immediate window in visual basic editor so many different

ways to execute procedures so like i mentioned we're going to be calling the procedure from another sub procedure in this lesson we're going to click at the end of the end sub statement in your move cells public sub procedure and we're going to press enter twice we're going to create another sub procedure here going to do it in a different way and it's going to be known as the calling procedure you'll see how this works and i'll explain more after we get it done now we're going to actually just type this in we're not going

to use the add procedure insert procedure dialog box and so we clicked after in sub we pressed enter a couple of times and we're going to type sub and then we're going to name it get new inventory and press enter twice so you get the line the dividing line between the move cells sub procedure notice we didn't have to type the opening and closing paren or the end sub statement the visual basic editor will supply you with those when you're typing it from scratch so now we're Going to press i'm going to just do shift

tab to out dent let's do a comment here i'm trying to be a good example we're going to type an apostrophe and we're going to put this is a calling procedure that is calling three other procedures and press enter at the end of the line it turns green press enter again and then tab to indent we simply need to give it the names of the procedures so this is known as a calling procedures and the procedure names we're going to type are known as called procedures so we are going to type get new vehicles enter

move cells enter add formatting enter it should look like this on my screen so get new inventory's purpose is to kind of bundle those three other procedures together in the order that they're listed there and it will execute them when it is executed so we get three for the price of one we execute get new inventory and it executes three other sub procedures for us go ahead and compile your project and save we have our three procedures that are called procedures in our get new inventory sub procedure Two of which reside in this same module

ad formatting resides in mod first we're going to talk now about the scope of procedures and why it's possible that we can access the ad formatting procedure from a different module so of course we're going to review the scope of procedures on the slide because again the slide deck is in the files in the video description and you'll be able to re use it for future reference so scope determines where the procedure may be used you have basically three different scopes really two so the first one is public if a procedure declaration is preceded by

the keyword public this makes the procedure accessible to all modules in the visual basic project if the procedure declaration is preceded by the keyword private this makes the procedure available only in the module where it resides it cannot be accessed from any other modules or from the excel workbook and then you have undeclared so we have no private procedures at this point if no keyword is inserted at the start of a sub or a function declaration the default is public okay so i'm going to switch back Over to the visual basic editor and we'll take

a look at this and i'm still in mod new vehicles so we have the result of a macro we recorded a macro and we named it get new vehicles it simply named it sub it didn't put public or private so it's considered undeclared which means it's public then we created a procedure by using the insert menu and because in there and i'll bring up that dialog box the scope is defaulting to public so when we created the move cells procedure it added the public keyword and then we create it from one from scratch sub get

new inventory we didn't type public before sub we left it undeclared so all of these are public procedures and if we go to mod first add formatting is also undeclared which means it's public and i look at the end of that and i want that blank line above in sub so i just modified it and did that so because ad formatting is public i'm going to go back to mod new vehicles we can access it by calling it from this module that's why that's possible the scope of procedures matter now one other little fix we're

gonna do because i already know it's going to be an era and you may not even have this line in your get new vehicles sub procedure in mod New vehicles but that first line of code since get new vehicles is included in our calling procedure if we have the first line saying active window close it's going to just close the file and then it won't be able to run the rest of the code so that very first line active window close if you have that line you know could cause you to have an issue with

the code if you don't have that line as your first line if your first line is workbooks dot open file name so on and so forth you're good to go but if not you want to delete that first line active window closed we're going to leave it at the bottom but not at the top and then go ahead and save your code and now we're ready to alt f11 to switch over to excel and we're on the new vehicle sheet in cell a1 we're going to do alt f8 to bring up our macros box and

we want to run our calling procedure get new inventory so i'm going to just double click it and my screen will flicker for a moment and then ultimately stop okay so what happened well it ran the bundle let's switch back over alt f11 the first thing that it did is it ran get new vehicles so it opened the received vehicles File it selected everything and then it copied everything and then it went back over to our vehicles macro enabled file made sure it was in cell a1 and it pasted the data and then it went

back over to received vehicles file and closed it after that it did the move cells sub procedure so once it brought in that information from received vehicles it selected everything in column b and it pasted it it cut it and then it pasted it in column g and then after that it did add formatting which is in mod first and that's where it added the headings for us right so it added our column headings and we didn't want it to create a new row there because it came in with a heading row so we had

already modified this it did the currency formatter accounting number format and it auto fitted the columns it also made the headings bold and centered in their cells so it ran the bundle we ran one sub procedure that called three other procedures so when i go back and look at it in excel we're still on new vehicles right it moved the dealer costs to the appropriate column applied the appropriate formatting to it and it added the headings instead Of the headings that came in from the received vehicles file and the formatting that it did so you

saw that calling procedures lend themselves to efficiency but there is further efficiency to be had earlier we discussed how sometimes a sub procedure is known as an event procedure and that happens when you attach it to an event so first thing we're going to do here is we're going to do control a on this new vehicles sheet to select everything and then we're going to press delete we're going to clear the sheet just click in cell a1 and switch back over to your visual basic editor now on the left side you attach to in order

to make something an event procedure you have to access an object's events so we're going to double click in the project explorer window we're going to double click sheet 3 new vehicles now notice at the very top of your screen you have two different drop downs above option explicit which we still haven't talked about but we're getting there you have a general box and to the right you have a declarations box both of them have drop downs on their far right sides the left side where it says general is known As the object list the

right side where it says declarations currently is known as the procedures or events list so we're going to do the drop down arrow to the right of general and the only thing because we have sheet3 selected the only object we're going to see is worksheet so we're going to select worksheet and it creates this whole like sub procedure framework it's private so it's only available within where we're putting it it's attached only to the new vehicle sheet which the system knows as sheet 3 right so the first thing the default event for a worksheet if

you look at the right now where it used to say declarations it says selection change that's just the default event that's not the event that we want what we want to happen is we want our calling procedure to run whenever the new vehicle sheet is activated so we're going to do the drop down all the way to the far right of selection change and these are all worksheet events right activate before delete calculate you have a whole list we want to select the activate event so now it gives us the framework For a private sub

procedure that it names worksheet underscore activate typically with event procedures you don't change the names right it's kind of like hard coded so it's that particular worksheet activate event that we're going to code so we don't need this second block we don't need this this is just what was created as soon as we selected worksheet it selected the default event which is selection change so we can just delete that click at the end of the parentheses on the private sub worksheet activate line press enter twice just so you get that blank space again we're going

to press tab so it's indented and we're simply going to call our calling procedure by name we named it get new inventory so that's the one that has the the three called procedures within it so we're saying whenever the new vehicle sheet is activated go ahead and run that bundled calling procedure known as get new inventory go ahead and compile your vba project using the toolbar you want to get in the habit of doing that and save and switch back to excel Just make sure you're in cell a1 on new vehicles which we deleted everything

off of and now we're going to go to our inventory sheet tab at the bottom and then we're going to click back on the new vehicle sheet tab to activate it and voila as soon as we activated that sheet it ran our get new inventory calling procedure so we get the same result we didn't have to do alt f8 to get into the macros dialog box to make it run or anything like that we simply made it an event procedure and it was the activate event for this particular sheet we are going to once again

clear this sheet by doing control a and delete just click back in cell a1 and go ahead and save your vehicle's file so now we're ready to create a function procedure and if you remember from earlier a function procedure performs a calculation and returns a single value or an array of values so differs from a sub procedure so when you create a function procedure in vba it can only be executed in two ways compared to the nine ways you can execute a sub procedure you can call the procedure from a sub procedure or another function

procedure or you can use the function in a worksheet formula It becomes a user defined function in your excel application and you'll see both of these play out when we do this we're going to create a new module for this so i'm going to just go up to the insert menu in the visual basic editor and i'm going to choose module so it gives me a new module one and and let's rename that module using the properties window we're going to call it mod and then capital f function and in this module we're going to

create a function and what it's going to do is we're going to give it different values for an msrp and depending on the value it's going to display a message and once we get it typed in and stuff it'll be easier to kind of explain it to you so in your mod function i'm going to click at the top after option explicit and just press enter and we're going to type the word function and a space and we're going to call it msrp so all of that is capitalized and then capital s status and then

in the parentheses we have to have an open parenthesis we're going to type msrp that's a parameter you'll also hear it as an argument so we have to pass The function an argument in order for it to give us a value as its return and after that you're going to press enter and notice it gives you the end function statement just like it does when we do a sub procedure and it gives us the end sub statement so i'm going to press enter again and i am going to press my tab key to indent it's

just a structure thing visual basic doesn't care now we're going to build what's known as an if construct it's more specifically it's an if then else if else construct which is a lot to say and can be overwhelming it's similar to the if function in excel you're being rudely introduced to it for this function but you'll get a deeper dive in control of flow structures in a later lesson so i will explain this after we get it typed in so we're going to type if and then msrp we're going to do the less than symbol

20 000 no punctuation on the 20 000 and type then and press enter now tab a little bit more one more time so if the msrp value is less than 20 000 then if that is true it's going to do what we say on this line we're going to type now we're going to type msrp Status the name of our function equals and in double quotes less than 20k and close your quotes so if the msrp value is less than 20 000 it's going to say as the function return the result of the function it's

going to display the text less than 20k press enter and then shift tab and you're going to type else if and it's all one word initial capital letters msrp less than thirty thousand then next line down you're gonna tab msrp status the name of the function equals and then double quotes less than 30k and close your quotes enter and shift tab we're almost done we have two more lines now we're going to type else just else not else if space msrp status the name of the function again equals and then double quotes greater than or

equal to 30 k and close your double quotes enter and we need an end if statement so end if and press enter one more time so you get that blank line between end if and in function so If the msrp is less than 20 000 it will display the tax less than 20k if it's less than 30 000 meaning it's not less than 20 000 but it's less than thirty thousand it is say less than thirty k to display that text if neither of those statements are true if it's not less than twenty thousand and

it's not less than thirty thousand it's going to just display greater than or equal to 30k so the else statement and notice when you got to the end of the line it put that colon after else you didn't have to type that so there's a line editor every time you get to the end of the line it's checking the line to make sure it's okay so this is known as an if construct it's going to do different things depending on whether a statement is true and that's how you create the function procedure now we should

go ahead and compile our project so again you just need to be anywhere between function and in function and do your compile project make sure there's no errors and then we're going to save so there are two ways to execute a function procedure the first way is by using what's Called the immediate window and it's really testing the function to make sure that it works properly and so we're going to do that way first and then i'll tell you about the second way which is really super cool in my opinion um and you'll see that

soon so the first thing we need to do is get the immediate window open in visual basic editor so we're gonna go to the view menu and we're going to choose immediate window and it opens in the bottom of the screen in the immediate window we can test this function procedure i'll explain it after we type it in you're going to type question mark and then msrp status the name of the function and then in parentheses now if you notice right underneath where we're typing in the immediate window right right underneath that it's showing that

in the parentheses it's waiting for us to give it a argument so we use msrp in the parentheses as a parameter and now we have to pass it an argument in order for the function to work which means we have to give it a number that represents an msrp in the parentheses we're going to just type 10 000 no punctuation close the parentheses and press enter question mark means the same as print in vb a right so It's saying print the result right there in the immediate window so if we look at our if block

we said if it's less than 20 000 we want it to say less than 20k and that's exactly what it did now we can use either the question mark or the print keyword to the same effect in the immediate window so now in the immediate window you're going to type print and then msrp status open paren and we're going to put 20 000 in there and close the parentheses and now press enter so it's giving us less than thirty thousand so question mark and the print keyword do the same exact thing in the immediate window

we're testing our function to make sure that it works so let's do another one we could do either question mark or print whichever your preferred one is and we're going to set it up again msrp status and this time in the parentheses we're going to put 40 000 close the parentheses press enter and we should get the greater than or equal to 30k the else statement is working because it's not less than 20 thousand it's not less than thirty thousand the parameter that we gave it the argument that we gave it in the parentheses It's

forty thousand so we're getting greater than or equal to thirty k if you're creating super complicated function procedures you would want to test them in your immediate window giving it different values to make sure it works the other way to test it let's go ahead and switch over to excel i'm doing alt f11 there okay and let's go to the inventory sheet tab and click in cell i to and so i2 you're going to type an equal sign like you would for any built-in excel function and then you're going to start typing the name of

the function we're going to type ms and our msrp status function shows up here it's a user defined function in the excel application once you create it unless you go and delete that function you will be able to use it in excel we're going to click on or since msrp is already selected i'm going to just tab key it's called tabbing it in and then we are going to click on cell h2 the very first msrp and we don't even have to type the closing parentheses here because we're doing it in excel application so go

ahead and press enter And then click on i2 and the little notch in the lower right hand corner of the cell is known as the fill handle and once i put my mouse on top of the fill handle it looks like a thin black cross and i am going to double click so you'll notice it'll fill it all the way down to the bottom until it runs into a blank right so if i do control n it filled it all the way down to row 105. and it's looking at each msrp's value and determining what

to display in the adjoining cell and so we are going to right click on the column i column heading and delete that column and i want to show you something else since we're in here go to the formulas tab on your ribbon and the first button is insert function click it where it says or select a category most recently used do the drop down arrow and you might have to scroll down to see it but you're looking for a category called user defined now there's going to be stuff in here that you're like what does

that mean don't worry about it this stuff that comes into excel automatically but you'll see your msrp status in that list so that is a user defined function in the excel application Which i think is just pretty cool we can just cancel out of that and go ahead and save your file so our last topic in this lesson is getting context sensitive help in the visual basic editor so i'm going to do alt f11 and switch back over to vbe and in vbe i'm still in my mod function module i'm going to just click anywhere

in the word function at the beginning of that function procedure and i'm going to press f1 on my keyboard so it brings up the context help box because you're in excel visual basic for applications and it's saying that there's an object in both visual basic and excel called function we're interested in the vba function so we can just either double click it or go over and click on help it's already selected so it launches your browser and i can tell you that the vba documentation from microsoft is awesome so it gives you the function statement

from vba it lets you know that it declares the name arguments and code that form the body of a function procedure it gives you the syntax so you know just like sub procedures there's different statements public Private right if it's undeclared it's public um there's also friend and static and then the function name and then in parentheses the argument list and you can give it a data type as its return as well which we didn't have to do on hours right um there's also an exit function statement that we didn't need to use so it

gives you the breakdown about everything descriptions on everything right and so and then there's remarks at the bottom and so it says there's one line in here that says all execute all executable code must be in procedures you can't define a function procedure inside of another function sub or property procedure and notice that sub there is a link so if i click on that link it brings me to context sensitive help about the sub statement and the other thing i'll point out in here is on the left side it lets you know that you're looking

at the sub statement right so if i scroll up on the left side there's the if then else construct and again we're going to do a deeper dive into that later but if i wanted more information on that i can just click on it on the left side right and it gives you all the information you need to Know about that if i continue to scroll up it's all under the heading statements which is under the heading reference which is under the heading language reference so anything you need to know i'm going to just switch

back over to my visual basic editor you can access it by pressing f1 when you are on a particular keyword right so it works that way it gets you into the microsoft documentation really good context specific help at your fingertips so we covered a lot in lesson three and just to recap what we covered we went into a deeper dive about the types of modules that are available within visual basic and the types of procedures then we went on to creating a standard module we created a sub procedure and we called procedures by creating a

calling procedure you also learned about the scope of procedures then we created a function procedure and we tested it using the immediate window before going into excel and accessing it in its interface as a user defined function after that you were shown how to get context sensitive help from within the visual basic Editor thank you for attending excel 2019 visual basic for applications video course hi everyone i'm trish connor cato welcome to the excel 2019 visual basic for applications video course this course is for beginning users looking to automate repetitive and recurring tasks in microsoft

excel vba is microsoft's programming language and it's built into the office applications our focus during this course is on excel specifically you'll be equipped with the basics to start writing your own vba code modify the code behind macros you've already recorded and have an understanding of how vba lends itself to creating efficiency in your daily tasks we'll start with the basics where you'll learn the types of things you can do with vba versus recording macros in excel as well as some key terminology which will help in your vba journey and you will get to edit

vba code you'll learn the importance of macro enabled workbooks and how to save them as well as how to modify some security settings we'll move on to understanding the excel object model collections and how to reference objects in vba code 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 off-site 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 below in our first lesson getting started you're going to 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 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 going to be using two files that are in the video description there 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 want to do is grab those two excel files vehicles and vehicles too from the video description and put them somewhere on your system where you have easy access to them before we get hands-on let's go ahead and get an overview of vba so as mentioned in the introductions vba is a microsoft programming language that is currently built into the

excel word powerpoint outlook and access applications it's one of the many programming languages that evolved from the basic programming language which was developed in the 1960s it was first released in excel 5 and that was in the office 1995 suite since then it evolved to encompass The applications that are mentioned in this slide vba is known as an object-oriented programming language oop 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 cells worksheets charts pivot tables shapes etc each object has its own properties for example a worksheet has a name a workbook can be opened and closed and these are a couple of a workbook's 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 vba 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 dialog boxes and message boxes in excel so we're going to 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 want to prepare excel for recording a macro there are several ways that you can start a macro recording but we want to add

the developer tab to the ribbon as that is one of the ways that 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 going to choose customize the ribbon in the customize the ribbon options box on the right side everything that has a check mark is a tab that's showing on your ribbon we want to check the box in front of developer and then

at the bottom you're going to click ok 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 going to be using this make vehicle information so we have make-believe then numbers we have year we have make model classification color dealer cost and manufacturer suggested retail price columns in here and we're going to 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 going to click on the record macro button and don't worry it's not going to be recording until after we clear the dialog box so when you click that button the record macro dialog 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 alphanumeric and can contain the underscore character no spaces so we're going to name this macro capital a

add capital f formatting almost together so it follows the naming convention we're not going to 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 going to 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 going to 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 going to type in a description box add column headers comma bold and centered period apply accounting format to column g period autofit

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 ok 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're saving in our macro so for right now the first thing that we want to do here is right click on row heading 1 and choose insert so we get a blank row a new row one and that's the row that we're going to be adding our column headers in and so we're going to click in cell a1 and we're going to type then v i n and i'm doing it in all Capital letters so vehicle identification number

i'm going to press my tab key to get over to b1 and i'm going to type year tab make tab model tab again classification the next one is color the next one is dealer cost two separate words and lastly in all caps msrp for manufacturers suggested retail price after that one i'm going to just press enter so we have our headers in where they need to be and then we're going to select row heading 1 again and we're going to go to the home tab of the ribbon in the font group we're going to make

it bold and in the paragraph group we're going to use the center alignment button so the headers are centered within their cells now we're also going to add in adjusting column width in here so don't worry about that right now the next thing we're going to do is we're going to 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 going to click on the dollar sign for the accounting number format and then the last thing we're going to do is

we're going to select column headings a through h and i just clicked on a and dragged across and We want to auto fit these columns with right so that's what we want to do we want to 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 going to select the format drop down and from that drop down you're going to 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 width 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 going to 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 ctrl z which is undo and the only thing it undid was the column auto with 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 autofit so we want to redo that step so we're going to just do 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 want to 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 going to click on macros and we only have one ad formatting

it's already selected and on the right side you're going to 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 the 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 going to focus on the vba window right now and so before we get started in here we're ultimately going

to 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 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 tool bars that we use 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 properties window at the bottom if you don't have both of those panes 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 ctrl r to bring up project explorer and f4 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 xlsx 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 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.xlsx 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 2 and a sheet 3 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 1 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 going to 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 going to do is we're going to go up to the tools menu and click on options we will revisit some of these 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 windows 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 going to click ok so font size and If you want to go back in and change the font i'm cool with the code looking font courier 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 going to 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 want to add three icons to the toolbar that we'll be using frequently throughout this course so what i'm going to do is i'm going to just right click in a blank area of any toolbar

and go to customize and the customize dialog box opens you want to 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 it 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 uncomment block so what i'm going to do when i find comment block i'm going to click and hold on it and i'm going to drag it right after the question mark on my toolbar and then i'm going to grab uncomment block and drag it right after comment block now we have one more command that we're going to add up there under categories we're going to click on debug and the command at the top of the list is compile project i'm

going to grab that and drag it right after uncomment block so i have comment block uncomment block and compile project when we get ready to use those commands i will explain what they're going to do for us they're going to 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 ad 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 end sub statement you have to have both if you have a sub and not an n

sub you're going to get an error message but it did this automatically while we were recording our macro it gave it the name that we named it ad 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 dialog box right we

named it ad formatting and we put a description in add column headers bold and centered apply accounting format blah 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 the 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 clean up there um we don't need that blank apostrophe at the top so i'm going to just delete it and then i'm going to 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 rows one colon one dot 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 in cell a1 and we typed it in so that's range even though it's a cell range a1 dot select notice the quote the double quotes inside the parentheses there surrounding a1 and an active cell which is now cell a1 don't worry about formula r1c1 right now But activecell.formular1c1 equals whatever we typed in that cell then b1 c1 d1 e1 f1 g1 h1 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 false to true and then you have what's known as a with 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 width 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 going to 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 so what happened is when we did that the dot horizontal alignment equals excel center it was changed from excel left because typically

things are left aligned in a cell so all of these other settings are the default settings the only thing that changed was excel center one of the things you you want in your code is for it to be as concise as possible we don't need everything that's in that with block we we really would just keep the horizontal and vertical alignment but we didn't do any wrap text orientation indenting any of that kind of stuff so we're going to select the lines from wrap text all the way down to the dot merge cells equals false

statement we need to leave that end with statement underneath that so once you have dot wrap text through dot merge cells line selected go ahead and press delete and then i'm a little ocd so i'm gonna shift tab to get the end with statement back in line with the with statement so if i have to troubleshoot code i want to make sure that i can see with and end with at the same margin if that makes sense And when we start putting our code together i'll be walking you through how it should be formulated so

when we were recording our macro we formatted column g as currency column g is the dealer cost column and we should have formatted the manufacturer's suggested retail price column column h well it says currency in here but it's actually accounting okay so we want both of those columns to be formatted in accounting underneath your end with statements you'll see columns and then in parentheses in double quotes g colon g dot select the second g you're going to change to an h so now we're telling it when this macro runs select both of those columns and

apply the format on both of those columns and that's what we want to do and then we have where we selected columns a through h and we did our columns autofit and then i said at the end before we stop recording click on any blank cell whatever cell you clicked on it will say range and then in parentheses and double quotes that cell dot select we're going to change whatever that cell is that cell reference we want it to be a one and so we just modified the code that was generated for our macros now

we're just going to Modify our comment our second comment line here to make it accurate so apply a counting format to columns we're going to make that plural columns g and then and h and when you move away from that line it will turn green again indicating that it's a comment now that we've edited our code and we'll test it shortly um we want to change the name of module one you want your module names to be as descriptive as possible um it's kind of like folders that you're organizing your code in so we're gonna

have several modules throughout this course and we don't want them to be module one module two module three module four so we're gonna use the properties window to make some changes to properties so we're going to start with renaming module 1. in your project explorer window click on module 1 and then if you look down at your properties window it says properties for module one the object that we have selected and the only property a module has is the name property so if you double click on name it highlights module one for you and we're

going to talk about a prefix that we're going to use here To identify it in a list as a module we're going to use lowercase mod mod to indicate that it is a module and then we're going to give it its name and that's going to be capital f and the rest of the word first so mod first if we're looking at a bunch of stuff in a list we'll know that it's a module because it has that three character prefix of mod and then when you press enter you'll notice that it updated the name

of the module in the project explorer window you can change the names of sheet tabs in here as well so in your project explorer you have that microsoft excel objects folder where you're seeing your three sheet tabs we have one and it's called sheet1 and in parentheses inventory sheet1 is how excel knows that sheet inventory is what it was renamed to but the system identifies it as sheet1 and then we have sheet2 and we have sheet3 we're gonna click on sheet3 and in the properties window you'll notice that there are two name properties the one

at the top is indicative of the system name right so what excel knows is that name is the name property At the top my arrow got a little skewed there and then you have a name property lower down in your properties window and the one that's lowered down is whatever you decide to name that sheet so the second name property the one that is not in parentheses you can double click right on name and it selects sheet three and we're going to type new vehicles two separate words just like you would do it in excel

if you were renaming the sheet tab and press enter so notice in your project explorer now you have your sheet1 which had already been renamed to inventory sheet 3 which we just named new vehicles we added the compile project icon to our toolbar and what that does is it kind of goes through your code for you and it checks to make sure that everything is okay if there's a problem it will let you know by highlighting that problem so what we're going to do is make sure you're clicked anywhere between your sub and in sub

statements you can be anywhere between those two statements and go up to your toolbar and find your compile vba project icon that we added there and just give it a click and it dims out and you didn't get any notifications of anything wrong so your code is Good good habit to get into good icon to have on your toolbar so now that we've modified our code we want to save this file and if you look up at the top you know your title bar is vehicles.xlsx the standard four character excel workbook extension if we don't

save this file in the right way it's as if we didn't record a macro and we wouldn't have any vba code in it so you have to save it as a macro enabled workbook as a matter of fact let's check this out on the toolbar you have a save button and notice when you hover over it it wants to save the file go ahead and click on that save icon and you get this pop-up message that says the following features cannot be saved in macro free workbooks and then it says vb project that's your visual

basic project to save a file with your code in it click no and then choose a macro enabled file type to continue saving as a macro free workbook click yes we are going to click no when you click no it gives you the save as dialog box now we're going to leave the same name vehicles but we need to change the type right underneath the file name where it Says save as type click on excel workbook and we want the second choice excel macro enabled workbook again if we just save this as a regular excel

file it's not going to keep your code so we need it to be macro enabled and part of that is for your security which i'll explain in just a few moments and after we have it set as excel macro enabled workbook we're going to go ahead and click save so now if you look up at the title bar it has a different extension dot xlsm that's your macro enabled excel workbook extension and so it will retain our code and now that we've saved it as macro enabled we're going to switch over to excel to test

our edited code there's a couple of ways that you can switch over to excel back and forth between excel and visual basic first of all and i'm not going to use this way but the first button on the toolbar is excel so i can use that to switch back over to excel i'm a real shortcut key person so alt f11 will also switch me back and forth between excel and the visual basic editor so i can just keep doing alt f11 and you see that It switches back and forth between the two separate application windows

that are open when i am back in excel i'm going to do control o to get the open dialog box to display so this is where we're going to open the vehicles 2 file and by the way if you look at my screen we have two files we have vehicles and vehicles too right we have two versions of vehicles i'm pointing to the macro enabled worksheet and then we have our original one which is just a regular excel file and if you look closely the icons are slightly different the macro enabled worksheet icon has an

extra little thing in its lower right hand corner than the other regular excel icons so we want to open the vehicles to excel workbook and so we have two excel files open we have our macro enabled vehicles and we have vehicles too earlier when we went to record our macro i said if you save your macro in the personal macro workbook it opens behind the scenes every time you open any file in excel this is a workaround we save that macro in the vehicles file but we can use it in vehicles too as long as

the vehicles dot Xlsm file is open i don't typically save in the personal macro workbook there are occasions that i do that if i'm going to use it across a wide range of files but typically my files are structured so differently that macros from one won't work well with others so what we're going to do here is we want to use the macro that we recorded the add formatting macro we want to use it in this file another way of getting to your macros instead of going to the developer tab is you can go to

the view tab of the ribbon and all the way to the right the last button is macros so check this out if i do the drop down arrow under macros i can view or record them um and i'm going to just go to view macros notice that it gives the file name exclamation point and then the name of the macro that vehicles.xlsm file has to be open and it's showing you macros in all open workbooks if we did the drop down and we choose this workbook right it's showing there as well because the other one's

open so you can access it but it really resides in that other file vehicles the macro enabled vehicles file and so we're going to just click on vehicles xlsm exclamation point ad formatting and choose Run so you'll notice that it gave us our headings they're centered and bolded notice one of the changes we made is we had it do the accounting format on the msrp column right so that worked we also cleaned up some comments and stuff in there as well so you will see that if you have the file that contains the macro open

you can access it from other excel files that you open also notice that the active cell is cell a1 because we changed that as well we're going to close this vehicles 2 file without saving any changes to it so i'm going to just close that file when it prompts me i'm going to say don't save the changes and i still have my macro enabled vehicles file open so in our macro enabled vehicles file first take a note look at your um sheet tabs so we renamed sheet 3 new vehicles and that's what shows on the

sheet down there we did that in the properties window in the visual basic editor so another change that we made in there that you're seeing in here and i mentioned earlier and we saw that you can't really undo the effects of a macro When we tried to do it it didn't really undo it it only undid the column autofit right so we saw that but i said that there is a workaround like let's say on the sheet 2 right that we wanted to test the macro and it's already been run so we certainly don't want

it to put another row in here right at the top so on sheet 2 what i'm going to have you do is just delete row one so i'm just right clicking on the row header and choosing delete and now i'm gonna go to the view tab drop down view my macros and i'm going to rerun this macro so it puts the headings back in right we see that we have the currency format on or accounting format on the columns g and h and it put us in cell a1 so you would have to kind of

manually delete the effects of a macro to rerun it on a sheet like that we're going to go ahead and right click on the sheet2 sheet tab and we want to delete sheet 2 and confirm the deletion so now we have two sheets in this macro enabled file we have our inventory sheet and we have our blank for right now new vehicle sheet let's go ahead and save And close this vehicle's file now we're going to explore what happens when you open a file that is macro enabled so what i'm going to do is i'm

going to just relaunch excel and go to open and navigate to wherever you saved your macro enabled vehicles file and when you're there you can go ahead and select the vehicles macro enabled file i'm going to just double click on it here and open it now because it's macro enabled you're going to get the yellow band underneath the ribbon that says security warning macros have been disabled and then there's an enable content button microsoft does this by default that's the default security settings for a file that contains macros that has been saved as macro enabled

and it's to kind of protect you so if you receive a file from an unknown source you probably do not want to enable the content it's not the typical way of delivering viruses to computers anymore but it used to be delivered via macros that were infected via viruses so if it's from a trusted source you can go ahead and enable the content which we're gonna do now that can be annoying if you're Working with a lot of macro enabled files and you know the people that are creating them maybe your colleagues yourself you all work

in teams or something like that so we're gonna modify a truss center setting and you'll see how this works so what we're going to do is we're going to go to the file tab and all the way at the bottom on the left you're going to go to options on the left side of excel options at the bottom you're going to click on trust center and then on the right you're going to click on trust center settings so the first thing i want you to see here is on the left side click on macro settings

so the default setting is to disable vba macros with notification that's what the yellow band that we got and i wouldn't suggest changing that what you can do to make it a little bit more convenient and you don't get that yellow band when you're you know using files back and forth amongst your teammates or colleagues is we can set either trusted documents on the left or trusted locations i always do trusted locations it's more global than trusted documents so click on trusted locations and i'll tell you um how this works right so i set up

a folder on my desktop it's called files for video description And it's where i've stored all the files that we're going to be using during this course and i want to set that as a trusted location so any of the macro enabled file formats that we're going to be using during this course that folder and its any subfolders will be a trusted location so what i'm going to do underneath here is i'm going to click on add new location toward the bottom right and then i have to navigate to that location so i'm going to

just do that i know it's on my desktop so you're navigating to wherever you put the files that you're going to be using so the folder name for me is files for video description and i'm going to click ok and then i'm going to say subfolders of this location are also trusted so any folders within that folder would also be trusted and then i'm going to click ok and once i do that it shows up in this list of trusted locations so it could be a shared network drive if you're all of your files that

you and your team are working together on are in a specific shared network drive you can use that as a trusted location so that you don't get the yellow warning and have to enable content at the bottom we're going to click ok And then let's close and reopen this vehicle's macro enabled file so when i open it it's in a trusted location now so i don't get the yellow banner under the ribbon i don't have to enable content and i didn't disable the default trust center setting so that's still in place but this file is

now in a trusted location so i'm able to open it with no problems just to recap what we covered in this introductory lesson one on the getting started section we reviewed what you can do with visual basic for applications you learn that it is an object oriented programming language that is part of the microsoft office suite you learned that it's the code that is produced in the background when you record a macro you also learn the advantages of visual basic for applications over recording macros we moved into customizing the ribbon to add the developer tab

and we recorded a macro in excel that added and formatted column headings on a worksheet we then toured the visual basic editor environment and were able to edit the vba code produced by our recorded macro we also changed some vbe settings Including font size and we saved a macro enabled file you tested your edited macro and lastly modified some trust center security settings by adding a trusted location you also understand the importance of saving a macro enabled workbook or else all of your work would be lost before we get hands on again in lesson three

we need to go over some background information and i should mention at this point that this slide deck is also in the video description and you can use it for your future reference no one is expecting you to memorize all of these things so we need to talk about the excel object model you need to understand collections and how to reference objects and also how to simplify object references you learned that vba is an object-oriented programming language and we discussed that that means that excel is an object that contains objects objects that have attributes known

as properties can be manipulated through vba and objects also have methods which are actions that can be performed on them via vba so this is a foundational lesson here and again you'll be able to reference the slide deck So the excel object model hierarchy is a hierarchical structure of excel and all its objects with excel the application residing at the top of the hierarchy so you can see on this slide that we have the application at the top of the hierarchy here on the left and then underneath the application you would have add in objects

window objects workbook objects and then we expand on the workbook object a workbook object has a name a visual basic project a workbook also contains worksheets worksheets can contain comments hyperlinks names ranges and pivot tables for example so a simplistic example is over on the right where you have excel the application which is an object at the top of the hierarchy then you have a workbook a worksheet is an object of a workbook and a range is an object of a worksheet and you're going to be seeing this as we're working in this video course

the next thing you need to understand are collections now collections are key in vba programming a collection is simply a group of objects of the same type a collection is also an object collections are useful when you Need to work with not just one worksheet but with a couple of them or all of them you'll learn later that your vba code can loop through all members of a collection and do something to each one we have a table that shows the collections the most commonly used collections in visual basic so you have the workbooks collection

and it would be all currently open workbook objects so all currently open cell files are part of the workbooks collection you have a worksheets collection and it's a collection of all worksheets contained in a particular workbook the charts collection is a collection of all chart objects so these are excel worksheets that only have charts on them a collection of all chart objects contained in a particular workbook object a chart sheet is not part of the worksheets collection as it is not considered a worksheet it is in fact a worksheet but it only has a chart

on it and then you have the sheets collection plural a collection of all worksheet objects contained in a particular workbook object so you have a worksheets collection and then you have a sheets collection what is the difference The sheets collection contains all worksheets and all chart sheets whereas the worksheets collection is just all of the worksheets and then the charts collection is just all of the chart sheets the sheets collection combines them all so you can reference these things sometimes across different collections as you will see when we resume with our hands-on stuff in order

to access the objects that you want to reference in your code you're going gonna have to learn how to reference them so you can work with an entire collection quite easily but it's most often the case that you'll need to work with a particular object in a collection for example if you need to work with a particular worksheet you'll need to know how to reference it when it comes to referring to an object in a collection you can either use its name or its index number to reference a single object from a collection you put

the item's name or index number in parentheses after the name of the collection so you have two examples on this slide that refer to the exact same object the first worksheet in the workbook so worksheets and then in Parentheses and double quotes sheet1 by name or worksheets and in parentheses 1 by index number when you refer to an object by its name the name must be enclosed in quotes when referring to an object by its index number you need to know that the first object in the collection has the index number of one using a

workbook with three worksheets as an example sheet one has an index number of one sheet two would be two and sheet three's index number would be three and index numbers are not enclosed in quotes we're going to spend a few slides on referencing objects so each worksheet in the worksheet's collection is an object in two collections which we mentioned already worksheets and sheets each chart sheet is an object in two collections charts and sheets so there's another example of how to refer to sheet1 you could use the sheets collection instead of the worksheets collection like

a typical office application you get several ways of referring to the same object you know there's multiple ways of getting the same thing done in excel and along the way you'll find a way that works best for you although i do have some suggestions so all excel objects are under the application object in the hierarchy remember excel The application is at the top of the hierarchy you access these objects by moving down the hierarchy and connecting each object with the dot operator you can even access the value of a particular cell on the worksheet by

using the range object and you saw that when we reviewed our code there is no cell object in vba really it would be the range object even with one element and here are additional examples on this slide of object references so referring to a specific workbook application dot workbooks and then in parentheses and double quotes the name of the workbook including its extension and then a specific worksheet in the workbook if you're using the entire hierarchy application.workbooks the name of the workbook and then dot worksheets 1 the first sheet referring to the value of a

cell your application.workbooksname.worksheets1 dot range a1 dot value the last example is known as a fully qualified reference it specifically refers to all the objects in the hierarchy from application down to range so again you have this power point in your video description for future reference now i'm going to tell you About simplifying object references you want it to be as concise as possible and i i mentioned that earlier it's easier to write and easier to troubleshoot should there be any issues so visual basic for applications can assume some of the object references for you for

example it assumes that the workbook's collection is within the application object and that means that you don't have to reference that object in most cases another example here is that if you only have one workbook open one excel file open it is the only one in the collection so vba assumes it is the active workbook and you don't have to reference the workbooks collection if the first worksheet is the active sheet in the workbook you don't have to reference the worksheets collection as vba will assume it so gonna go over these examples the code that

refers to the value of a cell so starting with the application application.workbooks the name of the workbook in parentheses and double quotes dot worksheets 1 by index number dot range a1 dot value if vba is assuming the application object it Would just start with workbooks in the name of the workbook so on and so forth if it's the only open excel file it will assume the workbooks object so you can just start with worksheets and if the first sheet is the active sheet all you would have to reference is range a1 dot value and so

the last example is the most concise and therefore preferable you don't want to have to type all of this line unless you just like to type like that my arrow drawing is getting horrible here but you don't have to type all of that why would you want to type all of that maybe fat finger to keyboard whatever set of circumstances could happen when all you would have to type would be this and also if someone is coming behind you or if you have to troubleshoot the code this is a lot more difficult to troubleshoot because

it has more on this line than the last and preferable example now that we've gained an understanding of the excel object model hierarchy collections and how to reference objects we're ready to dive into lesson three which is working with procedures and functions previously you were introduced to modules and sub procedures when we edited the vba code that was generated by the macro we recorded you're going to get more In-depth knowledge on those two topics during this lesson so we're going to start by doing a deeper dive into modules and then procedures then we are going

to move on to creating a standard module as well as creating a sub procedure you'll learn different methods of executing procedures or running them including how to call procedures in this lesson then we're going to create a function procedure and we're going to use the immediate window within the visual basic editor to run it you're going to learn how to get context sensitive help within the visual basic editor which is really cool we're going to use the vehicles macro enable file that we created as well as another file in the video description and it's another

excel file named received vehicles those two files need to be in the same directory for this lesson you saw earlier that when we recorded a macro vba created a module to store the visual basic code in and so we discussed how a module is a container of sorts that stores your vba code well there are two different types of modules there are class modules Which allow you to create your own objects and we'll begin using class modules when we get to lesson seven and then there's standard modules one of which you already saw and it

uses the application objects we also discussed that our code was put in a procedure specifically in our case it was a sub procedure began with the sub statement and end it with the n sub statement so a procedure tells the application what to do as it defines a specific task specifically it's a named group of statements that are run as a unit for example a block of code a sub procedure defines specific tasks like our ad formatting sub procedure it told the application what to do so insert a new row and then we typed in

the headers that kind of thing when responding to events you'll learn about these later they can be known as event procedures there are also function procedures and you'll be introduced to creating function procedures during this lesson a function procedure actually returns a value property procedures are outside of the scope of this course but they exist and they're are series of statements that allow a programmer to create and manipulate custom properties for example read-only properties for forms Before we dive in we're going to go over the naming rules and conventions for procedures now the rules cannot

be broken conventions are suggestions but just so you're aware of them you need to know what they are and i use conventions as well as the rules so if you inherit an excel macro enabled workbook and you're looking at somebody else's code they may be using the conventions so anyway let's go over this and then we'll get hands on and learn more about procedures and modules by recording another macro so when we're naming procedures the name may be up to 255 characters it can be alphanumeric the first character must be a letter you can't use

any spaces or any of the special characters listed on the slide and the names must be unique within a module so you can't have to add formatting sub procedures within the same module it will disallow it you just can't do it and then the conventions begin the names with a verb use proper case for each word within the name when two or more procedures are related place the words that vary at the end and there are some examples so you could name a procedure close workbook get new inventory get selected date begin and get Selected

date end so if you're looking at these in a list the ones that have the words that vary at the end would still be all together in the list so you know that they're related procedures i'm back in the vehicles macro enabled file that we created earlier and i'm going to switch to the new vehicles sheet tab now we're going to record a macro again to get this lesson started and along the way and going forward beyond this lesson whether we're recording macros or we're writing vba code from scratch we're also going to be using

properties and we're going to be using methods now you saw properties we use properties to change the name of a module we use properties to change the name of this particular sheet tab and a method is an action that is taken on an object so you'll learn how to access methods along the way as well and i want to show you another way of starting a macro recording without accessing the ribbon so i'd like to direct your attention to your status bar and it's at the very bottom underneath your sheet tabs of your excel screen

so the area all the way at the bottom now it starts with ready You may or may not have num lock and some of the other stuff that i have on my status bar but in particular i'm interested in making sure that you have this icon i don't know it looks like a spreadsheet with a little camera on it in a way and if you don't have it i'm going to show you how to get it down there you can right-click in a blank area of the status bar and you get the customize status bar

menu and notice that a lot of the things on my customize menu are already checked which means they will show in my status bar as appropriate so for example caps lock and num lock my num lock is on and that's why it's showing down in the status bar my caps lock is currently not being held down i'm not enabling caps lock on my keyboard i should say so that one is not showing there but where i to enable it it would show in my status bar the setting that we're interested in having you have down

there is macro recording that's that icon so if it doesn't have a check mark in front of it and you click on it it will check it if it does and you click on it it unchecks it like it just did on mine so i'll click on it again And then you just click on any blank cell you want to make sure you're in cell a1 before we start recording this macro and what we're going to do is we're going to go down to that icon in our status bar that looks like a spreadsheet with

a looks like a camera lens to me but if you hover over it it says no macros are currently recording click to begin recording a new macro and we're going to do just that and it gives us our record macro dialog box so you could start this process from the developer tab or you can start it from that icon on your status bar now for this macro we want to name it using our rules and conventions so we're going to start with a verb capital g get capital n new capital v vehicles because remember you

can't have spaces you could use the underscore character but i prefer to just use this convention we're not going to use the shortcut key we're going to store it in this workbook and we're going to click in the description box and we're going to type copy and paste data from the received vehicles file and again it once we click ok it's ready to follow our instructions so go ahead and click ok notice that the icon on your status bar now looks different and it looks like a stop button and if you hover over it it

says click here or a macro is currently Recording click to stop the recording so we can start and stop it from the status bar as well so what we want to do is we want to open the received vehicles file that is in the same directory as this file and i'm going to use the shortcut combination control o to bring up my open dialog box i'm already in the proper directory for myself and i'm going to just double click that received vehicles excel file to open it and once it's opened you'll see that it has

a limited amount of data there's some then numbers and dealer costs and so what we're going to do is we are going to click anywhere on the spreadsheet it's only on sheet 1 and we're going to hold down our control key and type the letter a and the letter c so a selects everything and c is the copy command and now we want to switch back to our vehicles macro enabled file so one way to do that is we can go to our view tab on the ribbon the next to the last button is switch

windows and i'm going to use i'm going to just click on vehicles to switch back to that file we're already in cell a1 in the vehicles file on the new vehicles tab i'm going to Just do control v as in victor to paste in the information we have one more step we're going to take before we stop this macro recording i'm going to switch back to the received vehicles file and i'm going to just close it and now i can go down to my status bar and stop the recording before we switch over to the

visual basic editor to look at the code that we just generated just wanted to give you some examples of methods so i've said that a method is an action that is taken on an object so clear contents is a method of the range object it would empty the cells delete everything that's in the cells clear all of everything that's in the cells in the range add is a method of the workbook's collection object and it's used to create a new workbook and then you have activate and that's a method of the worksheet object it's used

to activate a worksheet you know if i click on the inventory sheet tab i'm activating that sheet so examples of how you access the methods you start with the object so range a1 colon b11 in parentheses and double quotes you use the dot notation and then you can access the method clear Contents workbooks dot add worksheets you have to give it the name or the index number of the sheet dot activate so we're seeing the dot notation used to call methods as well and when you're in the editor and you do the dot it sometimes

gives you and usually gives you built-in help that it will list the methods that are available for that particular object i'm going to just delete this text box off of my spreadsheet then i'm going to alt f11 to switch over to the visual basic editor the first thing i want to point out is the project explorer window the upper left corner and if you look in your modules folder now we renamed what used to be called module 1 mod first so because we had closed the file and reopened it when we recorded this macro it

created a new module since module 1 wasn't in use anymore it used that and so we would have to go there to find the code that we just recorded the macro for so i'm going to switch to module one and it created a sub procedure we named it get new vehicles we put stuff in the description explaining what it is and you know we can get rid of those extra green lines that are preceded By apostrophes that don't need to be there just to kind of clean up the code of course you want it as

concise as possible when we did control o the open command it did this activewindow.close and then workbooks.open right so open is a method of the workbooks object close is a method of the active window object so we did workbooks open we had to give it the name of the file and these characters here so it says file name followed by a colon equal then there's a space and an underscore the space and the underscore means the rest of this syntax the rest of what is needed here is on the next line it's called the line

continuation character space and underscore combined like that in visual basic is the line continuation character and that's what we have after file name colon equal if we didn't have that there we would get an error message because it's saying hey you gotta give me the path to the file name including the file name so it just goes to where i have my on my hard drive users trish desktop files for video description and then it's in that received vehicles the name of the file is receive vehicles.xlsx And that line starting with c begins with an

open double quote and ends with a closing double quote and then i scrolled a little bit when i got over to that other file receive the vehicles and it took note of that that's this active window small scroll down and then we did control a to select everything which is a 1 through b11 dot select select is a method of the range object and then we have range a2 activate and then we did copy so we actually just did control ac and it created those three lines of code and then after we copied it we

switched back over to the vehicles macro enabled file we click in cell a1 and then we pasted what was on the clipboard onto the active sheet starting in cell a1 and then we went back over to received vehicles file calling the activate method and then we said close that window and then of course with every sub statement you have your n sub statement at the bottom now that we've reviewed this code that was generated from us recording that macro there's a few things we want to do we want to rename module one so it's more

specific and we want to create a new sub procedure from scratch in the newly named module so let's start In our project explorer window i'm gonna just click on module one go down the properties window double-click name and we're gonna call this mod remember that three character prefix which is a convention not a rule mod new vehicles and i'm using the proper casing all mushed together and then i'm going to press enter so we have mod first and mod new vehicles in mod new vehicles click at the end of your in sub statement and press

enter we're going to go to the insert menu and we're going to choose procedure and this is a microsoft product so this is just one way of doing this in the add procedure dialog box we're going to start work our way from top to bottom right in the name box we're going to name this procedure move cells so capital m capital c mushed together procedure name move cells the type defaults to sub for a sub procedure and we're going to talk about the scope in a little while but we're going to leave it on public

and we're going to just click ok so a couple of things happened it automatically put a dividing line between your get new vehicle sub procedure and this new sub Procedure and it has the keyword public because we did it from within the dialog box and again you'll learn about scope in a little while and that's what public speaks to it gave us our sub and our n sub statements and of course the open and closing parentheses at the end of the first line so what i'm going to have you do is i'm going to have

you click outside of the closing parentheses public sub move cells press enter twice and then press the tab key on your keyboard i talked about code being concise but also you need to know this you're going to probably end up spending more time troubleshooting code than actually writing code it's just the way it goes and there are certain things in terms of how you format you your code that makes it easier to review when you're troubleshooting and indentation is one of those things i like to look at my code and see the public sub and

in sub statements at the same margin and that's what we're doing by indenting here so now you're going to type a few lines of code four lines to be exact and you can take them off of my screen the first line is going to be columns Plural open paren open double quote b close your double quote close your paren so columns b so we're referencing column b you're going to use the dot notation and start typing select and if i don't type it with a capital s when i press enter at the end it will

automatically capitalize it for me and now we're going to type selection dot cut enter i'll explain it all when we get it in and then we're going to type columns open paren open double quote g close the quote close the paren dot select active sheet dot paste and typically i like to have a blank line after public sub and a blank line before end sub so my previous block my get new vehicle sub procedure i'm going to just get that blank line above the n sub statement so basically it's going to select everything in column

b and it's going to cut it put it on the clipboard and then it's going to select column g and it's going to paste what was in column b into column g congratulations you just wrote four lines of vba code from scratch now earlier we added some buttons to our toolbar and we used one of these earlier Be anywhere in between your public sub and your in sub statements and go on and click that compile vba project that we put up on the toolbar earlier and again if there were any errors in your code you

would be notified of it it's kind of line checking your code you accessed the select method of the columns object and once you have something selected it is known as the selection object so you access the cut method of the selection object and the paste method of the active sheet object in that code block so let's go ahead and save is save on the toolbar or you could do control s and then we're going to do alt f11 to switch back over to excel so we can test this code there is another way that you

can access your macros in here so earlier we accessed our macros from the developer tab by using the second button macros we also accessed it from the view tab of the ribbon by going to the last button which is macros well there's another way of doing it and it's a shortcut key so you can use alt f8 to open the macro dialog box and so in here you'll see your ad formatting macro your Get new vehicles macro both of those were based off of macro recordings i should say and then move cells is listed in

here even though we created that sub procedure from scratch so this is just another way alt f8 that you can open the macro dialog box and we're going to double click the move cells procedure so you could click it once and then choose run or you could just double click it to run it and it did exactly what we asked it to do just very quickly selected everything in column b cut it and then pasted it into column g so the next part we're going to do is going to further our automation what we want

to do is we want to edit the add formatting sub procedure so that it works on this sheet the new vehicle sheet that we're currently on and before we do that we're going to delete everything on this sheet new vehicles we're going to do control a to select everything and then we're going to just press delete and then just click in cell a1 now we want to go back over to the visual basic editor alt f11 will get you there and in the project explorer we're going to double click mod first Which is where our

ad formatting sub procedure resides now notice the first two lines in our code block that's when we right clicked on row one and we inserted a new row well we don't want to do that again we want to keep the row headings that we already have so we don't want it to insert a blank row above row one which now contains the headings so we need to delete just those two lines of code rows one colon one dot select and selection dot insert shift blah blah blah the first two lines so i already have them

selected and i'm going to delete those two lines and then what i'm going to do is in your project explorer window go ahead and double click mod new vehicles again to get back into that module where we have our get new vehicles which is the result of a macro and notice when you do a macro it gives you in green that it's based off of a macro you didn't create this code and then whatever description you put in right so we should probably add comments just so i can be a good example here to our

move cells sub procedure i'm going to click at the end of the public sub line and press enter twice And then i'm going to type an apostrophe now when you type an apostrophe it's like saying the visual basic hey heads up the next thing i'm going to type is going to be a comment which means you're not going to execute it when the code executes it's just there for informational purposes and we're going to say that this is going to select and cut contents of column b and paste into column g and then when i

press enter it will turn that line green because we started with an apostrophe before we do the next bit i just want to go over that there are different ways to execute your procedures or run them within the application or in vba so we've used some of these already um we've used the macro dialog box the ones that are highlighted on in yellow on this slide we will use during this course so you'll later on we'll use the run sub user form button on the vbe toolbar we've already used the macro dialog box we're going

to call the procedure from another sub procedure which is the one the example we're going to do next later on we're going to add a button to the quick access toolbar in excel you can also attach the procedure to an event which You will learn later and also you can run it from what's known as the immediate window in visual basic editor so many different ways to execute procedures so like i mentioned we're going to be calling the procedure from another sub procedure in this lesson we're going to click at the end of the end

sub statement in your move cells public sub procedure and we're going to press enter twice we're going to create another sub procedure here going to do it in a different way and it's going to be known as the calling procedure you'll see how this works and i'll explain more after we get it done now we're going to actually just type this in we're not going to use the add procedure insert procedure dialog box and so we clicked after in sub we pressed enter a couple of times and we're going to type sub and then we're

going to name it get new inventory and press enter twice so you get the line the dividing line between the move cells sub procedure notice we didn't have to type the opening and closing paren or the end sub statement the visual basic editor will supply you with those when you're typing it from scratch so now we're Going to press i'm going to just do shift tab to out dent let's do a comment here i'm trying to be a good example we're going to type an apostrophe and we're going to put this is a calling procedure

that is calling three other procedures and press enter at the end of the line it turns green press enter again and then tab to indent we simply need to give it the names of the procedures so this is known as a calling procedures and the procedure names we're going to type are known as called procedures so we are going to type get new vehicles enter move cells enter add formatting enter it should look like this on my screen so get new inventory's purpose is to kind of bundle those three other procedures together in the order

that they're listed there and it will execute them when it is executed so we get three for the price of one we execute get new inventory and it executes three other sub procedures for us go ahead and compile your project and save we have our three procedures that are called procedures in our get new inventory sub procedure Two of which reside in this same module ad formatting resides in mod first we're going to talk now about the scope of procedures and why it's possible that we can access the ad formatting procedure from a different module

so of course we're going to review the scope of procedures on the slide because again the slide deck is in the files in the video description and you'll be able to re use it for future reference so scope determines where the procedure may be used you have basically three different scopes really two so the first one is public if a procedure declaration is preceded by the keyword public this makes the procedure accessible to all modules in the visual basic project if the procedure declaration is preceded by the keyword private this makes the procedure available only

in the module where it resides it cannot be accessed from any other modules or from the excel workbook and then you have undeclared so we have no private procedures at this point if no keyword is inserted at the start of a sub or a function declaration the default is public okay so i'm going to switch back Over to the visual basic editor and we'll take a look at this and i'm still in mod new vehicles so we have the result of a macro we recorded a macro and we named it get new vehicles it simply

named it sub it didn't put public or private so it's considered undeclared which means it's public then we created a procedure by using the insert menu and because in there and i'll bring up that dialog box the scope is defaulting to public so when we created the move cells procedure it added the public keyword and then we create it from one from scratch sub get new inventory we didn't type public before sub we left it undeclared so all of these are public procedures and if we go to mod first add formatting is also undeclared which

means it's public and i look at the end of that and i want that blank line above in sub so i just modified it and did that so because ad formatting is public i'm going to go back to mod new vehicles we can access it by calling it from this module that's why that's possible the scope of procedures matter now one other little fix we're gonna do because i already know it's going to be an era and you may not even have this line in your get new vehicles sub procedure in mod New vehicles but

that first line of code since get new vehicles is included in our calling procedure if we have the first line saying active window close it's going to just close the file and then it won't be able to run the rest of the code so that very first line active window close if you have that line you know could cause you to have an issue with the code if you don't have that line as your first line if your first line is workbooks dot open file name so on and so forth you're good to go but

if not you want to delete that first line active window closed we're going to leave it at the bottom but not at the top and then go ahead and save your code and now we're ready to alt f11 to switch over to excel and we're on the new vehicle sheet in cell a1 we're going to do alt f8 to bring up our macros box and we want to run our calling procedure get new inventory so i'm going to just double click it and my screen will flicker for a moment and then ultimately stop okay so

what happened well it ran the bundle let's switch back over alt f11 the first thing that it did is it ran get new vehicles so it opened the received vehicles File it selected everything and then it copied everything and then it went back over to our vehicles macro enabled file made sure it was in cell a1 and it pasted the data and then it went back over to received vehicles file and closed it after that it did the move cells sub procedure so once it brought in that information from received vehicles it selected everything in

column b and it pasted it it cut it and then it pasted it in column g and then after that it did add formatting which is in mod first and that's where it added the headings for us right so it added our column headings and we didn't want it to create a new row there because it came in with a heading row so we had already modified this it did the currency formatter accounting number format and it auto fitted the columns it also made the headings bold and centered in their cells so it ran the

bundle we ran one sub procedure that called three other procedures so when i go back and look at it in excel we're still on new vehicles right it moved the dealer costs to the appropriate column applied the appropriate formatting to it and it added the headings instead Of the headings that came in from the received vehicles file and the formatting that it did so you saw that calling procedures lend themselves to efficiency but there is further efficiency to be had earlier we discussed how sometimes a sub procedure is known as an event procedure and that

happens when you attach it to an event so first thing we're going to do here is we're going to do control a on this new vehicles sheet to select everything and then we're going to press delete we're going to clear the sheet just click in cell a1 and switch back over to your visual basic editor now on the left side you attach to in order to make something an event procedure you have to access an object's events so we're going to double click in the project explorer window we're going to double click sheet 3 new

vehicles now notice at the very top of your screen you have two different drop downs above option explicit which we still haven't talked about but we're getting there you have a general box and to the right you have a declarations box both of them have drop downs on their far right sides the left side where it says general is known As the object list the right side where it says declarations currently is known as the procedures or events list so we're going to do the drop down arrow to the right of general and the only

thing because we have sheet3 selected the only object we're going to see is worksheet so we're going to select worksheet and it creates this whole like sub procedure framework it's private so it's only available within where we're putting it it's attached only to the new vehicle sheet which the system knows as sheet 3 right so the first thing the default event for a worksheet if you look at the right now where it used to say declarations it says selection change that's just the default event that's not the event that we want what we want to

happen is we want our calling procedure to run whenever the new vehicle sheet is activated so we're going to do the drop down all the way to the far right of selection change and these are all worksheet events right activate before delete calculate you have a whole list we want to select the activate event so now it gives us the framework For a private sub procedure that it names worksheet underscore activate typically with event procedures you don't change the names right it's kind of like hard coded so it's that particular worksheet activate event that we're

going to code so we don't need this second block we don't need this this is just what was created as soon as we selected worksheet it selected the default event which is selection change so we can just delete that click at the end of the parentheses on the private sub worksheet activate line press enter twice just so you get that blank space again we're going to press tab so it's indented and we're simply going to call our calling procedure by name we named it get new inventory so that's the one that has the the three

called procedures within it so we're saying whenever the new vehicle sheet is activated go ahead and run that bundled calling procedure known as get new inventory go ahead and compile your vba project using the toolbar you want to get in the habit of doing that and save and switch back to excel Just make sure you're in cell a1 on new vehicles which we deleted everything off of and now we're going to go to our inventory sheet tab at the bottom and then we're going to click back on the new vehicle sheet tab to activate it

and voila as soon as we activated that sheet it ran our get new inventory calling procedure so we get the same result we didn't have to do alt f8 to get into the macros dialog box to make it run or anything like that we simply made it an event procedure and it was the activate event for this particular sheet we are going to once again clear this sheet by doing control a and delete just click back in cell a1 and go ahead and save your vehicle's file so now we're ready to create a function procedure

and if you remember from earlier a function procedure performs a calculation and returns a single value or an array of values so differs from a sub procedure so when you create a function procedure in vba it can only be executed in two ways compared to the nine ways you can execute a sub procedure you can call the procedure from a sub procedure or another function procedure or you can use the function in a worksheet formula It becomes a user defined function in your excel application and you'll see both of these play out when we do

this we're going to create a new module for this so i'm going to just go up to the insert menu in the visual basic editor and i'm going to choose module so it gives me a new module one and and let's rename that module using the properties window we're going to call it mod and then capital f function and in this module we're going to create a function and what it's going to do is we're going to give it different values for an msrp and depending on the value it's going to display a message and

once we get it typed in and stuff it'll be easier to kind of explain it to you so in your mod function i'm going to click at the top after option explicit and just press enter and we're going to type the word function and a space and we're going to call it msrp so all of that is capitalized and then capital s status and then in the parentheses we have to have an open parenthesis we're going to type msrp that's a parameter you'll also hear it as an argument so we have to pass The function

an argument in order for it to give us a value as its return and after that you're going to press enter and notice it gives you the end function statement just like it does when we do a sub procedure and it gives us the end sub statement so i'm going to press enter again and i am going to press my tab key to indent it's just a structure thing visual basic doesn't care now we're going to build what's known as an if construct it's more specifically it's an if then else if else construct which is

a lot to say and can be overwhelming it's similar to the if function in excel you're being rudely introduced to it for this function but you'll get a deeper dive in control of flow structures in a later lesson so i will explain this after we get it typed in so we're going to type if and then msrp we're going to do the less than symbol 20 000 no punctuation on the 20 000 and type then and press enter now tab a little bit more one more time so if the msrp value is less than 20

000 then if that is true it's going to do what we say on this line we're going to type now we're going to type msrp Status the name of our function equals and in double quotes less than 20k and close your quotes so if the msrp value is less than 20 000 it's going to say as the function return the result of the function it's going to display the text less than 20k press enter and then shift tab and you're going to type else if and it's all one word initial capital letters msrp less than

thirty thousand then next line down you're gonna tab msrp status the name of the function equals and then double quotes less than 30k and close your quotes enter and shift tab we're almost done we have two more lines now we're going to type else just else not else if space msrp status the name of the function again equals and then double quotes greater than or equal to 30 k and close your double quotes enter and we need an end if statement so end if and press enter one more time so you get that blank line

between end if and in function so If the msrp is less than 20 000 it will display the tax less than 20k if it's less than 30 000 meaning it's not less than 20 000 but it's less than thirty thousand it is say less than thirty k to display that text if neither of those statements are true if it's not less than twenty thousand and it's not less than thirty thousand it's going to just display greater than or equal to 30k so the else statement and notice when you got to the end of the line

it put that colon after else you didn't have to type that so there's a line editor every time you get to the end of the line it's checking the line to make sure it's okay so this is known as an if construct it's going to do different things depending on whether a statement is true and that's how you create the function procedure now we should go ahead and compile our project so again you just need to be anywhere between function and in function and do your compile project make sure there's no errors and then we're

going to save so there are two ways to execute a function procedure the first way is by using what's Called the immediate window and it's really testing the function to make sure that it works properly and so we're going to do that way first and then i'll tell you about the second way which is really super cool in my opinion um and you'll see that soon so the first thing we need to do is get the immediate window open in visual basic editor so we're gonna go to the view menu and we're going to choose

immediate window and it opens in the bottom of the screen in the immediate window we can test this function procedure i'll explain it after we type it in you're going to type question mark and then msrp status the name of the function and then in parentheses now if you notice right underneath where we're typing in the immediate window right right underneath that it's showing that in the parentheses it's waiting for us to give it a argument so we use msrp in the parentheses as a parameter and now we have to pass it an argument in

order for the function to work which means we have to give it a number that represents an msrp in the parentheses we're going to just type 10 000 no punctuation close the parentheses and press enter question mark means the same as print in vb a right so It's saying print the result right there in the immediate window so if we look at our if block we said if it's less than 20 000 we want it to say less than 20k and that's exactly what it did now we can use either the question mark or the

print keyword to the same effect in the immediate window so now in the immediate window you're going to type print and then msrp status open paren and we're going to put 20 000 in there and close the parentheses and now press enter so it's giving us less than thirty thousand so question mark and the print keyword do the same exact thing in the immediate window we're testing our function to make sure that it works so let's do another one we could do either question mark or print whichever your preferred one is and we're going to

set it up again msrp status and this time in the parentheses we're going to put 40 000 close the parentheses press enter and we should get the greater than or equal to 30k the else statement is working because it's not less than 20 thousand it's not less than thirty thousand the parameter that we gave it the argument that we gave it in the parentheses It's forty thousand so we're getting greater than or equal to thirty k if you're creating super complicated function procedures you would want to test them in your immediate window giving it different

values to make sure it works the other way to test it let's go ahead and switch over to excel i'm doing alt f11 there okay and let's go to the inventory sheet tab and click in cell i to and so i2 you're going to type an equal sign like you would for any built-in excel function and then you're going to start typing the name of the function we're going to type ms and our msrp status function shows up here it's a user defined function in the excel application once you create it unless you go and

delete that function you will be able to use it in excel we're going to click on or since msrp is already selected i'm going to just tab key it's called tabbing it in and then we are going to click on cell h2 the very first msrp and we don't even have to type the closing parentheses here because we're doing it in excel application so go ahead and press enter And then click on i2 and the little notch in the lower right hand corner of the cell is known as the fill handle and once i put

my mouse on top of the fill handle it looks like a thin black cross and i am going to double click so you'll notice it'll fill it all the way down to the bottom until it runs into a blank right so if i do control n it filled it all the way down to row 105. and it's looking at each msrp's value and determining what to display in the adjoining cell and so we are going to right click on the column i column heading and delete that column and i want to show you something else

since we're in here go to the formulas tab on your ribbon and the first button is insert function click it where it says or select a category most recently used do the drop down arrow and you might have to scroll down to see it but you're looking for a category called user defined now there's going to be stuff in here that you're like what does that mean don't worry about it this stuff that comes into excel automatically but you'll see your msrp status in that list so that is a user defined function in the excel

application Which i think is just pretty cool we can just cancel out of that and go ahead and save your file so our last topic in this lesson is getting context sensitive help in the visual basic editor so i'm going to do alt f11 and switch back over to vbe and in vbe i'm still in my mod function module i'm going to just click anywhere in the word function at the beginning of that function procedure and i'm going to press f1 on my keyboard so it brings up the context help box because you're in excel

visual basic for applications and it's saying that there's an object in both visual basic and excel called function we're interested in the vba function so we can just either double click it or go over and click on help it's already selected so it launches your browser and i can tell you that the vba documentation from microsoft is awesome so it gives you the function statement from vba it lets you know that it declares the name arguments and code that form the body of a function procedure it gives you the syntax so you know just like

sub procedures there's different statements public Private right if it's undeclared it's public um there's also friend and static and then the function name and then in parentheses the argument list and you can give it a data type as its return as well which we didn't have to do on hours right um there's also an exit function statement that we didn't need to use so it gives you the breakdown about everything descriptions on everything right and so and then there's remarks at the bottom and so it says there's one line in here that says all execute

all executable code must be in procedures you can't define a function procedure inside of another function sub or property procedure and notice that sub there is a link so if i click on that link it brings me to context sensitive help about the sub statement and the other thing i'll point out in here is on the left side it lets you know that you're looking at the sub statement right so if i scroll up on the left side there's the if then else construct and again we're going to do a deeper dive into that later

but if i wanted more information on that i can just click on it on the left side right and it gives you all the information you need to Know about that if i continue to scroll up it's all under the heading statements which is under the heading reference which is under the heading language reference so anything you need to know i'm going to just switch back over to my visual basic editor you can access it by pressing f1 when you are on a particular keyword right so it works that way it gets you into the

microsoft documentation really good context specific help at your fingertips so we covered a lot in lesson three and just to recap what we covered we went into a deeper dive about the types of modules that are available within visual basic and the types of procedures then we went on to creating a standard module we created a sub procedure and we called procedures by creating a calling procedure you also learned about the scope of procedures then we created a function procedure and we tested it using the immediate window before going into excel and accessing it in

its interface as a user defined function after that you were shown how to get context sensitive help from within the visual basic Editor thank you for attending excel 2019 visual basic for applications video course