Press Alt+F11 to quickly jump to VBA. The VBA environment is dominated by the Code window on the right side; the Project Explorer window is on the upper part of the left side of the window amd the less important Properties window is located below it. If not present, activate any of these from the View menu.
- [Voiceover] Every time you create a macro with a recording process, you are, in effect, writing a program. Program in Visual Basic, or to be more precise, Visual Basic for Applications. In this Workbook, we're looking at, let's say, any worksheet, we happen to be looking currently at CurrentStaff. If we want to view the VBA code, we can press Alt + F11. If we didn't know that shortcut, we could go to the Developer tab and the far left button Visual Basic.
Once again, note that keystroke shortcut, Alt + F11, it will come in handy many times as we work with Excel macros. Click the button or press Alt + F11. We're into the world of Visual Basic, or we press Alt + F11, we're back into the Excel environment. Back and forth, toggled with Alt + F11. Now, the more you work with macros, the more you will begin to understand VBA, and in different degrees, perhaps you will be working with it more closely. It varies widely depending upon the person or what you're trying to do with macros.
Now, this window isn't always what we see right now. For many people, the two most important windows, and they're nearly always present, are the VBAProject window, typically on the upper left side, and then to the right of it, occupying the bulk of the screen, the code window. If neither of those is present, you wanna go to the View tab and there's where we see Code, that's the first entry. If the Code window's not visible on the right, you definitely wanna see this, View Code, and also the VBAProject window, it's called Project Explorer, we wanna see that as well, too.
The Properties window below is going to be handy occasionally, although not nearly as important as the other two. Now, any time you go to this window, you do see the current Workbook listed over in the left hand side. If there are other Workbooks open, you will see those names. Sometimes the view here is collapsed. So, off to the left here is a minus, I'm gonna click it. Now, that also changed the Code window, but if the view looks like this, it sometimes might throw you. But, by clicking the pluses just to the left, for example, PERSONAL, that's the personal macro Workbook, more about that in the next two movies.
Click the plus there, we expose aspects of it, including Modules. Modules are where macros are stored. So in the current Workbook, it's called WorkbookWithMacros. Click the plus if it hasn't been expanded, we will see Modules. And sometimes you'll see more than one Module. You can rename the Modules by where the Properties window below. If you haven't named them, every time you open this Workbook and create a new macro it will go into a new Module. And so, over time, you might wanna consolidate these.
So, here we see Module1, and you wanna double-click these to see what's in them, as I just did here for Module1. This is a macro stored in Module1. If I double-click Module2, there could be another one or two there, if I double-click Utility, there might be one there. I could keep them all in their separate Modules, or combine them into one. At different points, based on the nature of the macros, you might wanna categorize them differently, give them different Module names, although that's not critical. Now, within the macro code itself, I'm going to double-click Module2.
And as we look at the code, this is going to be true of all macros, anytime you see green, that is not code, that is a comment, and you always see a single quote on the left hand side. You can add your own comments. When you record a macro, the name you gave the macro is repeated right here. If you provided a description, you'll see that there as well, if you didn't, you won't see that line. If you provided a keystroke shortcut you will see this kind of a line. Do not in any way assume that if you change what we see here, that you're changing the keystroke shortcut.
That will not work. So don't consider this as a way to change it. That's a documentation tool. Often, just for the sake of taking up less space, I get rid of these two rows automatically, because it's simply a repeat of the name, but you can insert your own spaces anytime you wish. Certain keywords appear in blue, if everything here has been recorded, you never worry about that. There will be times when you type in some new code and at certain points you'll see the code turn blue or not, also as a general approach, if you are typing code, always type in lowercase.
Certain keywords will be recognized and they'll be capitalized. More about that in later movies. There will be times too when you look at certain code and you'll recognize that the code is longer than it needs to be. Here's a PasteSpecial line here. Because some of these are defaults, we could remove some of the code. Again, that's not critical, and it's not harmful that the code is longer, but sometimes you just want it to look a little tighter, a little more compact. But, you don't wanna be too bogged down at first with understanding every single line, that's just not likely to happen.
It's so easy to record this rather than somehow trying to look up what it is that you might want to type. Although we certainly can type code here, many times it's going to me much, much easier to record the process, think it out that way. And remember, we've got different Modules over here. Now, if I wanna combine the first two, for example, I might wanna take this code here and put it next to the macro that's in Module1, now I'm double clicking Module1, and we see that code, I wanna put those two together in the same Module.
So I go to Module2 here, I just double clicked it, I'm gonna highlight all this, I could copy or cut it, and then come back and get rid of it if I copied it, and we've got a menu over here with Edit, we can certainly use Cut Copy that way, or the keystroke shortcut. Maybe I'll use the Cut here, go to press Ctrl + X, I'll go to Module1, click below the other macro, right down here at the bottom, and paste it. Ctrl + V or go to the menu Edit Paste, there it is. Now Module2 doesn't have anything in it anymore, so I can right click Module2, and remove it.
I don't want to export it anywhere, it's gone. So this is called Module1. I might come back and give that a better name. Maybe these are formatting type tools. Or maybe they're just selection tools. Maybe I'll give it a name. Now, you can't change Module1 right here where we see it, but in the Properties window below, and if it's not available you can choose View, and Properties window will become available. Down here below where we see Name and Module1 I'm going to highlight this and I'll just give it a different name, a name that maybe encompasses what these two macros do even if they're different from one another, so I'll call them Selection.
Maybe I'll use macros. Can't use spaces in there. SelectionMacros, okay. There they are. So we changed the name of the Module. Not critical, but nevertheless, handy. So, at different times throughout this course, too, particularly in subsequent movies as we talk more and more about adjusting macros and creating them, we'll talk more about this environment. Again, it can be intimidating at first if you let it. Some people do take a strong interest in VBA, and they might take a course or two, there are courses available at https://www.lynda.com, they might be available also at your local community college, something like that, some people take a boot camp kinda course, five days a week, that sort of thing.
But, the need for VBA varies widely with Excel users. And I know a lot of proficient Excel users who use macros a lot who aren't necessarily expert at VBA, and they don't need to be, either. So, it's an environment you wanna become more comfortable with, I suggest every time you create a macro, look at VBA simply by pressing Alt + F11. Move back and forth, get a little bit familiar with it. Over time you'd be surprised how much you will absorb. Alt + F11, we're right back into Excel. Remember, at any time we can get to our Visual Basic environment, but with Alt + F11.
By the way, if you hold down Alt too long, sometimes you see the numbers that appear up in the menu, that's not really a problem. If F11 doesn't work immediately, sometimes you'll have to press Escape. But, we toggle back and forth, we become familiar with the VBA environment, and there's a lot more about that in subsequent chapters as we build longer macros.
- Understanding macros security
- Running macros
- Using Visual Basic for Applications (VBA) to program macros
- Recording macros
- Expanding macros with the if statement
- Using For…Next, Do While, and Do Until statements to repeat action
- Joining two macros
- Streamlining macros