Loop over range and find data-type

[Christiaan]

Hello everyone.

I am having trouble finding the datatype in a cell, using a function.
I have the sub to pass the argument into the function, the function itself is detecting the datatype (somehow it shows 0 as double, no decimals specified in the cell).
But in the subroutine the myCheck value remains empty.

What am I missing here? Please see the code below...

Sub FindTypeName()

Dim rng As Range
Dim myCheck As String

Set rng = Range("BG2:BG10")
    For Each cell In rng
        myVar = cell.Value
        If myVar = "" Then
            'Do nothing
            Else
            myCheck = Get_Var_Type(myVar)
            If myCheck = "String" Then cell.Value = ""
        End If
    Next
End Sub

   

Function Get_Var_Type(myVar) As String

If VarType(myVar) = vbNull Then
myCheck = ""
ElseIf VarType(myVar) = vbInteger Then
myCheck = "Integer"
ElseIf VarType(myVar) = vbLong Then
myCheck = "Long integer"
ElseIf VarType(myVar) = vbSingle Then
myCheck = "Single"
ElseIf VarType(myVar) = vbDouble Then
myCheck = "Double"
ElseIf VarType(myVar) = vbCurrency Then
myCheck = "Currency"
ElseIf VarType(myVar) = vbDate Then
myCheck = "Date"
ElseIf VarType(myVar) = vbString Then
myCheck = "String"
ElseIf VarType(myVar) = vbObject Then
myCheck = "Object"
ElseIf VarType(myVar) = vbError Then
myCheck = "Error"
ElseIf VarType(myVar) = vbBoolean Then
myCheck = "Boolean"
ElseIf VarType(myVar) = vbVariant Then
myCheck = "Variant"
ElseIf VarType(myVar) = vbDataObject Then
myCheck = "Data object"
ElseIf VarType(myVar) = vbDecimal Then myCheck = "Decimal"
ElseIf VarType(myVar) = vbByte Then
myCheck = "Byte"
ElseIf VarType(myVar) = vbUserDefinedType Then
myCheck = "Other"
ElseIf VarType(myVar) = vbArray Then
myCheck = "Array"
Else
myCheck = ""
End If

End Function

 

[Nine Zero]

I think you are missing the declaration of myVar in your DIMs

So it always blank therefore "Do Nothing" is called

 

[Christiaan]

Hey Nine Zero.

I tried setting myVar in the dim, as you suggested but that doesn't change anything.
The funny part is, that even without no myVar declaration in a dim, the function does see the data type, it is just not parsing it to the sub.

Any other ideas?

 

[Fluff]

Its because you're never setting a value for the function.
Everywhere you have myCheck inside the Function, replace it with the name of the function, ie Get_Var_Type

 

[Christiaan]

Its because you're never setting a value for the function.
Everywhere you have myCheck inside the Function, replace it with the name of the function, ie Get_Var_Type

Thanks Fluff, that did the trick indeed. I knew it had to be something fairly simple.

 

[Fluff]

Glad to help & thanks for the feedback