0:00:00
Welcome to another Fast Tips video brought to you by ExcelLearningZone.com. I am your instructor Richard Rost. In today's Fast Tip video, just a quick introduction to Excel VBA and Macros. Lots of you email me questions, how do I get started, how do I do this in Excel? A lot of this will involve some VBA, so this video is just going to give you a quick intro, teach you how to get started and how Excel VBA works. VBA works. I'm going to use this video in my future fast tip videos when I say you need to know a little Excel VBA so this one will get you started. You ready? Here we go. Now of course it goes without saying if you don't know Excel, if you haven't used Excel, go watch my free Excel level 1 class. It will teach you all the basics, everything you need to know to get started before you start trying to program in Excel. I'm not going to spend not going to spend a ton of time on definitions and concepts and all that stuff. My full course is for that. Today we're going to go over the quick basics.

0:00:55
So Excel VBA. VBA just stands for Visual Basic for Applications. That simply means it's a special version of the Visual Basic programming language written specifically for Excel. So this is the language you're going to use to make Excel do stuff. So let's make Excel do some stuff. So let's start with a blank So let's make Excel do some stuff. So let's start with a blank workbook And a good way to start learning how to program in VBA is to take a look at some existing code the best way to get some existing code is to record a macro and You can use the macro recorder that's built into Excel to have Excel write some VBA for you And a macro is basically just a series of instructions to do this then do this then do this you use it to automate repetitive tasks instructions to do this, then do this, then do this.

0:01:39
You use it to automate repetitive tasks. Let's say I had some text here, like my name. And I want to take that text and I want to bold it and I want to make it yellow and red and maybe change the font to 16 and put a border around it. And I want to do that a lot and pretend you don't know what the format painter is. And okay, pretend you don't know what the format painter is. And you want to be able to say click here and apply the same format to that cell. You can use a macro or some VBA code to do all of that. Let me hit undo a bunch of times, control Z, control Z. I'm going to type in Richard again.

0:02:23
And now I'm going to record a macro that is going to record that series of steps that I just did. macro that is going to record that series of steps that I just did. So go to view and then macros and then record macro. The record macro window appears. You can give the macro a name if you want. I'll call this my macro. You can give it a meaningful name if you'd like. You can assign it a shortcut key so that you can just press that key combination on the keyboard to run that macro.

0:02:51
Let's put an O in there, control O. that key combination on the keyboard to run that macro. Let's put an O in there, Control O. Pick something that's not already used. Okay, store the macro in this workbook is fine. Give it a description if you'd like to and then hit okay. Now, your macro is currently recording. So everything you do will be saved in this macro, so be careful. All right, so I'm gonna click on C2.

0:03:10
I'm gonna go back to the Home tab and let's fold it, put a border around it, make it yellow, make it red. and let's fold it, put a border around it, make it yellow, make it red, change the font to 16, maybe center it. Okay and now I'm done and you can do lots more than just formatting in here too. Macros are very versatile. Let's go back over to view, macros and then stop recording. Okay all that has been saved now in the macro now in the macro recorder. So let me delete this row, right click and then delete. Okay it's gone. All right let me run that macro again. Now there's a couple different ways you can run it. You can go back over here drop this down, go to view macros, there's my macro that's where it's saved, and then hit run.

0:03:56
There it goes. Okay notice I didn't get Richard and that's very important Notice I didn't get Richard and that's very important because I typed in Richard before I started recording the macro, right? I typed in Richard then I started the macro recorder. So you only get while the macro is recording what's saved in there all right again, let me delete that and The second way you can run the macro is using that keyboard shortcut right ctrl-o. Oh and there it runs it see All right. So there's two ways you can run it. I like the keyboard shortcuts myself and All right, so there's two ways you can run it. I like the keyboard shortcuts myself. And you can also assign them to buttons, but that's for a different class. Okay, let's save our workbook, Control-S to save our workbook.

0:04:38
What are we going to call it? Let's call it my workbook, whatever you want to call it. It's going to save in my G drive, right? Hit save. Notice the workbook file is an XLSM file. It's macro enabled. the workbook file is an xlsm file. It's macro enabled. If you try to save this, let me go to save as, if you try to save this as just a regular Excel workbook xlsx and hit save, you'll get a warning message that says you can't save VB projects in an xlsx file. Microsoft Microsoft does that for safety.

