Returning different types from a function

Question

So I have a function I wrote that checks if the string given to it is true, false, or something else. If it's True, I want it to return the boolean true, if it's false, I want to return the boolean false, and if it's anything else, I want it to keep returning the string.

Public Function ConvertToBoolean(InputString As String) As Variant
Dim TempResults As Variant


    If InputString = "True" Then
        TempResults = True
    ElseIf InputString = "False" Then
        TempResults = False
    Else
        TempResults = InputString
    End If

    ConvertToBoolean = TempResults


End Function

However, it keeps defaulting to strings. I could dim it as boolean, but then regular strings get returned as TRUE, instead of as a string.

How can I force/make the function type conversion work? Probably need it inside of the if statement, but I have no idea how to do that.

Can't dim inside of the if (Although perhaps I could re-dim? Going to check)

Edit: Further testing is showing that most of the time, a boolean is returned - but once in awhile it's returning a string instead, with all of the associated problems that I was trying to fix in the first place. So my question remains the same:

How can I explicitly declare a function (Or a variable) to return one given type or another?

Edit 2: Since people don't seem to believe that code doesn't always work perfect or the same every time:

Answer 1

For the sake of future readers:

The root issue was case sensitivity, solved by ensuring compares are by Text and not Binary

Here's the evidence

Note that Boolean values display UPPER CASE, center aligned. String values in orignal case, left aligned, numbers right aligned.

Here's a version, optimised to handle Errors, numbers, and self contained for Text based tests (this was not the version used in the tests below, as it contains additional features)

Public Function ConvertToBoolean(Val As Variant) As Variant
    If IsError(Val) Then
        ConvertToBoolean = Val
    ElseIf IsEmpty(Val) Then
        ConvertToBoolean = vbNullString
    ElseIf StrComp(Trim$(Val), "True", vbTextCompare) = 0 Then
        ConvertToBoolean = True
    ElseIf StrComp(Trim$(Val), "False", vbTextCompare) = 0 Then
        ConvertToBoolean = False
    Else
        ConvertToBoolean = Val
    End If
End Function

---

_{Tests were based on this code}

Option Explicit
Option Compare Text

Public Function ConvertToBoolean1a(InputString As String) As Variant
    Dim TempResults As Variant

    If InputString = "True" Then
        TempResults = True
    ElseIf InputString = "False" Then
        TempResults = False
    Else
        TempResults = InputString
    End If

    ConvertToBoolean1a = TempResults
End Function

Public Function ConvertToBoolean2a(InputString As String) As Variant
    Dim IsBoolean As Boolean
    Dim ReturnString As String
    Dim ReturnBoolean As Boolean

    If InputString = "True" Then
        IsBoolean = True
        ReturnBoolean = True
    ElseIf InputString = "False" Then
        IsBoolean = True
        ReturnBoolean = False
    Else
        IsBoolean = False
        ReturnString = InputString
    End If

    If IsBoolean Then
        ConvertToBoolean2a = ReturnBoolean
    Else
        ConvertToBoolean2a = ReturnString
    End If
End Function

