The Int Function in VBA: Convert Strings to Numbers
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.”
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.
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:
- 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.
- Decimals are permitted in the expression, but the return value will not contain the decimal part. Only the integer part will be returned.
- 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; 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 $" &amp;amp; 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.
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!