VBA For Beginners: Learn Macro Security & Automated Sorting In Excel

hello everyone welcome to the VBA Basics and fundamental series I've got an incredible training for you today we're going to be focusing on macro security VBA editor options you're going to learn all the fundamentals and a fantastic fully automated sorting option that you're just going to love and we're going to be getting into it this training is every Saturday I bring you fundamental VB for VBA beginners or for those of you who just want to brush up on those fundamentals I'll be giving you all the tools tips tricks and techniques that you need to build incredible applications as we do each and every Tuesday we build those and that's for our intermediate and advanced Excel and VBA users however these weekends are just for the basics so I'm super excited to bring this series to you even if you didn't see the last one no problem you'll be able to enjoy this short training it's really incredible I'm super excited to bring it to you so we're going to get right into it if you do like these trainings I just ask a few things go ahead and subscribe click that notification icon button below and don't forget to make sure to comment below I respond to each and every comment what do you want to see especially in this beginner series what type of trainings would you like me to do what features would you like me to present I'm here for you this channel is for you so make sure that you take advantage of that and let me know what you would like to see today we're going to focus on macro security what to do when we have disabled macros how do we unblock those macros in a single instance and also how do we create a trusted site so that workbooks that we download from trusted locations do not have those macros blocked we're then going to get into the VBA editor options we're going to go over option explicit we're going to Auto indent variable declarations an immediate window and how those features help you and then also single column sorting we're going to focus on how you can create very uh small macro one to two or three lines of code create really fantastic automated sorting and of course a great feature where you can add and update items and they're going to be automatically sorted for you whether it's names items or products or whatever it is so let's get right into it the first thing we need to do is launch our VBA editor and to do that we're going to go into the developers here and we're going to go into the Visual Basic you can also use the shortcut alt f11 alt f11 if you do not have the developers tab you can just easily rightclick on any of the menu items here and go into the customized ribbon and you want to make sure that developers is selected there okay so we're going to go into the windows here the Windows start here from Windows and we're going to type in internet and it's going to click on the internet options we want that control panel bringing that control panel into the view what we want to do is we want to go into the security tab then what we're going to do is we're going to go into the trusted sites and we want to then click on the site so we want to add that to a trusted sites notice that I already have mine where we get the download workbooks free workbook downloads that's a S3 an Amazon site and so wherever you're getting your downloads you want to take that link that hyperlink and you want to paste it in here now you wouldn't need the full hyperlink of the workbook itself you would just want to have the hyperlink of the site wherever you're downloading workbooks often enough you as long as assuming that they're trusted and good workbooks you would then simply paste in that site and click add once you do that that becomes an added site so that means any workbook that you download from that location macros will not be blocked so that's very very helpful so we can do that and then just click okay and cancel all right so that's pretty much all we need to do to ensure that macros are not blocked from trusted sites okay very good so that's how we handle macro security when macros are blocked now generally this happens only with Office 365 so it's only on the newer version so far so keep that in mind if you do find that it's an easy problem to fix whether it's a one time or you want to fix it uh have a safe website where you want to have a trusted site any website that you download those workbooks will be safe all right now we're going to get into the VBA editor options I wanted to go over a few different informations so we can familiarize yourself with this VBA editor now we did on initial we've got our VBA project here now of course we can show and hide that here so we've got the properties here so we can toggle that this is our properties here for any sheet we have our two sheets these are getting started sheet and we have sheet one which is we're going to focus on sorting and now what I would like to do is I'd like to share with you some of the information how we go about finding this now if we want to create a module on this we're going to insert a module right here called module and that's going to create a brand new module now what I would like to do is I'd like to set some of the preferences and eding and I wanted to go over how we change some of those preferences so what we're going to do is we're going to go into the tools and we're going to go into the options tools and option inside the VBA editor we want to go over some of the options here we have something called Auto syntax check and we have something called require variable declaration now required variable declaration is really important when this is not selected as it is now when we create a brand new module there's nothing at the top now when we create a macro let's just create a little macro right now let's just do SUB test macro it doesn't require variables and that's really important so let's just say last row equal 5 and then message box last row now we can get into some trouble here if we don't require and so that works just fine however what I really want to do it's very important that we declare our variables what I mean by declare our variables means Dimension last row as long so I really like the idea of that because it helps for greater programming detail and level and reduces the amount of bugs when we must declare our variables because VBA doesn't necessarily know is this a whole number is it a string is it a decimal is it a date right so what we really want to do is require these types of macros to make sure that we do declare the variables declare in memory otherwise known as Dimension here so Dimension lasts or as long so again it will work just fine but it's always a good idea to make sure that we actually declare our variables so one way to require that is when we go into the tools and we go into the oh we can type in option explicit okay so now when I try to run that macro it's going to tell us variable is not defined it's going to force us to define the variable first so we can do Dimension last row as log okay so now when we do that and we rerun the macro it's going to run just fine so what that's doing is saying hey please this variable has not been defined you have not told us what last row is what type of variable is it a string or decimal or date or whatever is so requires it so what I'd like to do is have this option explicit automatically insert it into any new module notice when I create a brand new module here that option explicit isn't there so we can remove the module just remove the module and we don't want to save it however if we go into the tools and we go into the options here and we require variable declaration we select that now when we create a brand new modu we insert a module we see that option explicit is already there and that's super helpful because it's requiring us to declare those variables so that's really important I wanted to share that with you okay so what about this one what about uh if we go into the tools here back into the options and we saw Auto syntax check now this can be really helpful or can be a little bit annoying I would say it's helpful when it's selected and what do I mean by that when I make a change if there's an issue last row let's put equals sheet 1. range A1 and I'm going to forget the quotation marks value okay so when I enter that we're going to get it's going to one it's going to turn to red and it's going to tell us that we expected a list or separator so there's an issue there because we forgot the quotation marks now that little popup right that little popup that came up is kind of nice right it tells us what the issue is when we make the fix right it's very helpful then there's no issue right but sometimes once you get a little bit uh better at VBA just having it in red is enough to say there's an issue you need to fix you don't necessarily need or want the popup so if you don't want that popup let's show you one more time right sheet I want to show you sheet 1. range A1 and then again we're going to forget the quotation. value right so this popup can get a little bit annoying you know there's an issue so some people don't want both right so all we would need to do is just go into tools options here and then we unselect the Auto syntax check click okay so now if I type it in again it will go to Red which is what I want sheet one. range a1. value do value that would work too so here you see it goes to Red we know there's a problem with it that needs to be fixed but we don't have that annoying popup so the popup could be helpful or you know as soon as you make the correction in in the correct it turns from red to Black so that can be really really helpful so I wanted to go over that with you so that's what that is so let's go back into our tools and our options and we're going to take a look at a few other things we have autol list members Quick Info this is helps and we have Auto data tips we can also drag and drop text editing and default and I wanted to show this to you this tab width that's kind of interesting so what is that auto indent and then the tab width tab width is the number of spaces so if we write a statement something like if let's see last row is less than for then then we say do something right so we're GNA do something except for that Loop there never mind do something that's Auto hockey that automates that could be really helpful so notice that the number of spaces here now that helps keep your code really organized so that we're inside a loop or inside an if statement and it really helps us to keep track and keep things organized so that's what autoindent is so if you want more spaces you would increase it so it automatically dents a number of four spaces so we can change that so that's what Auto indents is 1 2 3 4 and then so that's where that came from so if we were to change that if we wanted a larger indent we could change this to 10 and we can see how that affects and then click okay and so now if I do if last row is less than four then okay so now we see a larger indent right so we see that's automated in so that can be really really helpful so I wanted to go over now there's a lot more features if you want to increase or decrease the font size if you want to change the colors you can can do all of that from the editor format here in the options so if we were to reduce this font down to 12 and click okay we'd see that it's now smaller so we can change a lot of the information here and the way that it look and feel of it simply by changing there and we can change whether it is let's now we've changed it back here so we change it back to 16 but you can also change the selection text we can change the syntax air note remember it was red so there's a lot of features that we can change we won't necessarily need to go over each each one of them but you can kind of play with it and see how it looks changing these if you want to change the look and feel so there's a lot of it and then General docking this kind of helps us whether whether we're going to dock the properties whether we're showing grids and things like that some things that aren't necessarily that important also you may want to set it to break on all unhandled errors that'll ensure when you break on those unhandled errors that we break when any error happens we want to make sure especially those unhandled errors and compile on demand so that is how we have it so we can go into a little bit more detail on that but what I really want to do is get into the Sorting so that's what we're going to do now we're going to take a look how do we create this incredible sort so I've got some information here on sheet and I really want to be able to sort these names alphabetically and I want to do it with a macro so how can we do that well we can write a simple macro now let's take a look inside our project Explorer we see that sorting is sheet one so we want to write our first Macro for that so how can we do that we can go into any the modules here and we can write that macro so we're going to start out with the sub routine sub sort names and once we do that it's going to automatically put that N Sub so that we have that available and the first thing I want to do is write the easiest macro possible and that is simply just to sort the names in column C so how are we going to do that well the first thing is to specify a sheet it's not absolutely necessary but I do highly highly recommend it because otherwise it's only going to go on what the actor sheet is so there's two ways to call the sheet the first of which and my favorite is just to call out the code name of the sheet so sheet one this is also sheet one is the code name sorting is the the sheet name or the friendly name so what we're going to do is we're going to call out the code name which is sheet one and then we can specify the range do range now we need to call out the range what is the range that we're going to be sorting in this case the range is going to be the entire column C so if we want to specify an entire column we're going to use the quotation marks C C Colon c and then end quotation marks and then the end parentheses now what do we want to do with that range well I want to use sorting so I'm going to use Dot and then sort and we see that intelligence comes up automatically and so we're going to put in the parentheses here and now it's going to give us some information it's going to give us that prompt right that popup here it's going to show us what do we want first is the key and that means what is the first cell that we're going to sort by so what we can do is we can type in key 1 colon equals and what is the range again it is we're going to specify the sheet again sheet 1. range and C1 is the first value in our range so that's the first key so I've set the range that's our key that's the first item then what we want to do is we want to then add in what is the sort order right so we want to just put in the order so we can do order one po equals and then we say ascending or descending so I'm going to choose ascending okay next up what I'd also like to do there's a lot of options here but we really want to do just the header and that's fine so if we take a look inside there let's pull this bring this over a little bit so we can see more of what's going so we've got a lot of options here we can do header as yes no or guess so let's type in header and then a going colon equals and then we get the prompt and we're going to say no there's no header on here okay so that's all we need to do in fact it's relatively easy we can just get rid of this parenthesis there so that's all we have to do so now let's bring this over here and we're going to run this macro here and we see that everything got sorted let me show you that one more time I'm going to take my unsorted list I'm going to copy it back over into my list and now you see these are unsorted now I'm going to run my macro so run my macro we can use F5 or we can just use run sub so here perfect okay so that works really nice so we've done that but now what if I'm using this column where I have a header I'm not going to sort the entire column what if I have other values inside the column such as a header that I do not want sorted well what we want to do is we want to specify a very specific range so we can do that also now the other way to call out this sheet which is a little bit longer and something I don't recommend we can use this workbook do sheets then we specify what sheet now this would be the friendly or sheet name sorting okay so if we take a look at there there we can this will work just as well so we can see that we run it and it works just fine the reason that I like using the code name when I do that this is the code name sheet one is because that if you're Distributing a workbook you don't want end users of course if they change the sheet name you don't want that to stop the macros from working by using the code name they unless they get into the VBA they wouldn't be changing that code name so I always like to use the code name here and of course if we want to change the code name we can do it from right from here so if we want to do sort sheet we could do that here and of course if we run the Macer now that we've changed it if we run the macro it's going to give us hey there was a variable not defined that means it doesn't know what sheet one is because we've changed it so sort sheet dot if we do hit the dot we realize that intelligence pops up which means we do have a correct sheet name so we would change that in both locations here so we do that right from here and it is a good idea to rename your code names so that you know what your sheets doing so it's better to rename them so we understand looking at each sheet and we know what the sheet is okay so we have that there okay so that's not the correct name so notice I missed the correct name and look the S is NOT capitalized but as soon as I put in the correct name you see it does get capitalized here so that's kind of a good trick to know that you have the sheet names and if I run the macro it's going to work just fine without any errors okay great so what what if I want to do a very specific call so how can we do that we know the first cell is going to be E2 but what about the last one what if I add names what I need to do is create a dynamic range so that as I increase the values here so does the range so what we're going to do is we're going to create a very specific range so we're going to use a a variable for that we're going to Dimension di last row as long it's going to be a whole number okay and what I want to do is I want to determine what is the last row of a value rate so if I'm going to specify a very specific range I need to know what that last row that contains that value so we can set that last row is equal to sort sheet. range then we're going to use that column it's column e and then we're going to use a very high number such as 9 N well beyond what your data is capable of and then dot end so that's going to look for the end of it and it's going to look XL up so up right so I want to know the last value in that range and what do I want to return I want to return the row I want to know the row that that's on okay great so if we have that now what we can do is we can determine what that value is and I want to know what that value is when I go inside the code there's a few ways to determine what that value is if we use f8 or we decide that we're going to step through the code I can do that right here so if I use f8 which is the stepping through the code so it's going to first start here the next one it's going to skip that and it's going to go to the last row now as soon as it skips it says 34 here and if I put my mouse over here 34 here now there's another great way to to view that and we can use a few different things we can use the immediate window so the immediate window is through here if we take a look here we can use contrl G that's the shortcut to get to it and we're going to click here and what does the immediate window do well it helps us understand what what's going on with the code before after during a macro and so if I want to know what that is and I want to determine what the last R is I can do put the question mark in in last row and what that's going to do is return that I can also print to that so if I want to do let me reset this I can also print and actually put that value in here using debug print so how do I do that debug do print and then the last row now again when I use f8 and we move through this we see last was 34 but now I want to take that 34 and I want to put it directly inside the immediate window and that's what this is going to do debug print last row it's going to put it right in there so we see the value of that okay great so we know that with the last row is but now we need to update our code for that where's our starting position of our range our starting position is going to be in E2 so that's the range so we need to set that up inside E2 so I'm going to change this here change it to E2 two and then it's going to go all the way to e and what well it's the last row that's the dynamic so we're going to put in the and and the last row okay then we also have to change our key what is the first sorted value it is going to be E2 so we're going to change this to E2 okay great so that's all we need to do we can extend this so we can see the code okay so now we have a dynamic range in which we're sorting and that's going to be very very helpful to us let's bring this over here whoops too far okay so just a single column is sufficient so now let's go ahead we can also step through this code one by one so we're going to do f8 we see the last row has been signed we can print it which is not necessary um and then what we're going to do is we're going to run this code and now you see it's now sorted the dam very helpful and if I had one more let's say let's say we add another one Jack James or hes yeah let's do Jack James and now now what I want to do it's going to automatically sort if I run this macro again here just by pressing play it's going to automatically put jack James correctly in the right spot so we determined the last row which is now 35 and it automatically ran that and we see now that the last row has changed from 34 to 35 inside our immediate window and if we don't want to use this we can comment this out by entering a comment the apostrophe here will mean it's ignored or we can use it for information right so we use commenting as a great idea so this is the last value in a column okay or at least based on the Range so we can put a common in here and basically anything after that apostrophe notice it's in a different color it's going to be ignored by the code but it's helpful for informational use and it's always a good idea to comment your code out okay great so we see how we can automatically update this so that any new names get added but what I would really like to do is I would like to be able to run this map we can get rid of this now and we can remove the immediate window we can just close it out well it's always available if we need it okay so what I would like to do now is when I make a change to anything here if I remove something or change something or add something I want that list to automatically sort I don't want to run a macro if I do want to run it based on a macro that's very easy we can insert a shape we can insert a shape here and we can insert right here we can call this sort names so we can do that we can also give it a nice look and feel we can drop this down and we can then pull it over here we can write justify and put the text in the middle here if we want we can also add an icon to that using the icons here we would just select on that if we want to add an icon we can search for an icon sort so we can look for that icon we can enter it I like this one so then we're going to insert that we can then move it over here on top of the button here if we want to give it a sort and then we can also color it maybe we want it in white so we can use Graphics fill white then I'm going to hold down the control I'm going to select both of them I'm going to make sure that they're in the middle and I'm going to group them together and I can assign a macro to both items inside there by right clicking assign macro we only have one macro so we're going to select it and click okay all right now if if I copy this list over of our unsorted names and I paste the values here it's unsorted but clicking this button automatically going to sort them great we see how we can assign a Macker to the button and have that happen automatically but now what I would like to do is I would like to add a name to this list here and then I would like to automatically have that list updated as soon as I make the change so how can we do that well that is what happens on what's called a worksheet change event and worksheet change event means when something changes want some event to happen so how do we do that well back inside our code here that's going to happen actually on the worksheet so this is the worksheet now each individual worksheet has the ability to add code two notice we were inside a module one here we can remove module tools because we're not using that remove it and we don't want to save so our code was here basically but now we're going to focus directly on the sheet so we're going to select on double click on the sort sheet or we can right click and view code that's as well and so what's very special about this type of code is that we can create events and we're going to create a worksheet of events so we're going to select on this drop- down list worksheet and then we have all these types of events now keep in mind that once we were in the module there is no worksheet event inside the module right there's only a list of macros so keep in mind that only inside the sheet do we have the worksheet and we have the worksheet event now the first one's selection change now selection change is the kind of message box test and that means anytime you make a selection something's going to happen just selecting something we're not really interested in that right now what we really are interested is making an actual change to the seet so that event is called change and we select it right here so it is this event that we're going to have so when we change something something happens let's do that message box test okay so that means that when I select nothing's going to happen but if I make it change any kind of change then that going to pop up so so here we see any type of change to the sheet even deleting or something like that is going to also trigger that message box so we see how the change event Works however in this change event we can remove the selection change in this change event we want to be very specific to an actual range of cells so how do we do that I want to specify only E2 all the way down so what we're going to do is we're going to base it on something now we're going to use Target Target means the cell that was changed how do we know that well what we can do is we can change this to target. address target. address now Target is the cell that changes so what I'm asking for is give me the address of the cell that was changed so that we understand how Target works so if I make a change in here it's going to tell me that change is made on f8 so we can easily see that we know that Target means the cell that was changed okay so knowing that it's Target what I want to do is I want to find the intersection I want to look inside a specific range if the user made a change to the Target within that range then I want to run that sword so how do we do that we can use intersect for that so how does that work it's going to be an if then statement so if how do we do it not I know it's a little bit confusing it's a counterintuitive but there's two negatives they're going to count each other out intersect and what is the intersection well the first is the target meaning the what the user has changed Target and then it's going to say is the Target in a specific range so is there some intersection between the target the cell the user change and the range you're specifying if those targets intertwine or together then we know that that's true so what is that range so the range that we're going to specify is E2 all two all the way through let's say a large row e999 okay so and then we need to close that so there's no other arguments we don't have any other targets so we're going to close that as well and now is nothing then now I know this is a little bit confusing not and nothing cancel each other out so that means it's a positive two negatives make a positive so that means if the user has made a change inside this range then do something and what is it that we want to do we want to run this macro right here once they make a change we're going to run this macro so I'm going to copy that contrl c I'm going to go back in here just run that macro so all we need to is just paste it in there run sort Macer we're going to put a little bit of a note so that's all we need to do it's very very simple so that means any change that we make on here and within this we are going to then run that Macer to sort the names so if I add Freda Fred Frankl it'sin that range it's GNA automatically be sorted notice that it automatically got sorted up here so we can see that Fred Frankl is right here so and also if I decide I'm I'm going to copy the entire list and paste that paste the values in there that's a change notice it got automatically sorted right even though last one although we have two Tina James so if we delete anyone let's delete one here and let's delete another one and let's delete Fred frers no I can't delete Fred he's got to stay let's delete Dave okay so if I delete Dave that's also a change right removing it so notice how it got automatically sorted and moved up very very very helpful now if you only want to affect one cell at a time any changes Beyond one cell maybe we don't I don't know why you could do something like this if target. count large is greater than one meaning this the target the number of cells users changing is greater than one then exit the sub and this would mean large if the user makes a change to more than one cell nothing's going to happen so so that so you see how nothing happened but if we remove that and and now we paste in all of our big list it's automatically going to be sorted so very very cool so now we see how Larry Johnson even adding a name is automatically going to sort it without even clicking a button all right that was really cool we got had a lot of fun this week we learned macro security how we can handle disabled macros either with a single instance by um checking the box to unblock the macers or learning how to create a trusted site so that means any workbook that you download on that site is automatically macros are unblocked very very helpful we went over some really important critical and helpful options within the VBA editor options whether it's uh option explicit requiring variables to be declared Auto indent and the immediate window we saw how powerful the immediate window is that's going to help us show what's going on with the code and then of course finally we did the uh single column sorting where we were showing how we could sort a single column or just a range specific number of cells and how we could automate sort using the change event thank you so much it's been a really cool training fun I can't wait till next week let me know your ideas in the comments below don't forget to subscribe and of course if you are interested in learning all about VBA in a shorter sequence I've got an incredible course from Daniel strong on my website that is VBA full automation it's an entire 32-hour course so you can supercharge your learning of VBA and develop incredible applications and be super successful in VBA thanks so much and we'll see you next week

Channel: Excel For Freelancers

Views: 15,696

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, inventory, inventory management, inventory management software, inventory management in excel, excel inventory, inventory in excel, inventory software, excel inventory formulas, erp system, erp software, erp system tutorial, excel vba tutorial, excel vba tutorial for beginners, vba

Id: aoLu_iuTYy4

Length: 31min 28sec (1888 seconds)

Published: Sat Nov 18 2023