_{And in a separate module (I've modified Davids version slightly to give a fair comparison. His original version handles leading/trailing spaces, but thats an additional feature and costs time)}

Option Explicit

Public Function ConvertToBoolean1b(InputString As String) As Variant
    Dim TempResults As Variant

    If InputString = "True" Then
        TempResults = True
    ElseIf InputString = "False" Then
        TempResults = False
    Else
        TempResults = InputString
    End If

    ConvertToBoolean1b = TempResults
End Function

Public Function ConvertToBoolean2b(InputString As String) As Variant
    Dim IsBoolean As Boolean
    Dim ReturnString As String
    Dim ReturnBoolean As Boolean

    If InputString = "True" Then
        IsBoolean = True
        ReturnBoolean = True
    ElseIf InputString = "False" Then
        IsBoolean = True
        ReturnBoolean = False
    Else
        IsBoolean = False
        ReturnString = InputString
    End If

    If IsBoolean Then
        ConvertToBoolean2b = ReturnBoolean
    Else
        ConvertToBoolean2b = ReturnString
    End If
End Function

Public Function ConvertToBoolean3(Val As String) As Variant
    If StrComp(Val, "True", vbTextCompare) = 0 Then
        ConvertToBoolean3 = True
    ElseIf StrComp(Val, "False", vbTextCompare) = 0 Then
        ConvertToBoolean3 = False
    Else
        ConvertToBoolean3 = Val
    End If
End Function


Public Function ConvertToBoolean4(Val As Variant) As Variant
    Dim s As String
    s = UCase$(Val)
    Select Case s
        Case "TRUE", "FALSE"
            ConvertToBoolean4 = CBool(s)
        Case Else
            ConvertToBoolean4 = Val
    End Select
End Function

_{I've also run a speed test to compare performance, using this code}

Sub Test()
    Dim n As Long, i As Long, j As Long
    Dim T1 As Single, T2 As Single, T3 As Single, T4 As Single
    Dim res As Variant

    Dim Dat2(1 To 5) As String
    Dat2(1) = "true"
    Dat2(2) = "false"
    Dat2(3) = "   true"
    Dat2(4) = "zx"
    Dat2(5) = ""
    Dim cl As Range

    Application.Calculation = xlCalculationManual

    n = 1000000

    T1 = Timer()
    For i = 1 To n
        For j = 1 To 5
            res = ConvertToBoolean1a(Dat2(j))
        Next
    Next
    T2 = Timer()

    T3 = Timer()
    For i = 1 To n
        For j = 1 To 5
            res = ConvertToBoolean2a(Dat2(j))
        Next
    Next
    T4 = Timer()

    'Verion 1a OP 1
    [F22] = (T2 - T1) / n * 1000000#
    'Verion 2a OP 2
    [H22] = (T4 - T3) / n * 1000000#


    n = n / 100

    Set cl = [f10:f15]
    T1 = Timer()
    For i = 1 To n
        cl.Calculate
    Next
    T2 = Timer()

    Set cl = [h10:h15]
    T3 = Timer()
    For i = 1 To n
        cl.Calculate
    Next
    T4 = Timer()

    'Verion 1a OP 1 UDF
    [F23] = (T2 - T1) / n * 1000000#
    'Verion 2a OP 1 UDF
    [H23] = (T4 - T3) / n * 1000000#

End Sub

_And

Sub Test2()
    Dim n As Long, i As Long, j As Long
    Dim T1 As Single, T2 As Single, T3 As Single, T4 As Single
    Dim res As Variant

    Dim Dat2(1 To 5) As String
    Dat2(1) = "true"
    Dat2(2) = "false"
    Dat2(3) = "   true"
    Dat2(4) = "zx"
    Dat2(5) = ""
    Dim cl As Range

    Application.Calculation = xlCalculationManual

    n = 1000000
    T1 = Timer()
    For i = 1 To n
        For j = 1 To 5
            res = ConvertToBoolean3(Dat2(j))
        Next
    Next
    T2 = Timer()

    T3 = Timer()
    For i = 1 To n
        For j = 1 To 5
            res = ConvertToBoolean4(Dat2(j))
        Next
    Next
    T4 = Timer()

    'Verion 3 mine
    [J22] = (T2 - T1) / n * 1000000#
    'Verion 4 david
    [K22] = (T4 - T3) / n * 1000000#


    n = n / 100
    Set cl = [j10:j15]
    T1 = Timer()
    For i = 1 To n
        cl.Calculate
    Next
    T2 = Timer()

    Set cl = [K10:K15]
    T3 = Timer()
    For i = 1 To n
        cl.Calculate
    Next
    T4 = Timer()

    'Verion 3 mine  UDF
    [J23] = (T2 - T1) / n * 1000000#
    'Verion 4 david UDF
    [K23] = (T4 - T3) / n * 1000000#


End Sub

Answer 2

Unless I'm missing something:

Public Function ConvertToBoolean(InputString As String) As Variant
Dim s As String
Dim ret
    s = Trim$(UCase$(InputString))
    Select Case s
        Case "TRUE", "FALSE"
            ret = CBool(s)
        Case Else
            ret = InputString
    End Select
    ConvertToBoolean = ret
End Function

Answer 3

So many thanks to @MathieuGuindon for explaining to me exactly how variants worked, and pointed me on the right path.

With that, my code is now:

Option Compare Text
Public Function ConvertToBoolean(InputString As String) As Variant
Dim IsBoolean As Boolean
Dim ReturnString As String
Dim ReturnBoolean As Boolean


If InputString = "True" Then
    IsBoolean = True
    ReturnBoolean = True
ElseIf InputString = "False" Then
    IsBoolean = True
    ReturnBoolean = False
Else
    IsBoolean = False
    ReturnString = InputString
End If

If IsBoolean Then
    ConvertToBoolean = ReturnBoolean
Else
    ConvertToBoolean = ReturnString
End If


End Function

Of course, as @MathieuGuindon mentioned, if you want to make this code ACTUALLY WORK, (as opposed to being a "force the type" exercise), you'll want to add StrComp() (Preferable), or Option Compare Text (Less preferable, apparently buggy)