VBA: Three Ways to Handle Text in Numeric Fields
If you’ve written VBA code to work on you Excel data, you’ve probably already discovered that bad data will cause you endless headaches. Case in point: you expect a given cell to contain a number, but there is text in it.
If your code attempts to use this value in calculations, you’re likely to get errors. Let’s look at an example.
As usual, I’m keeping this example simple. In this case, you can see that I’m taking the total number of absences, and dividing by the number of students to get the average.
Calculations in Code
Instead of calculating the average using a formula, let’s say that we’re making the calculation in VBA. Of course, in my over-simplified example, there’s no need for that. But there are many reasons why this may be necessary in your VBA code. You might want to save this data in a separate file, for example, or you may need the calculation for use in other calculations that the user does not need to see on the Excel sheet. Whatever the reason, here’s what the VBA code might look like:
This code works great as long as there are valid numbers in cells C3 and C4. But let’s see what happens when the data entry goes awry:
Here, the user has typed in a 0 for the number of students. Since division by zero is not valid, you’ll end up with an error message, as shown.
Here's another example of a data entry mistake:
This time, the user has typed some text (“days”) in addition to the number in cell C3. In this case, you get a Type Mismatch error.
Here's one that might surprise you:
It looks like the user has left “Total Number of Absences” blank, but that’s not the case. A blank would have been acceptable, and Excel would have treated it like a zero.
Instead, the user has typed in a space in the Number of Absences cell. Spaces are not the same as blanks. In fact, a space is considered text. For that reason, it can’t be used in calculations, resulting in the Type Mismatch error.
How do we fix this?
Your VBA code will need to handle both these scenarios, since they occur quite often. You’ve got three main options when it comes to dealing with them:
Train your users. Teach them that whenever they see one of these error messages, they need to click the “End” button, and then correct their data. There’s nothing wrong with this approach, as long as you have users who can follow your directions. Note that if they click the “Debug” button, they’ll end up looking at the VBA code, and you’re likely to get a support call.
Intercept the errors, and create your own. You can write some simple code that circumvents the built-in error messages, allowing you to create a message that is more relevant to the situation. An added benefit is that you won’t have that “Debug” button, which is useless to the typical user, and often becomes the source of a support call to the developer. I’ll show an example of this below.
Validate your data. With this option, you’ll add some simple validation code before you perform your calculations. This is the user-friendliest method, since you can specifically let the user know what you expect in each cell. I’ll show an example of this as well.
Adding an error handler to your VBA code is a simple step that allows you to intercept the built-in error message, and give a better one. In this example, I’ve implemented an error handler using four lines of VBA code:
Let’s break them all down.
On Error GoTo ErrHandler
This statement instructs VBA to skip to the section of code labeled “ErrHandler” when any error occurs. I chose “ErrHandler”, but you can use any valid VBA label. This label corresponds to one of the lines further down, which I’ll explain in a minute.
This is important! This instructs VBA to stop executing code if it hits this line without encountering an error. If you don’t include this statement, the code will continue, and the user will end up seeing your error message, even when no error has occurred.
This is a label, as evidenced by the semi-colon at the end. A label is not an executable statement. It’s just a placeholder to identify a specific location in your code. It has to match the “On Error GoTo” statement, except that here it’s followed by the semi-colon.
Msgbox “Enter numbers in cells C3 and C4”, vbOkOnly, “ERROR”
The Msgbox function displays a message to the user. I’ve specified the message, the buttons that will be displayed (the OK button only), and the title of the dialog box. When displayed, my message will look like this:
As you can see, there’s no “Debug” button here, so the user won’t end up looking at the VBA code. Also, I’ve included instructions to let the user know exactly what’s expected on the worksheet.
This is a very simple error handler, and in real life, I’d expand on this by specifically telling the user which cell (C3 or C4) is problematic. But this is a good start, and certainly better than the built-in error message.
As I mentioned, another great option is to validate the data you’ll be using in your calculations. I’ve added some simple code to check to make sure each cell contains a number, using the IsNumeric function:
I first check to see if cell C3 is numeric, and then I repeat that for cell C4. If either is not numeric, I display a message to the user, telling them exactly which cell is problematic, and how to fix it.
On cell C4, I also check to make sure it’s not zero, to avoid the divide-by-zero error.
On all three of those “If” statements”, I include an “exit sub”, so that the routine will not continue on to the calculations if I find a problem.
When I run the code, this is the error I get when the Number of Students is zero:
Taking the time to validate your data and trap errors is usually a worth-while investment when writing Excel applications. Both steps can increase user understanding and head off support calls for simple issues.
Of course, I’ve used extremely simple examples here, and your case is likely to be more complex. Don’t hesitate to reach out if you’d like help brainstorming ideas that will work for you.
Did you know that VBA considers a date to be a numeric value? True story. This has important implications for VBA programmers, making it easy to calculate due dates, aging, and other date-related calculations. In my next VBA blog, I’ll be taking a look at this feature. To be notified when that goes live, subscribe using the form below.
That’s all for today. As always, reach out to me if you’ve got questions or want to discuss your project. I’m looking to take on a couple of new clients in 2023. Hit me up to start a conversation.
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 email@example.com.
#northportsolutions #learningneverstops #smallbusiness #excel #microsoftexcel #msexcel #vba #excelvba
© North Port Solutions, LLC, 2023. All rights reserved.