Last week, I posted a blog about using dates in your VBA code, either in Excel or Access. We saw that it’s quick and easy to perform calculations on dates, because they are stored as numbers behind the scenes.
Today’s topic is related. We’re going to talk about something called “Automatic Type Conversion”, and see how that impacts your VBA code.
If you’ve written much VBA code, you’re probably aware that you define your variables for use with a specific type of data. You might decide that one variable will contain text, while another will be used for dollar amounts, and a third will be for dates.
One of the cool things about VBA is that it will automatically convert data from one type to another, if need be. Why would that happen? Let’s see an example.
One thing we know for certain about humans is that they make mistakes. Let’s say you have an Excel application, and you’re asking the user to type a number into a cell, which you’ll then use in calculations. My spreadsheet might look like this:
I’m expecting the user to type a number into cells A2 and B2. I will then use VBA to do some math with them.
But again, because users are human, I know that they might make a mistake, and accidentally enter text into one of those cells. I’ve written some simple VBA code to demonstrate this, which will read the two numbers from the spreadsheet, multiply them, and put the result back on the sheet. It looks like this:
When I run the code, I’ll get an error if the user types text into one of the two cells:
This “Type Mismatch” error is the result of trying to read “one” from cell B2 into variable SecondNumber, which is defined as a numeric “double” data type.
To circumvent this, I change both of my variables to strings. That way I can read in any values from the spreadsheet, and then check to make sure they’re numeric:
Of course, there are many other ways to head off this problem, but this method works well, and will highlight the type conversion situation.
Now I’m working with two string variables, and you might think that the math won’t work. After all, I can’t multiply a text string by a text string. But Visual Basic, being a very friendly language, automatically converts variables to appropriate data types behind the scenes in this scenario. This is called Automatic Type Conversion. You’ll see here that the code works perfectly:
It doesn’t always work, though. Sometimes, VB gets confused about what you’re trying to do. Here’s what happens when I change the code to add instead of multiply:
What’s going on here?
Visual Basic only performs automatic type conversion when it believes its required. When I was doing multiplication, VB believed the only way to make my code work was to convert my variables to numbers, so it did that. But now that I’ve changed it “+”, VB has two choices. The “+” operator is used for both addition and concatenation. When VB executed this line of code, it found that the code worked perfectly for concatenation, so it went ahead and did that, instead of adding.
This is both a blessing and a curse. It’s great that VB automatically converts types when it needs to, but as you can see, you can get burned if you rely on it too heavily.
One easy solution is to manually convert your variables to numbers when you’re performing calculations on them, as shown here:
As you can see, VB now understands that I want to convert each variable to a number, and then use the “+” operator on them. Since they are both numbers, VB assumes that I want to add them, rather than concatenate.
Cool, right?
As you can see, VB’s type conversion feature is very convenient, but to make your app as resilient as possible, be sure that you’re always using numbers for calculations.
In my next VBA blog, I’ll continue with our data type conversation, and we’ll talk about maximum and minimum values for different data types. To be notified when that post goes live, fill out the subscription form below.
As always, reach out via email or phone if you’d like to discuss your Excel or Access project!
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, 2023. All rights reserved.