Master Microsoft Excel Macros and VBA with 6 Simple Projects | Kyle Pew | Skillshare

Master Microsoft Excel Macros and VBA with 6 Simple Projects

Kyle Pew, Microsoft Certified Trainer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
40 Lessons (2h 38m)
    • 1. Welcome to the Course

      4:06
    • 2. Understanding the Why and How Behind Excel Macros

      3:17
    • 3. Introduction to Project #1: Inserting and Formatting Text

      1:38
    • 4. Project #1: Start Recording!

      7:12
    • 5. Project #1: Running a Macro with a Button

      2:11
    • 6. Project #1: Opps! I Need to Edit My Macro

      4:02
    • 7. Project #1: Practical Uses of Excel Macros

      2:16
    • 8. Excel VBA Concepts

      0:30
    • 9. The Visual Basic Editor

      2:06
    • 10. Excel VBA Modules

      1:14
    • 11. Creating Excel VBA Procedures

      2:04
    • 12. Understanding Excel VBA Variables

      5:14
    • 13. Working with Excel VBA Loops to Repeat Blocks of Code

      4:09
    • 14. Building Logic with an IF Statement

      2:38
    • 15. Project #2: Intro to Interacting with the User

      2:41
    • 16. Project #2: Breaking Down the VBA Code

      3:04
    • 17. Project #2: Prompting the User for Information

      5:33
    • 18. Project #2: Alerting the User of Errors

      7:03
    • 19. Project #2: Other Practical Uses of Messages Boxes and Input Boxes

      3:17
    • 20. Project #3: Intro to Cleaning and Formatting Data

      2:58
    • 21. Project #3: Using the Macro Recorder to Lay the Foundation

      1:53
    • 22. Project #3: Preparing to Use to the Macro Recorder

      2:27
    • 23. Project #3: Inserting Headers

      2:39
    • 24. Project #3: Formatting Headers

      5:05
    • 25. Project #3: Putting it All Together with a Little VBA Magic

      9:52
    • 26. Project #4: Automate Microsoft Excel Formulas

      2:22
    • 27. Project #4: Automate the SUM Function through Code

      9:28
    • 28. Project #4: Loop the SUM Function Over Multiple Worksheets

      5:54
    • 29. Project #5: Creating the Final Report

      2:55
    • 30. Project #5: Putting it all Togehter to Create the Final Report

      9:55
    • 31. Project #6: Working with Excel VBA Forms

      3:13
    • 32. Project #6: Creating an Excel VBA Form

      2:47
    • 33. Project #6: Adding Controls to the Form

      2:41
    • 34. Project #6: Changing Control and Form Properties

      4:54
    • 35. Project #6: Adding VBA Code to the Intialize Event

      7:24
    • 36. Project #6: Adding VBA Code to the ComboBox Change Event

      4:18
    • 37. Project #6: Adding VBA Code to the Add Worksheet Button

      6:13
    • 38. Project #6: Adding VBA Code to the Create Report Button

      2:02
    • 39. Project #6: Showing the Form

      3:26
    • 40. Congratulations

      1:02

About This Class

Join me in this course and take control of Microsoft Excel mastering the process of automating tasks with Macros and VBA.

During this course you will build a foundation of working with Macros and VBA. This foundation will be built as you engage and participate in 5 Excel Macro/VBA projects detailed throughout the lectures within this course. Each of the 5 projects will build upon one another, introducing a new concept each time, starting with the basic building blocks of automating tasks within Excel and ending with a fully customized Excel VBA project that will automate a series of tasks.

Transcripts

