I am a big fan of using named ranges in my VBA code. It makes referring to cells in formulas easy, and I can also helps in auditing the code. For instance, I can simply search for “RangeName” (including the quotes) to see if that particular range name is being used in my code.
Excel has a “Create From Selection” functionality that allows the user to Name Ranges using the Top/Bottom row or Left/Right Column labels as names. When you have a label that does not qualify as a Name, it automatically cleans it up and names the range. Click here to learn more about the rules to naming a range.
There are two things I do not like about that feature,
- First, I don’t get to see the ‘Final’ name on the sheet.
- Second, it replaces all the spaces with underscores.
It is a good idea to have the Actual Named range visible in a sheet because it helps users to identify the range without having to dive into the formula bar to see those brightly coloured ‘boxes’. Replacing the spaces with underscores makes it incredibly difficult for the users to type in the range names into their formulae (and Code). Imagine having to reach for the ‘SHIFT’ button a billion times.
For this reason, I am a patron of the Capitalize-Each-Word-And-Ditch-The-Space rule. Capitalizing makes it easier for the user to identify each word. And since Excel names are not case sensitive, the user can just type in the entire name in lowercase while using them in formulas. Excel automatically converts them into the capitalized form.
The quickest way to accomplish this is the remove all the “illegal” characters (by using the Like operator in a loop); convert the string into proper case (using the StrConv() function) and remove all the spaces (using the Replace() function). However, there is one tiny problem with this method that ticks me off. It converts some of the uppercase letters that the user intentionally typed in, to lowercase. For instance, if you planned to use the label ‘DOB’ for ‘Date of Birth’, the proper case method will change it to ‘Dob’. For some people with OCD (like me), this can be a deal breaker.
After I got introduced to Regular Expression for VBA, I realized I can fix this. Regular expression is a very powerful tool, but its implementation in VBA is not complete. RegExp for VBA does not have a few features like changing case for groups. I had to get creative, I introduced a space before every capital letter, converted it into proper case, and finally got rid of all the spaces. I am very proud for having thought of this by the way, do let me know if there is another way of accomplishing this.
The ‘CreateName’ function takes a string for an input and returns the “Cleaned-up” name:
'========================================================================== 'Function to clean up Labels to be used as Range Names 'Author : Ejaz Ahmed - email@example.com 'Date: 02 February 2014 'Website : https://strugglingtoexcel.wordpress.com/ '========================================================================== Function CreateName(ByVal WhichString As String) As Variant Dim strTemp As String 'Keep only Letters, Numbers, Underscores and Spaces strTemp = RegExpReplace(WhichString, "[^a-z^A-Z^0-9^\s^_]", _ vbNullString, True) 'Enter Spaces before capitals strTemp = RegExpReplace(strTemp, "([A-Z])", " $1", True) 'Insert space after underscores, to capitalize letters after 'underscore, when converted to proper case 'Remember that Excel's Proper Function is different from 'using StrConv with vbProperCase. strTemp = Replace(strTemp, "_", "_ ") 'Convert to ProperCase strTemp = StrConv(strTemp, vbProperCase) 'Remove all Spaces strTemp = Replace(strTemp, " ", "") 'Use trim to remove any trailing spaces and output to cell strTemp = Trim(strTemp) 'Names Cannot have a Number for the first character If IsNumeric(Left(strTemp, 1)) Then strTemp = "_" & strTemp 'Names Cannot have more than 255 Characters CreateName = Left(strTemp, 255) End Function
In one of my previous posts, I published a simple function to simplify the use of Regular Expressions. It is required for the ‘CreateName’ function to work. The post also has information about how to add a reference to “Microsoft VBScript Regular Expressions 5.5” in your Visual Basics Project, which is needed for RegExp to work.
'========================================================================== 'Wrapper function to impliment the Replace method of the RegExp object 'Author : Ejaz Ahmed - firstname.lastname@example.org 'Date: 08 December 2013 'Website : https://strugglingtoexcel.wordpress.com/ '========================================================================== Function RegExpReplace(ByVal WhichString As String, _ ByVal Pattern As String, _ ByVal ReplaceWith As String, _ Optional ByVal IsGlobal As Boolean = True, _ Optional ByVal IsCaseSensitive As Boolean = True) _ As String 'Declaring the object Dim objRegExp As Object 'Initializing an Instance Set objRegExp = CreateObject("vbscript.regexp") 'Setting the Properties objRegExp.Global = IsGlobal objRegExp.Pattern = Pattern objRegExp.IgnoreCase = Not IsCaseSensitive 'Execute the Replace Method RegExpReplace = objRegExp.Replace(WhichString, ReplaceWith) End Function
You may use a Cover macro to clean up all the cells in a selection:
'Cover Macro Sub CoverMacro() Dim rngEach As Range For Each rngEach In Application.Selection rngEach.Value = CreateName(rngEach.Value) Next rngEach End Sub
Occasionally, when I create a spreadsheet application, I start of by typing out the Range names, so I can create a working prototype quickly. Later, when I am adding in the final touches, I need to add descriptions for each of the range names. Basically reverse the process. The following function can be used to create “Descriptions” from range names.
'========================================================================== 'Function to reverse what CreateName does. 'Author : Ejaz Ahmed - email@example.com 'Date: 02 February 2014 'Website : https://strugglingtoexcel.wordpress.com/ '========================================================================== Function LabelFromName(ByVal WhichString As String) As Variant Dim strTemp As String 'Add a space between lower case and non lower case letters strTemp = RegExpReplace(WhichString, "([a-z])([^a-z])", "$1 $2", True, True) 'Replace Underscores with spaces strTemp = Replace(strTemp, "_", " ") 'Remove multiple spaces strTemp = RegExpReplace(strTemp, "(\s)(\s+)", "$1", True) 'Use trim to remove any trailing spaces and output to cell LabelFromName = Trim(strTemp) End Function