xlf | GetStrType function


GetStrType - about the function


Description: evaluate the data type (limited) of element in a delimited string

Syntax: GetStrType(Str)

Arguments: Str (required): the string element to evaluate


What data type is delimited string element? A function to return Date. Boolean, Double, Range, String. Function name - GetStrType - see code 1. Assumes that string in comma delimited.


1. GetStrType - the VBA code



Code 1: Function procedure GetStrType returns element type from delimited string 
Function GetStrType(Str As String) As Variant
' GetStrType - Is delimited string element a: Date, Boolean, Number, Address, or String?
Dim tmp As Variant

' 1. isDate ===
On Error Resume Next
    tmp = VBA.DateValue(Str)
    If VBA.Err.Number <> 13 Then
        GetStrType = "Date"
        Exit Function
    End If
On Error GoTo 0

' 2. isBoolean ===
On Error Resume Next
    If Not VBA.IsNumeric(Str) Then tmp = VBA.CBool(Str)
    If VBA.VarType(tmp) = vbBoolean Then
        GetStrType = "Boolean"
        Exit Function
    End If
On Error GoTo 0

' 3. isDouble ===
    tmp = VBA.Val(Str)
    If IsNumeric(Str) Then
        GetStrType = "Double"
        Exit Function
    End If

' 4. isRange ===
On Error Resume Next
    Set tmp = Range(Str)
    If TypeName(tmp) = "Range" Then
        GetStrType = "Range"
        Exit Function
    End If
On Error GoTo 0

' 5. isString ===
    GetStrType = "String"

End Function

EXERCISE



Write the GetStrType procedure using one If...Then...Else construct to replace the four If...Then statements.


 

2.1 - test individual element



Code 2: Sub procedure TestGetStrType demonstrating individual elements 
Private Sub TestGetStrType()
Dim Ans As String

    Ans = GetStrType("1-Jan-20")
    'Ans = GetStrType("123.45")
    'Ans = GetStrType("A1")
    'Ans = GetStrType("Data")
    'Ans = GetStrType("False")

Stop
End Sub

2.2 - test comma delimited string


Sample string elements include leading spaces. These are removed with the VBA Trim function.



Code 3: Sub procedure TestGetStrType demonstrating 5 element string 
Private Sub TestGetStrType2()
Dim inString As String
Dim varString As Variant
Dim elementType() As String
Dim i As Integer, ub As Integer

    inString = "1-Jan-20, 123.45, A1, Data, False"
    varString = VBA.Split(inString, ",")
    ub = UBound(varString)
    ReDim elementType(0 To ub)

    For i = 0 To ub
        elementType(i) = GetStrType(VBA.Trim(varString(i)))
    Next i

Stop
End Sub