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
- This example was developed in Office 365 version 2009.
- Published: 28 October 2020
- Revised: Saturday 25th of February 2023 - 10:13 AM, [Australian Eastern Standard Time (EST)]