0:05:13
You have to specifically use an XLSM file. All right, I'll say no right now and then close that. Okay. All right, now, we use the macro recorder. The macro recorder wrote a bunch of code for us. Let's go take a look at it. Drop this down. Go to View Macros, click on your macro and then click on Edit. That opens this guy up, the Microsoft Visual Basic for Applications Editor. That opens this guy up, the Microsoft Visual Basic for Applications Editor.

0:05:41
You can see all the code that that macro ran. Now, if you don't see this over here, this VBA project pane, you might see just this. That's okay. Go to View and then Project Explorer. That turns that on. Sometimes that gets turned off. All right. This is all of the code that the macro recorder wrote for you. I'm scrolling down. There's a lot of it.

0:06:00
that the macro recorder wrote for you. And I'm scrolling down, there's a lot of it. And we're not gonna go over all of this today. But just take a general look at it. You can see right here, all right, range C2 select, that means it's selected cell C2. It set the font to bold, it set the borders, all right. Set some colors down here. All right, you can go read through this if you want to. But that's what the macro recorder does.

0:06:24
this if you want to. But that's what the macro recorder does. It records all of your steps and translates that into VBA code. Now right now the first thing it does is select cell C2. So if I come back over here, okay, and I let's say I want to apply that macro to G6. If I hit ctrl-o it goes right back to C2. All right that's the first command in the macro. So let's go back over here and That's the first command in the macro. So let's go back over here and just delete that line. Watch this. Backspace over it or delete it. Okay.

0:06:59
Save it. Control S. Come back over to Excel. And now if I click over here and I go Control O, look at that. It applied the formatting to the cell I was on because I removed the command to go back to cell C2 first. See that? Okay. to sell C2 first. See that? Okay, that's how easy it is to modify these macros. Okay, let's come back over here. I want you to close this window, right? Here's the top of it, just close that window down. And I want to show you an easier shortcut way to get into the VBA editor. All right, we're going to turn on the developer tab. Now, normally they don't want just anybody poking around in the VBA editor, so they hide the developer tab by they don't want just anybody poking around in the VBA editor so they hide the developer tab by default.

0:07:40
But we can turn it on by going to file and then options and then customize the ribbon. Come right over here, scroll down, there's developer, notice it's turned off, turn that puppy on, hit okay and now you got a brand new tab up here with a whole new ribbon. Look at that. And the easiest way to get back to where we were is just click on visual basing. Oh, look at that. And the easiest way to get back to where we were is just click on Visual Basing. Oh, look at that. Pops it right up. All right.

0:08:06
So, let's take a look over here. So, all of the macros that you record, if you recorded multiple macros, you can make more of them. All right. Those will be stored in modules down here. All right. And the reason why they're in modules is so that anybody can use them. All right. Any object up here can use them in that module, inside of that workbook. What do I mean by objects?

0:08:26
Well, there's different objects. What do I mean by objects? Well, there's different objects. There's a this workbook object that is for the entire workbook file. Then you have individual objects for each sheet. Now we only have one sheet in our workbook, but if we added more, you'd see more sheets appear right here. Let's take a look at the code for a sheet. I'm going to double-click on sheet one. That's going to open up the sheet one code.

0:08:48
It's blank right now. There's nothing in there. All right, it's blank right now. There's nothing in there. And again, there's a general section which is code for the entire worksheet. You could put your own functions and subs in there. And again, that's a topic for a future video. But if you drop this down, you'll see a worksheet object. Click on that. And now it creates something called Worksheet Selection Change.

0:09:09
All right. Now there's a bunch of events that will fire at different times during the life of the a bunch of events that will fire at different times during the life of the sheet the selection change happens when you move what cell is selected or select the range or something like that and there's all kinds of tables get updated recalculated calculations and all kinds of stuff in here but worksheet selection change happens when you selection change happens when you move from one cell to another or select the range.

0:09:42
Now we can put our own code in here. Let's do this. Put a couple of blank lines. All right, tab in. Good programmers always tab in. And type in the command message box, M-S-G-B-O-X, and then a space, and then your prompt. Now the first thing any programmer always learns is inside of quotes, hello world. always learns is inside of quotes, hello world. That's the first program any programmer is supposed to write and if not they're in trouble. The programming police will come for you. Okay? Okay. All right that's it. That's all we need. Now save it. Control S. All right come back over to your spreadsheet. Oh look at that. As soon as I click on it, look at that. It says hello world. Okay. Why?

