Join Dennis Taylor for an in-depth discussion in this video Introducing the VBA environment, part of Excel 2007: Macros in Depth.
When you create a macro either by recording or a combination of recording and typing code, you in effect create a small program. And that program uses a language called VBA, Visual Basic for Applications. Every macro, no matter how long, involves using VBA. And a big question in a lot of people's minds is, well, what exactly is this VBA and what do you really need to know about this programming language? And you have to become a programmer to really become adept at using macros? The answer is no.
But on the other hand you should become somewhat familiar with the VBA environment. Now in this worksheet called MixedNames there are some formulas in column B. And you can see by the results what's happening here. You don't need to understand the functions necessarily. But the function in B2 and in all the cells below it simply takes the data from column A and makes only the first letters of each word capitalized. It also trims out leading and trailing spaces. You see that happening in rows 5 and 7 and 8, 9 and so on. And similarly there are formulas in Column E. The one in Column E is simply all the letters uppercase, a fast quick look at that.
And in Column A it's a restructuring of the part numbers by using a variety of Excel's text functions together to clean up that data and convert it into something else. The main issue here though is the idea that when you are using formulas to cleanup data you often reach a stage where you say in effect, "I'd like to keep the results and throw away the formulas." In other words, let's have the ability to copy this elsewhere, move it elsewhere, and not have it anymore tied to the data in Column A. And the standard way to do this manually would be to select the data, right-click and copy, and then right- click again on that same data, use Paste Special and choose Values and then click OK.
And that will turn these into the results. Called copy/paste formulas. And you might want to do it here, you might want to do it there and you might want to do it in this workbook and that workbook eventually. A lot of different situations. And so if you select data before recording a macro, the macro simply works off a selection whether it's one cell, two cells, a whole row, a whole column, or a multi-range. No matter how big the size, it's going to work on all the cells at once. So let's begin by selecting the cells. There is an example here column B that contain formulas.
And we want to manually step to the process while recording and creating a macro that in the future will allow us to convert other formulas immediately into their values. In the lower left corner in the Status bar we are going to click the button for recording a new macro and we will call this ConvertToValues. Keystroke shortcut here might be Ctrl+ Shift+C. Keystroke shortcuts are not required but they are valuable and they are helpful and they are real handy too. We'll start in this workbook, even though in long-term we might eventually want to have this copied to a place where other workbooks could get to it as well.
And again the description, desirable and helpful, but we don't always have the time to do it. But you'll find this valuable over time when you open other macros that others have created. It's helpful to see a nice description there as to what actually happens here. Convert formulas to Values. Good enough for now. Click OK. And we are in recording mode so we'll right-click the selection, choose Copy, right-click it again, choose Paste Special, then Values, then OK.
And a minor point, but certainly you don't want to see those marquee lights around forever, just press Escape. That's all part of the macro as well. And we are done. We are going to stop recording. And we'll take a look in Column B and that doesn't have any formula anymore. That's the Baxter, Donna. That's the way we see it and that's the way it looks and so are all the others here. They are all taken care of. And let's do the same thing in Column E perhaps. Now it might be a different setup. It might not be a whole column. Maybe it's only a selection of them. I just try it here to see if it works. How about Ctrl+Shift+C? Look at one of these.
Yup, no formulas anymore. Here are the results. Probably there you would have done the whole column. Ctrl+Shift+C works just fine. So in all cases here we've thrown away the formulas. We do the same thing in Column I or in other situations just like this. The size of the selection doesn't make any difference. So we've created a small macro here. Let's take a look at it. One way to get here is on the View tab. And also notice when you click this button, View Macros, Alt+F8, do it this way, we can do this and come down this path.
But this is one way to get to our code. We can then click Edit and see the code that way. But a faster way and one that has a dual advantage is a keystroke shortcut called Alt+F11. Alt+F11 throws us into the VBA environment. And when we get here sometimes the macro we are looking at isn't the one we just created. But then we begin to look around a little bit. The two major portions of the screen typically when we come into VBA are the code window, usually on the right side, and on the left side is something called VBAProject or the Project window.
And you'll notice here that the name of the current workbook, VBA, right here is listed. All the sheets that are in it are listed. And then you'll see the word Modules. Now if you open a workbook and record a new macro it's going to be placed into a new module. So the latest module here is Module3. I am about to double-click it, and there we are. And here is the macro that just got recorded. Remember the name ConvertToValues, there it is. And as we look at this we see the actual code here. Again you don't really need to understand every single line.
But on the other hand it's not a bad idea to at least begin to absorb a little bit of the meaning of this. We copied data and that's how it gets translated into code. We did a Paste Special and the key term there was pasting the values and that's what we see here. Other parts of this code are unnecessary. Eventually you might want to get rid of them but it doesn't hurt to leave them here. And you probably would never guess what this means unless you thought out what we did. This was what happened when we pressed Escape to get rid of those so-called Marquee Lights. So here and there some of this might strike you as a little bit strange.
And we need to talk about a few more things regarding VBA. But don't be intimidated by it because there is not necessarily a lot you need to learn. It's a programming environment. It's easily accessible from any workbook and anytime we press Alt+F11 we'll either enter this environment or leave it and go right back into Excel.
- Running macros
- Understanding the VBA environment
- Storing macros in a Personal Macro Workbook
- Recording macros in stages
- Writing VBA code
- Using loops
- Debugging macros
- Testing in step mode
- Optimizing macros for fast performance