In order to convert a string to an integer in VBA, first, we need to check whether the string can be converted. If not it will return a different string.
In order to check whether the string is a numerical value, we will use the ISNUMERIC Function. Then we will use Cint to convert the string to an integer.
Let’s take a look at the following example. You have a list of values. There are numerical values, numbers formatted as strings, blank cells, and non-numerical strings.
This example shows, in column B how the values are converted. In order to insert the function, press Alt + F11. A new window will appear. Right-click on the VBA project and insert a new module.
Inside this module insert the following function.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Function ConvertString(myString) Dim finalNumber As Variant If IsNumeric(myString) Then If IsEmpty(myString) Then finalNumber = "-" Else finalNumber = CInt(myString) End If Else finalNumber = "-" End If ConvertString = finalNumber End Function |
Now, you can insert this function into an Excel worksheet.
Code explanation
3. The function checks whether the value is a numeric value,
4. An empty cell is considered a numeric value, so it will return 0. There is a second if there because we want to return a dash instead of 0,
7. If the value is not numeric, convert a string to an integer.
13. The value is returned and displayed inside a cell.
Convert string to int for a selected range
This code is a bit different than the last one. This time we will use a Sub, instead of a function. It works the way that you select the cells you want to convert and execute the code.
This is an example.
Insert this code, select the cells you want to convert, and run the code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Sub ConvertStringSub() Dim finalNumber As Variant For Each cell In Selection If IsNumeric(cell) Then If IsEmpty(cell) Then finalNumber = "-" Else finalNumber = CInt(cell) End If Else finalNumber = "-" End If With cell .Value = finalNumber .HorizontalAlignment = xlRight End With Next cell End Sub |