Master Excel MACROS & VBA in ONLY 1 HOUR!

g'day guys and girls and welcome to this webinar thank you for giving us your one hour way means that you want to get better in Excel and we are here to achieve that goal I have Kyle Pugh who is a certified Microsoft trainer he is a world expert in VBA macros and he's going to be teaching you video macros in the next 45 minutes so he is an amazing teacher I'm so glad that I got to do this free webinar for you you want to pass it over to Kyle and he is going to take you on an Excel journey that's gonna save you a lot of time and hours and money and just make you better excel so over to Kyle let's get into it hello and welcome to this Microsoft Excel macros and VBA webinar my name is Kyle Pugh and I'm a Microsoft certified trainer with over 17 plus years of live classroom instruction and online training during this session I'm gonna introduce you to some key concepts to help you further understand and develop your macro and VBA skills inside of Microsoft Excel now if you're brand new to the magical world of Excel macros and VBA I want to start out by talking a little bit about what this is all about and how this is ultimately gonna help you to become more efficient and understand and get you on that path to mastering Excel macros in VBA so let's start out with a little bit of an introduction and then I'm gonna take you in and show you a couple of examples of how you can use macros and VBA to help you out so if there's nothing else you take away from this this little webinar this is the key this is really what Excel ba and macros are all about its automation I want to automate my experience imagine this and I'm sure you can imagine this quite clearly you work inside of Excel perhaps at the daily tasks maybe there's a specific series of tasks that you need to accomplish perhaps this is daily weekly monthly quarterly whatever it might be as an example I need to create a report every Friday I get this data dump it comes down from some other system perhaps a database in the background a CSV file whatever it might be I get this data dump I bring it into Excel now once it's inside of Excel I need to do several things to get this data ready for presentation first I need to clean it up perhaps I need to add headers to the list I need to format those headers I need to format the data get currency values in there format the dates appropriately remove stuff that I don't need maybe I'm filtering sorting I'm creating calculations charts pivot tables you name it you've got this report that you've got it finalized and make this data presentable present that message that your co-workers our boss or whoever the viewer is needs to see and I need to do this every week and on Friday when it's my time to create this report it takes up a large chunk of my day so now I'm working through lunch I'm working after hours it's Friday I want to hurry up and get out of here I feel a pain there I'm going through several steps well in comes macros and VBA and we can automate the experience I want to build it click a button one single button press and get my work done that's it that's what macros and VBA is all about automate your experience whatever that experience might be whether it's a report whether it's something simple like inserting a worksheet and renaming it that takes a couple of steps we can narrow it down into one step all through the use of a macro now ultimately this saves you time it saves you money it helps you to become more efficient as you work inside of Excel as you do your daily work now I'm not spending as much time working on this report this project I've got macros in VBA to do some of that work for me or perhaps all of it and that frees up your time for other important aspects during your day like having your lunch and going home on time things like that and we can accomplish all of this again through a single button press of the mouse now that's just a little bit of an introduction let me take you into Excel now and I want to show you a couple of examples of how you can get in there and start to quickly automate your experience even with just a little bit of knowledge of what macros in VBA is you can already get in there and start to automate it the first thing I'm gonna show off here this is perhaps one of the most important things that I like to show off when you're first getting into VBA is what's called the macro recorder so let's jump into Excel and let's take a look at how this works now open in front of you I've got just a blank workbook this is even something you can try I don't have any special files open nothing just a a blank Excel document now I want to be able to automate a series of tasks now we might think well in order for me to automate something I've got to get into the visual basic code the VBA Visual Basic for applications I need to get into the code and I need to be a full-blown programmer in order to do this well you know what with the macro recorder this built-in tool inside of Microsoft Excel it will allow you to record yourself literally record yourself you just jump into Excel you click on buttons you type stuff in you click on sheets you click on cells it will record you doing everything that you do inside of Excel now why is that important that's great you can record yourself doing something all right well the reason why that's important is the next time you want to perform that same of steps those same tasks you just run the recording you say Excel last week you recorded me doing a B and C let's do that again now you hit a button and it does it all for you all just by recording yourself using tools instead of Excel that you're already familiar with so take a look here's the big scenario I want to record myself putting a title on the worksheet just some nice title up here inside of c1 ABC company or quarterly report or whatever my title is gonna be I want to format that title one changes background color its font color its font size I want to Center align that I want to put a border in there and then I want to drop down into cell c2 and I want to put today's date in there now that's a series of steps I typed something in I did some formatting color borders font font size so on its own center align it you know and I got 10 12 15 20 steps that I just went through to be able to put that title on the worksheet well every time I need that title I got to repeat all those steps here's the macro recorder now first off inside of Excel we have a hidden tab we're familiar with the tabs we got home insert page layout and so on there's a tab that's not typically on your system it's one that we have to activate and by activating this tab it's called the Developer tab it's gonna give you access quick access to the commands that you'll use to help you record and get into VBA code now I'm gonna activate the Developer tab and the way that you do this I'm gonna right click on the Home tab it could be any of the tabs so I'm just gonna choose home I'm gonna right mouse button click and I'm gonna go to customize the ribbon and open that up inside the options window I've got customized ribbon and here I'm gonna go over and turn on the Developer tab now once I turn that on it's on I don't have to come back in and turn it on again it's on the only way it's gonna go away is if you go back into here and you turn off the Developer tab yourself so I'm gonna go ahead and click okay this now opens up my developer tab there it is on my screen and on the far left I've got all my code tools in Visual Basic macros record macro that's what we're about to use relative references and something about macro security now let's take a look at recording some actions now remember I'm gonna record myself using the macro recorder to put a title on the worksheet and format it and put today's date in there so here we go so I'm gonna go ahead and hit record macro this opens up my little recorded macro dialogue box first and it wants for me is to name the set of steps that I'm about to record so I'll go ahead and give it a name I'm just gonna call this worksheet title now very important there are some restrictions to macro names one of them is no spaces Excel will not allow you to put a space inside your macro name just doesn't wanna in fact if you do it you put a space in there you hit okay it's gonna let you know that you did something wrong you can have up to 255 characters inside of a macro name I don't think I ever used that many characters but I try to be directin to the point all right short but clear I try not to get too wordy inside of all that's my opinion that's my experience inside there just try not to get too long with them but you do have plenty of real estate for you to use so I've got my name of my macro no spaces optionally I can give it a shortcut key let's say I do ctrl J all this puts the J key there now when I want to be able to run this same set of steps that I'm about to record I just hit ctrl J on my keyboard and I've automated that experience now I can also do shift J and my shortcut key would become ctrl shift J I'll leave that up to you if you're trying this out this is totally an option there are other ways to run macros as well I had like shortcut keys the only problem is I got a remember room and I've got a hard enough time trying to remember everything else that I got on my plate let alone a shortcut key but it is a great way to automate the experience I'll just do a button press in this case of shortcut key so I got control J the next option asked me where I want to be able to store the macro or more specifically where this macro is going to be available to me and potentially to others now the default location is this workbook meaning the active workbook that I'm working inside of the nice thing here is when I record this all these steps in the automation I'm creating is gonna be available in the active workbook so that I can use it when I have the workbook if I throw this up on a network drive or SharePoint or I email it to somebody they grab it they pull it down they have the macro as well which is great but maybe that's not what you want maybe the macro that you're creating you're automating these series of steps it's just a one-off you do it once inside the document and that's it we don't need it anymore and maybe I want to be able to use it across multiple workbooks well if you want to do that you store it instead of what's called the personal macro workbook by storing it here it's now available to any workbook that you open on your system pretty neat get automate it create it once automate the experience never have to do it again because you've stored it in the personal macro workbook and you could use it inside of any Excel document you open on your computer now for the first example I'm gonna go ahead and put it inside of this workbook the active workbook now my next step is I get to leave a description this is optional don't have to do it but think of this have you ever inherited work from somebody somebody in the office they've left the team they've left the project they've they're out sick whatever it might be now you've been given some of their work they ever leave notes for you they ever tell you why they did things what was their thinking raised you really might be really hard to get into their head and understand well why did they do it this way what does this do well that's what descriptions all about leave a note let yourself if this is a reminder for yourself and for others that might interact with your macros let them know what's going on what is this macro do so I'm gonna be really short here just just for the sake of the video I'm gonna say this macro places a title on the worksheets yadda yadda yadda alright and I keep going here you can be a bit wordy or if you'd like to as well but it's all about leaving notes leaving some description behind alright so this is our macro recorder we're just doing the initial setup here here comes the magic so I'm gonna hit OK the moment I hit OK I just did it is now recording you anything you do in the application you click in a Cell you type something in you click on a a button on the tab you delete something you add something whatever it is Excel is writing it down ok so keep that in mind as you start recording here so for me I'm gonna go ahead and click in the C one and remember Excel just wrote that down ooh you just like to see one now i'm gonna jump into there i'm gonna type in let's see well do i quarterly I spell that right quarterly report so I'll hit my Enter key I'm gonna drop down a Cell I'm gonna put a formula in here to put in today's date so I'm gonna say equals today open closed print then my Enter key again there's today's date now I'm gonna go ahead and format that I'm gonna jump in there and let's see I'll go to my home tab and I'll change it's a little bit of font format in here I'll make it bold let's see if it's bump up the font size let's make this blue alright let's go ahead and center align that I'm able center line this as well put a border on again everything I'm doing here I'm going through a series of steps there's I'm not keeping track of how many I'm doing I'm probably ten plus so maybe 20 20 steps now going through this series of steps in Excel is writing it all down for me the next time I want to perform this I just recall or I run the recording that Excel performed for me so let's actually do one more I'm gonna highlight that date then I'm gonna go give it a custom format here for dates let's ground like that one alright looks good so let's say that's it that's all I need to do that's all I wanted to automate so now I'm gonna go to my Developer tab and back to the left hand side I've got the code section I'm gonna go ahead and hit stop recording you want to make sure very important remember excels writing down everything you do once you're done stop the recorder if you don't Excel is just gonna keep recording just keep writing everything down so I'll hit stop alright good to go so I'm now recording myself putting that title in there and formatting it so imagine this the next week rolls around I need to recreate that title for a new report that I'm putting well now and I'm just gonna insert a new sheet here just as an example I got sheet 2 it's currently empty I'm gonna run my shortcut key I recorded that I did those steps earlier so now I do ctrl J that was my shortcut key and there's my title quarterly report got the date in there got it formatted all in one button press done what do you think of that save yourself time money become more efficient inside of Excel just by recording yourself performing routine tasks that's it the macro recorder and remember that was from the Developer tab that we had to activate just right-click the tab customize the ribbon activate the Developer tab and then you can start recording your actions tools features that you inside of Excel this could be things like sorting a list filtering a list inserting data calculating data whatever you do manually inside of Excel we can automate it through recording your steps all right that's the macro recorder and that's perhaps one of the number one tips that I can give new users to Excel macros and VBA because it's just how quick it is now you're always gonna want to take your knowledge a step further or perhaps various steps further multiple steps further well the macro recorder it's great it's quick it's easy to access and I highly recommend it and there's a few other topics that we want to get into there as well but the next tip I'm gonna give you is Excel just wrote all that down just said hey you click them to see one you type this in you did this that and the other thing as far as formatting was concerned now what if you want to modify it well you know what we no longer use blue as our company color I no longer want to take quarterly report I want to say weekly report well you could go back to the macro recorder and rerecord it your you can get into the VBA code all everything that it wrote down for you and you can modify it there take a look so the next tip I'm gonna take you into the VBA window and introducing you to making edits to the VBA code watch this so I'm going back to my Developer tab I'm gonna go ahead and go back into the code section and I'm gonna open up the visual basic button now this is gonna open up your VBA window or your visual basic editor it is a completely separate window from Excel if you get into macros and VBA into inside of any of the office applications Word PowerPoint access whatever you get into the VBA side of things it's the exact same window so what you're gonna learn here during this webinar and the courses that we offer on Excel macros in VBA you're going to be able to apply similar concepts to any of the off applications so watch this inside the VBA window I've got what's called my project panel inside the project panel I've gotten a kind of a replica of the workbook itself the open workbook and I've got a folder called modules and this is a topic that won't get much more in-depth into throughout our Excel macro and VBA courses that we offer now instead of a module this is where your code is stored your VBA code so if I give module one and double-click here's my code you might recognize some of it the first line is called the sub procedure and it's got my worksheet title that's the name and the macro that I created down below I've got some green lines these start with an apostrophe these are comments they're not really a runnable code they don't execute anything they're just comments notes that you can leave behind inside of your procedures your macros and then down below we've got everything that Excel wrote down as it recorded you doing your tasks in this case the very first line the first thing that executes is range c1 we're selecting cell c1 select c1 range the next one we're taking the active cell which is range c1 and we're gonna make it equal to quarterly report so I decide you know what we're no longer calling it quarterly report this is now gonna be a weekly report all right I'll just change it from quarterly report it's a weekly and I'm just changing it right here inside the code made the change I can modify anything else I need in there I can delete stuff I can add stuff to it and that's all stuff that your we're gonna go through as we get deeper and deeper into Excel macros in VBA within our courses so I've made a change I'm gonna go ahead and close this window I hop back out now I'm gonna insert a new sheet just for clarity got a new sheet completely empty I'm gonna go ahead and run my shortcut ctrl J and there's my macro just automating that experience and I've got weekly report tagged up there inside of c1 there we go all right so making edits to the VBA code you don't have to rerecord yourself don't reinvent the wheel if something changes just get into the code and make the appropriate edits and you're done so we got the macro recorder get quick on updating and and getting in there and automating your experience you can save yourself time money get more efficient as you work inside of Excel by automating the experience get into the VBA code now the VBA code or the Visual Basic for applications code it can be bit daunting definitely can be a bit daunting especially if you've done zero programming if you've done programming in other languages you're gonna see a lot of similar concepts but if you've done 0 Q sake nothing you've done you've never never picked anything up as far as program is concerned it can be daunting it can be hard there's a lot to learn within there but that's my goal that's John's goal to help you master this and become more efficient and not only recording yourself and automating quickly but getting into the VBA code and writing your own visual basic code to automate just about anything or I should say actually anything inside of Excel so I want to take you a bit deeper during this webinar we got automating through the macro recorder we've seen a little bit of VBA code just quickly make it an edit to it that's a quick intro let's jump a bit deeper into the code so we can see a bit more and introduce you to a few more concepts that we're gonna knock out of the park as you get into our Excel macro and VBA courses take a look so I'm gonna go ahead and close this workbook and you know what actually before I close it a very very important concept that we should discuss here you've now created some code all through the macro recorder we solve that big chunk of VBA code in there I want to be able to save this so I can reuse it well when Excel 2007 came out Microsoft made a pretty big change as far as documents that contain code normally you would save your Excel document as a dot XLS document or dot xlsx document when 2007 came out now if it contains any type of code you need to save it as a macro enabled workbook or a dot XLS M document take a look so if I close this right now if I do file close it's gonna prompt me to save it all right great I need to save this so I'm gonna go ahead and hit save so pens up my typical Explorer window and right now it is a dot xlsx the newest format ever since jaws a seven version of the office suite well in order for me to save the code I need to change the save as type from an Excel workbook to a macro enabled workbook or a dot XLS M if you do not do this you will lose the code you save it as a standard Excel workbook you close it out give it a name so on and so on close it next time you reopen it all that code that you either recorded or wrote by hand is now gone so make sure you do save it as a macro enabled workbook you can give it a name for my purposes I'm just gonna go ahead and save and I'm done all right any type of code make sure you save it as a macro enabled workbook there's another tip for you all right I'm gonna create a new workbook I'm just gonna do file new I'm gonna create a new blank workbook so once again nothing extra special open in front of you you can try this out if you'd like as well now the next example that I want to give you here of automating your experience using macros in VBA inside of Excel I want to build an insert a worksheet alright we know how to do that we can go down to the little plus sign we can hit the plus sign and that will insert a worksheet right I'll try it right here plus sign there's my new sheet now I want to be able to rename that sheet well now I double click sheet 2 or I right-click and I go to rename however you do it and I give it a new name let's call it week 1 alright there's my new worksheet now how many steps did I just go through there you know 2 3 maybe 4 steps to be able to insert that new worksheet and rename it well that's not that big of a deal but what if I need to insert a whole month worth of works worksheets well maybe I want to do one for each week of the year right whatever it might be now I got to insert a worksheet rename and insert a worksheet rename I got to do that multiple times well yet macros in VBA we can automate that experience one button press get the work done and now we've got all the worksheets that we need for our monthly or yearly report or whatever it is that we're creating all right so that's what we're gonna do we're gonna create a little macro here that will allow us to jump in and automate the experience of adding worksheets and renaming those worksheets all in one button press alright so let's try this out here I'm gonna go ahead and record just a little bit of code to help us automate this experience and then we'll call it done alright so what I'm gonna do here is all my Developer tab I'm gonna go ahead and record a macro I'll hit record and I'm gonna call this monthly worksheets I'm gonna record myself inserting a worksheet here which ultimately we're gonna modify to add multiple worksheets to the workbook now I'm gonna change it up here a little bit than what we saw earlier I'm not gonna use a shortcut key although those are nice I'm gonna store it inside of what's called the personal macro workbook this will allow me to store the code in a hidden workbook it's always open it's just hidden from view that I could use inside of any workbook that I open up inside of Excel on my computer that way I can use this automation across any work that I open and then I'll give it a description will say uh this macro places for worksheets will say weekly worksheets in the workbook alright I'll go ahead and hit OK alright and I'm now recording and I'm only gonna record just a little bit here so I'm gonna go ahead and hit the plus sign we know that that inserts the worksheet I'm gonna double-click the new worksheet and I'll rename it week one and I'll hit my Enter key alright that's it that's all I'm gonna record so I'm gonna go back to my Developer tab then I'm gonna go ahead and stop it that wasn't that big recorded myself inserting a worksheet and renaming it well now we're gonna get into the code we're gonna take what we discussed earlier recording and editing VBA code several steps further and I'm gonna introduce you to some key concepts that you're gonna master as you experience more and more macros in VBA we're gonna talk about how you can add loops to your code to get something simple just like that adding a worksheet to happen multiple times get it to loop multiple times alright so let me delete this worksheet we don't need it anymore and that was just just for recording purposes I'm gonna go back to my Developer tab then I'm gonna reopen the Visual Basic window now in the visual basic window because I just recorded that to the personal macro workbook I got something new inside my VBA project window got my VBA project personal I'm gonna expand that there's a module just like before and I'll double click module one all right there's my monthly worksheets macro that we created and it's got three little lines of code nothing big three things it says sheets dot add it's gonna add it add a new sheet after the active sheet it's going to select the new sheet and then is going to rename its gonna name she three two week one now here's what I want to do I want to get a little bit of this code to repeat multiple times I want to be able to add four worksheets when I run this macro add four worksheets rename the worksheets week one week two week three week four get it all done for me and delete out the original worksheet because we don't need it anymore all right here's what we're gonna do so first concept that I want to introduce you to here inside of the VBA is a concept called variables and this is a concept that's gonna live with you throughout your career as you work with macros and more specifically with VBA simply put a variable is some place I can store data I can store a value so that I can pull it out later now in order for Excel to know that you're gonna use some storage location to put data into we need to do what's called declare the variable and that's what this line is here dim I as integer I'm declaring a variable you start that with the dim keyword I'm gonna call it I that's just the name of the variable that I'm creating it could be called Bob it could be called adding sheets it could be called whatever I want to call it as integer let's it know what type of data you're gonna put into that variable in this case I'm gonna put an integer or a numeric value inside of that variable now once I've got the storage created you typically want to put something inside of it so here I'm gonna say I equals 1 now why wine well it's an integer it's a numeric value but this next part that I'm about to put in here the loop gets something to happen multiple times is really where this is gonna become much clearer for us now instead of Excel VBA we've got techniques that we can use to repeat code these are called blue then I'm gonna introduce you to one type of loop that you can use inside of VBA it's called a do-while loop watch I'm gonna say do while I is less than equal to something and then I'm gonna loop we've essentially got the beginning of our loop with a condition do while I remember I equals 1 is less than equal to something I don't know what that something is yet we'll discuss that then I'm gonna tell it to do something and then it'll loop it it'll jump back up and check to see if the condition is still true and it'll continue its loop if necessary if the condition is no longer true then it just moves on it says K the loops done let's go do whatever we're supposed to do next now this part right here this something do while I is less than equal to well how many times do I want the loop to run remember I want to be able to create new sheets and specifically I want to be able to recreate and for new sheets and rename them week 1 2 3 & 4 so I want this action of adding and renaming sheets to happen 4 times so I'm gonna say do while I is less than equal to 4 however many times you need it to loop now I get to jump in between the doing the loop line and tell it what to do well that's why I borrow some of the recorded code that we created earlier I need this one I'm gonna cut that out I'm gonna paste it up above this is the line that tells it oh we're gonna add a new worksheet we're gonna add it after the active sheet ok so that as the sheet now I want to be able to rename that new sheet so I'm gonna take this line right here the one that does the renaming and I'll paste it in here there we go now this line right here the Select line we really don't need it the macro recorder just put it in there so I could just delete that let's get rid of it it's one of the beauties of the macro recorder you your do-it-yourself doing stuff and then you can get in and just modify it alright so we've got a line here part of our loop that's gonna add a new sheet every time it loops it'll add a new sheet now the next part is gonna get a bit trickier we're not always gonna have a sheet three so where it says sheets sheet three name essentially go to sheet three get to its name and rename it that's not always gonna work so I'm gonna take this out instead of sheets sheet three I'm gonna take that out and I'm gonna replace it with active sheets dot name so whatever the active sheet is whatever sheet they got done just adding in it'll take that sheet and gets to its name now second part here is I don't always want to call them week one in fact we wouldn't be able to do that Excel would blow up and say oh you already got a sheet called week one you got a name is something else so I'm gonna get rid of the one and I'm gonna use a technique here called concatenating our combining I'm gonna take two values and I'm gonna combine them together to make a more dynamic name now there's a couple of pieces to this one we've got this literal week space it's got quotes around it that's what we want week with the space but then I want to take that and I want to combine it and the way that we combine this out of vba is with the ampersand the + symbol so we're take week with a space combine that with a number some number well the first time I loop runs it adds a sheet and then we want to rename it and call it week one then I want to loop again and I want to add another sheet and call it week two and so on give this seen the loop four times well this number here is something that we need to change every time it loops all right here's something pretty neat this is one of the beauties of using very bull's within your loops we've got a variable called I currently currently it's equal to wine so the first time our loop runs due while I which is equal to 1 is less than equal to 4 well every time our loop runs we want to increase the value of I by 1 that way the second time it runs I is now equal to 2 that's still less than 4 so it runs the loop then we'll increment it to 3 oh that's still less than 4 then we'll do our thing then inka minute to 4 it'll jump back up and say ok I can do one more time but we're gonna increase that I variable by 1 every time it loops so I'm gonna jump into my loop here and I'm gonna say I equals I plus 1 so every time it loops I gets increased by 1 now I can take that I value and I can use that inside of my dynamic name so now it'll be the active sheets name is equal to week space combine that with whatever I is currently equal to that's it now this is pretty slick we're taking a little bit of what we recorded and we're now modifying it to get it to do more for us get ultimately automating your experience now there's one more thing after the loop is all done I want to delete the original sheet that came in the workbook the brand-new workbook I want to delete it out of there so I'm gonna say worksheets 1 dot delete that means the very first worksheet in the workbook delete it out worksheets one red one represents the first worksheet dot delete we're gonna delete it out of there all right what do you think recorded a little bit got some of the magic done with the macro recorder now we're getting in a little bit deeper in the VBA these key concepts variables loops it's the sin text the way that you write your VBA this is all stuff that's going to come to you as you practice and as you go through our Excel VBA and core courses this is all stuff that we're really gonna nail down I'm gonna give you plenty of examples of how to work with this but let's take a look at what this magical little monthly worksheets macro would do for us so I'm gonna close this out so imagine this the next week rolls around you need to add a new workbook and have four worksheets in it called week one two three and four all right so how do we run this well you got multiple ways to do this I'm gonna introduce you to modifying your quick access toolbar to give you a button to press top left corner of your screen you've got your quick access toolbar I'm gonna go a little down arrow I'm gonna go down to more commands inside of here I'm gonna change it from popular commands to macros there's macros and there's my monthly worksheets macro that's in my personal workbook I'll select that I'm gonna hit add that adds it into my quick access toolbar I'm gonna hit OK and now I've got a button up there that runs my macro all right watch this you ready for the magic all right I'm gonna hit that button all right Excel will permanently delete the sheet do you want to continue remember the last line deleted the original sheet I've got my four new worksheets in there week one two three and four how quick was that and now we're gonna delete sheet one I'll delete it it's gone and I'm left with just the four worksheets that my macro created all in one button press I mean you name it you think of what you can do inside of Excel and you can automate it you can record a little bit of it get in there start recording yourself and then you can get into the VBA and start to modify it all right what do you think of that hey guys john here sorry to interrupt but if you have any questions please write them in the chat box now and i'll get back to you as soon as i can and there's a lot of questions coming through if I can't get to you now then as soon as the webinars over : I will send you a reply straight okay back over to Kyle now before I turn the time back over to John I want to show you just a couple more quick tips and tricks that you can utilize to help you expand your knowledge of Excel and more specifically with Excel macros and VBA so take a look so I've got the book to open this is that blank workbook that we just got done working with and it's got the four worksheets in there that are macro inserted for us and renamed for us let's let's reopen the VBA window for a moment all right here it is all right so here you can see this on my project panel that I've now got for worksheets in there earlier I don't think I called it out but there was only one worksheet in there but now that we've had the procedure run the macro run it's added the four worksheets and they show up here inside the project panel now now keep in mind when I originally recorded the macro I recorded it to the personal macro workbook so any code that I have in there in any code that I've modified and added to is now inside the personal macro workbook it's not inside of the book to project that we ran the macro within just keep that in the back of your head here alright so what I want to show you here is two things one I want to show you how to add an additional button to a worksheet itself I showed you just a few moments ago how to add a button to the quick access toolbar within your Excel application that's great but that's a part of your application you send a document to somebody that contains a macro they don't get that button it's not there that's part of your application so we're gonna take a look at how to add a button to the worksheet itself so users can click on a button and it will run whatever process that button is tied to whatever macro that button is tied to the second thing I want to show you here and this is perhaps one of the neatest things I'm about to show you here especially if you're just starting out with an Excel Excel VBA is called the step into feature this is gonna help you with two things one learning code and to how to debug code one technique that you can use to help you debug it if you run into errors so first the button take a look I'm gonna open up a new workbook let's do file new just a new blank workbook now remember the code that we wrote that we originally recorded and then modified is in the personal macro workbook so I've opened up a brand new workbook I'm gonna create a button in there that ties itself to the code inside the personal macro workbook really simple to do take a look let's get this back off my screen here so inside of the new workbook or whatever workbook you want to put a button inside of I'll go back to my Developer tab inside of the controls section of the Developer tab I can go to insert and the very first one in there looks like a little gray rectangle this is your button control or you might hear referred to as a command button so go ahead and select that I'm just gonna click on it once and then I'll come down to my worksheet I'm gonna click and drag just left-click and drag to create this button now very important as soon as I let go here it is it pops up with my assign macro window I've only got one so I grab my monthly worksheets macro then I hit OK and I've now got a button that when I click on it it will run that macro and I can change the text in there let's just delete that out let's say add weekly weekly sheets oops all right click away I got my button so now when I click that button it's gonna run that procedure so I'll go ahead and click it there it is Oh excel is about to delete the worksheet I've got my four new worksheets down there at the bottom of my screen I hit delete from it now I've got it so one button press right there just a button on the worksheet remember that's just on the Developer tab insert and insert a button and then assign it to one of your macros pretty quick pretty simple there's lots of different ways that you can run macros we just seen a couple of them during this webinar as you go through the Excel VBA courses that we offer we'll introduce you to some other techniques that you can use as well to help you run your procedures or run your macros now I mentioned there's one more thing I know John's itching to get back in here and talk to you a little bit of himself but a little more thing that I want to show you here and this is perhaps one of the neatest tricks I can give you if you're especially if you just starting out with an Excel VBA now think about this I know you've probably gone out to your favorite search engine and typed in something like how to do this inside of Excel okay or how to do this with the macro you come up with hundreds or thousands of websites that have samples and answers to your question you may have even got out to one of these websites copied some code and pasted it into your Excel application the VBA window now that's great there's loads of code out there stuff that's already done for you but that code somebody else wrote it and they may have done in a very specific way now I want to be able to learn how did they do that why did they do that what does that do okay so what I'm about to show you here is called a feature a feature called step into or step through take a look I'm gonna go ahead and close this workbook this is just the one that I created a button and I don't really need it I'll just close it I'm gonna create a brand new one just so I have something fresh to start with and I'm still gonna work with the same procedure the same macro that we just created but just in a brand new workbook now I'm gonna bring my code window over as well and I'm gonna get these two windows just side-by-side each other just so I can see the Excel workbook that brand-new workbook and I can see my macro now we've seen you can attach this to a button to a shortcut key a button in an interface or a button on the worksheet and your users can run it now for you the developer of the procedure of the macro you have a couple ways that you can run your code as well one I've got my procedure my macro monthly worksheets if I click into there just as long as my cursors in there there's a play button or a run button at the top of my VBA window and I can just run the procedure right there done now that's neat but I want to show you the feature here called step into and what this is gonna allow you to do is step through your code your macros code a line at a time so imagine this again you've gone out you've copied a bunch of code or you maybe even recorded a bunch of code just through the macro recorder and now you want to go through and see what's actually happening here step into the code or step through the code so again I got my two windows side by side each other I'm gonna click into my procedure and if I go up to the debug menu there's an option called step into now I like to use the shortcut key f8 just the function key top your keyboard f8 so I click into my procedure I'm going to tap the f8 key now one tap it activates the macro it's gonna start running it but in order for it to continue to run each line of code I need to continue pressing the f8 key step through each line of code one statement at a time so I'm gonna hit f8 again so here's my I variable that we created what is it currently equal to and this is really neat as you're stepping through it I can see what I is equal to in this case it's equal to zero hasn't done anything yet well it's hit f8 again now what's it equal to it ran that line I equals one and now if I hover my mouse over that it says I is equal to one now it's gonna go through my loop I is currently equal to one which is less than equal to four so I hit f8 again it's down that line its inner my loop the condition is true so now it's gonna add a sheet keep an eye on the sheets over here I'm gonna hit f8 again and now I've got a new worksheet there well what's it gonna do next it's gonna change the active sheets name to week plus the I what's I equal to oh one so I hit f8 again and now it's equal to week one and I can continue hitting f8 over and over again here I is equal to one i press f8 again it runs that line and now I is equal to two because if we added one to it now I keep going I'm just gonna f8f8f8 f1f1f1 seed equal to now I is equal to four but I hit f8 again now it's equal to five so it's gonna try the loop it's gonna try to loop remember I is equal to five which is right is no longer less than equal to four so when I hit f8 again it skips the loop and it goes down to my worksheets delete now remember we got that original worksheet in here called sheet wand if I hit f8 again I'm gonna delete the worksheet great it's gone and now I'm just left with the four worksheets that the procedure created now it's on the end sub line it's all done I hit f8 again and I've completed the procedure done step into very slick you're brand new getting into Excel or you're running into errors and you're trying to figure out where it's happening you can check variable values as you step through step through your code dissect it figure out what's going on or just figure out what the code does if you inherit work from other co-workers and they've written their own macros it's a great way to learn what's going on inside the code and maybe even borrow from it repurpose it to fit your needs f8 remember your debug menu step into alright so there's a couple additional tips there I hope you enjoyed those especially if you're just starting out with an excel vba and into the programming world there's some neat ones right there now John and I have put together a huge course all about Excel VBA now if you haven't done any programming before it can be daunting it can be hard there's a lot to remember there's a lot to do there might seem like a lot of mmm trying to memorize key words and and syntax and so on but that's why John and I are here we're here to help you we're here to help you become more productive within Excel by automating your tasks through Excel macros and VBA so we put together this course and I want to talk just a little bit about it right now so the first part of the course is an introductory level to Excel macros and VBA I'm gonna introduce you to some of the key terminology objects properties methods and so on that are gonna make up your experience and career within Excel VBA and macros we're gonna talk about things like objects what's an object all about what does that look like inside your code we're gonna talk about looping structures what does this really mean what's going on in there how can we best approach we want to be able to repeat a block of code really fundamental stuff understanding Excel macros and VBA now once we filter Foundation and that's very important we build a solid foundation anytime we get into any programming language VBA or otherwise once we've done that then we'll jump into some projects I'm gonna take you through seven real-world project-based examples and you're gonna programming yourself you're gonna record macros you're gonna repurpose those macros you're gonna edit the code within there you're gonna write your own procedures create modules create macros and procedures write your own code you're getting your hands dirty you're gonna get in there and you're really gonna learn Excel macros in VBA through these seven project-based real-world examples that's the second section of the course the third part I'm going to take you into user forms we want a nice intuitive user-friendly interface for our users to be able to interact with our Excel documents that's what Selvi ba userforms are all about present the user with the window got buttons checkbox radio buttons drop-down lists whatever it is present them with this nice user-friendly interface to be able to interact with your excel content all through excel vba user forms and we'll talk all about that setting up the forms the controls writing the code interacting with the users receiving input doing something with that data and so on interacting with the users presenting them with a nice user interface that's the third part of the course the fourth part we're gonna take you into dashboards how to create effective intuitive user friendly dashboards drop down menus with filters a little bit of VBA in there to make it more interactive and to interact with the data between the user and your excel data we'll talk about charts slicers and so on building a beautiful dashboard that you can show off for your reports so I appreciate you all coming out joining me for this webinar once again I know John's waiting there he wants to jump back in and talk with you as well so I'm gonna go ahead and hand it off to John again thank you very much for coming out and joining me and I'll see you inside the course so I'll pass it on over to John all right Kyle thank you very much for these awesome webinar you've covered a lot I'm sure that a lot of people have opened their eyes into what's possible with Excel and VBA and macros in particular so thank you for your time but that would just the tip of the iceberg what Kyle showed you guys is just scratching the surface because you know we've only had 45 minutes for this webinar there's so much more than you can do with macros and VBA and we have created a course we are launching it today and it's called the extreme macros and VBA online course it's a lifetime online access and you can videos now this is in the course which I'm going to go through now it's over 10 hours a material over 150 short tutorials that talks about everything and anything you need to know about video as a macro so you can save time and money now let us go through this course here let me just scroll down I mean this is the webpage here the course and it just explains a little bit of the course if we just scroll down a little bit more here okay let's go to the course Griffin so you get four modules the first one is the introduction to VBA then we'll go on to mastering keen macro and VBA concerts with the seven projects that real life projects would go through them Kyle goes through them because here's gonna be your instructor he is the expert in VBA and macros and number three cause them to go through master Arina VBA user forms these are awesome we'll talk about them in a second and then how to create a VBA dashboard now let's just go through this a little bit so module 1 is an introduction to VBA so if you don't know macros or VBA or you know a little bit about it this is for you now the first one here is nine videos you can see here in 45 or 44 minutes you can show the content and it just shows here what we're going to talk about here so we just talked about an overview of VBA on what is that and then COG actually the worksheet object here's how you can reference um a worksheet change its name edit count create and print and also delete worksheets using VBA then the range of object here you're going to learn how to reference a range of cells in your worksheet have you select them change its values and reference a cell on another worksheet relative references then here we explain the difference between absolute and relative reference and also had a reference to be active sheet or an active cell and introduce you to the officer command which is great formula that you can use to select different ranges modules and procedures Carl talks about what our module is and how you can rate your first procedure or simple terms macro you'll also learn how to run a procedure using a button adding comments to you may be a cold and saving your Excel macro enabled workbook now variables are great that are very important when creating VBA and very powerful indeed you're gonna learn how to create a variable that will enhance your VBA coding skills and macros we explain the different types of variables as a string integer and boolean conditional statements and loops now these are the if statements just like similar to your normal if Excel formulas and you can create various loops as well that it's gonna truly automate your work it's a very important section within this module and it's just gonna take your your automation on to another level and then finally within this first module you create your first message box that prompts users to an action and receive years of import via an input box as you can see here module two is mastering key macro in VBA concepts with seven projects on the first project Kyle shows you handle recorder macro editor save it and run it to your Excel workbook and everything else that you need to know about recording and storing macros project number two is about sorting your data with an input box and message box now in here you can create a nibble box for your user to sort their data and a message box to alert the user of any errors as you can see here that's a message box there you can choose how to sort and then banks also automatically number three is preparing cleaning up data with VBA very important we will show you how to in format headers in your data for every single worksheet with a press of a boat imagine having a hundred worksheets you know that believe is manually copy/paste no no no bang click of a button just like that all your sheets get updated in seconds not hours president before is using VBA to automate Excel formulas you know learn how to automate the Sun function using VBA and loop the Sun function over multiple worksheets that's gonna save you a lot of time as well project number five I wouldn't bring everything we've talked about in the previous projects and create a healer report so all the knowledge and skills you're gonna console your data into a yield report and we'll show you how to do that project number six is introducing me to VBA is a four percent these are very cool these are awesome I love them you should use them it just gives interactivity onto your Excel workbook I mean it allows users to pick and choose and reduces the chance of error so this is like here's a from here someone can click and drop down and add cheese and run the report great little feature you can do this using VBA it's called VBA is a box you can learn how to create user form with drop down some buttons that allow the user to add new sheets and run the yield report created in project number six number seven is imported data from text files now we say how do we pull data from multiple text files into separate worksheets within your work very powerful feature a lot of people ask for this and we've got a section for you now module number three is mastering VBA userforms ok so in the first section we're going to build the user interface with a VBA years ago this is it here this we can do using the V at a time and it's very easy just dragging and dropping and you have it there you know you'll understand what user forms are all about has a layering creator form had to use different controls like text boxes command buttons combo boxes and checkboxes this is great I love them now pretend I'm a tree is connecting the user form to a worksheet for data entry so you only have to transfer the data from this user form onto a worksheet list with a simple press of a button project number three is creating a print report user form you can create a new user form interface to print out a report based on the records entered from Project number two that's great you can just take information from your master data press the button it opens up a new work syncing you can print it out number four is building a statistical user form now look at this imagine having a button break it shows you the average call time that maximum call time justkeep sophistic s-- that's just very powerful so we're going to show you how to build the user from the shows that kind of information now this is just a quarter but imagine having sales you can apply the sales you can apply that to the employee data to whatever you like and the final module is creating a VBA - but oh yeah this is going to be a great credit module here so we introduce you to this dashboard that you can create using formulas and automate it using VBA so with a couple of presence of buttons these automatically updates and you don't need to do anything else now we show you how to clean your data with formulas we show you the proper the upper choosing text formulas it just cleans up the raw data that you get and just to make your dashboard more presentable we also go into the lookup data here we'll show you how to do the data validation drop down and also talk about the vlookup and index magic or formulas great formulas the most popular form us will show you how to use them feel free data we create an advanced filter to show you specific data on your dashboard and then here's a macro in VBA to automate this process we also show you how to use subtotal function and why you need to do that using your dashboard it's more powerful than the count or average or max subtotal is a great function and we explain that in here now we also go into the pivottables and pivotcharts interactivity we have that you can analyze data in seconds and we show you how to create a pivot table in a pivot chart and also use VBA to connect update and automate your dashboard very powerful just with some code a few lines you have that and then each time you change something or you press something Bank it updates your dashboard we also include interactive buttons slices is a great we include them in your dashboard and this brings interactivity into your dashboard in your user and management and love and the father touches some important steps about protecting your dashboard and how to make it more presentable so that is it there you can click down here to view all the tutorial goes all the way down to the bottom of the page and these are the individual tutorials yeah there are about three to five minutes long and it's all lay down here so you can have a look at the exact tutorials that we go about there's 160 or so videos you know we're gonna be adding some more videos as well through the time ok so going up to the page again here we just go through a couple of more stuff that you only get through this course and also talk about the bonuses that I mentioned at the start of this webinar these special bonuses that you're going to get for attending this webinar now first of all what you get when you sign up is you get this course learning and management system is delivered in these management system and you don't need to installing the software it's already there will you do you're just going the course and you get access to all the workbooks and all the bonuses and all the videos you can view it online you could vid on your computer on any device and it is just an amazing course management system so go down here as I said before you get all the downloadable and interactive Excel workbooks to follow along and hone your skills so all the workbooks that Kyle goes through you get them in a start format and also in a finished format with all the codes in the VBA code so you get that it's four year you can keep it forever and you can refer to work whenever you like your secure videos here so all the videos 160 plus videos that are in this you can download them you can download them and when you get traveling you can view them or if you don't have Wi-Fi if you're not connected just put onto a hard drive and view them you know while you're traveling and a plane or wherever so that's a great feature of buying this course today's you're going to get all the videos downloaded into HD quality mp4 format so you're secure lifetime online access and course updates so it doesn't matter where you are you can access this course or whether you're at work or at home as long as you have internet access you can access it from wherever you like you only get new features or we may come out we're gonna be updating the course as well you also get a certificate of completion so once you complete the extreme macros in the VBA course you're going to receive a certificate of completion that you can print frame and also put on your CV you can also put in within your LinkedIn profile and show that in the achievements sections for other professionals to see and that's a great feature now finally you get a 30-day risk free full confidence money-back guarantee now we're so confident that you're going to get lots of value from this course that you can take it for the full 30 days study it apply to your work and see results now if you are not 100% satisfied and then I am personally more than happy to refund the full purchase amount even if it's midnight on the 30th day so give the course book go watch all the videos down on the workbooks and ask me any question in 30 days ask me or Kyle you have both of us here now if you don't feel you'll be able to March with a craft of using Amazon macros and bb-8 to complete your objectives then contact us and we'll refund you your money no questions asked and no hard feelings that is our moto here microcell online with all our training courses that we offer so you literally have nothing to lose alright fair enough let's go to the bonuses alright that I mention it so you get five bonuses for signing up to this course it's valued at over $900 cause the bonus number one it's a four hundred nine dollar bail you get twelve months of the dedicated pro level email support so you're gonna receive dedicated personal email support during the first four months you're gonna be a fast expert level whenever you get stuck or in need of an issue resolved by our macron and VBA guru copy you can also interact with Kyle via the discussions area inside the course and after Tommen's you still receive continued support so we're here to support you course bonus number two is $197 value get lifetime discount 33% on any of our other courses now we have a PivotTable course we also have a power query course and we're bringing out more courses on formulas and PowerPivot so as soon as you sign up to this course you get your unique coupon the king use on any of our other online courses course bonus number 3 this is a great little me book downloadable ebook offers VBA macros you today over 100 VBA coding examples before Excel Outlook Word PowerPoint and access this is a must-have for any of office user so so you can just sign up you get this download you can download it in the course or you can view it within the course learning and management system so it's a great ebook and one that you must have is your learning VBA macros in this gives you over 100 different examples cause bonus number four is a gray - add-ins here so these are by the Ryan wells now Ryan wells as you may know him he is our internal VBA expert and he's being I'm helping Mike's online users for the last few years he runs the free for gnarly VBA lessons and he has his own website wells are calm and he creates add-ins now he's created two atoms here and for signing up today you're gonna get these two for free okay and I'm explaining what they are so this Wells Pro add-in is an Excel add-in the less you automatically import macros from wells Icom directing to your spreadsheet without their believing in yourself plus you can organize your existing macros and even share them with the entire wealth Pro community right from your Excel ribbon now the other one is a mouse - macros is an accelerating that we cause your mouse clicks and cursor movements and converts reactions to a VBA macro for instant playback that's just insane you finally be able to quickly create repeatable cursor movements and mouse clicks for your VBA projects so these two add-ins the worth $65 you can go to the website have a look at that you get these free signing up today and finally the webinar the way through today you get that as well it's in the course you can download it because a lot of people ask you know the webinar replay is only valid for a few days and it is and then we're going give access to it because it'll close the course but when you purchase the extreme methods and VBA course you get this as well so you get lifetime online access TVs and you cannot say download so you can download it and view it offline so that's a great value so these are the bonuses there all right so now you gonna be asking me how much does just course cost John well this course with all the bonuses that you get today is gonna cost you just 297 dollars and also for signing up today you get a 20% off the retail price or you leave you just applied the coupon code Excel so you see here Excel the letters Excel you apply them in and check out and you're not gonna pay you to 97 you get a further 20% discount because you attended the webinar and this is just another bonus that we are giving today a further 20 percent on the retail price now if you compare live training now live training your travel there cost a lot of money because you've got to pay the instructor in his time or her time you've got to travel there you're gonna stay in a hotel also you get the uncomfortable conference rooms you get divided attention because the your Excel level and the next person so level is not the same there's a lot of distractions you may be in on the same level and you know you only have access to that person for for one day or two days so a lot of training is expensive and also so is one-to-one training chittering is about $100 per hour and that's a going rate so if this course here with 10 hours of material if that was a one-on-one lesson that's about a thousand dollars but you're getting this of course just for three hundred ninety seven dollars a USD you get lifetime access you get all the bonuses that we've talked about you're all the workbooks that you get your personal support a 30 day money back guarantee and also has typical completion so now what I want you to do is in here you get the two options you can pay three months installments or one payment of 297 dollars so you can click in here just click on there and it goes in the check screen here if you already have an account just click on that if you want to create a new one you can just put in your name here quickly so you fill me in details fill in your email and your password and press create account you can also sign up with LinkedIn and this is it here you have a coupon yes you do because I gave you a clip on it's called x-l press apply and bang you get your 20% for a new student discount yeah now you can pay by credit card or you can check out we've PayPal so let's see pretty quick and easy decision to do that bang you get access to the course you can access to the bonuses even if you use a three month option as soon as you pay the first month you get access to all the bonuses and all the videos there all right so let's recap what you're gonna get when you sign up today you get lifetime online access and course updates with extreme macros and VBA course you get four modules on macros VBA userforms and VBA dashboards yet ten hours of short video tutorials you get over 150 downloadable videos and workbooks you also get a certificate of completion and also our 30-day 100% money-back guarantee plus get these limited time and bonuses valued at nine hundred of $69 bonus number one twelve months of dedicated pro level email support bonus number two is 33% lifetime discount on any of mike's online.com courses bonus number three is the excel the book 100 plus offers VBA macros you can use today and bonus number four is to time Excel add-ins the wels Pro add-in and the mouse to macro added and finally bogus number five you get the macros and VBA webinar replay which you can download the one that we went through today now this webinar here is being recorded as I said before and we're going to send you an email with the webinar replay but it's only going to be available just for the next few days because the course is going to close because we need to help our students so this course the three members of UVA cause is only open for the next few days and webinar replay is open until then so after that the webinar replay goes away but once you purchase the extreme macros a movie a course you get access to all the bonuses and then you also get there webinar replay you can download it and view it again and so now we'll go through it just a couple of questions that people have you just put in the chat box and I'll try to answer them as quickly as I can so just give me one second okay a couple of people talking about the South Village then you can as long as you have Excel for Windows 2007 2010 2013 or 2016 office 365 this is compatible excel for mac 2011 and 2016 what parameters you accept well we accept all major credit cards Visa MasterCard American Express as well as PayPal the course fees are in USD you can also pay by cheque or bank transfer if you want to do that just send an email to me John at Mike's are calm and I will get that organized for year we also have tax invoices for your records so if you need to present a tax invoice we can set that up and mail email to you and any other paperwork that your company or government may require people say can you purchase these for large number of staff yes you can we have multi years at discounts so if you have you know more than two people that want to sign up then just email me once again John and Max online.com and you know we'll give you a discount for getting multiple people signing up to this course a couple of people asking whether this is for them or not well let me just tell you we should take this course new users beginners and intermediate and also advanced of cell users that are new to macros and VBA now there's a lot of experts out there in himself they don't really know how to use macros in Libya so this course is for them as well now anyone who wants to use Excel to automate their tasked with a simple press of a button this course is for them people who prepare reports are required constant updating with new data on a daily weekly monthly basis these courses for them professionals work in the corporate world or government sector you want to leverage Excel for increased productivity I create that imagine and more this causes for them people who want to change career and apply for a new job that requires knowledge of macros and VBA this course is for you and also there is no prior programming knowledge necessary to participate in this course we explain to you so all you have to do you just go in there and we explain to you everything ok so you keep on putting your questions if we haven't answer them then : I after this webinar we're gonna go through all the questions we get a whole list of them email to us and we're gonna go through them and email to you directly so now we're gonna close the webinar just a couple of minutes left I'm gonna put a slider of the testimonials here once again thank you for participating in the webinar click on the link on the webinar here you can click on that and it goes straight to the sales page and you can have a look at it here what we went through here it's all interactive you can click on there go to all the different areas there's also sample videos there you can have a look at so the link is on your webinar page now if you don't see it I'm gonna send you an email in the next 30 minutes with the webinar replay and you're gonna have links to the Islamic or sales page okay and Carl and I we can't wait to see you inside the course and make you better at exhauster you can stand out from the crowd and get those promotions and recognition that you deserve because you know we're here to make your life easier and increase your your excelsior so we can't wait to see you inside the course you

Channel: MyExcelOnline.com

Views: 418,873

Rating: 4.8713036 out of 5

Keywords: excel for beginners, excel vba, microsoft excel, vba excel programming tutorial, excel macros, macros in excel, vba excel, excel macro, excel vba tutorial for beginners, excel macros for beginners, how to create a macro in excel, Excel VBA Beginner Tutorial, excel macro button, excel macros vba tutorial, excel macro recording, excel macros advanced, excel macro mastery, excel macros tutorial for beginners, excel macros intermediate

Id: _tPY5BGIsJQ

Length: 83min 26sec (5006 seconds)

Published: Thu Mar 01 2018