Wrong variable data type returned with VarType Excel VBA function-CodePudding

I had a simple question regarding the VarType function in Excel.

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 in question consists of the study ID, and is a positive integer which varies from 1 to n.

The problem I have with my function is that is does not seem to return the expected Integer data type.

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. Note that I have tested the field input with 2.0000 and 2, and both return 5.

The expected result when entering "2" into the field should be 2 (Integer) and not 5.

Please see the code of the custom function I have built below:

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 belows is supposed to display the correct 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

My question is as follows: How can I return the correct data type for the field in question (2, Integer) for my user defined function for the field in question instead of the current data type of 5 it currently returns?

Any help would be greatly appreciated!

CodePudding user response：

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.