VBA Integer Data Type Tutorial

VBA Integer Data TypeA VBA Integer Data Type typically stores counters, quantities or as an index within a loop. An Integer variable does not store fractional values, holds whole numbers from -32,768 to 32,767 and uses 4 bytes (16 bits) of memory.

Numerical values with a fractional component such as prices should not be stored in a VBA Integer variable because this variable type only stores whole numbers and will round fractions to the nearest whole number. If you need to store values that have decimal places, such as prices, a VBA Double Data Type is usually the recommended data type.

VBA Integer Data Type – Sample Code

The following code snippet shows the declaration of an VBA Integer data type:

Sub Demo_Integer()

Dim nProductCount As Integer

nProductCount = 1%

nProductCount = CInt(12.0)

End Sub

You can force a type conversion into an VBA Integer data  type using the VBA function CInt().

The type declaration character for an Integer is the percent sign (%). When  you use the % after the number this tells VBA that the number is an integer data type and will not leave VBA to guess as to the data type.

You will notice that I name my variables in a very specific way. This is what is known as “coding standards” which I use as best practices in all my SpreadSheet SuperStar content. The prefix for an Integer variable is “n”. After the prefix I recommend using a name that is as descriptive as possible. Here we know that “ProductCount” is counting the number of products. I have found that using coding standards to name variable to be much more effective than naming my “a” or “myvar” where I have no idea what these variables are intended to store.

VBA Integer Data Type – Common Errors

The first error that can occur with an Integer variable is a “Stack Overflow Error – 6”.

What is happening is that you are trying to assign a value to the variable that is greater than the minimum or maximum allowable value of the data type and VBA does not know what to do. The following lines of code will generate a stack overflow error:

Sub Create_Overflow()

Dim nRowCount as Integer

'/ Greater than an Integer's maximum value of 32,767

nRowCount = CInt(123456)

'/ Less than an Integer's minimum value of -32,768

nRowCount = CInt(-987654)

End Sub

There are two ways to avoid a stack overflow error.

The first way to avoid a stack overflow error is to use a VBA Long Data Type instead of an integer. This is in cases where you know that the value of the variable could exceed the minimum or maximum value of an Integer.

The second is to check the value to make sure it is valid before assigning it to a variable, in this case that the value is between the minimum and maximum value for the variable type. This would be a solution if you are having a user input values that should be between specified values that are valid for the data type. An example would be an input that is the number of days since the beginning of the year. This value is always going to be less than 365, so it will fit into an integer variable. However the user could make a mistake and input the number “45912” that could cause a stack overflow error.

The second problem that can occur is that an VBA Integer only stores whole numbers. When you assign a numerical value with fraction to an integer, VBA  rounds the number to the nearest whole number and the fractional portion is removed. An integer data type should never be used with financial monetary transactions because the fractional portion of the currency will be removed. An additional note is that the removal of the fractions is not an “run-time error”  that will stop the code and you will not be notified. It is technically a feature of the way VBA compiles and works seamlessly behind the scenes.

For example this code will strip off the fractional portion of the code:

Sub Demo_Integer_Rounding()

'/ NOTE: Never Use Integer with Prices,

'/ I’m Doing This To Show You What Not To Do

Dim nProductPrice As Integer

'/ Will Assign the value of 12% to nProductPrice

nProductPrice = CInt(12.1)

'/ Will Assign the value of 12% to nProductPrice

nProductPrice = CInt(12.5)

'/ Will Assign the value of 13% to nProductPrice

nProductPrice = CInt(12.6)

'/ Will Assign the value of 13% to nProductPrice

nProductPrice = CInt(12.9)

End Sub

One final note about an Integer: I have read posts on the internet that behind the scenes VBA converts all Integers to a Long and that using Long data types may be faster. I don’t fully understand this comment as I am still able to generate stack overflow errors with an integer data type with recent version of VBA. I mention this as something to think about.

, ,

No comments yet.

Leave a Reply