How to return variable data type?

Question

I am writing a macro in Excel that uses a custom function to verify the data type of a field in a form, in order to validate user input later on. The field consists of the study ID, and is a positive integer which varies from 1 to n.

When I enter the number in the field as an Integer (for example, 2) my function returns 5 (VbDouble), which according to this link is a Double-precision floating-point number.
I tested the field input with 2.0000 and 2, and both return 5.

The expected result when entering "2" into the field is 2 (Integer).

Function InputCheck(FieldValue As Variant) As Integer
    Dim TypeCheck As Integer
    TypeCheck = VarType(FieldValue)
    Select Case TypeCheck
    Case 2 'Integer
        InputCheck = 2
    Case 3 'Long integer
        InputCheck = 3
    Case 4 'Single-precision floating-point number
        InputCheck = 4
    Case 5 'Double-precision floating-point number
        InputCheck = 5
    End Select
End Function

In my Sub, the code below is supposed to display data type (Integer).

If InputCheck(.Cells(iRow, 2).Value) = 2 Then
   MsgBox "Integer"
ElseIf InputCheck(.Cells(iRow, 2).Value) = 3 Then
   MsgBox "Long integer"
ElseIf InputCheck(.Cells(iRow, 2).Value) = 4 Then
   MsgBox "Single-precision floating-point number"
ElseIf InputCheck(.Cells(iRow, 2).Value) = 5 Then
   MsgBox "Double-precision floating-point number"
End If

How can I return the data type (2, Integer)?

Answer 1

Your VarType is just going to read what type you defined the variable. I changed your as integer to as string because I think that removes a step, but you can easily go back to old way. You can also add more conditions if you need a more specific answer.

Option Explicit

Sub Test()
    MsgBox InputCheck(InputBox("Enter Data to test", "DataTypeTest", "")), vbOKOnly, "DataTypeTest"
End Sub

Function InputCheck(FieldValue) As String
    If IsNumeric(FieldValue) Then
        If CDbl(FieldValue) = Round(FieldValue, 0) Then
            InputCheck = "Integer" '(Or long or whatever...)
            Exit Function
        Else
            InputCheck = "Decimal" '(or double.. or whatever you want to call it)
            Exit Function
        End If
    Else
        If IsDate(FieldValue) Then
            InputCheck = "Date"
            Exit Function
        Else
            InputCheck = "String"
            Exit Function
        End If
    End If
End Function

Test with msgbox sub.