In my last VBA blog, I talked about data types, and the automatic type conversion feature in VBA. Today's topic is also about data types. This time, we'll be looking at the minimum and maximum values that you can store in your variables, depending on which data type you use.
Let me give you a scenario. I've developed an Excel application that allows the customer to add new orders to an Excel sheet. In this prototype, I bring up a user form which allows the user to choose the customer from a drop-down list, and then type in the order date. When the user clicks the button to add the order, I automatically generate the order number for them, by adding one to the last order number.
Sample Application
Here's the Excel sheet:
When the user clicks the "Add Order" button, this form appears:
When they click the "Add" button, I find the last order number on the worksheet, add one to it, and use that as the new order number. Here you can see the new order that I added:
The Error
In this scenario, the application worked great for a long time. Suddenly one day, it started throwing this error message:
Not a very helpful message, is it?
When I click the "Debug" button, I'm brought to this line of code:
It's a simple line of code, and appears to be well-formed. So what's the problem?
It's the data type I used for the "NewOrderNumber" variable. I've defined it as an integer. In VBA, each data type has specific upper and lower limits - the maximum and minimum values you can store in a variable. You can view the entire list here: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Locate "Integer" on this list, and you'll see that it has a range of -32,768 to 32,767:
You can put any number you like in your Integer variable as long as its between those two numbers. If you try to store a value outside of that range, you'll get the "Overflow" error.
Here's another example of this:
When I assign 32,767 to the variable, it works. When I assign 32,768, it fails.
A quick fix for this is the change the data type from "Integer" to "Long", which has a maximum value of over 2 billion. With this simple change, my VBA program will now work correctly, at least until the customer exceeds 2 billion orders!
A Free Lunch
As you can see, choosing the appropriate data type is important when setting up your variables. This exact situation once earned me a free lunch, when my manager offered to treat if I could find the issue with a program. Took me all of about 10 minutes to locate and correct the problem, including the time it took to explain the situation. Easy win!
Got a similar problem that you can't track down? Set up a one-on-one call with me, and I'll see if I can work the same magic on your program. If I can fix it in less than 15 minutes, no charge!
As always, reach out via email or phone if you’d like to discuss your VBA 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.
© North Port Solutions, LLC, 2023. All rights reserved.