Wednesday, December 4, 2013

Integer Test (Function)

Being self-taught in VBA, I am certain there are functions I don't know and end up writing functions that already exist.  This may be one of those.

Function IsInteger(inpval) As Boolean
    IsInteger = False
    If IsNumeric(Trim(inpval)) Then
        inpval = CDbl(inpval)
        If CInt(inpval) = inpval Then IsInteger = True
    End If
End Function

And sometimes I need only positive integers, or negative

Function IsPositiveInteger(inpval) As Boolean
    IsPositiveInteger = False
    If IsInteger(inpval) Then
        inpval = CDbl(inpval)
        If Abs(inpval) = inpval And inpval <> 0 Then IsPositiveInteger = True
    End If
End Function

Function IsNegativeInteger(inpval) As Boolean
    IsNegativeInteger = False
    If IsInteger(inpval) Then
        inpval = CDbl(inpval)
        If Abs(inpval) > inpval And inpval <> 0 Then IsNegativeInteger = True
    End If
End Function

"So", one may ask, "Why not just declare an Integer-type variable or use Cint() with a lead-in On Error Goto <error section>?"  Here are what I think are obvious answers

  1. GoTos should be avoided.  (It's my Structured Programming background.)
  2. Generic error-handling makes me cringe.  (The "Something broke. We don't know what or why.  Just keep moving" seems sloppy.  I'll use it when developing a module to get over a problem until I can find a better way, but putting it into production seems to be asking for big trouble when the danged thang breaks!)
  3. In-code testing for each possible type of error seems a waste of time and effort.  (Testing for what works and doesn't is the right thing, and results in better code.  The above was tested for a variety of data types, including String.)
  4. This is reusable.  A single function instead of re-solving this problem over and over, aka I'm lazy.