Last week, I wrote a blog post about creating a validation harness in Excel VBA. I laid out a structure you can add to your Excel file that allows you to add custom validation as users are entering data. Now that we’ve laid that framework, we can work on adding some useful validation.
Phone Numbers and Extensions
Today, we’re going to look at phone numbers and extensions. When setting up an Excel file, it’s common for people to create a single column that is used for both, as you see in this example:
We’ve got lots of variation in our phone numbers, and many of them include an extension. Extension is often at the end of the cell, but not always (see rows 5 and 6, which don’t have extensions, and row 8, which has a note after the extension).
We’re going to add some validation to our cell, so that every extension will appear in the same format. For today's discussion, we’re going to use this format:
999-888-7777 Ext. 1111
We'll include the phone number with dashes, followed by “Ext.”, and then the extension number. Now our task is to pick apart the number entered, regardless of the format used, to separate the phone number and the extension, and then to format it according to our rules.
Common Traits
Before I start writing VBA code for this, I’m going to analyze the contents of the phone number cells, and identify commonalities. This always a good idea, because it’ll help to solidify the direction your VBA code will take.
I’ve identified the following traits of phone numbers in my file:
The phone number appears first in each cell, and ranges from 10 to 14 characters long. Examples:
9786608833 (10 characters)
(978) 660-8833 (14 characters)
1-978-660-8833 (14 characters)
Phone number formats vary, and may include:
Dashes
Parenthesis
Spaces
If there is an extension:
After phone number, there is a space
After the space, there is usually text, including:
x
Ext
Ext.
That text may or may not be followed by a space
Next, we have the extension
Extension is usually – though not always – at the end of the string
The extension number itself (with digits only) ranges from 1 to 4 numerals
With extension, the maximum length of the phone number string is 24 characters, calculated as:
Phone: max 14 digits
1 Space after phone
Longest text: “ext.” – 4 digits
1 space after text
4-digit extension
Pseudocode
Now I’ll write some pseudocode that identifies the rules I’ll be coding. Pseudocode is a written description of the steps you’ll take, written in a manner that will emulate the VBA code. Put another way, it’s like coding, but English syntax instead of VBA syntax.
If the phone number cell contains less than 10 digits, give an error message: “Phone number is not valid”.
If the phone number cell contains more than 24 digits, give an error message: “Phone number is not valid”.
Remove “x”, “ext”, and “.” from the string
Because we’re going to have code later to fill in our “Ext.” text. For now, we’ll just get rid of whatever they typed in.
Check to see if there is a space after the 10th digit.
Because we know that the first 10 digits must contain the phone number. If there is another space after that, it must be the space before the extension
If there is not space after the 10th digit, there is no extension. Do nothing further.
If there is a space:
Create a variable called “PhoneNbr” that contains all text before the space.
Create a variable called “Ext” that contains all text after the space.
Trim spaces out of the variable.
If Ext is not numeric, display error “Extension is invalid”
Otherwise, set value of cell to concatenation of PhoneNbr, “ Ext. “ and Ext.
Add the Harness
Now I’m ready to add the validation code. I’ll start by saving my Excel file as a macro-enabled file, and then add my “harness” code, which I covered in a previous blog. My code looks like this:
This is my basic harness, which I can drop into any Excel worksheet module and use for validation. Now I’ll go through my pseudocode and add the appropriate VBA code.
Adding the VBA Code
To make it easy for me to remember what I want to code in VBA, I’ll copy & paste my pseudocode into VBA as comments:
Now I can fill in the code under each comment. Here’s the first one:
For this one, I trim the contents of the cell, in case the user typed in leading or trailing spaces. If the length of the trimmed string is less than 10, I display the error message. I then want to move back to the cell in question, so that the user can correct the problem. I do that with the “LastCellChanged.Actviate” method. However, before I do that, I set the ValidationInProgress flag, so that validation won’t be performed on the cell again before the user has the chance to fix the problem.
I will repeat this process to check for the 24-digit max:
Next, I’ll use a series of replace statements to get rid of the “x”, “ext” and periods, if there are any. While I’m at it, I’ll also get rid of any leading and trailing spaces.
Rather than making these changes directly in the cell, I’m going to create a variable for this. I didn’t have that in my pseudocode, but that’s OK. Pseudocode is just a guide. If you realize that you need changes while you’re writing the code, that’s fine.
Here are the replace statements, and the variable declaration:
Now I check to see if there is a space after the 10th digit of the string. I’m adding a variable, although there are other ways to write this code that wouldn’t require that. But when you’re learning, it’s easier to follow the code with a variable:
If you’re not familiar with “InStr”, it stands for “In String”. It checks to see if one text value resides inside another. In this case, we’re looking for a space inside CellValue. The first argument is the starting position. I’m telling it to start searching at the 11th character.
InStr returns 0 if the string you searching for isn’t found. In our case, that means that there is no extension. I use a simple “If” statement to exit the validation code in that case:
The next two lines of pseudocode dictate that I create two variables, so here are the declarations:
I then use the Left and Mid functions to extract the phone number (before the space) and the extension (after the space):
My next line of pseudocode told me to trim spaces out of the Ext variable, but I’ve already done that. Once again – I’m being flexible, and using my pseudocode as a guide, but adjusting as necessary.
I then check to make sure the extension is numeric:
And finally, I put all the pieces together, filling that value into the cell:
Here’s the completed validation procedure:
For the final test, I type in this:
And my code changes it to this:
There you have it! We now have code in place that ensures that all phone number entered into the sheet will have the same format, making our file easy on the eyes. Obviously, there's still work to do to clean up old values in the column, but going forward, we'll be in good shape. To read out one option for cleaning existing values, see my blog on cleaning Excel phone numbers using Access.
Although it looks like a lot of VBA code to a novice, it’s all pretty simple. The trick is to take it in pieces – one step and at time – and build on each, exactly as I did in this article.
Next week, I’ll be moving on to the Credit Limit column in my sample file. We’ll take a look at the mess that you can get into when you expect a cell to be numeric, but there’s text in it instead, and, of course, we’ll see how to handle that. If you’d like to be notified when that post goes live, subscribe using the form below.
Did this work for you? Share your experience using the Comments section, below.
As always, I’d to chat with you about your Excel or Access issues. Call me at 978-582-9550 or email kmartin@northportsoftware.com. I love helping my fellow small business owners, so I’ll be happy to answer quick questions if I can. If your issue requires more than that, we can set up a one-on-one work session, or I’ll prepare an estimate for you.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
#northportsolutions #learningneverstops #smallbusiness #excel #microsoftexcel #msexcel #vba #excelvba
© North Port Solutions, LLC, 2022. All rights reserved.