In recent posts, I’ve been talking about validating your Excel data using VBA. I talked about creating a validation harness, which you can leverage to validate any Excel data. Then I covered some phone number validations you might add to that harness.
Today I’m going to talk about using that harness to automatically replace text that a user may enter. I consider this part of data validation, because you can use it to head off data issues. Here’s an example:
Converting "K" to a Number
See the “75K” in my Credit Limit column? I’d like this column to contain numbers, so that I can use it in formulas. I’ve already talked about using Excel’s data validation features to do this. Now I’d like to look at another alternative. In this case, whenever the user types a “K” in this column, I’ll change that “K” to “000”, which will convert the value they enter into thousands. For example, if they type in “75K”, I’ll change that to “75000”.
To implement this code, I’ll save my file as a macro-enabled workbook, and then add the validation harness, which looks like this:
Now I add a simple line of code to replace a “K” with “000”:
To test this, I type “25K” in a cell in column D. The code automatically changes that to “25000”. Play the video below to see that:
A Few Other Fixes
While I’m in there, I’ll make a couple of other improvements. I’ll replace “TBD” with “Pending”, which is our preferred term for a credit limit that has not yet been established; and I’ll remove any spaces in the column. After that, I’ll check to make sure the field is numeric, and throw an error message if not. I’ll also set the background color of the cell to visually remind the user that it needs attention.
When I test, you can see that these validations now result in values that are more consistent. Here's a video that shows all of the fixes:
What's Next?
In this series, I’ve demonstrated several methods of keeping your Excel data clean and consistent using VBA. Next week, I’ll wrap up this series by talking about finding nearly identical entries in your list. For example, if you’ve already got “ABC Company” on your list, “ABC Co.” is probably a duplicate. We’ll take a look at how to detect this with VBA code. If you’d like to be notified when that post goes live, subscribe using the form below.
Have you tried this? Let me know in the comments if you’ve done something similar.
As always, reach out to me if you’d like to discuss a VBA project, either in Excel or in Access. I’m happy to work alongside you, allowing you to utilize your own skills, and filling in the blanks with the more complex issues. You can reach me via phone (978-582-9550) or email (kmartin@northportsoftware.com).
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.
Comments