Integer Datatype

In Microsoft VBA, variant is a common datatype. It can be used to store strings or integers or even dates. Integer is a specific datatype which can be used to store numbers of a specific length. The bigger version of an integer is called “long.”

For example:

Dim int_age as integer
Int_age=45

This code means that the int_age variable is declared to store data of integer type and it is allocated a value of “45.”

The Integer Function

VBA offers a function called “INT” which can be used to extract the integer portion of a string or expression. 

Syntax:

Int(< expression >)

Where “Int” is the name of the function and expression is the string expression that should contain only numeric values without spaces or any numeric values except decimals.

Points to note:

1. The string parameter should contain only numeric values, though the string datatype permits other characters. If not, there will be a runtime error indicating a mismatch in datatype.
2. Decimals are permitted in the expression, but the return value will not contain the decimal part. Only the integer part will be returned.
3. Commas are also permitted but are ignored in the extraction to integer form.

Examples

Displaying User-Entered Number in Integer Form

This program receives input from the user in the form of a string. Then using the Int function, its integer part is extracted and stored in a variable of integer datatype. Then the integer is displayed.

Please run the program several times with a variety of input values to see the runtime errors and output values.

Sub int_demo()

'declare variables that are required for this program
Dim str1 As String
Dim int1 As Integer

' receive an input
str1 = InputBox(" Enter some text ")

'Convert it to an integer
int1 = Int(str1)

' display the extracted integer
MsgBox "The extracted numeric value is " & int1
End Sub

Here’s what happens when you run it with various input and output values:

S.no	Input values	Output values
1	56	The extracted numeric value is 56
2	I’m 30	Datatype mismatch error
3	i20	Datatype mismatch error
4	34.3	The extracted numeric value is 34
5	99.9	The extracted numeric value is 99
6	45,6	The extracted numeric value is 456
7	8*7	Datatype mismatch error

In this example you can see that positive numbers (numbers without a (-) sign) will be rounded down through the Int function.

Example of the Int Function with a Negative Number

If you use a negative number, the Int function will round down the decimal number to the nearest whole number.

Here is a similar program which will display the integer value of a negative number that is input:

Sub int_demo2()

'declare all variables that are required for the	 program
Dim str12 As String
Dim int12 As Integer

' receive an input
str12 = InputBox(" Enter the loan amount with a ‘-‘ in front ")

'Convert it to an integer
int12 = Int(str12)

' display the extracted integer
MsgBox "You owe the bank $" & int12
End Sub

S.no	Input values	Output values
1	-56	You owe the bank $ -56
2	-I’m 30	Datatype mismatch error
3	-i20	Datatype mismatch error
4	-34.3	You owe the bank $ -35
5	-99.9	You owe the bank $  -100
6	-45,6	You owe the bank $ -456
7	-8*7	Datatype mismatch error

In the example, because of the “-“ ( the negative sign), the value is rounded up/negatively rounded down. Ex: -10.99 is greater than -11.

Extracting Date and Time Separately From a Datetime Value

In this program, I have date and time together in a column. I have the need to separate them into two columns from one to hold only the date and the other to hold only the time.

Here goes my program:

Sub INT_Example1()

' declare required variables
  Dim y As Integer

' for loop to iterate through each row - first 3 columns
  For y = 2 To 16
    ' Extracting date to the second column using the int function
    Cells(y, 2).Value = Int(Cells(y, 1).Value)
  
    ' Extracting time to the third column
    Cells(y, 3).Value = Cells(y, 1).Value - Cells(y, 2).Value
    
  Next

End Sub

Now as, we see the time is not in the required format. So, we can use format cells🡪 time option to change the format.

Conclusion

The Int function is helpful for us to use in intelligent extraction of data from strings that hold a variety of essential information. And a final warning — don’t forget that macros once run to work on your data, cannot be undone!