Hi Excellers, and welcome back to another #MacroMonday #Excel blog post in my 2020 macro series. It is time to sit up. We will be using the Proper Function today!. You may (or may not) know that you can change the case of text using the Excel PROPER function. What the heck does that mean?. Well, say you have text like this.
I lOVE eXcel.
If you apply the PROPER formula to this cell, Excel will happily convert your text string to ‘I Love Excel’. This is converted to the correct case for your text string.
The Manual Way To Use The Proper Excel Function.
Now, the normal or usual way to use the PROPER function is to use the following syntax to change the case of your text string.
=PROPER(text)
where
text. this is a required argument. Text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize.
So, Excel will capitalize the first letter in a text string and any other letters in text that follow any character other than a letter. It then converts all other letters to lowercase letters.
Here is the same example and how it looks using the normal Excel Proper Function or Formula.
Writing An Excel VBA Macro To Convert Text To Proper Case. Proper Function.
Now, we have seen that we can use the normal PROPER function or forumula in Excel. There is a really quick way however, to run a simple Excel VBA macro, apply it to all of the cells at once that we want to convert to PROPER case. We just need a tiny bit on code to do all of the work for us.
By using the Macro we can change a whole range of cells with a couple of clicks
What Does The Proper Function Macro Do?
This macro will convert any range of cells that the user selects into the PROPER case if the cells that the user selects contain a text string. It capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. All other letters are converted to lowercase.
How Does The Macro Work?
This macro uses the WorksheetFunction.Proper Method. This method loops through the user selected range of cells and fixes capitalization issues, or converts the text strings to PROPER Case.
Starting The VBA Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Either by hitting ALT +F11 or
- selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
Before we begin to write any code you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook.
What’s the difference?.
Well, if you save the macro in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook. When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location, by default this macro workbook is named Personal.xlsb. This is a very useful macro. I will store this in my Personal Macro Workbook for future use.
If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Preparing To Write The Macro.
We need to start off the process by inserting a New Module. Do this by selecting the Personal.xlsbworkbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called the macro ProperCase. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines of code.
[stextbox id=’info’]
Sub ProperCase()
End Sub [/stextbox]
Using The For Each Loop.
The next few lines of code use the For Each Loop method to move sequentially through range of selected cells. The Proper function is applied to each text string. This process is repeated until all of the range has been evaluated.
[stextbox id=’info’]
For Each rng In Selection
rng.Value = Application.WorksheetFunction.Proper(rng.Value)
Next rng
[/stextbox]
Ending The Macro.
Finally, the code ends once all looping of cells has been completed with the “End Sub" piece of code. This was already entered into the module when the name of the macro was set.
[stextbox id=’info’]
End Sub
[/stextbox]
Let’s Test The Proper Function Code!
Well, its all written. Let’s go ahead and test the macro. This really is my favourite part!.
If you want more tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
How To Excel At Excel – Blog Posts.