0:10:25
That's because the selection moved over here to A3. Click on a different cell. world. Okay, why? That's because the selection moved over here to A3. Click on a different cell. Oh, hello world. That's interesting. Look at that. That's kind of annoying but it gives you an idea of what's happening. Anytime I click on a cell, that event runs. What's that event? It's the selection change event. We've changed the selection. Let's useful. Let's change the prompt and say you selected row and then a space, close the quotes, ampersand, right that's shift 7 ampersand, that's the concatenation operator meaning we're gonna put two things together and I want you to type in target dot row. Alright and then press enter. What does target dot row mean? Well mean? Well, if you look up here, the target gets sent into this subroutine by Excel and the target is a range indicating what cell you're in basically. Alright, so target.row is the row that you're on. Okay, or more specifically, this should say you selected a cell in row cell in row and then the row.

0:11:38
All right? Let's see what happens now. Come back over here, click, and look at that. You selected a cell in row 8. Hit OK, come back up here. You selected a cell in row 2. OK, so we can use the target parameter inside here. You can also use the column, too. Target.column. Oops, I can't spell today.

0:12:01
Target.column. Oops, I can't spell today, target.column and you selected a cell in column. All right, save it, come back out here. All right, column one, it doesn't use letters, it uses the number of the column, column 11. Yes, there's a way to switch to it, that'll be a future video. Okay, but you can see how you can give messages with useful information in it. information in it. Alright, remember this guy that we recorded earlier called my macro? Alright, well watch this. Go back to sheet one and our selection change. Get rid of this message box command and just type in my macro in there. Okay, save it. Every time you click, it changes that cell with the formatting properties of your MyMacro macro.

0:13:00
All right, you click, it changes a selection, so selection change runs MyMacro. Where's MyMacro? Well, it's right here inside of module one. You can find it by right-clicking and going to definition, and it puts you right on your macro. Look at that. That's kind of cute, huh? definition that puts you right on your macro. Look at that. That's kind of cute, huh? Okay, let's go back to sheet one.

0:13:18
Let's get rid of this. Let's delete that event. I don't want that to run anymore. I'm going to delete the whole thing, everything from the worksheet selection change all the way down to end sub. Just delete that or backspace over it. Now that's gone. Now that won't run now if I click in here. See, it's not running. All right, let's get rid of all this stuff.

0:13:35
Delete. Goodbye. Okay, fresh worksheet basically. Delete. Goodbye. Okay, fresh worksheet basically. All right, back over to your VB editor. Now in addition to the sheet, there's also the workbook. Double click on this workbook. Okay, again, general will be different functions and subs that you can write yourself or drop this down and pick workbook.

0:13:58
And now you're in workbook open. If you look over here, there's a ton more of events If you look over here, there's a ton more of events that happen when the workbook does stuff. When you open the workbook, when you create a new chart or a new sheet, when you deactivate it, all kinds of stuff in here. When the windows resized, you name it. There's lots of nuts stuff after you save your file. All right, but we want to work in workbook open. This happens once when you open the workbook. And I'm gonna come in here and say tab, always tab in, message box, right? come in here and say tab, always tab in, message box, right? Greetings, Richard.

0:14:32
Today is space, close your quotes, ampersand to put two strings together, and then date, just the word date, and then press Enter, just that. OK, save it, close that, let's close this. Now, let's open up Excel again and let's open up my workbook and look at that. In the open event for the workbook, my little message box runs and says, greetings Richard, today is the date. And I use the ISO date standard which is year, month, day, that way it's universal for everybody around the planet. That's actually a Windows setting, you can set it in your control panel, I got a whole video that describes that, go watch this.

0:15:14
If you're interested, I'll put a link down below. a whole video that describes that. Go watch this. If you're interested, I'll put a link down below. Alright, but there we go. We got our message box. I'll hit OK. And if I want to go modify that code, how do I get there? Right? Developer, Visual Basic, alright, and it's in this workbook right there. There it is. And my modules are right here with my macros in them still. See, there we go. Alright. And that's it. You're a programmer now. You And that's it! You're a programmer now. You now know how to program in Visual Basic for Excel. And that's it. That's all you got to do. So there's your fast tip for today. I hope you learned something. Live long and prosper and I'll see you next time. So that's it. Want to learn more Excel? Be sure to like this video and subscribe to my channel. Stop by my website to watch my free Excel Level 1 course. It's over 90 minutes long and it Stop by my website to watch my free Excel Level 1 course.

0:16:03
It's over 90 minutes long and it covers all the basics. And if you want me to post more Excel videos, I need to hear from you. About 90% of what I do is Microsoft Access, but I'm also a published Excel author and I love Excel. So if you want to see me post more free Excel videos, post a comment below and let me know. Say, hey, I want more Excel. I want more Excel!