Checking datatype in excel VBA-CodePudding

I wrote a code that checks whether the entered data is numeric with the isNumeric function. Now i want to specify and check whether it is an Integer. As far as i know, there is no function like isInteger. How can I check the datatype?

I posted a snippet of the code below, I hope it makes sense like this. If not please let me know.

Thank you for your help!

Sub CheckColumnsHardwareDefinition()


 
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Hardware Definition") 
Dim Target As Range
Dim Target2 As Range
Dim lr As Long
Dim lr2 As Long


Dim DblLengthMin As Double
Dim DblLengthMax As Double
Dim DblWeightMin As Double
Dim DblWeightMax As Double

Dim dynamicArray1() As String
Dim dynamicArray2() As String

Dim f1 As Integer
Dim f2 As Integer
f1 = 0
f2 = 0

DblLengthMax = 20000
DblLengthMin = 5
DblWeightMin = 0.0001
DblWeightMax = 10000


lr3 = Application.WorksheetFunction.Max( _
        ws.Range("A" & ws.Rows.Count).End(xlUp).Row, _
        ws.Range("B" & ws.Rows.Count).End(xlUp).Row, _
        ws.Range("C" & ws.Rows.Count).End(xlUp).Row, _
        ws.Range("D" & ws.Rows.Count).End(xlUp).Row, _
        ws.Range("E" & ws.Rows.Count).End(xlUp).Row, _
        ws.Range("F" & ws.Rows.Count).End(xlUp).Row)

For Each Target3 In Range("A2:F" & lr3)

    If IsEmpty(Target3) Then
    Target3.Interior.ColorIndex = 8
    End If
    
Next Target3

lr = Application.WorksheetFunction.Max( _
        ws.Range("C" & ws.Rows.Count).End(xlUp).Row, _
        ws.Range("D" & ws.Rows.Count).End(xlUp).Row, _
        ws.Range("E" & ws.Rows.Count).End(xlUp).Row)
        
        
For Each Target In Range("C2:E" & lr)
    If **Not IsNumeric(Target)** Then
    
     f1 = f1   1
     Target.Interior.ColorIndex = 3
    ReDim Preserve dynamicArray1(0 To f1)
  
          dynamicArray1(f1) = "Row " & Target.Row & " Column " & Target.Column & " wrong 
          entry: " & Target.Value
    End If
    
    If **IsNumeric(Target)** And Target.Value > DblLengthMax Or Target.Value < 
    DblLengthMin 
    Then
 
    f2 = f2   1
 
    Target.Interior.ColorIndex = 46
    
    ReDim Preserve dynamicArray2(0 To f2)
         dynamicArray2(f2) = "Row " & Target.Row & " Column " & Target.Column & " wrong 
         entry: " & Target.Value
          
    End If
    

Next Target

       Inhalt1 = Join(dynamicArray1, vbCrLf)
       MsgBox ("Wrong datatype! " & vbCrLf & vbCrLf & f1 & " Datatype Errors (marked 
       red)" & vbCrLf & "Only numbers can be entered. Check again" & vbCrLf & Inhalt1)

       Inhalt2 = Join(dynamicArray2, vbCrLf)
       MsgBox ("Entries out of range!" & vbCrLf & vbCrLf & f2 & " Range errors (marked 
       orange)" & vbCrLf & "The value is out of range. Check for unit [mm] " & vbCrLf & 
       Inhalt2)
    

End Sub

CodePudding user response：

Let's take advantage of the "internal" casting of VBA

 Function isInteger(val As Variant) As Boolean

    Dim i As Integer

    On Error GoTo EH
    
    i = CInt(val)  
    
    If i = val Then  ' check if it was cut or not
        isInteger = True
    Else
        isInteger = False
    End If
    
    Exit Function

EH:
    isInteger = False

End Function

As i was declared as integer i=val will cause an overflow and therefore the result is FALSE for 33000. If you do not want that you have to declare i as long and use CLng()

A short version would look like that

Function isInteger(val As Variant) As Boolean

    On Error GoTo EH
    isInteger = (val = CInt(val))
    Exit Function

EH:

End Function

CodePudding user response：

You can use VarType() or/and TypeName(). In this case, you need to take into account that any number taken from an Excel sheet cell has the Double type. See the example below.

Sub test1()
    Dim i As Integer, v As Variant, s As Variant
    
    i = 100
    Debug.Print VarType(i), TypeName(i)
    
    v = 100
    Debug.Print VarType(v), TypeName(v)
    
    ' any numeric in cell is Double
    ActiveSheet.Cells(1, 1) = 100
    s = ActiveSheet.Cells(1, 1)
    Debug.Print VarType(s), TypeName(s)
End Sub

Output:

 2            Integer
 2            Integer
 5            Double