On the heels of Bill's last Podcast ["Changing Case in Excel: Lower, Upper, Proper: Podcast #1356"] Episode #1357 shows us the methods for converting text to Upper Case, Lower Case, or Proper Case in Excel using a VBA [Visual Basic for Applications] Macro.
Transcript of the video:
[ Music ] Hey, welcome back to MrExcel Podcast, I'm Bill Jelen, from MrExcel.com.
Today, we're talking about changing case in Excel using a Macro.
Now, yesterday, episode 1356, we took a look at how to do this using the UPPER, PROPER, or LOWER functions in Excel.
But that's kind of a hassle, having to insert the extra column and everything.
Today, we're going to take a look at how to do this with a macro.
Okay, so we have some data that we want to convert from, in this case, proper case to upper case or lower case, and rather than always having to insert a column, use the formula, copy, paste values, and copy over, deleting the Column, well, let's just come up with a macro that will solve this problem.
Now, I want to put this macro in the Personal Macro Workbook.
This will not be a macro that we can record.
We're going to have to type this macro, but I want to make sure the Personal Macro Workbook is there, first of all.
So, in Excel 2010, go to the View tab, over here to the Macros drop-down and say I want to Record a macro.
Now, it doesn't matter what you're going to call this because you're actually going to end up deleting this, but it's really important you say you want to store the macro in this-- not this workbook-- but the Personal Workbook.
Personal Macro Workbook is a workbook that is available every time that you open Excel, so the macro stored there will work in every workbook that you ever open.
So we click OK, and then we're just going to do something very innocuous, just, maybe, apply Bold.
Alright?
Just something really simple to make sure that we get a macro there, the stop button is right down here next to Ready.
Alright, so, now there is a very simple macro that we probably will never, ever use, but the beautiful thing about that, is that macro has created, for us, a Personal Macro Workbook, stored in the correct folder so, that way, it'll open every single time.
Alright.
Let's switch over to VBA to do that We do Alt+F11, here's our VBA window.
It's just big and gray and intimidating.
First time you're in VBA, you want to go to View, and Project Explorer-- this shows you a list of all of your open workbooks.
So, there's Podcast 1357 with Sheet1, there's my ColorAddIn-- one of my an add-in that I installed-- and then PERSONAL.xlsm.
So, let's open PERSONAL.xlsm and-- Excel Objects and Modules-- let's open Modules, there's Module1 with the code that we just recorded now.
I actually don't need this to be here at all, so I'm going to delete that and we're going to create our own macro.
I'll call this-- these always start with the word Sub, and then-- let's call it UpperSelection-- UpperSelection as in, we're going to uppercase everything in this selection.
Very simple, five line Macro.
Here, we say For each cell in selection.
So the idea will be that you select some cells and then run this macro-- actually, it could be a three line macro, but I'm worried about formulas-- and we say If not cell.HasFormula Then-- this is where the actual good stuff happens, let's see if I can make this a little bit smaller over here-- and we'll say cell.Value = -- you would think that we would say Upper here, but, it's funny, in VBA, for whatever reason, the upper function is not called upper, it's called UCase, as in upper case-- cell.Value.
Right there.
That will take the value in the cell, upper case it, and put it back in the cell.
We'll put an End if, here, to finish that If statement, and then a Next cell.
Right there.
That's all it takes-- those five lines of code will create a macro that will upper case everything in the selection.
Very important, it will not touch cells that have formulas, alright, because if we did that we would lose the formula.
So, I don't want to do that.
Let's switch back to Excel now.
We'll go File, Close, and return to Microsoft Excel.
And I'll show you how to add an icon up here to the Quick Access toolbar to run that macro.
So, I'm going to right click on the Quick Access toolbar, say Customize-- all right-- and we start out with a list of Popular Commands, I want to open that drop-down and change to Macros.
And, sure enough, there it is-- my PERSONAL.XLSB!UpperSelection.
We'll add that.
Alright, now, I need to get this up here on the screen so that what you can see it.
At the bottom of this drop-down, there is a modify button-- I realize that's outside of the screen for you-- we choose Modify, we get to have a whole series of different icons-- none of these are going to make me think of uppercase.
Maybe this one here with the up arrow, I don't know, and I'm going to put a good tool tip here-- I'll call it Upper Case Selected Cells-- and click OK.
And down here at the bottom, click OK again.
So, there's our new Macro button.
Watch how cool this is-- I choose some cells, hit the button, BAM! everything is now Uppercase.
Alright, so, that solves one-third of our problem, let's take a look at how to solve the rest.
Again, to get back to VBA, that's Alt+F11, and we're going to create a new macro called LowerSelection.
Alright, so I just did a copy and paste there, call it LowerSelection, and here, instead of UCase, we're going to do LCase.
Alright?
And then, you think that you've got the whole thing figured out, and then they're going to throw a curveball at us.
Okay, for PoperSelection-- ProperSelection, all right-- there is not a PCase-- that would be really cool-- but when VBA doesn't have a function and Microsoft Excel does have a function, we get to use that function with a little bit of extra work.
Alright, so I'm going to put an underscore there-- that lets me go to a new line-- I'm going to say Application, another underscore, .WorksheetFunction, another underscore, .Proper.
Alright, so that actually lets us use the Excel PROPER function to come up with this answer.
So, If Not cell.HasFormula Then cell.Value = Application .WorksheetFunction .Proper (cell.Value).
Okay, now, in your situation, you can put all that on one line.
I wanted it to fit here in the screen size, so that's why I put those underscores-- in real life, just one big, long line that stretches out there to the right.
Now, of course, back in Excel we are going to add a couple of more items to the Quick Access toolbar.
Let's do that-- Customize Quick Access toolbar, again, here, choose Macros, and then after choosing Macros, I'm going to choose Lower case, I'll add it to the Quick Access Toolbar, and we'll modify that, I'm going to go with the down arrow for this one and Lower Case Selected Cells.
Alright, I'm already thinking I had the Proper.
I have no idea which one is-- which icon-- is going to remind me of Proper-- it's just really-- usually, it's used for names, so let's put the person, then that-- whatever, whatever will work for you.
We'll go to Proper Case Selected Cells, click OK, click OK.
Now, the beautiful thing about this Macro, is you only have to do this once.
Once you've done it, those icons will be up there all the time.
Let's try it out.
Let's try and go to Lower Case-- perfect.
Proper Case-- look how easy that is.
You'll never have to insert new Columns, use the function, convert to values, and copy back.
Excellent, excellent way to go.
Again, it'll take you, you know, five minutes today or tomorrow to get these macros in your Personal Macro Workbook.
Once they're there, you're good to go.
Okay, I want to thank you for stopping by, see you tomorrow for another netcast from MrExcel.
Today, we're talking about changing case in Excel using a Macro.
Now, yesterday, episode 1356, we took a look at how to do this using the UPPER, PROPER, or LOWER functions in Excel.
But that's kind of a hassle, having to insert the extra column and everything.
Today, we're going to take a look at how to do this with a macro.
Okay, so we have some data that we want to convert from, in this case, proper case to upper case or lower case, and rather than always having to insert a column, use the formula, copy, paste values, and copy over, deleting the Column, well, let's just come up with a macro that will solve this problem.
Now, I want to put this macro in the Personal Macro Workbook.
This will not be a macro that we can record.
We're going to have to type this macro, but I want to make sure the Personal Macro Workbook is there, first of all.
So, in Excel 2010, go to the View tab, over here to the Macros drop-down and say I want to Record a macro.
Now, it doesn't matter what you're going to call this because you're actually going to end up deleting this, but it's really important you say you want to store the macro in this-- not this workbook-- but the Personal Workbook.
Personal Macro Workbook is a workbook that is available every time that you open Excel, so the macro stored there will work in every workbook that you ever open.
So we click OK, and then we're just going to do something very innocuous, just, maybe, apply Bold.
Alright?
Just something really simple to make sure that we get a macro there, the stop button is right down here next to Ready.
Alright, so, now there is a very simple macro that we probably will never, ever use, but the beautiful thing about that, is that macro has created, for us, a Personal Macro Workbook, stored in the correct folder so, that way, it'll open every single time.
Alright.
Let's switch over to VBA to do that We do Alt+F11, here's our VBA window.
It's just big and gray and intimidating.
First time you're in VBA, you want to go to View, and Project Explorer-- this shows you a list of all of your open workbooks.
So, there's Podcast 1357 with Sheet1, there's my ColorAddIn-- one of my an add-in that I installed-- and then PERSONAL.xlsm.
So, let's open PERSONAL.xlsm and-- Excel Objects and Modules-- let's open Modules, there's Module1 with the code that we just recorded now.
I actually don't need this to be here at all, so I'm going to delete that and we're going to create our own macro.
I'll call this-- these always start with the word Sub, and then-- let's call it UpperSelection-- UpperSelection as in, we're going to uppercase everything in this selection.
Very simple, five line Macro.
Here, we say For each cell in selection.
So the idea will be that you select some cells and then run this macro-- actually, it could be a three line macro, but I'm worried about formulas-- and we say If not cell.HasFormula Then-- this is where the actual good stuff happens, let's see if I can make this a little bit smaller over here-- and we'll say cell.Value = -- you would think that we would say Upper here, but, it's funny, in VBA, for whatever reason, the upper function is not called upper, it's called UCase, as in upper case-- cell.Value.
Right there.
That will take the value in the cell, upper case it, and put it back in the cell.
We'll put an End if, here, to finish that If statement, and then a Next cell.
Right there.
That's all it takes-- those five lines of code will create a macro that will upper case everything in the selection.
Very important, it will not touch cells that have formulas, alright, because if we did that we would lose the formula.
So, I don't want to do that.
Let's switch back to Excel now.
We'll go File, Close, and return to Microsoft Excel.
And I'll show you how to add an icon up here to the Quick Access toolbar to run that macro.
So, I'm going to right click on the Quick Access toolbar, say Customize-- all right-- and we start out with a list of Popular Commands, I want to open that drop-down and change to Macros.
And, sure enough, there it is-- my PERSONAL.XLSB!UpperSelection.
We'll add that.
Alright, now, I need to get this up here on the screen so that what you can see it.
At the bottom of this drop-down, there is a modify button-- I realize that's outside of the screen for you-- we choose Modify, we get to have a whole series of different icons-- none of these are going to make me think of uppercase.
Maybe this one here with the up arrow, I don't know, and I'm going to put a good tool tip here-- I'll call it Upper Case Selected Cells-- and click OK.
And down here at the bottom, click OK again.
So, there's our new Macro button.
Watch how cool this is-- I choose some cells, hit the button, BAM! everything is now Uppercase.
Alright, so, that solves one-third of our problem, let's take a look at how to solve the rest.
Again, to get back to VBA, that's Alt+F11, and we're going to create a new macro called LowerSelection.
Alright, so I just did a copy and paste there, call it LowerSelection, and here, instead of UCase, we're going to do LCase.
Alright?
And then, you think that you've got the whole thing figured out, and then they're going to throw a curveball at us.
Okay, for PoperSelection-- ProperSelection, all right-- there is not a PCase-- that would be really cool-- but when VBA doesn't have a function and Microsoft Excel does have a function, we get to use that function with a little bit of extra work.
Alright, so I'm going to put an underscore there-- that lets me go to a new line-- I'm going to say Application, another underscore, .WorksheetFunction, another underscore, .Proper.
Alright, so that actually lets us use the Excel PROPER function to come up with this answer.
So, If Not cell.HasFormula Then cell.Value = Application .WorksheetFunction .Proper (cell.Value).
Okay, now, in your situation, you can put all that on one line.
I wanted it to fit here in the screen size, so that's why I put those underscores-- in real life, just one big, long line that stretches out there to the right.
Now, of course, back in Excel we are going to add a couple of more items to the Quick Access toolbar.
Let's do that-- Customize Quick Access toolbar, again, here, choose Macros, and then after choosing Macros, I'm going to choose Lower case, I'll add it to the Quick Access Toolbar, and we'll modify that, I'm going to go with the down arrow for this one and Lower Case Selected Cells.
Alright, I'm already thinking I had the Proper.
I have no idea which one is-- which icon-- is going to remind me of Proper-- it's just really-- usually, it's used for names, so let's put the person, then that-- whatever, whatever will work for you.
We'll go to Proper Case Selected Cells, click OK, click OK.
Now, the beautiful thing about this Macro, is you only have to do this once.
Once you've done it, those icons will be up there all the time.
Let's try it out.
Let's try and go to Lower Case-- perfect.
Proper Case-- look how easy that is.
You'll never have to insert new Columns, use the function, convert to values, and copy back.
Excellent, excellent way to go.
Again, it'll take you, you know, five minutes today or tomorrow to get these macros in your Personal Macro Workbook.
Once they're there, you're good to go.
Okay, I want to thank you for stopping by, see you tomorrow for another netcast from MrExcel.