1. Welcome to the Course: Now, before we jump in and start to discuss macros and VB a within Excel, I want to introduce you to the agenda or the topics that will be getting into and you'll be mastering as we go through this course, the 1st 1 that we're gonna be getting into deals with one of the projects that you're gonna be working on. As you go through this course in this project, you're gonna master the Excel macro recorder tool. This is perhaps one of the most efficient ways to create automation inside of Excel. It's a nice user interface. And if you're already familiar with Excel, you could jump in and start recording your actions that you would like to automate at a later time just by using the macro recorder. So we'll discuss all about the macro recorder, pros and cons out of it and just how to work with it. Then we're going to stop for just a moment, we're gonna get away from the project structure, and before we get into project to, we're gonna do a little bit of discussion about XlV be a concepts some key programming concepts that you need to master to really become efficient as you work inside of the XlV. Be a interface. So we'll talk about things like modules, procedures, variables, creating loops, getting stuff toe happen. Multiple times we'll talk about building logic inside of your procedures or your Mac rose through the use of if statements will really nailed down these key programming concepts that again, you're gonna master as you go through this course here and then we're gonna get back on track and we're gonna discuss our next project, this RIA world based Project toe help you master these techniques that we just discussed in the previous section. But now it's all V. B A. We're gonna talk about interacting with the user, getting input from whoever runs our macro, asking them questions, getting information from them and then be able to use that information inside of our macro . Then we're gonna carry forward into Project Number three, where we're going to talk about cleaning up data. You've got a block of raw data, some list of records, and I want to clean it up at headers, format the data, do whatever we like with it, make it look presentable. But rather than us manually doing that we're gonna get excelled to do for us through our macro project number four within our real world examples here, we're going to talk about automating excel formulas or functions things that you would normally do manually. Like the some function or V look, ups or an average function. How can we programmatically and perhaps Luke that function toe happen automatically for us over a whole set of worksheets? We'll talk about automating a function inside of project number four. Now we're going to talk about all sorts of techniques within the macro recorder and within V B A and programming in general, this last project, project number five. We're gonna bring all of this knowledge everything you've mastered up to this point into this final project, and we're going to create a final report through one button press. You're gonna build a cleanup data across multiple worksheets, calculate that data and then create a final some rice report based off of that data, all in one button, press through our macros and procedures that you'll be creating and mastering inside of Project number five. Now, together with all these topics that we're gonna be getting into, make sure you download the exercise files that are provided in these lectures that you participate in the discussion board, leave your thoughts, your questions, whatever you have, jump into the discussion board and interact with me and the other students within this course. So take advantage of all the resource is that we have here to really master it Cell and Master Mac Rose and VB A. So let's go ahead and jump into our next topic here. 2. Understanding the Why and How Behind Excel Macros: So it's important before you start to really learn anything new that you learned the why and the how. So here I'm going to introduce you to the why and the how of Excel macros. So first the why Why do you want to perform or create Mac Rose inside of Excel? Well, it sells a big application. We spend a lot of time inside of it doing any number of tasks through the use of Mac Rose. It's all about automation. I want to automate my experience. I want to get Excel to do some of my work for me. Perhaps not all of it. There's some stuff that I have to have my hands inside of. But for those more routine fundamental tasks, whether it's a single step or a whole slew of steps that take you several minutes, if not hours to complete, we can automate through the use of a macro within excel. Now here's the how. There's two ways inside of Excel that we can create automation. The 1st 1 that we're going to discuss and this is going to be a part of Project number one within this course, is you're going to get in and master using the Excel macro recorder tool. This is perhaps the most intuitive and the most accessible interface. To be able to create automation inside of Excel, you simply record a series of steps. Think about something that you doing so excel. Maybe it's adding text to a document. Maybe it's formatting headers. Maybe it's sorting or filtering data. Whatever it might be, we know how to do those stains inside the Standard Excel interface. Well, now all you do is record yourself performing those steps, and the next time you want to repeat those same steps, you just run the recording. It's that simple. So Project number one, we're going to take a look at using the macro recorder to be able to create this automation and streamline our time and become more efficient. Now the second way. And we're going to see this on and off again as we go through the remainder of the projects is we're going to get into BB A or visual basic for applications. Through this programming language, we are gonna be able to create much more robust and dynamic logic based macros. Weaken, do whatever we like anything that you liked autonomy inside of Excel, even interact with the users of your Mac rose or interact with outside data. Whatever it is, we can get into visual basic and create our automation, custom procedures or custom macros with visual basic. Now, throughout all the projects, these five projects that you're gonna get into masters and said this course we'll talk about why, why we're doing this. But we're also going to talk about how you're going to do this. How do we write the visual basic? How do we work with the macro recorder? How do we save time and automate our experience as we work inside of Excel? So let's take a look. Let's go ahead and jump into our first project. 3. Introduction to Project #1: Inserting and Formatting Text: so welcome the project number one within this excel macro on VB a course. Now before we jump in and get her hands dirty, I'm gonna quickly demonstrate what you're about to create here and how it can save you time by automating these tasks inside of excel. So the project that you're about to jump into imagine this. Here's the scenario. So you've now imported a block of data, something like what you're looking at right now. And as this data comes in, I now need to clean it up. I need to add headers up at the top of the columns and I wanna put a title on this worksheet. Now imagine this is something you have to do several times a week. You got to create these little reports based off this data that's coming to you and to excel. Well, I've created a macro that will automate this experience, and this is what you're about to create. Take a look. I've added a button here that previously I recorded a macro that's gonna perform all these steps for me. So now in one button press, I'll give that a click. It's now done all the work for me. It's got headers along the top of my document. Now, all of my columns there, and I've got a header up there at the top of my document as well. First quarter expense report All in one button press save you time, become more efficient and really take advantage of your day. Not so much time spent side of excel, because now you're automating your tasks inside of excel. So get ready. You're about to create this project right now inside of this course. 4. Project #1: Start Recording!: So let's start recording the first macro that you're going to create here is gonna work with this workbook that I have open in front of me right now. This is the example file that comes with this lecture. So do feel free to download it and practice within this file. What we're gonna accomplish here is we're gonna record ourselves putting headers on each of these columns. Year column, A B, C D. And so on that air related to the actual data that's in these columns and we're gonna put a main header at the top of the document kind of a title. And then we're gonna format. Those things become bold change the colors, change the background color and so on. But keep in mind, we're recording a macro here. We're literally gonna record our steps each and every little step that we do that we perform inside of Excel. So the next time we want to do these same steps maybe with a different set of data but same columns and same title, we now hit a button or hit a shortcut. Key and excel automates those tasks for us. So take a look. This is how you record a macro inside of Excel. So my first step, I get to go up to my view tab top of my screen here inside of Excel. I'm then going to go into the far right. I'll go to Mac Rose and I'm going going to record a macro. So this opens up my record macro dialog box where it wants just a little bit of information from us Before we can start recording first step, we got to give the macro and name something to identify the macro through. Now few rules that we have to adhere Teoh. As we create a macro one, the macro name could be up to 255 characters in length. Now I recommend Don't don't use all 255 characters, but keep it short. Keep it precise. The point is you want some money to build to identify the macro and what it does just by its name, so keep it simple. In my case, I'm gonna call it add headers. Here's rule number two no spaces inside your macro names. Excel will not allow it Now. The reason why is were later on when we get into the code side of things. Spaces means something completely different. And they're not allowed in the macro or the procedure name, so just ignore them. Stay away from special characters, things like at symbols, dollar signs, hashtag pound symbols, that and so on. Just stay away from special characters. In general, you can use numbers and your macro names, but you cannot start a macro with the number. So there's a few rules for you. Toe fall, though, as you name your macro. The second option here is we can give our macro shortcut key now for demonstration purposes . I'm going to use control. L But do keep in mind that you can override system shortcut keys here so you wouldn't want to use something like control s or control P or control. See something that you already use inside of Excel. Don't use it, otherwise you'll override it. Now there are many ways to run, mouth grows, shortcut keys just happen to be one of them. In the next lesson, we're gonna take a look at creating a button to build a runner macro. Okay, so we'll see a couple different ways to initialize or to run a macro. The next one, I'm gonna leave it set to this workbook. But here we get to dictate where your macro is accessible. From where can you use this macro? Where do you store the macro right now? In this workbook means Aiken Onley work with this macro and it's being stored in this active workbook. If I put it in the personal mackerel workbook, this is one that's always open on your machine. It's kind of a hidden workbook in the background, always there. And it's accessible for you. Which means you could use it anywhere. Now again for the demonstration. I'm gonna keep it set to the default all store the macro in this workbook. And last, we can leave a description. Some type of notes leave some notes behind, letting yourself as well as others that might interact with this macro. Maybe some description. I mean to say something simple. I don't see this macro places, uh, column headers and a title on lower sheets. All right, that sounds good. Now, I'm gonna go ahead and hit, OK? And the moment that I hit OK, here we are now going to start record interactions. Let's try it out. I'll hit. Okay. Now I want to put headers on here, so I'm gonna jump up. Teoh. What is that? A three. And this one is gonna be called division. I'll have, ah, category, and I'm gonna do three months. Thes represents expenses for these different categories. And so we'll have January will have February and we'll have march. Then we're gonna have a total expense column as well. Now open the top. I'm gonna go into, like, see one there and we'll call this first quarter expense soups, Expense reports. That sounds good. Now I'm gonna go back and do some formatting here to make it more presentable. But once again, keep in mind, Excel the whole time we're doing this, we are recording. So it sells writing down everything we're doing. So I'm gonna jump back into here. I'll grab my headers. I'll go to my home tab and I'm gonna add on. Maybe I'll make it a green background White textile making bold. Let's change something about the Hatter up there. I'll repeat it. Green and white. Bold. But let's make it bigger. Let's center a line. That, uh so you know what, actually gonna grab all of that and just merge those cells. There we go. Nice big title there. I'll put an underlying on it again. All these little things that I'm doing to this document right now, kind of step by step. I'm recording. All right, That's the last thing I want to do here. So very important. Next step. But you're all done recording your macro. You then go back to your view tab back to the far right in the Mac Rose. And I'm going to stop recording. Very important that you do this. You don't want to move on, do anything else because you'd still be recording. So I'm gonna stop the recording. And I've now recorded my first macro. How easy is that? Taking advantage of excel running tools that you're already familiar with inside of Excel, doing whatever, but just having excel record you doing it Now let's see what happens when I run this. So I'm gonna go to this example. She just right next door called Run Macro and let's see what was my short guy? Key control L I'll hit control. L and I've now automated putting those titles on the worksheet, putting the main title up there at the top and doing some for Matty. So what took me, you know, a few minutes to do and several steps within Excel. I've now got one button breasts, one shortcut key, and I've now automated that experience. 5. Project #1: Running a Macro with a Button: All right. Welcome to the next session. So in the previous recording, the previous lecture, we took a look at creating a macro through the macro recorder, just recording our steps. Literally. We perform tasks inside of us. It cell and Excel is busy writing down notes about everything we're doing. We saw that I had a shortcut key. So later on, when I wanted to repeat those same set of steps, I just hit the shortcut key and I was done here. We're going to take a look at taking those set of steps that macro and tying them to a button. Super simple to do. Take a look. So here I am. Instead of excel, I'm gonna go to my quick access toolbar, which is up here in the top left corner of my screen. I'll go to the little down arrow there and all the way down at the bottom. I'm gonna go to Mawr Commands. So this is gonna open up my excel options window, and from here, you can see that I'm on the quick access toolbar. I'm currently looking at a list of popular commands. I'm gonna change that from popular commands to Mac Rose. There's my macro. This is the one that I created in the previous lecture. I'll go and select Add headers and I'll add it. So now on my quick access toolbar, I've got the standard buttons, plus a reference to my new macro called All Add Headers. And if I hit OK, I've now got a new button up there. Let's test this out so you could see that I'm on the worksheet called Run With Button. I'm gonna go ahead and click on my macro, but now and I've repeated those same steps one button press and it's added headers and it formatted on Put the title up their first quarter expense report, and I'm done saving time using Excel Macro is becoming more streamlined, more proficient, utilizing Excel and getting excel to do Teoh really do the work for us. So tying a macro to a button just through the quick access toolbar go up there and customize it. Place all your favorite commands up there 6. Project #1: Opps! I Need to Edit My Macro: All right. So hopefully this is starting to give you a foundation of working with macros inside of excel. We've taken a look at recording a macro using the macro recorder. We've taken a look at tying a macro to a button. Now think this a step further. I'm going to set you up for the next section within this video lecture series. Now, let's say that I've recorded this macro super slick. I've now automated some of my work, but I now want to get in and make some adjustments to the macro that I recorded taking a look at the document in front of you. I've got some columns here, division, category, some months and a total. And I decided you know what? January February. That's nice blights. I actually want to spell it out. January, February, March and so on. Or maybe I want to build to use this same macro, but for quarter two and three and four. So I need to get in there and modify the macro. I'm not going to recreate it. I'm not gonna re recorded or create different ones for different quarters. But let's just reuse what we already have. So In order for you to edit a macro, you got to get into the code. The visual basic editor. Take a look. This is how you activate the visual basic editor. So I'm gonna hop back up to my view tab top of my screen. I'm gonna go back over to Mac Rose inside a Mac Rose. I'll go to view Mac Rose from here, I get my macro window. I'm gonna make sure that I have my ad headers macro selected and on the far right, I'm gonna go to edits. So this is going to open up a completely new window. It's completely separate from Excel. You can see excels there in the background, but this is called the Visual Basic editor. And this is where all your mac rose all the code that Excel wrote down for you as it was recording your actions. This is where it's all kept in the visual basic editor. So I'm gonna make a quick little edit here just to show you a difference and see how quickly can make changes to your recording macros. But in the next section, we're going to do a deep dive into this window and We're going to take a look at the ends and outs and become comfortable creating your own macros within this interface. So take a look this I can edit something. So let's say, for example, that I want to get back into here and make this 1/4 to document. So I confined the appropriate line here. I've got active cell. That formula are one. C one equals first quarter expense report, and I want that to be second quarter expense report. It's all just change that from 1 to 2. Let's actually say to Indy second quarter and we know second quarters not give me January through February but forcibly fed regenerate through March. But it will be April through June. It's all just changed. These will say April, May and June. So all I've done here is just make a couple of quick little edits to the code with in my macro, these air, all the notes, all the code that Excel wrote down. So now I made a quick edit. I can close the visual basic editor window back to excel and let's see, I'm just gonna rerun it. Never that button that I created earlier I'll give that a click. And now I've made my update. I've now got my second quarter expense report with the appropriate three months there for the second quarter. So quick little edits I'm not going to re record it. I'll just jump into the code, make the minor tweaks that I need in there, and I'm now updated my macro. Now get ready for the next section. We're gonna do a deep dive into the visual basic editor and see the ends and outs of code make adjustments to and perhaps write her own code within the visual basic editor. 7. Project #1: Practical Uses of Excel Macros: So before you jump into the next session where we're going to get into the visual basic editor and really do a deep dive into the code itself. Let's stop here for a moment and think about some ways that you can use Excel Macros. So we saw in the previous few lectures that recording a macro really isn't a big deal. You got to your view tab, You go to Makro, owes you record a macro. You fill in the little macro window you hit. Okay, But you're now recording anything that you dio think about that for a moment. What do you do inside of excel? What would you like to be able to do? What is your manage? Your asking you to do? What would you like, toe perform and really automate? Let's get excelled to do some of the work for you. So what are those? Seems some of those things that you dio Well, let's make a short little list year. Perhaps you import data from some other system, you dump it into excel, right? Every day, every week, once a month. I have to do this whatever the time frame is. But now we can record ourselves importing data into excel. So the next time you need to do it, you hear a shortcut key. You hit a button in the interface and you're done. Maybe you create charts. How many steps you have to go through instead of excel The credit chart? Well, we could just record ourselves creating that chart based off of some data. Maybe you got to filter or sort data or format data. This is something regularly that ideo well turned it into. Macro. Get Excel to do that for you. So again, stop and think for a moment. What do you do inside of Excel that you would like to automate? Get Excel to do that information for us or do that work for you? So think about that before you move to the next session and then, to me a favor, engage, go into discussion board within this this training environment and share some of these ideas with us. Where can you use macros to automate your work inside of excel and maybe will spark an idea within somebody else and give them an idea of where they could be using it as well? It's all seeing a little bit with the next session 8. Excel VBA Concepts: Now, before we jump into the projects, I want to introduce you to some of the key concepts that we're gonna be discussing. As we get into macros and VB a within excel. We're gonna talk about a handful of features here, and then we're gonna translate or transition into the five projects that you're gonna master these concepts within. So get ready. Let's go and take a look at the first concept here. 9. The Visual Basic Editor: So the first concept that I want to introduce you to here is your development environment within Excel for V B A or visual basic for applications. This is called the Visual Basic editor, and I want to give you a quick introduction to this environment, and then we'll continue to work in here as we go through the various projects. Now there's multiple ways to get into the visual basic editor or where you're going to write your code. But I'm gonna give you a short cut key here, and you'll use this a time and time again. Throughout the various projects, it's Ault F 11. So on my keyboard, first off, I've got Excel open here. But on my keyboard I'm gonna press Alz the all turkey and F 11 1 of the F keys at the top of my keyboard. And this will open up the visual basic editor and just f y I the same shortcut key works in word. It works in Power Point anywhere you're gonna be using visual basic within the office applications. Alz F 11 is your shortcut key. Now this is your development environment. This is where you're gonna be writing your code when you get into visual basic. Now, as you can see on my screen, it's It's pretty empty at this point, but I've got a couple of windows. I've got my VB a project window open. I've got my property sheet open. If one of those is gone, let's say I close them here to sit in the axe. I confined those windows, the project window in the properties window by going to view underneath view. I've got my Project Explorer. There it is, and once again interview. I've got my properties window, and I've now got those open. So this is just my default environment within my visual basic editor. This is where you'll be doing your development of your Mac Rose and your various procedures that you'll be creating through a custom visual basic for applications. Now, the next one that we're going to take a look at the next concept is getting into modules. This is where you're gonna store your code. So let's take a look at the next next concept here 10. Excel VBA Modules: So here's concept number two that I want to introduce to you when you open up the visual Basic editor, we need someplace to put our visual basic code. One of the more common areas to place our code within is called a module. Now, there's actually multiple different places. You can put code, but this is the most common area to place our code within a module. Now, taking a look at my visual basic editor, I've got my project Manu open. And in there you can see it's making reference to my workbook called Book One. I've got reference to my single sheet in there and this workbook, but I don't have any modules in this project. So here's a quick step Teoh. Insert a module where you can write your own visual basic code. I'm gonna go up to insert. I'm gonna go down to module and that's it. You can see now in my visual basic project window. I've gotta modules folder with a single module in there and it's just got an empty module where I can start to write my own custom visual basic code insert module and you're ready to start coding 11. Creating Excel VBA Procedures: So here's the next concept that I want to introduce you to within our visual. Basic editor. We've got a module. This is where we can put our code. But your code can consist of two different types of blocks of code. We have procedures and functions. During this course, we're gonna be focusing on the com or common of the two different blocks a coat, a procedure or what you might refer to as a sub procedure. Or you might refer to this as a macro kind of in user front and within Excel will call Mac Rose here and said the code will call him procedures. Now that we've got a module, we can now create a procedure. Here's how we create a procedure, or at least the beginnings, the kind of the stub of a procedure. I've got my visual basic editor open. I've got a module I'm gonna go to insert, and I'm gonna insert a procedure. Now. It's just a little bit of input on our parts. We're gonna build a procedure. First step, I get to give my procedure name. I'll just call this fun with V B A. Now I'm gonna leave the rest of the setting set the default. We're gonna create a sub procedure. This is just going to run a block. A code doesn't return any values. It just runs a block of code for us. Whatever we tell it to do. And I'm gonna make its scope set to public meaning within my project anywhere within this project, I could make reference and utilize this procedure called fun with V B A. I'll hit, okay? And I've now got the beginnings of my procedure. It's a public sub procedure called fun with Phoebe A and ends up. Now, all of our code is gonna go somewhere between those two lines. Whatever we want this procedure to do Well, put it right here. Insert procedure, give it a name, set up, its scope set up. It's it's availability, and we've got a procedure there 12. Understanding Excel VBA Variables: So now that we've got some of the basics out of the way, we got the visual. Basic editor, this is your XlV. Be a development environment. We've got modules where you're gonna be right in your code and we got procedures, which is really your block of coat. How you're gonna onto me within excel. You're gonna run this procedure. Mine's called phone Phoebe A and it's gonna do this. That and the other thing, whatever that procedure does for us. So now that we've got those basics out of the way, let's take a look at some of the programming concepts that you're going to need to know as you continue to develop within excel Vehbi, eh? Now, the first concept that I wanna introduce you here and regards the programming a very, very important 11 that you're going to rely on time and time again throughout your procedures, these air called variables. Now, if you've done any programming in any other language, I'm sure this sounds familiar. If you haven't, this is one of the more base foundational concepts that you need to master as you program within any language. So here's how we work with variables instead of xlv b a. Now I'm gonna use this procedure that created in the previous lecture within the public sub fund with VB a right in between the two lines year. I'm gonna create a variable now. First, this is how I declare a variable. It looks something like this in B B A. I start with the dem keyword. Now imagine this. Here's the purpose of a variable. I want to build a gather input from some other source, perhaps from a user, or find the value of an active cell within my cell document. Or maybe I'm importing data from some other source or somebody's filling out a form. But I want to gather data from some source and bring it into my procedure here. In order for us to grab that data in store, it's whether it's for immediate use or perhaps for later use some later time Within our procedure. I store that value inside of a variable. Now, what I like to do here in this example I'm using dim. It's the reserve keyword to declare a variable. I want to gather input from the user, so I'm gonna create a variable here called user input simple. I've just made that up. That's not a reserve. Were not a not a specified word within visual basic. I'm creating a variable here that I'm gonna store user input inside of. And I'm imagining that I'm gonna store a string value, some text value, maybe users air filling out of form like their first name. I get that user input I stored inside this variable. I now have that information, so now it's dim. It's called user input, and it's gonna store strings or is the string type I've now declared a variable. Now I can start to put content inside of this variable. I could say user input is now equal to something, whether it's, ah, the value of a cell that sits inside my worksheet or the value of an input box or a form that the users filling out. Or maybe it's just something that I'm gonna put in there myself. Programmatically it's all say this is equal to George. User input is equal to George. And now any time that I make reference to user input, I get the value of George back. Take a look. I'll say Active cells value is equal to user input. This just move this out of the way for just a moment, so we'll see what happens here. I'm gonna run a really neat technique inside of my visual basic editor here that's going to allow me to watch my code run a line at a time. It's a great way to learn visual basic. It's also a great way to debug visual basic. Take a look. I'll click into my procedure. Doesn't matter where just anywhere in there and notice. I'm currently out in my worksheet here instead of D. What is that? 12345 66. So on my keyboard I'm gonna tap the F eight key and this is gonna allow me to step into my procedure and I'll just keep tapping it. F eight. So now I've gotten into the user input equals George by hit F eight again. I was gonna hover my mouse over the user input and you could see it's equal to George. If I come down here, I could see it's equal to George now. If I hit F 81 more time we're now gonna take the active cell. Changed its value to be user input, which is George. So hit F eight And there's George. So I've created a variable someplace. Aiken store information. Wherever that information comes from outside source or something internal. And now I'm turning around and using that within my procedure in this case, just changing the value of an active cell based on that storage that value within the variable. 13. Working with Excel VBA Loops to Repeat Blocks of Code: All right, so we got the concept there of variables, the ability to build store information with inner code for immediate or later use. Let's take a look at the next concept here. Widely used within programming, not just VB a but but every language you pick up, you're gonna want to perform loops. I want to be able to get a single statement, a single line of code or perhaps a block several lines of code to repeat multiple times, both in visual basic. We've got a couple of different types of loops, and I want to introduce you to one of them. Here. You're gonna experience another wine when you get into one of the later projects is well within this course. So any time you want to get something to repeat over and over and over again, whether it's a set number times Hey, I need this to happen 10 times or it's an unknown number of times. I want something to happen based on how many worksheets I have in the workbook. Well, do I have to? Or 10 or 200 worksheets in there? I need to look for each one. So here's an example of creating a loop within our visual basic within excel Take a look. So I've still got my procedure here called Fun with B B A. Now what I like to do is I want to create a little bit of code that will go through all of these numeric values here and make them bold. Take a look. Here's a loop I'm gonna utilize was called a do wild loop I'm going to do something while something is true So I'm gonna say Do while And what I like to do is go through all of these numeric values here found in the a call I want to go through 10 12 58 13 21 Sona, some song Make him all bold and tell it finds an empty cell So I want to continue to do this wild the empties a while The cells contain a value the moment that they do not contain a value that I want to stop. So I'm gonna say do while the active cells value does not And this is how we say not inside of BB A. It's the less than greater than symbols can eaten one another is not nothing. Just in empty string and then I'm gonna loot. Now, do what do wild. This is true. But now between the doing the loop line, I'm gonna tell it what to dio. I mean, tell to do two things. First, let me tell it to take the active cell, whatever cell you're currently on and change its fonts, it's bold. Too true. This is a bullying operator. It's either true or false. I'm gonna make sure that sets its bulk property. It's font bold property to true. Now, once it's done, it's for one of the numbers, like, 10. I then want to go to the next number. So I'm gonna use active cell again, which is whatever selling currently honest, I'm Excel Document, and I'm gonna use a method here called Offset. And I'm just going to tell it to go down one row zero columns and select that next cell. So here's my loop. I want you to loop while the active cells value is not empty. I want you to change the active cells font bold property to true, and then go to the next cell, just one line down below, and then it'll loop again. and this will continue going until it finds that empty cell. And then it'll stop. This is a very traditional, a very common type of loop to create inside of your XlV. Be a projects. Take a look. I'm gonna click into my procedure. I'm gonna hit the play button. This will run my sub procedure and I'm done. It's now bold. Made each of those numbers there bold did it all for me in a simple little loop A very key concept that you're gonna master through the projects within this course. 14. Building Logic with an IF Statement: So here's one more concept that I want to introduce you to within xlv b A. This deals with building logic into your excel procedures. Now, in the previous example, we're talking about loops. But the loop that I created, it lived through this numbers here, this this column numbers and it made all of them bold. What I'd like to do is say, if the value of the cell, the active cells value is greater than 10 then make it bold. But if it's not greater than 10 didn't just leave it alone and go to the next cell. Check that one. So take a look. This is how you build logic using an if statement within excel procedures. Take a look. So I've got my move still open here, and I'm gonna utilize an if statement to build this logic. So I may say, if the active cell stop value is greater than 10 then I'm going to do something and I'm gonna NDF any time you open something like in, If you got it, end it. I'm ending the FM saying that's all the choices that I've given you. So now I'm gonna tell it to do something in this case, I'm going to say, Take the active cell. It's font. It's bold property and set it to true. So now if the active cells values greater than 10 then it makes it bold and just for fun. Let's try one more thing out here. Let's say if the uh active cells values greater than 10 and make it bold. But then let's also say Let's let's change the interior or the background color. Let's make it blue And I'm gonna use a built in Excel function here called RGB. And I must say it's gonna be zero red zero greed, but 255 or 100% of blue and that's it. Now I building a little bit of logic, using an if statement to determine what path my code should take. If it's greater than 10th and do this, if it's not, then let's move on to the next one. Let's check that one. So take a look. I'm gonna go hit the play button here and my visual basic editor. Watch my cells over here on the left. Why it play and I'm done. If it's greater than 10 it's formatting them form a done building logic within your visual Basic using an if statement. This is something you're gonna master throughout the remaining projects that we're gonna be going through here during this course. 15. Project #2: Intro to Interacting with the User: welcome to Project number three of this Excel, Macros and VB A course. Now before you jump in and start to get your hands dirty and build this next project, I want to demonstrate what you're gonna be mastering here as you go through these next few video lectures. Now open in front of you. I've got a completed version of the Sorting Records Excel document. I've already created the Mac Rose and I want to demonstrate it to you here. So I've got a simple little lists, division category, months and some total amounts. And what I'd like to dio is provide the user with an interface to intuitively sort this lists. And we're gonna give users a few options. They can choose to sort the list by division by category or by total sales. Take a look. So I've created the macro I cook on this sort lis button. This is gonna presents the user with what's called the input box. It gives them a message. Hey, if you pick a number, one will sort the list by division. You pick a two will do by category. You pick a three, we'll do it by total sales. Now I'm gonna answer a two. I'll hit. Okay? And I've now sorted my list by category. Let's try that again. I click on that button, give me my sort window. I'll type in the number three, which is total sales. Okay. And now I've sorted my list by total sales. So a nice, intuitive, user friendly interface that the users can go to to interact with my Mac Rose than in turn sorts the list for them. Now, you're gonna master the techniques of input boxes like that little prompt that was coming up on the screen, building logic with if statements inside your macros and you're gonna get into it's called a message box. Take a look. I'll click on that button one more time. Now, what happens if the user type something in there that we haven't specified? Like they tape in the number four they hit. Okay, well, number four is not a valid response. So now we prompt the user with a message box, letting them know Oh, hey, you did something wrong. Would you like to try again? And if I hit? Yes, we'll return them back to the sort window and they can continue along their way. So these skills here input boxes, message boxes and conditional statements are the techniques that you're gonna master during this project. Project number two. So go ahead and jump in and let's begin. 16. Project #2: Breaking Down the VBA Code: welcome to the start of Project number two within this course on Exelby B A and Malik Rose . Now, before we jump in, get her hands dirty and start to create these. Mac Rose, I want to introduce you to some of the code that's already been written for you in front of you. I've got the example. File the exercise file that comes with this lecture. Not. Do you feel free to download it so you can practice these concepts and go through and create this project on your own? But first, I'm gonna introduce you to a little bit of script a little bit of visual basic that I created for you, and it's really not much, but it's going to get us started. So I'm gonna open up the visual basic editor. Take a look. First thing I'm gonna do, I'm gonna go to my view tab, top of the screen. All my view tab will go over to Mac Rose and I'm gonna go into view, Mac Rose. Now we're gonna find that there's three default Malik Rose already provided for you in this example file. And like I said, they're not very big, but I want to break it down a little bit and show you what's happening within each of these macros. And then And then we're gonna take a look at how to make calls to these macros and build an interface for our users to interact with these various macros. So it's like one of them really doesn't matter which one I grab. I got categories. Sort their selected, so I'm gonna go ahead and hit. Edit. This will launch my visual basic editor. There it is. And you'll find if you open this up on your own, that there's three Mac Rose in here already or three procedures. We got one called Division Sorts one called Category sort and one called total sort. Now they're practically identical in their code. The only difference is each of these macros sort the list. This list here inside the background, this guy right here a little bit differently. So the 1st 1 called Division Sorts, sorts our division column source sorts the list by the division column. The category sort sorts the list by the category, and then the total sort will sort our list by the last column called Total. So once again they're all very much identical. The only difference in the references or in the Scripture is which column it is sorting on . So you can see this 1st 1 called Division Sort is gonna sort by column a four or sell a four, which is within the division column. A four. I've got the next one. They're called category sort, which will sort by before, which is the category calm and so on. So I've created these 33 little macros for you. It's nothing big. Just like I just performed a quick sort on each of the columns here. Now, in the next module, we're gonna build a an interface that allows the users to pick how they want to sort the lists by one of these three options here, and we're gonna provide them an interface to build, to go in and enter which sort they want. So let's take a look at how you're gonna accomplish this inside of our next lecture. 17. Project #2: Prompting the User for Information: welcome to the next session of Project number two, sorting this list. Now during this section, we're going to take a look at prompting the user for some input. This input that we get from the user is gonna tell our application or our macro how it should sort the list whether we want to sort it by division category or by total sales. Those three Mac Rose that we saw in the previous lecture. Now, this is how we're gonna do it. I've opened up the visual basic editor. I did this in the prior session. Now I'm going to create a new procedure or new macro, but we're gonna write this one by hand rather than recording it like we did back inside of Project one. So take a look. This is what we're gonna dio. So I'm inside the visual basic editor. I'm gonna go up to my insert menu, top of my screen, and I'm going to create a new procedure. Now, a procedure is really just a really fancy name for macro. It's just how we refer to macros within our code. We call him procedures. It's all go insert procedure. It's getting asked me a little bit information about the procedure that I want to create. So my first step is I'll give it a name. All the same rules that we talked about with macro names earlier. Apply here as well. So I'm gonna call this sort list. Remember all those rules that we had to adhere to? No spaces, 255 characters, no special characters. You can use numbers in there but can't start the procedure. Macro name with the number number. All that stuff all still applies here within procedure names exactly the same. Now, the rest of the information I'm gonna leave, set the default, we're gonna create a sub procedure, and it's gonna be public in scope. I'm gonna go ahead and hit, OK? And this is gonna create a little placeholder that I can use for my new procedure. It was just resisting a position that on the screen so we could see it a little bit better here. There we go. Now, in between the public sub line and in sub is where all of our code is gonna go now. The first segment of code that we're gonna build here is gonna be about prompting the user asking the user, How do they want to sort the list now? Any time that I get information from the user, I want to store that input. The user input inside of what's called a variable and a variable is really just a storage location in code that we could make reference to that stored information that user input. So really, my first step here is gonna be to declare or create a variable. And I'm gonna call this variable user inputs and I get to tell Excel what type of data I'm gonna store inside this variable. In our case, it's gonna be a string or texts. And now my next step is I'm gonna tell it what we're gonna store inside of this variable called input box. And what we're gonna be storing inside of this variable it's actually called user inputs is an input box or the results of an input box. And an input box is really just a prompt. That's gonna come up on the screen for our users and ask them for some information. In my case, I'm gonna prompt him for one of three values. I'm gonna say a one equals a sort by division, A two will equal sort by category, and a three will equal sort by total total sales. So we're gonna have a simple little prompt that comes up and prompts the user for a 12 or three. And that's it. I need to gather information from my users, all present them with an input box, get that information from them, and then store it inside of this variable in our case called user input, prompting them for that information, gathering its story in it and then have the ability to then retrieve that information when we need it. So let's take a look at what we got here so far. I'm gonna go ahead and pull my window just off to the side so I can see into my Excel document in the background. And I'm currently my cursor is currently inside of my procedure or macro called sort list up in the top of my screen. I've gotta play button. This is gonna allow me to run the active procedure, the one that my cursors inside of. So I hit that button. This runs my procedure, and there's my prompt. This is my input box and you can see my simple little message there one equal sort by division to go sort by category and so on. What misspelled that the user. Then type something in there like a all type of number two when they hit. Okay, that value The number two there is now stored inside of this, this variable here called user input. And now we can use that instead of our code to direct the rest of the actions that should be taken place year. So we've created a simple little variable or simple procedure with the simple variable in there getting information from our users. In the next session, we're going to take a look at building logic that we can now direct our macro or procedure to do different things based on that user input. 18. Project #2: Alerting the User of Errors: All right. Welcome to the next session. We're going to continue building this sort list procedure. We just left off building the logic, the if statement that checked for the user input based on what the user supplied and then sorting the list appropriate, Basically based on that value. So now I want to take this a step further, cause you're probably thinking to yourself right now. Well, you know what? This is great. But what if the user enters a four or five or they type A B C inside of this box? Well, what happens then? We need to build a catch. Incorrect input from our users and then give our users the proper response. Hey, you entered a four. That's not a correct sort value. You gotta pick 12 or three. Would you like to try that again? So I want to provide them with some proper output as they give us information for our application. So take a look. I'm gonna add to this if structure that we created in the prior session and I'm gonna introduce you to a message box, take a look, so I'm gonna jump back into my sort lis procedure. I'm gonna go back into my if function here, and I'm gonna add to the end of my function. We've already got if user input is equal to 12 or three. But what if they put something that doesn't exist? So here, I'm gonna introduce it else. This is like a catch. All Hey, if one of these earlier three don't work, then do this else. So now I'm gonna say OK, well, you know what? They didn't pick up 12 or three. They pick something else. So now I want to supply them a message that tells them what they did wrong. And this is where I'm going to bring in a message box. So before I do anything else that's on my if statement, I'm gonna jump back up to where I declared my user and put Variable, and I'm gonna get and declare another variable here. I must say, dim. I would call this try again. I want to prompt the user, tell me what they did wrong and then asked them if they'd like to try again. Now, with this variable, it's gonna store an integer or a numeric value, and I'll talk about why that is here in just a moment. So it's called Try again and it's gonna store manager. So now I'm gonna come back down into my l statement of my if and I must say, try again is equal to a message box. Now, a message box is very similar to an input box. Remember the input box earlier Prompting user to give us some information. Well, this is a little bit different because now it's just the message that tells them something and then gives them buttons to click on like Yes. No. OK, cancel. They get some buttons. Now we can figure out which button they click Don because the message box returns a number back to us based on the button that they chose. For example, if I give the user a yes no button if they click Yes, the message box returns a six back to me. Take a look. So I'm gonna say for my prompt inside of my message box will say and correct value. Try again. Office asking you Would you like to try again now? The type of message box that I want to return is gonna be a yes. No I just want to know. Do you want to try again? Yes or no? So for the second argument of my message box prompt here, first I get the input or the prompt itself that I'm gonna essentially asked the user question comma, No, I'm gonna given the button choices. And from here, this little menu I'll pick VB. Yes. Now visual basic? Yes. Now. And I'll getting close the rest of the arguments for the message box prompt here. And I've got this quick little variable here called Try again where I'm gonna prompt the user for some input. Now, remember, it's a yes. No pick Yasser Pick No. If they pick a yes, try again. Now holds a number six. If they were to choose No, the value that would get returned back to us in our variable called try again would be a number seven. So now that we know which button they pressed, we can perform the appropriate actions. So I'm gonna continue here. I'm gonna say, Well, if try again, I'm gonna do conditional check here. If it's equal to six, then no, in my f, then I'm gonna allow them to try again and the way that I'm gonna have them try it again is I'm just gonna recall the procedure called sort list. I'm just gonna rerun it again so they can go back through all the entire steps, put in a 12 or three and continue going. I'll just make a call. It's just sort list, and that's it. So I've got this simple little variable in there called Try again. It's an integer value. I'm making that equal to a message box, prompting the user telling what they did wrong. Hey, and correct value. Would you like to try again? They pick? Yes. Then we'll rerun the macro of the procedure once again if they choose no. Then it has nowhere to go, and it just completes. It gets down to the end. Sub in the procedure just ends. So let's take a look at what we got here. Once again, I'll move this out of the way. I'm gonna click into my sort lis procedure. I'll go up and hit the play button top of my screen. This runs my procedure, so we know if we type in the number two, I'll hit. Okay? It runs. I'm now sort of my list by category, but let's try that again. So a quick back into my sort lists hit the play button and let's say I type in the number four or I taken Fred or I type in something other than 12 or three. I hit OK, and now I get the message box. Hey, incorrect value. Would you like to try again? And I hit? Yes, and it just reruns the procedure again. Now I can read the text. I'll type in a number three and there I go. I've now sort of my list by total. So we've introduced variables in there getting input from our users. We've introduced conditional statements using if logic to detect What did the user put in and then what should I dio? And we've introduced the message box, prompting the user of potential errors within the code, telling them what went wrong and then receiving input about how they would like to continue all through this message box. Super slick. What you can do here within Excel, visual basic and these really dynamic interfaces that we can supply to our users and start to automate their experience and build logic inside of this automation as well 19. Project #2: Other Practical Uses of Messages Boxes and Input Boxes: so before you jump into the next project, Project Number three I want is pause right here and let's do a little bit of brainstorming . How else can you use input boxes and message boxes to get and inform users within your Mac Rose? Think about that for a moment. What do you do instead of excel, perhaps on a daily basis, where you have to input data into an Excel document? How could you use an input box toe? Help autumn me Some of that experience gather information from the user, ourselves or others, and then feed that into a macro, feed that into procedure and then perhaps built some logic that checks the value and the performs its appropriate tasks. Based on that value. That's an input box supply some information type in the number or some taxed, and that gets into a variable that's being stored inside the procedure. How could you use a message box? How could you or why would you want to build a prompt the user? What type of information would you like to give him? Is this more for errors? Is this just information? Hey, thanks a lot for that information or congratulations. You have sort of your list or what type of information would you like to just present to the user? And maybe by extension, give him options. Say, Hey, here's a little bit information for you. Would you like to pick? Yes or no? Or would you just like to cancel this window, given those options and then in turn, weaken Then include that inside of her macro and do something specific based on that input from the message box. Now, we've got one example here with the great great little project about sorting we've used in input box and we've used the message box in there with logic, using it in a statement. But once again, where else could use this out? Perhaps when somebody opens up initially, they open up a workbook. And maybe this workbooks got, ah 100 different worksheets in here and we could ask them as the open the workbook or hit a button. This macro runs and ask them. Hey, which work she would you like to go to? They type in the name of the worksheet hit OK, and we can have our macro director users to that worksheet. Maybe once they get to that work. She we could have a message box that pops up and says, Hey, was this the correct worksheet you're looking for? Or maybe it's a message box that says, Oh, I couldn't find that worksheet. Are you sure you spelled it right? Would you like to try again? You know, we can prop the users any time you need to gather information. Think oven input box. Any time you want to supply information back to the user, think of a message box. So see you for a moment to a little bit. Bring story, maybe whip out a piece of paper and a pencil and jot down some ideas on how you can use input boxes and message boxes inside of Your Excel experience and do me a favor. Open up the lecture environment here and jump into the discussion board and share these ideas with myself and with the rest of the students that enrolled in this course. Give us some ideas as well. What are you thinking about? Spark some ideas of our own as well 20. Project #3: Intro to Cleaning and Formatting Data: All right. So welcome to Project number three of this Excel Macro and VB A course I want to show off before you jump in and start recording and writing some visual basic. I want to show off or demonstrates the project that you're about to master and create during this section. So open in front of you. I've got a completed version of the project that I've created earlier, and here's the big picture. So a coworker has just given you this Excel workbook and it contains four worksheets. But you're gonna find out here quickly that it really doesn't matter how many worksheets air in here. But in this case, we've got four. Now, each of these worksheets they deal with the expense report records that we've been working with so far. They've got yeast west, north and south. Now, within each of these, they contain expense records. They look very similar. Some of worksheets have 14 Some have 15. Some might have more. Somebody have less, but some unknown number records, but they're all they all have. The same columns division category, January, February, march and total expense there all laid out the same way. What your co workers asked you to do is to add headers to this document, essentially clean it up, and headers, format the currency values and do it for all of them. So whether it's four records or four worksheets or 100 worksheets, you got to go through and do that. Well, we're gonna create a macro here that does it all for us in one button press. That's it. And we're done. No. So let me demonstrate it to you here. Now, I've got the foreword sheets. I've already got the macros created. There's actually three of them, one that adds the headers, one that formats the headers and one final 13 write that does the loop to get it to happen for all of the worksheets. And I'm gonna perform all this in one button press take a look. So I'm gonna go up to my view town. I'm gonna go into Mac Rose. I'm gonna go to view Macros. This is going to show you the three macros that I've created that you're gonna create else like the one called Worksheet Loop. This does the loop, and it calls to the other two macros to actually do the clean up of the work. One button press year hit Run and I'm done. Take a look. East Records Formatted Hatters Currency West. Same thing North. Same thing South, All of one button press How much time can we save and using Excel VB A macros in Excel to do the work for you. So fall along with in this project and you're going to see how to create this and master a few macro and VB a techniques. 21. Project #3: Using the Macro Recorder to Lay the Foundation: So welcome back to the next project. Within this excel macro and vb a course, this is project number three. In the last project, Project number two, we moved away from using the Excel macro Recorder tool and we got into the visual basic and started to write some of our own code. We started taking advantage of things like the input box, the message box building conditional statements using an F statement, all things that we would need to use visual basic it into the code ourselves in order to create during this session, Project number three, we're gonna jump back into the macro recorder Little bits record some of the more foundational, typical commands that you can just find this. I think so interface. And then we're gonna move back into the visual basic, and we're gonna modify it to get it to doom or for us. Now, there's key parts of any macro or any section within visual basic where perhaps using the macro recorder is gonna be a little more efficient for us than perhaps not as time consuming. There's just more rudimentary type commands and a series of commands that I want to perform that I could just really quickly record. And if I need to make edits to it in any way, or perhaps add logic to it, we can get back into the visual basic editor and start to make those adjustments through the code. So we're going to see this in practice here. The next couple exercises we're gonna build using the macro recorder, and then we're gonna quickly switch gears and we're gonna get into the visual basic editor , and we're going to start to modify the code using visual basic. So let's start this out and let's get recording again. 22. Project #3: Preparing to Use to the Macro Recorder: So Step number one within our Project three exercise here, we're gonna record ourselves using the Excel recording macro tool to add headers to this worksheet that's in front of you right now. So here's a scenario. A coworker has just handed you this workbook. It's got four worksheets inside of here records very similar records across all four worksheets, but for different divisions. As you can see, we got East, west, north and south. Now it's our job to get into here and start to clean this data up. I need to add headers to this document. I want format those headers, and I want to format the data that's inside this list, these air currency values so they should look like currency values. Now, if this was Real world and the coworker had just added this document on my plates and said , Hey, can you clean this up for me? Well, before I start creating the Mac Rose, I really don't want to create a macro based on the live data, the actual data. So what I'm gonna do to get a started here is I'm gonna critic copy of the first worksheet . I'm gonna write quick on East records. Just the tab there at the bottom. All right, click. I'm gonna go to move or copy. I want a credit copy of it, so I'm gonna make sure you turn on, create a copy, and I'll go ahead and hit. OK, so I've now got a copy of the East Records worksheet, and this is gonna be the one that I'm gonna record all of my macro code on this way. I don't disrupt the actual live data. Now I'm gonna rename it here. I'll call this, uh, recording. Just lack of a better name. There. There we go. Hit my enter. Key recording. Ah, and that's it. I just want to make sure that I have this this worksheet, that it doesn't matter if I mess up or I mess around with. I don't know how to really start to record anything on the live data, so let's keep that separate. Let's create a copy of the worksheet and then weaken base all of our recordings off of that later on. After I'm all done and I feel like the macro is rock solid. Then I'll remove that one out of this document and get back to the actual data. So before you do any recording, don't do it on really live data. Do it on something. Practice. Just some dummy data in this case. Just a quick copy of a worksheet then and based your recordings off of that. 23. Project #3: Inserting Headers: All right. So step number one of this project tree Macro. We're gonna do a little bit of recording here. I'm gonna record myself adding headers to this recording worksheet that I have open in front of me. So this is step number one. I'm gonna go up to my view tab top of my screen. I'll go into Macros, and I'm gonna record a new macro, and it's gonna be a really simple macro. We actually did this exercise in an earlier lecture here, but this is gonna set up for a much larger experience for us this time around. So I'm gonna call this one ad headers, and I'm not gonna use a shortcut key that's finally that open. I'm going to keep it inside of this workbook and I'll give it a description, places titles on the worksheet or headers on the worksheet. And I'm gonna go ahead and hit OK, And I am now recording. So we talked about this earlier. Anything that I do at this point excels busy writing down all the visual basic for us. This is the beauty of the Excel macro recorder tool. We don't have to touch any visual basic so form or routine fundamental tasks and perhaps a larger Siris of tasks. It's perhaps mawr efficient and less time consuming for us too quickly. Just record our steps. So I'm recording right now. I want to add headers to this document, so I'm gonna quickly insert a row. All this Grab that road number one. I'm gonna use a shortcut key here, control. Plus, I could right click and insert as well. But however you do it, insert a row. I'm not gonna add the headers. So I've got Let's see, the 1st 1 there is division and then I've got category And then I got the three months worth of expenses here. I'm going to stick with the first quarter. So here's January and February and March. And then I got the total expense column. Love, Spell that right. All right, I'll hit my enter key and I have now recorded myself adding those headers. Now, remember this step perhaps one of the most important steps as you recording. I need to make sure I stop recording. So I'll go back up to my view TEM back to Mac rose far, right, And I'm gonna stop recording so Now I've recorded a macro that does something really simple . Just adding headers to this document in Siro. Drop in some text. I've now got my column headers inside of this list. So that's step number one. We're gonna jump into the next lecture here, and we're gonna take a look at step number two. We're gonna do some formatting. 24. Project #3: Formatting Headers: All right, So now on to step number two. Within this project three discussion, we're gonna record ourselves doing some formatting of this little worksheet. This little lists. Now, as we record it through the excel macro recorder tool. So first let me start recording here. I'm gonna go back up to my view tab back in the Mac Rose, and I'm going to record a new macro. Now, this one, I'm just gonna call it, uh, format lists. No shortcut key Again. I wasn't gonna start instead of this workbook, and I'll give it a description, Will say, uh, formats, the headers and the currency columns. All right, so I'm gonna hit, okay. And once again, I am now recording. So I'm gonna run a series of steps here. I'm gonna format road number one. They're all the headers and I'm gonna form at the C, d. E and F columns. These air dollar amounts I want these still look like currency. So first my headers we'll go and select a one threw up one, and I'm gonna hop up to my home tab. And I'm just going to do some stuff here. Just the format, um, making more presentable getting to stand out. So I'm gonna make this bold. What I'll show you do here. I'll I'll change the background color. Let's go for Blue. That might change the font bloom black perhaps not as much contrast. I'll make a white that kind of pop out of there and I want to add a bottom border was just put a bottom thick border here. All right, there's my hatters. Nice and format it. But now I want to grab all of these dollar amounts. Now, I want you to think about this for a moment. Remember, we are now recording, so anything I do it is recording it. I just clicked in the cell. C two Excel just wrote that down that said, Hey, you just quick and see to Now here's a potential issue. This macro that I'm creating right now doing some simple formatting is perhaps gonna be applied to multiple different lists, like in our scenario. Remember East, West, north and South, four different worksheets and each worksheet probably has a different amount of records. One work. She might have five records, another work. She might have 100 records. Well, the issue is I'm recording right now. And if I tell it Oh, get from C C to F whatever that is F 20. Well, every time I record or every time I run this macro, it's always gonna do C to two F 20. That's not what I want. I needed to be dynamic. I want to pick up on whether I have five records or 100,000 records or somewhere in between . It needs to be dynamic enough to figure that out. So here's a way that we could do this using the macro recorder. So remember I cooked in the cell C two. I've essentially told Excel through the macro recorder that hey, Mike, data starts inside a cell C two. But here's the dynamic way of telling it where my data is. So I mean, he's a shortcut key on my keyboard. I'm in a press control shift and my down arrow so this will select all the way down. Tell finds an empty cell, essentially the end of my data. So I didn't tell it. Hey, go from C two to C whatever, but I just said, find the end of it, find that empty cell, and then stop now. I need to go to the right. So once again, control shift, Right arrow. And now I've grabbed that chunk of data dynamically. Control shift in your arrow keys. This looks for empty cells. As soon as it finds it, it stops. It's not looking for a specific in point, is just looking for that last empty value and then stops a dynamic range of data now. So now I'm gonna format it back to my home tab over under number all turn us into a currency value. And I formatted all my data there. Easy. I form out of the headers. I four amount of my data. I'm done recording. So back to my view tab back in the Mac Rose, and I'll stop recording. Very important step. Always make sure that you stop recording your Mac Rose. All right, so we've completed the 1st 2 steps using the macro recorder. We put the headers in there. That was one. We formatted the headers and we formatted the currency values. That was step number two. So now we're gonna move into step number three. We're gonna get away from the macro recorder. Put that away for a little while and we're gonna get into the visual basic code, and we're gonna start to write our own code to get this to not just apply to one worksheet , but to all four worksheets and several workbook or however many works you to have. So if all along with me was jumping to the next step 25. Project #3: Putting it All Together with a Little VBA Magic: All right, So here comes the fun part. Step number three of this Project three exercise. We're gonna jump into the visual basic. We're going to take advantage of the two macros that we've created so far, inserting headers, adding headers and formatting those headers and the currency values. And we're gonna create a loop so that we take those two Mac rose and get them to be applied or work with the other four worksheets or five or six. Or however many work should you have inside of your workbook. So we're gonna discuss a new concept here that we haven't gotten into loops within our visual basic. Let's take a look at how this is accomplished. So first I'm going to get into my visual basic window. Now, there's a shortcut key that we could use here on my keyboard. I'm gonna press Ault F 11 and this will launch my visual basic window. There it is. Now, I also want to point out that in the earlier lecture, we also saw that you go to your view tab. You go to Mac Rose View Mac Rose, you could find one of your macros and then edit it all, it's F 11 is just your shortcut key toe, open up the visual basic window. So now that I'm in here, I'm gonna make sure that module one on the left hand side is open its active. I'll just give it a double click. Mine is. I can see that. I have my ad headers macro in there and my format list. Macro, those are the two that we recorded in the previous two lectures of this project three. So now we're gonna get her hands dirty. We're gonna jump in and build our own procedure. Our own macro here with just visual basic. So here's our first step. I'm gonna go to my insert menu. It's often my screen, and I'm gonna create a new procedure. I'll give this one a name. We'll just call this, uh, would see worksheet loop. That sounds good. I'm gonna leave the rest defaults. We're gonna create a sub procedure, and I'm gonna leave the sculptor public, meaning that we can access it in any module or anywhere within this project, and I'll hit. Okay, So now excel builds kind of the stub the beginning of my worksheet loop procedure. Now, here's all of our code. The idea here. Big picture. I want to be able. Teoh, apply both my ad headers macro and my format list macro to every worksheet within this workbook. This is where Luke comes in the play. I don't want to have to write the same macro four or five or six or 100 different times based on however many worksheets we have. But I want to create a simple loop that will go through all of my work sheets for me. So now it doesn't matter if I have four or five or 100 different worksheets. My loop will just do it for me. And it'll apply these to Mac Rose throughout all of those worksheets. So this is a loop. This is a pretty neat concept, something you're going to use a lot of as you build mawr dynamic type macros or procedures within excel. Now there's a few different flavors of loops. The one that I'm gonna use here is called the Four Next Loop. And it's a very common type of loop. I want to build a loop, some block a code, some operations a number of times in this case, I want a loop it for each of the worksheets within my workbook. So here's what my loop looks like. I'm gonna say it's a four loop and I'm gonna use a variable here that I'm gonna create cult . I I'm just picking I out of the air here. We can really call it whatever you like, But I is gonna act as a numeric value that we're then gonna iterated through. Take a look. So you may say I for I equals well, say, as equal to zero Oops, that should be a zero to now. I wanted to start actually gonna have started out as one. I'm gonna talk about why here in this moment. So I for I equals one and it's going to start out as one. But I need to loo through however many worksheets we have. Well, how many works she says This workbook have It's got the recording one. That's the practice one and it's got the East, West, north and south worksheets. Right? So we got a total of five worksheets Now, I could do this, uh, to five, but that's a hard coded value, meaning I put that in there and if I delete one of the worksheets, it'll still try to run five times. If I add three worksheets that's still gonna run five times, I need to make this more dynamic. So I'm gonna make reference to built into XlV B A. We have an object called the Worksheets Object and the worksheets object is really a collection of all the worksheets in our workbook. Okay, So if I have five worksheets is a collection of all five of those worksheets, if I've got 10 or two it knows how many worksheets are in this workbook. And it knows this because the worksheets, object or collection has a property called Count. So now knows. Hey, worksheets. You got five months. You got 10 of them. You got two of them. It knows how money works, shoots air in here. So I'm gonna tell it toe loop from one to the worksheets count. And however many that IHS and I'm gonna finish off the loop, I'll say next. I Okay, so now every time I loop runs, it starts out and says eyes equal to one. It's gonna go to five or whatever it does, It's stuff, and then it's going to go to the next. I would then be two. It would repeat the process. 345 Do your stuff and done. This is a four next loop. So now in between the four line and the next line, I'm gonna tell my macro what it should be doing. Now. What I'd like to do here is I need to let's see what you think about this for a moment. And I want you to use in your own noggin here as well. Big picture. We needed to go from one worksheet to the next and complete the job. So my first step here is I need to get to the appropriate worksheets. So I'm gonna say Ellipse would say worksheets, and I'm gonna tell it to go to a specific worksheet. And the worksheet that I wanted to go to, at least initially on its first Lubitz for cycle is to the first worksheet. Now, what's equal? The one right now? Hey, I I told I was equal to one, so I'm gonna tell it to go to work. She number I or eyes really equal the one and I'm gonna tell to select it Now, remember the next time I loop runs, what's I equal to? It starts out as one, but then it does it stay in the loop, stir and then eyes equal to two. It does it stuff. And then I z equal to three and so on. It'll do its job, so I'll tell. Hey, go to the first worksheet. Now what? I want to do the first worksheet. Well, I wanted to add the headers and then format it. Well, we've already got that. I recorded to Mac Rose earlier. So now I could just make calls to those macros. The 1st 1 was called ad headers. Huh? What was the next one called? Let's take a look. I'm gonna scroll back up here, and it was called format lists. So I'm gonna come back down here and say OK, well, now you gotta do format lists. Great. So goes the first worksheet. Does this stuff now what we need to do? Well, now it needs to go to the next worksheet. Well, that's the loop. That's where the four next comes into play. So I says OK, did this the first worksheet? Now I'm gonna go back up, check to see if I is still within that range from one to the worksheets count. And if it is with Zeke, a minute to two and let's move on to the next one and do it all over again. So let's test this out to see if this works for us. I'm gonna go ahead and minimize my visual basic window. I'll get down just just to my worksheet here, my workbook and I'm gonna run the macro. I'll go back to view Mac Rose, and I'm gonna view my macros. This will take me And where I now have three Mac Rose, including my worksheet loop Else like that and I'm gonna run it. Cost of my fingers here. This should loop through all five worksheets and for Madame, make him look good. I mean, hit, Run and look at that. I'm now on this out. That was quick. I'm now in the South Records worksheet. Look, that's got headers. It's got the currency values. North is the same. It looks pretty. It's cleaned up. There's west and there's East. I have now created a macro that a loop through all of my worksheets and workbook and applied my two previous macros, adding the headers and formatting the data simple is that and I did it through a loop. Let's take a look at the loop one more time. Here we'll go back to view and macros and view macros, and I'm going Teoh. Hop into my worksheet loop and I'll edit it. Take me back to my code here. Let's bring that back up. Came upon my other screen here. There it is, a simple loop, a four loop looping from one to the worksheet count and the performing thes three operations, and we're done. 26. Project #4: Automate Microsoft Excel Formulas: welcome to Project number four of this XlV B A and macro course. Now, before we jump in and start building our procedures building RBB A, I want to energy you see to the project that you're about to create a master. This is gonna be a simple one. We're actually gonna borrow some of the concepts that we discussed in the previous lectures , but we're gonna build it into a new feature here. Now, I've already created this procedure. Are these two procedures that you're gonna be creating and I want to demonstrate it here to you before you jump in to start to build it on your own, So I'll take a look. I've got the completed version of the auto some functions worksheet that has been provided for you within this lecture. So I created the procedure. Take a look. I'm gonna run it. So you see what it looks like? I'm gonna go to my view tab inside of Excel. I'll go to Macros underneath. Mac Rose, I'll go to view Mac Rose. Now, this opens on my Mac Rose windows, something we're familiar with from the previous exercises. I've got two procedures in here and these were the two that you're gonna build now, The end product. Big picture here is this Loop some. We're going to create a procedure that will loop through all four or however many worksheets you have in your workbook and it will automate summing up the total Expense column One button Press some of the total Expense column for all your work sheets in your workbook. Take a look. One button press. I got loop, some selected, I'm gonna hit, Run and I'm done I'm now on South Records. You can see my nice little some right there. Here's North some There's West's There's eats, its one button pressed. Just a little bit of visual basic. You're gonna find that it's done a lot, but some key concepts that you'll master. We're gonna talk about finding the appropriate data within the worksheet. What range of cells do you want to sum up? We're gonna talk about, then looping that giving this toe happen across all the worksheets in your workbook, becoming much more efficient, much more streamlined in our work within excel. So get ready to jump into completing these two procedures within project for 27. Project #4: Automate the SUM Function through Code: All right. Welcome to the first part of Project number four. We're gonna take this into two stages. The 1st 1 we're gonna take a look at writing our own visual basic code within the visual basic editor on how we can automate a function. In this case, we're gonna automate thesis, um, function in the second half. We're going to take a look at how we can then take that automation of the some function and loop it, get it to happen across all four of our worksheets inside of this example file. And by the way, I have the example file open in front of you. Right now. This is the one that comes with this lecture. So do feel free to download this fly on, fall along with me or get into the practice exercise lecture. And you can practice it there as well. This file's called auto some functions that XLs acts. So first step, we're gonna jump into the visual basic editor. We're gonna write her own procedure here to automate the some function. So this is how you do it? First off, I'm gonna use my shortcut key to open up the visual basic editor. So on my keyboard, I'm gonna press Alz half 11 this open up. My visual basic editor. There it is. And because I didn't record anything using the macro recorder, I gotta create a new module here, so go to insert and module and no code in there, so I'm gonna create my procedure. So I'll go back up to insert into procedure, and I'll call this auto some. I'm gonna leave the rest. Defaults will be a sub procedure. We're gonna be public in scope. I'll hit, okay? And I've got the beginnings of my auto, some procedure that we're gonna create here. Now, let's think about this for a moment before we get any coding happening in here. I want you to think about how would you do this manually? How would you sum up a column of cells if you were to do it just inside the Excel interface manually? So let's think about that. Let me get rid of the visual basic editor for just a moment. And I'm sitting here on the East Records worksheet. Now I want to get a some of the total expense column. Now again, How would you do this? manually. What would be the steps? What's the data that I actually want to sum up here? Well, I know that I want the Total Expense Column. So I want from F two right from that cell right there to the last cell, whatever that cell is, that's 20 or 15 or 25 or whatever it ISS. But I want that range of cells and I want to sum it up right here. Now we need to translate that sum up that range of cells into visual basic. Now there's a given here, all four these worksheets which eventually I want toe auto someone them all The total expense column always starts in F two. There it is. Enough to there. It is enough to now the moving target. The part that's gonna change is gonna be the last cell. Is it F 15? Is it f 20? Is it f 16? What is it? Well, we need to write the visual basic that will one grab the F to sell, but then two will find the bottom of that column so that we know our range that we want to sum up. So let's jump back into the visual basic window once again, and that's what we're gonna right here. So the first part, I'm gonna create a variable, and I'm gonna call this last cell. And this variable, it's gonna hold the string value and this string or this text values gonna make reference to the last cells address, whether it's F 15 or F 20 or whenever that last cell is in the total Expense column. Now come back to that in just a moment. Now I'm gonna tell my procedure here. When somebody runs this, it's gonna jump right to the F two column, will write to the F to sell. So I'm gonna say, get my range objects and I'm gonna tell it to go to the F two and I'm gonna tell it to select the F to sell. Now, remember, all four worksheets that total expense column begins enough to That's a given. A constant, always that way. But now I want to find the last cell. What is it? Half 12 at 15. Again. What's that last cell within that column. So now if I were to do this back inside the Excel interface, I could do control, and then my down Arrow and that would jump me down to the last cell and said That column Essentially tell them that empty spot. While that control down arrow command shortcake e that you can use in the Excel interface, I can program it here, take a look. I can say selection dot end and I'm gonna tell to go excel down and I'm gonna tell it to select it. So this is saying hey, from f two, which is the selection go all the way down and select. That's like control down arrow. So now I'm sitting on whatever the last cell is half 12 15 of 20 whatever it is And I'm going to say, well, that variable that I created Let's go ahead and make it equal to the active cells address and that's it. So now there's this variable called I Sell now holds the address of that last cell in the column. So now I know the beginning F two and I know the last cells addresses well f 15 of 12. Whatever. It happens to be selection in Exel down. And we can always tell it to go in any direction we tell. Go up and left and right. Doesn't matter. We're just telling to go down because I'm gonna find the last cell in that column. Now I want to build the formula, but where do I want this formula to show up at? Well, I wanted to show right below the last cell, So I'm gonna write another command here. We're going to say so Say a active cell and I'm gonna offset. I'm gonna offset one row zero columns and I'm gonna tell it to select it. So now we're just moving down One cell from wherever that excel down took us to Simon's. I have an empty cell now and now I'm gonna take the active cell and changes value. I'm gonna make it equal to this formula. And really all this formula it is. It's a some function, but taking those two bits of information the F two and the last sale, and can Canton ating them, combining them all I'm going to do here is create this fancy little can Canton ated string to build this some function. So it's gonna look a lot like you would write a standard. Some function. Now I've got the beginnings of my automated some function here equal some F to colon. Those were given months again. I'm creating some function. I want to start in cell F two, but I need the other half of the range of cells. Where's that at? That's in the last cell that's in the last cell variable. So I'm gonna can Canton eight and I'm gonna use Thea plus symbol here, a string operator to build a combine lips that since Tribbey last cell to build a combine that previous portion equal some F to colon plus whatever last cells equal to which is the address of the last cell at 15 f 20 or whatever. And then I want to combine that with the closing parentheses and that's it. So we found the first cell we found the last cell excel down your your there. We grab the address from that cell, Then we offset one cell down. And all I'm doing is changing the value of that cell, putting something in it. And here I've just gotta can Canton industry ing building this formula, combining these different pieces of information into my formula. So let's try this out here. I'm gonna go ahead and minimize my visual basic editor for moments. Let's get back at him and I click away from F two F two just so I'm someplace else and I'm gonna go run my procedure So I'll go to view to Mac Rose View Mac Rose. I'll grab my auto some procedure there and I'm gonna run it and thats gonna be quick, but I'm gonna jump right up to have to jump down to f whatever and drop in the formula. And that's it. There's my 266,285 I've now automated, the some there simple, just a couple little lines instead of our code to create something a little more dynamic. Doesn't matter how many records you have because we're used that Excel Down command to find that last cell in this block of data. Now prepare for the next section here. We're going to see how we can automate what you just did here to go across all four of our worksheets, so jump it's in the next lecture, and let's continue this procedure here 28. Project #4: Loop the SUM Function Over Multiple Worksheets: All right. So we've already got the beginning of Project number four. We've created a procedure that will automate summing up the total expense column, but that that procedure is limited, Meaning it can Onley run once Each time you run it. And it on Lee sums up one worksheets. Now, I want to take that procedure a step further, and I wanna automate it. So that goes through all of our worksheets here. East, west, north and south. Something all up in one button press truly automating our experience here. So we're gonna borrow a concept that we discussed in an earlier project. We're gonna get back into loops. So this is how we're gonna lou the auto some function across all of our worksheets. Take a look. So my first step, I'm gonna open up my visual basic editor. I'm gonna hit the short cut key. Alz f 11. There's my visual basic editor. So now you can see I've still got the previous auto some procedure that we created Now I could build the loop within the auto. Some just use a for loop right there. Tell toe loop this stuff right here. But by doing that, by incorporating the loop within the auto. Some procedure the auto, some procedure will always loop. And what I'd like to dio is potentially reused the auto some procedure that we created for other scenarios, perhaps scenarios where it loops differently or doesn't loop it all. So rather than building the looping logic within that procedure, we're gonna build another procedure. We're gonna build a loop in there that will then make a call to the auto some procedure. By doing this, we become more efficient. We can create reusable code in this case, although some reuse it for different scenarios. Let's take a look. I'm gonna build another procedure here. I'll go to insert procedure. I'm gonna call this loop some, and I'll leave the rest set. The default is gonna be a sub procedure, and it's gonna be public and sculpt. I'll hit, okay. And I've got the beginnings of my loop, some procedure. And like I said, this is gonna be a simple one. We're gonna use a loop much like we did in the earlier project, and I'm gonna use afford next loop here again. So my first up, I'm gonna create a variable here called I This is gonna be up interest your type and I'm building that I variable that I can use within my loop This is gonna be the variable that iterated from 1 to 2 to three And so on Each time I blew goes through its it's looping cycle So here's the structure of my loop I will say for I equals 12 Well, so my lips gonna start at one first time it loops, I will be equal the wine But I need this thing blew a number of times and in my case, I wanted to look through all my work shoots. Well, I know I've got four. But what if I added another 1/5 1 or 61 or 100th 1? How do we know how Maney worksheets are in our workbook? Remember that we get to use the worksheets, objects and now worksheets. Object is really just a collection of all the sheets within our workbook. So I'm gonna use the kelp property. We're just going to say, Hey, I want to go from one to however many worksheets there are in this workbook. That's how many times I want a loop now. Simple loop there. All I'm gonna put inside of the loop the operations that I wanted to perform as it loops is too little things. First I want to select the appropriate worksheets, so I'm gonna have it select the I worksheet Every time it loops member, I will be equal the one So first time it loops, we're gonna slight the first worksheet. The second time it loops Will select number two eyes number equal the number two and so on . So I mean, I got got to select the appropriate work she first. Now imagine this year on worksheet one. Now you're loop just ran. What's next? What you want, Dio? Well, I want to sum up the Total Expense column. Well, we've already done that. I've got the auto some procedure that I created earlier, and that's going to sum up the total expense calling for me And that's it. A simple loop, taking our previous procedure, selecting the appropriate worksheet, then taking our previous procedure and looping it. You need it to happen throughout all of our worksheets. Really simple. Take a look. So I'm gonna minimize that now I want to run it. I'm currently on the East Records worksheet. I'll go ahead and go to view to macros View macros. I'm in a select loop some and I'm gonna run it now. Now this is give me quick. I mean, I hit the button, I'm gonna be done. And there it is. I'm now on South records. You see, I have my 480,000 north is summed West is summed. There's West and there's East. We've taken the auto some procedure and we've looped it within another procedure. One button press and we gotta work Done. How quick is that? How much more efficient area? Because of using macros and vb A within excel. Make sure you try out the exercise. Try this out on your own. Nail down the auto. Some nail down the loop, hit your button, run your procedure and you're done. 29. Project #5: Creating the Final Report: welcome to Project number five. Now, before we jump in there and once again get her hands dirty and start to build this final reports use it legalizing previous procedures that we've created and previous concepts within visual Basic. I want to demonstrate what you're about to create here. This will be super quick. Now open in front of you. I've got a completed version of the quarterly report document that comes with this exercise comes with this project. And this is the document that you're gonna be working with as you engage and participate in completing this project. Now I've got the macro is already created for you, but before I run it here, I wanna talk about what this macro is gonna do for you. Now open inside of this. This Excel document I've got the East, West, North and South records these expense reports. Each of them contain the same blocks of data. You're familiar with this if you participate in the previous projects, but I've got a new worksheet. There's north or South. I've got the yearly report which is just empty. Nothing in it. Now what? This macro or procedure is going to do that you're about to create is it's gonna go through each of these worksheets east, west, north, south. It's gonna clean him up. Auto summer, add headers, format the data. It's thinking to copy the content, that little list there go to yearly report and paste it in. And it's gonna create one master sheet year with all of the data from the previous four worksheets. So take a look. This is gonna be super quick. 11 procedure, one button press here, and I'm gonna complete that, all saving you time throughout your day. Things that you would normally do manually got one button press. It's all gonna happen for you. Take a look. I'm gonna go up to view Mac Rose View, Mac Rose, You're gonna find all the procedures in here You're gonna be creating the one called Final Report. With that one selected, I'll hit Run and don't blink. This is gonna be super quick. I've now created my final report. Do a little bit of clean up here. This is also something we could potentially automate. But I've now got the yearly report that's got all the data in their copy and pasted. And the South nice and cleaned up. Got headers, got the auto. Some got the currency values north the same way There's west and there's east one button press all four worksheets, air cleaned up presentable. And I got the yearly report, the summarized worksheet with all the data in one spot. So participate in this project here, and you're going to create this procedure to save you time and automate all of these steps in the one button press. 30. Project #5: Putting it all Togehter to Create the Final Report: welcome to Project number five. So we've talked about several macro and VB a concepts in the previous exercises now and projects. Now we're gonna create one master procedure year within the visual basic editor That creates a final report utilizing all the rest of the techniques that we've discussed earlier. One button press. And we're going to get several things perhaps several hours of your day trimmed off into one button press. So take a look. This is how we're gonna create the final report within our visual basic within Excel Self. First off, I've got the example file open in front of you. This is called the quarterly report XLs X document inside of this document, I've opened up the visual basic editor and in there I've got a few procedures that you're already familiar with from previous exercises. I've got the auto, some procedure. I've got ad headers and I've got format data, three procedures that we've created earlier. Two of them, perhaps through the macro recorder and one of them I'm just a regular procedure that we created in the visual basic editor. Now I've got 1/4 1 that's specific to this project, and it's called Final Report, and it's got some some of the visual basic already created for you. But concept that your should be familiar with by this point, we're gonna be talking about a loop once again inside of this procedure called Final Report . I've got a variable called Eyes of Interest, Your type. And I've got a simple Lupin there. We're gonna loop this procedure that were about to create here That makes calls to our other procedures throughout our workbook each of our worksheets. Now, here's something new that we haven't discussed. How many worksheets do we have in this workbook? Well, we know we got north, east, south and west, but we now we've got one called the Yearly report. And the yearly report is where we're gonna create the one master sheet that's copy data from the other four worksheets. So, in reality, I don't want to run the loop on that fifth worksheet. I just want to clean up and copy the data from the previous four. So, in order to accommodate, to not loop through that 5th 1 I'm just subtracting one from the worksheet count. So work she count would give you five, But I only want a loop four times for the four original worksheets. So I'll just subtract one some simple math inside. They're nothing more than that. But that's the only thing knew that I'm throwing in here with this loop. So now let's take a look at the specific code that we're gonna put in here to create this final report. All right, so my first step, I want to make sure that I'm on the appropriate worksheets, so I'm gonna go grab that sheets. So no use I again our loops each time it runs for Senate runs Isaac with one. So it selects the first worksheet runs against I is equal to two grabs the next worksheet and so on. So now that I'm on that sheet, I want to do three things. I want toe sum it up. Total expense. Somewhat the total expense. Call him. I want to add my headers, and I want to format the data. Well, I could put all that code right here and said, Luke, but that's what these other procedures air for. Auto. Some ad headers, format data. So I'm just gonna do right here. I'll say my 1st 1 I'm gonna auto some. I'm going, Teoh, add the headers and I'm gonna format the data and I'm done. That's that's it. I don't have to write any of that code again because we've created separate procedures that will do that stuff for us. And that's just borrowing from what we created earlier in the previous projects. So now here's something new. Once I have summed up the total expenses I've added the headers I format of the data. I now want to take the data from whatever she time on copy it moved to the yearly report worksheet and pasted it. Now think about this for a moment. I'm on sheet number one. I've summed it up. I've added the headers. I formatted the data. Now I've grabbed the data. I'm gonna copy it. I'm gonna go to the your yearly report I pasted in. Now the loop takes me to sheet. Number two does the same thing. Auto. Some add the headers format. The data copy moved to yearly report. Oh, where do I paced the data and I want to make sure that when I paste the data and then I don't wipe something out that's already there. I want to jump down some cells and make sure I'm in an empty spot. Now, there's several techniques that you can use here, but here's one that I like to use. So first I'm gonna make sure I copy the data. And in order for me to be able to copy the data, I need to make sure that I'm in some place within that data. So I'm just gonna jump up Teoh A to I know that I have data there. I'm going to select it and I'm going to say, Let's take the selection object and I'm going to get into the current region and I'm gonna select it. So this will essentially take us from a two to the end of the data. It will now highlight all the data within that block, and then I want to take the selection and I want to copy it. So now I've got that block of data copied onto my clipboard. So now I'm gonna jump to the next worksheet. So in this case, I'm gonna go to a very specific work. She called the yearly report worksheets, and I'm going to select it all right now, once I'm on that works. You don't wanna pace to date in. But remember, I want to make sure that this is looping. This is gonna happen a number of times and I don't want to paste over the top of something that is already there. So here's the technique that I like to use. What I'm gonna dio is jumped someplace clear down towards the bottom of the worksheets. And I'm going to do this by saying, you know, and I'm gonna use the range object. And I mean to say, you know, let's go to, like, a 12 30,003 and I'll select it. So now I know I'm someplace way down towards the bottom where there's no data. I know that. No data there. Now, what I'm gonna dio is now that I'm clear down towards the bottom, I'm now gonna jump back towards the top and tell Excel finds either one of two things it finds the top of the worksheet or it finds data data that perhaps I pasted there earlier. So now I'm going to say, Let's go selection end, and I'm gonna go excel up and select so you might recall this from a previous project will reset, Excel down to build to find the end of the data. Well, now it's gonna take me clear. Back up until it finds data. Once it's found data, I'm thinking offset a couple of cells. This will give me a nice clean spot to build a paste. My data. And so I jumped to the yearly report work. She I found some empty spot, went back to the top. Excel said I found some data would stop right there. And then I'm gonna offset two rows down and I'm in a paste. And with the pastes, I'm gonna take the active sheet, object and paste. So a few little commands here? Nothing huge. It's a loop. The loop is gonna run each of our procedures, auto, some at headers format the data. But once it's done doing that, we're then gonna grab that data. The current region, we're going to select it. We're gonna copy it. We're gonna go to the other worksheet, make sure that we found an empty spot on there by kind of navigating around looking for that. But once we did well, then paste it in and that's it. simple, simple. We're taking something that that's gonna perhaps taken our plus of your day, and we're automating it right here through single button press. Clean up that data format it. Add the out of some copy and paste it Create this report. All right, here through a little bit of visual basic and some really key concepts. Let's try it out. This is gonna be super quick, so I'm gonna minimize my visual. Basic editor. I'm on the East Records worksheet. That's great. I'm going to go up to my view tab, I'll go into macros view, Mac Rose and see. I want to run the final report, Macro. So what? That one selected all hit run, and once again, this give me really quick and I'm done. I've now got my yearly report there, and you see that I've got all my data. Perhaps maybe a little bit of clean up here that I can do just to increase the column with , sir, There we go. But all on one worksheets. While the previous worksheets are all nice and formatted headers, dollar signs, the total expense summed up there, all done in one button press and I've got my yearly report. All the data in one spot get saving you a ton of time on a meeting. These simple, fundamental steps that you do inside of excel. Make sure you trap the next lecture and tried this this project out. Try this exercise. Go through the steps to build automate creating this final report. 31. Project #6: Working with Excel VBA Forms: welcome to bonus Project number six. Now, just like all the other projects that we've gotten into up to this point throughout this series, I want to introduce you to the project before we jump in, get her hands dirty and start to master these concepts that were going to introduce to you . Now open in front of you. I've got Microsoft Excel, but you'll notice that there's no document open inside of this copy of Excel. Now all of the Mac rose or procedures that we've couldn't up to this point, we've had to create a button to build to run them. We've had to go to view and Mac Rose and run the macros from there. What I like to do here is present the users with the more intuitive interface a nice form that can open up a zsu nas. They open up the document where they can interact with our buttons. So during this session, and during this project, you're going to master the beginnings of working with an excel VB a form. So take a look. Like I said, I've got a blank copy of Excel Open in front of me and I'm going to go up to file, and I'm gonna open up in existing file that I have here. It's called quarterly report form Dash 01 It's XLs M. This is a completed project here, so it's already got the macros. It's already got the procedures and code and within it. So as soon as I opened up this Excel document, not only do I get the Excel document in all of its work sheets that she looked familiar from the previous project, but I also get this nice quarterly report form, and it's got some controls for me to use. I've got a little drop down here that lists out all the worksheets. I've got a button to build. Add additional worksheets and I've got a button there that will create the report that will essentially run the last project that you created in the prior Project Project number five . So take a look. Here's what some of the stuff that this thing does give the button to click. Now I get to pick and worksheets so I can navigate to different sheets. Now I'm on the North Records. Let's change that. I'm gonna go to East and here's the year the report, which is currently empty. Let's go back to north so I can navigate around within larger workbooks just through a simple little drop down here on this nice little XlV Be a form I can add worksheets. I'll give that a click. This brings up in input box. We talked about input box in an earlier project, said I get to enter the name of the new worksheets will have year up records and I'll here okay. And I've now got a new worksheet right there at the bottom. It takes me to that worksheet and renamed it for me. And I can also had to create report button which once again this would create the yearly report, much like project number five all right here through the simple little form. So we're gonna talk about concepts of building the form, adding controls to the form adding co two, the form changing properties to the controls on the form, really mastering the fundamental concepts of working with excel VB a forms. So I hope you're ready for this. Let's take a look at the next lecture 32. Project #6: Creating an Excel VBA Form: So the first part of working with an XLV be a form is we need to create the form. So this is gonna be a quick one. We're gonna jump into the visual basic for applications window and we're gonna add a new form. This is much like adding a new module that we discussed in an earlier version. The place where you put your code. Essentially, let's take a look. This is how you build a new XlV. Be a form within the V B a window. So I need to get into the visual basic window. First, I'm gonna use a short cut key Alz f 11. We could also go right up to our view Tab Goto, Mac Rose, go to view the macros, but I'll use all deaf 11 year now, This is essentially the exercise file that came from Project five. I've just repurposed it here, So we've already got a module and this module contains all the code from the project number five on that big loop to build, create the final report and so on. Now I want to introduce a new form here. Well, how did we insert a module earlier? Remember that. Well, I'm gonna go to my insert menu, top of the screen, and I'm gonna go to user form, So this does a lot of the work for us. I've already got a foreign there. It's called user form one. Nothing on a yes, pretty simple form. But if I were to go hit the play button here at the top, my screen, the start button, the run button or F five on my keyboard on this will run the form. But like I said, it's a simple little form. Not much to it, but it's super simple. The credit form insert user form. You've got an Excel BB a form. Now I'm gonna close that out. Now we've got to start adding controls to the form, so if you take a quick glance around within you R V B a window. Once you've inserted a user form, you should also have a tool box that's floating around on your screen somewhere. This toolbox contains all of your controls. Drop down menus, text boxes, check boxes button on and so on. All the controls that you can add to your XlV be a user forms. Now I want to point out that toolbox is not there. Close it. It just didn't show up for some reason. Well, up on your toolbar, top your screen about 3/4 the way to the right. You got what looks like a little hammer and wrench. And if I give that a click, this will open up the tool box with all the controls. So try this out. Open up the exercise file that comes with this lecture. It's called the Quarterly Report. Form dash zero wine and insert a user form within the V B a window and confirm that you got your toolbox. Because the next video we're gonna take a look at adding controls to the form. 33. Project #6: Adding Controls to the Form: So now that I've got my form out here, remember, just insert user form. I've got my toolbox. With all my controls on it, I'm now going to start to the design. This form. Now, we're gonna start out first by just throwing controls on here. We're gonna have a drop down menu that's eventually going to be populated by all of our sheets. We're gonna have a couple of buttons out there, one for creating the reports and one for adding a new worksheets. So it's really gonna be just three controls to take a look. My first control is I'm gonna utilize the Kabul box Control or the drop down menu. This is where the user's gonna be able to pick from the sheets that they want to build to navigate to. So I'll give that a click. Just the left click. Come out to my form here and you can see I've got a little plus sign with the icon of the couple box that I'm gonna left click and drag just to create this control on my form that I'll place it up some someplace towards the top. That looks good. Now I've got two more controls than I want to try here to button controls. So back into my tool box. Let's see, third row down second, call them in. I've got my command button control. I'll give that a click and I want to create two of these. So I'm gonna left, click and drag created but not here. And I'm gonna go get that same little command button once again and I'll create another button here roughly the same size just on the other side there. And I've got two little buttons and I can resize those and I can move them around just by dragging him. But now got three controls, a simple combo box A and two buttons. Now you can see that the buttons, They've got some generic names inside there. We're gonna change that. We're going to talk about properties in the next video, But let's see what we got So far, I'm gonna cook at my form. Doesn't matter where just picked up the top there, and I'm gonna run this again. So remember the little play button or F five is your shortcut key. I'll hit a five. So there's my form and it's starting to come together. I've now got a little drop down. Although it's empty, doesn't do anything for me yet. And I got a couple of buttons and I can click on those buttons and it really doesn't matter how many times I click on it's not gonna do anything yet. We haven't created any the code for these buttons at this moment. So once again, I'm gonna go ahead and close this, Take me back into my visual basic window and then the next lecture, we're gonna take a look at changing properties, really starting to prepare this form before we add any code to it. So try this out. First, make sure you got your form in there and then add your three controls. Grab the combo box control from the toolbox and two buttons, and you'll be set for the next lecture of starting to manipulate these properties of these controls. 34. Project #6: Changing Control and Form Properties: All right, So we've got our form and we've got our three controls out there combo box and two buttons . Now, before we jump into start to do the code, we need to start setting the properties of these controls. And specifically, there's really two different properties that I want to control here, the name and the caption. The name is what you're gonna use within your code to reference the controls or the form itself. The caption is what the user's gonna see, what they'll actually see, displayed on the buttons or on the form itself. So let's talk about some properties here. So first, let's tackle the form itself by default. When you created the form, you credit a user form, so it got a caption of user form one. We could see it there at the top of the form. Now I want to change that caption, and I want change the name of the form itself. So I'm gonna cook on the form just to make sure that it's selected, and I'm gonna go down into my properties window here. We'll just move that up so we could see more of it, and I want to point out Hey, if the properties windows missing, you're looking at your screen like, what are you talking about? There's no properties, form or no properties window. You're gonna go to your view menu top of your screen, and you're gonna turn on the properties window and notice right below that you've also got the toolbox there. I'll click on Properties Window to get that back. Now, once again, with the form selected, I'm gonna go change the name so I don't want to be called user form one, but I want to call this FRM. Let's see, we caught quarter form. Well, I'm gonna hit my enter teeth, and I'm now renamed that form, but you'll notice that the name appear at the top didn't change. The name property once again is for you to use in your code. The users don't see this. It's for your code. Now the users, they see what's called the caption. So I'm gonna find the caption property and I'm gonna rename it here. So I'll call this quarter form hit my enter key and I've now relabeled or re captioned the form. That's what the users will see now. I want to do the same thing with each My controls here, I'm gonna grab one of my buttons. Will grab this command button one. This is the button that the user is gonna click on to add an additional worksheet to the workbook. So I'm gonna rename it. Remember, the name is what we using code. I'll call this a C M. D. It's a command button and I call it Add worksheets. My energy. Now I want to re caption it, so I'll find the caption property. Then I'll change that to add worksheets. There we have it. And I want to do the same thing to the other button. This is the one is gonna create the reports, so I'll give that a click. Command McCall, this cmd create reports. All right, that's the name. That's what I'll use in code. And then the caption will just say, create reports. Now I do want to point out, as I'm naming these, I am pre fixing them just with the three letter abbreviation that matches the type of control that I'm working with. Remember this way back when this was like, unless the number one we talked about macro names, what can you not place inside of a macro name? There was a list of things, but I'm doing one of them here as well. No, spaces Don't put any spaces in anything that you name that you're gonna end up using inside of your code. So any of the properties name properties of these controls of the form itself crammed that all together Don't put any spaces in there now, the drop down menu or combo box year. If I select that this one doesn't have a caption for me to work with, users are going to see anything other than the values that are inside of that. So there's no caption for this, But there is a name and already name this one, CBO uh, which sheets? This is going to contain a drop down of all of these sheets within the workbook. And I just hit my enter key there to make sure that it accepted that. So now I've changed some of the properties. There's a number of properties you can work within their There's also colors that you can use If you really want to get fancy with your forms, you could change colors. You've got all sorts of different controls you can work with. But we've got the beginnings of our form. We've changed the name and the caption of the form itself. Remember, the name is for our code, the captions for the users. Then we did the same thing with their buttons and with our combo box as well. So make sure you re name these a few name of something, other difference than what I did. Then just take note of it. Because when we get into the code in a moment, we're going to start to use these names and identify the controls and the form by its name . So set this up and then we'll jump in and start to actually create the code to drive this form. 35. Project #6: Adding VBA Code to the Intialize Event: All right, so we've got our form set up. We've got the controls out there. We've changed the properties of those controls, identifying them by name and giving the users something to look at, such as caption. Now we're gonna get into the magic. We're gonna jump into the code within this form to start controlling the button presses and the drop down menus and so on. This is called Code Behind. Now, the reason why it's called code behind is toe access the code of your form or of a control . You're gonna double click on it. You're going to go into the code behind this control. So the first Vatican code that I want to put in the year deals with the drop down menu. Now, I'm not gonna actually put the code directly onto the drop them in your combo box, But I'm gonna put it onto the form and I'll talk about why here in just a moment. So I want to put some code on the form called quarter form or its FRM quarter form. So if I give the form of double click, this will open up essentially a module it's code behind for the form, and it's given me an event of the form. It's called my user form. Underscore. Click. Well, you know what? I don't really want to trigger something when somebody clicks on the form, but I want to fill up that little combo box, the drop down menu with all the sheet names. But I want to do this when the form opens. So with the form, there's an event called Initialize. Essentially, when the form opens its initialized, And when that happens, I then went to populate the drop down menu, the combo box with all the sheet names that live inside of our workbook. So I'm gonna get rid of this one called Click. I don't need that. I was gonna highlight and delete it, and I'm gonna change this. But CME to dio my user form, change it from general here to my specific object called user form. And now I'm gonna get into its events here. We're going to change it from the clique events to the initialize events. All right? And I guess I deleted out one step too early. I'm gonna get rid of that quick event. I don't need that. So now I've got a private sub procedure called user form underscore initialized where I can put the code for this specific form. So now this is gonna be simple little code, but I want to populate that combo box with all the various sheet names. So we're going to get into a concept here that we discussed in a couple of the earlier projects. Now I could Let's see, I've got 1234 I got five worksheets Northeast, Southwest and the yearly report worksheet. Ah, and I could just add them all individually, but that would be five individual lines of code. Kind of redundant. So how can I get something to repeat one line of code toe happen over and over and over again? How do you do that in V B? A. Well, we create a loop, so I'm gonna create a loop here, let me start out by declaring a variable in this will act. Is that iterating value that we're gonna loop through? It's gonna be I and it's going to be an insecure and I'm gonna create my loop that I'm gonna use a for loop. So say four I equals 12 and this loop is essentially going to populate. Drop down with each of the five sheets, their names so users can pick. Um, and it would take them to that sheet. So how many times do we need to loop here? Well, I need to loop five, right, cause I got five worksheets. But how do we make this more dynamic? You know what? If I add 1/6 sheet or 10th sheet or whatever I need to build accommodate that. I don't want a hard code. This right Number five in here Because then if that changes, I have to go in here and manually change it. So how did we do that? Remember that it was using the worksheets objects, and we got into a property of the worksheets object over this when you utilize counts. And I finalized, my loop here will say next. I So now that I've got my loop, we're gonna blue 12 However many worksheets we have in here 5678 Whatever. Now I want to build a populate the drop them and you're the combo box with each of the sheets. So every time it loops, we're gonna get the next sheets data. So I'm gonna jump Teoh A specific sheet will say, uh, worksheets I first time our loop runs. I will go to the first worksheets or whatever she were on. And I want Teoh get into that control. I'm gonna use a little bit of shorthand here. I'm going to use a keyword called me. And what me references is the form the user form. I could say FRM quarter form here, that'd be fine, but I'm just gonna reference me and this is short hand. It's just referencing the form itself. Now on that for my have my combo box, which is called CBL. Which sheet? And I'm going to use a method of that combo box called Add Item. This is gonna allow me to add additional items to this combo box and in this case, I want to add the current worksheets name, so use that I value again. So it's referencing whatever the actors sheet is at this moment and we're gonna get into its name. There we have it all hit my enter key to drop down the line. So it's a simple little loop. When the form is initialized, we create a loop that's gonna lose 12 However, many worksheets we have in here is going to do two things as it loops. It's going, Teoh, select the appropriate worksheet. Whatever. Eyes equal to 12345 Now. And as it goes that specific sheet, well, then grab its name and add it to the combo box. So let's take a look at what we have here so far. I'm gonna go ahead and close. Or actually, I'm just gonna go back to my form back over here on the left. Let's make that a little bit larger. I'm gonna find my FRM quarter form. I'll give that a double click. There's my formal hit the play button up above. So this is running my form or its initializing it. So the initialized event just ran, which means our co just ran. So now my combo box now has all of the worksheet names in there Really simple initialize create the loop. Go to each sheet, grab the name as you go for me cheap, and add that name to the combo box here. So make sure you try this out, add the code to be able to create the subtle combo box or populated with all of its values . Let me close this. This will take me back to the V b a window. And remember, we got to that by double clicking on the form itself getting into the code behind. There it is, mirror. You get the click event for the form by default. So all you do is change the events in the corner from click to initialize. And now you're gonna drop in your loop. So try it out the next video session. We're going to continue along code, but we're gonna add some code to our buttons. 36. Project #6: Adding VBA Code to the ComboBox Change Event: All right, so we've built our form. We've got a little bit of code in there. On the initialize event of the form, we've now populated the drop down menu. Now, I want to add some code to the actual drop down menu. Users can click on the drop down the combo box and see the sheet names. But what I want to have happen, it was when somebody picks chooses one of those sheet names, it should take them to that sheet. So I'm gonna add some code to an event on that combo box. How do we get to the code of that comma locks? How did we get to the code of the form? Same day? I'm going to select the combo box just cooking on once there and I'm gonna give it a double click. This will take us into the code behind for the form. And it took me right to the combo box called CBL. Which sheet into the change event. Now that's the event that I want to work with. But we do have that little drop down box. You're at the corner that will ask us for different types of events and There are several different types events that you can work with and ad code two for a simple little combo box . I'm gonna do the change every time the option which she is selected changes. It'll run this code. So there's gonna be a simple little bit of code. All I want to do here is when somebody selects one of the sheets, it will take us to that appropriate sheet. So how do we make reference Teoh a sheet, or how do we go to a specific sheet? I don't think we've talked about this in the prior. Well, I actually asked me out. We've talked about this a few times instead of prior lectures, so this may sound familiar. I'm gonna say worksheets. We'll get into the worksheets object and we've been using, like, worksheets. I we did that a lot and said the loops. But that reference the number that was just being looped through 123 and so on. I want to go to a very specific sheet in this case, the sheet that the user picked from the combo box. So I need to get its name. Essentially, I need to get the chosen value from the combo box. Well, how do we make reference to the combo box into the currently selected value? Well, remember using me earlier. What is me? Reference? That's the form. That's the form itself. I need to get to the form. And then I can get to the combo box called Which sheet? And I want to get to its value what was chosen when the change event ran. And then what am I gonna do here? I'm gonna select it. There we go. Hit my enter key. So I've got my worksheets object in there. I'm telling it the specific worksheet or the name of the worksheet that I want to go to, which is coming from the value of the combo box, and then I'm going to select it. Now, remember, this code is running Every time the combo box changes, somebody picked something else from the drop down, and it's going to run again. So let's take a look at this one. I'm gonna go ahead and double click on my form back here on the left off FRM quarter form, and I'm gonna run it. So now I've got my little drop down there it's populated thankfully for my initialize. A loop on the form itself got all of the worksheet names in there. And if I select one like North Records, it'll take me to North Records. Let's say that again. I'm gonna change the value, which runs the change event of the coma box, and it runs my coat. Try it out that this it's so neat what you can do with a little bit of visual basic creating a much MAWR intuitive, flexible, interface accessible interface for our users to go to and interact with our macros. And with our code, it's like south. There we have it. I mean, it closed the form. This will take me back to my form. But if I double click on the combo box once again, this will take me back to my code. And it was really one little line there on change of that combo boxes value. We've gotten the value and told it to redirect us to that specific sheet. So try this out. Add in the little bit of code on the change event for the combo box. When somebody chooses Pacific specific sheet, it will take him there 37. Project #6: Adding VBA Code to the Add Worksheet Button: All right, we're gonna finish out the code here by adding the code to our two buttons one for ad worksheet and one for creating the reports. So let's do the ad worksheet first. This will be the larger of the two. The create Report is gonna be super simple one. We've already got all that code generated already. It's sitting inside of a module. We just have to make reference to it. So let's add the code to add worksheet. I'm gonna go ahead and select my ad worksheet command button here, and I'll give it a double click. Once again, this takes us into the code for that specific control. And I can see it's taking me to my cmd add worksheet underscore click events. Now, once again, just like all the other controls. You do have several other events and you can axum through that little drop down menu. Then I'm gonna stick with the click when somebody clicks the button. That's what I want the code to run. Now two things are gonna happen here. I want the user to be able to add a new worksheets. It's called ad worksheet, right? But then I also want to ask them to name the worksheet. So let's take a look at the code here, adding a worksheet and renaming the worksheets. All right, so for my code, first thing I'm gonna do is add the worksheets, so I'm gonna use the worksheets objects. Ah, and now I'm not gonna add a work specific worksheet, you know, like one that already exists out there. So I'm actually not going to use the parentheses like we've been using. I just want to get to the worksheets Object. Now, if I hit my period or dot this will take me into all the methods and properties of the worksheets object, and you could see that we have one in their cult add. So I'm gonna go ahead and select, add, And now for ad. If that's all I do, it'll add a worksheet. But where does the work she get added? Well, we do have the opportunity to use some parameters here, and one of them is called before we also have after, but I want to add this new worksheet before the first worksheet in this workbook. So I'm gonna use the before parameter, and this is gonna be an assignment operator. So you do. Colon equals what I'm gonna say. Worksheets. I want to add it before the first worksheets inside of this workbook. That's it. I'm gonna add a worksheet. Where's this work? She gonna go? I'm gonna add it before the first worksheet in this workbook. There we have our button now adds worksheets. Now, that's the first part. The second part that I want to do here is going to allow the user to rename the worksheet. So I'm going to say, Now, let's take the active sheets Because when you add a worksheet actually takes you to that work. Should as well. I'm gonna take the active sheet, and I'm gonna change its name and what I'm gonna change his name to. Well, I want to ask the user what they want to call this. How do you get input from the user? Remember doing that in an earlier project? We can use an input box, so I'll say input box. And I'm gonna prompt the user and is going to say enter new. She name only. That sounds good. All right. Hit my enter key again. And every time I hit my enter key. I checked to see if everything becomes capitalized like it should. If something doesn't capitalize, like input box remains lower. Case I Then I probably misspelled something, so So that looks good. So now we're adding a worksheet, and I'm allowing the user to rename the sheet using this input box. Now, I actually want to do 1 1/3 thing here. I wanna add the sheet that the user just created and renamed to the combo box. So I'm going to revisit a little bit of code that we did earlier. I'm gonna get into me, which once again references the form and I'm going to say, Well, we need to get into the which sheet control. And how do you add things to the combo box? We got a method for it called At Item, and I'm gonna add the active sheets. Name? Yeah. You could hit my enter key again. There we have it. All right, so me dot CBO Which sheet? That's the control out there. I'm gonna add an item to it. And what I'm gonna add to it, I'm gonna add the name of the active sheet. The one that user just added and just renamed. So three little lines here, we're gonna add the sheets. We're gonna add it before a specific street in the sheet. Number one, we're gonna allow the user rename it, and we're going Thio added to the combo box so now becomes an option for the users to pick from. So let's try this out. I'm gonna go ahead and double click on my FRM quarter form Here, let me go ahead and run it. So I got my little drop down there. Still, There it is. I'll select North. Just a demonstrate how it takes me the North. Still, I'll hit Add worksheets. This brings up my little prompt. It's still got the sheeted out of the sheet sheet to That was generic, But let's rename it what's Caught Europe Records and I misspelled that fix. That's and I'll hit Okay, And I've now added a worksheet and renamed it and it's inside of my little drop down here. There's Europe Records. Yeah, just three little lines of code. Nothing big. Try this out, jump into your button, add worksheet, give that a double click, take you into the click event, and then you're gonna add your code. Users want to build add sheets. They want to be able to rename the sheets and the that new sheet should be added to your combo box. I'm gonna close. My form will double cook my button and there's our code right there. So try this out. We've got one more button to do here, and we've got a bill to create the final report. This is going to be quick one. But create this button first and the jump into the next lecture must take a look at creating the final reports through the create report button. 38. Project #6: Adding VBA Code to the Create Report Button: So we got one less little bit of code to place into this form. This deals with the create report button. Now, this is gonna be super simple, because all we're gonna do here is referenced the code that you created inside a Project five. But let's do a quick little remind. You hear? What was project number five? Well inside of this exercise file called quarterly report form Dash 01 that hopefully you've downloaded and you're starting to fall along with me. I'm in a double quick module one. This will take you into all of the code that you generated during project number five. Now, the one that I'm really concerned with here is called Final Report. That's what I want to run when somebody clicks the create report button back on our form. So super simple. I'm gonna open my four months again. I mean, a double click might create report button. This again takes me to the code to the click event for that button. And all I'm going to do here is make reference to the procedure that I created called final reports. I'll hit my enter. Key capital F capital are looks good and that's it. I've now added the code to that button which will now run the final report, which goes through all your worksheets, right? Cleans him up copies of data calculates and brings it over into the yearly report Worksheets. One little change in there. Just making a reference to the procedure. So try this out. Double click on your create report button. Get the event in there. You're gonna get click event for that button and add in reference to the final report procedure and you're done. That's all of our code. Few lines. Just not very much inside there. We got a loop. We got a few little statements in there about working with the worksheets. Make sure you catch up here, get all this code in there because inside the next video, we're gonna take a look at how you can display the form when somebody opens the workbook. 39. Project #6: Showing the Form: we've actually got one more piece of code that we're gonna place inside of here. This is gonna be another very simple 11 single line. Now what I want to have happen here is when somebody opens up this workbook this coronary quarterly report form dash 01 workbook right away. The form should open up on display to him. Just like that, they don't have to cook a button. It's just a soon as the workbook opens, there's an event here. The open event of the workbook were then going to show our form and I take a look. This is how you're gonna control the open event of a workbook. Now I'm back inside the V V a window. And in here I've got reference to not only my forms of my modules, but I've also got reference to all the objects that make up this workbook, the worksheets and a reference to this workbook, meaning the workbook that you're actively working in. Now I'm gonna double click on this workbook. This brings me into essentially a module or a place to put code. But for this workbook Now, I'm gonna change reference here from general to workbook, And this is gonna take me to all the events for this workbook default. I get the open event, but just like the controls were talking about on the form earlier. You do have other events that you can work with for this specific workbook. Now, I want the open event. As soon as somebody opens this workbook, the form should open up for him. And like I said, this is a super simple one. What I call the form earlier. Let's find the name of that. Mine was called FRM Quarter form, so I'm gonna make reference to that at 4 a.m. Quarter for make sure I spell it the same way . I'm gonna hit dot which will take me into the properties and methods and so on for this form. And all I want to do here is use the method called show. Well hit my enter Key capital Que capital f looks good. That's it. On open of the form forces me on open of the workbook. Right, You double quick this workbook here. I've changed it from general toe workbook. I've got the open event and one single line in there. So now when the workbook opens. That event is triggered. Will then show our form. So let's take a look at the final product here. I'm gonna close my vb a window. I'm gonna make sure I save all hit the little disc up there at the top or Control s. Now I'm gonna close the workbook. Let's go file on a minute. Close. I'm gonna keep it cell open, but I'm gonna close reference to that workbook. All right, Go on. Now, let's reopen it. And here's the magic file. I'm gonna open. I got underneath my recent list. Their open that up and right away I've opened up that form. Got it? I got my little drop down because the form initialized I got my worksheets in there. I could add additional worksheets. I can create the report a nice, intuitive, accessible interface for our users to build, interact with our macros and to generate this report and maintain this workbook simply all through this form. So make sure you try this out, get into the project, download this file, create the simple little procedures within their for events on this form and create this very accessible interface for you and your users 40. Congratulations: congratulations. You are now an excel macro and VB a master. Now your journey hasn't ended yet. Make sure that you've gone. It's gone through and downloaded all the exercise files and you've gone through. And not only just watched the videos, but you participated and engaged within the exercises themselves. Practice, practice, practice and, by the way, also within this course. Jump into the discussion board and share your results there. Give us some feedback. Let us know what's worked for you. What Perhaps hasn't worked the way that you intended to and come up with your own projects . Share those ideas with this as well. But once again, congratulations for enrolling this course and becoming in XlV b A and Macro Master, I'll see him later courses.