The Int Function in VBA: Convert Strings to Numbers

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

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


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.


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 " &amp;amp; int1
End Sub

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

S.noInput valuesOutput values
156The extracted numeric value is 56
2I’m 30Datatype mismatch error
3i20Datatype mismatch error
434.3The extracted numeric value is 34
599.9The extracted numeric value is 99
645,6The extracted numeric value is 456
78*7Datatype 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 $" &amp;amp;amp; int12
End Sub
S.noInput valuesOutput values
1-56You owe the bank $ -56
2-I’m 30Datatype mismatch error
3-i20Datatype mismatch error
4-34.3You owe the bank $ -35
5-99.9You owe the bank $  -100
6-45,6You owe the bank $ -456
7-8*7Datatype 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.

Program with date and time together in a column.

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

End Sub
Date and time are now in two separate columns.

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

Using the format cells🡪 time option to change the format
Date and time in two columns in the correct format.


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!

Leave a Reply

Your email address will not be published.