How can I convert any Cell into an Integer if possible in VBA for Excel?

Question

I would like to create a function that will take as a parameter a Cell and return an Integer. If the conversion is not successful it should simply return 0, without throwing error messages to the user.

I don't care about the cells that contain float values, they could also return 0 since it doesn't look like integer. But text values like 00001234 should return 1234 and 12 34 should return 0.

Solution 1

How about this:

Option Explicit

Public Function ConvertToInteger(Cell As Range) As Integer
   On Error GoTo NOT_AN_INTEGER
   ConvertToInteger = CInt(Cell.Value)
   Exit Function
NOT_AN_INTEGER:
   ConvertToInteger = 0
End Function

Note, for example, that a value of 5.6 will return 6, though. If you want it to be 0 instead, you must check for it and do accordingly.

Solution 2

If the requirement is to return an integral number regardless of length of the input string, you could format your cell to have 0 decimal places and do something like below, which automatically promotes the data type to the precision required. Otherwise you would have to truncate the input value if you really just wanted a VBA integer.

Public Function ConvertToIntegral(Cell As Range) As Variant
   On Error GoTo catch
   Dim result As Variant
   result = Val(Cell.Value)
   ConvertToIntegral = result
   Exit Function
catch:
   ConvertToIntegral = 0
End Function