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