VBA Handling multiple custom datatype possibilities

Question

I have done some research and haven't found any similar question.

I have a VBA macro that imports a .CSV file containing telegrams sent by a device.

In the end of this macro, I want to create a graph with the time elapsed on the x-axis and the value corresponding to the telegram.

The issue is that this value can be of different types: hexadecimal, boolean, integer... And that they don't respect the standard Excel number format, which means that they can't be used to create a graph. Here are some examples (with " around the value to show its start and end) :

• hexadecimal : "A7 C8"
• Boolean : "$00" or ""$01"
• Percentage : "$30"

And here is an example of data, with custom time format and boolean value

Here is my related code so far, where I try to convert into a custom type then convert back to numeric to get a common number datatype :

If wsRes.Range("R1").Value Like "$##" Then
    wsRes.Range("R1:R" & plotLine).NumberFormat = "$##"
    wsRes.Range("R1:R" & plotLine).NumberFormat = General
End If

If wsRes.Range("R1").Value Like "??[ ]??" Then
    Dim valArray(1) As String
    For i = 1 To plotLine Step 1
        valArray = Split(wsRes.Range("R" & i), " ")
        wsRes.Range("R" & i).Value = ToInt32(valArray(0) + valArray(1), 16)
        wsRes.Range("" & i).NumberFormat = General
    Next i
End If

I haven't been able to test it with hexa yet, but the conversion trick doesn't work with percentage/boolean

EDIT :

First, thank you for your answers.

Here is my final code for anyone's interested, adapted from Vityata's.

This method will allow to easily add other datatypes if needed.

Sub TestMe()
    Dim RangeData as String
    Set wsRes = ActiveWorkbook.Sheets("Results")

    For i = 1 To plotLine Step 1  'plotLine is the last line on which I have data
        DetectType wsRes.Range("R" & i).Value, i
    Next i

    RangeData = "Q1:R" & plotLine
    CreateGraph RangeData 'Call My sub creating the graph
End Sub



Public Sub DetectType(str As String, i As Integer)

    Select Case True
        Case wsRes.Range("R" & i).Value Like "??[ ]??"
            wsRes.Range("R" & i).Value = HexValue(str)

        Case wsRes.Range("R" & i).Value Like "?##"
            wsRes.Range("R" & i).Value = DecValue(str)

        Case Else
            MsgBox "Unsupported datatype detected : " & str
            End
    End Select

End Sub



Public Function HexValue(str As String) As Long
    Dim valArray(1) As String 'Needed as I have a space in the middle that prevents direct conversion
    valArray(0) = Split(str, " ")(0)
    valArray(1) = Split(str, " ")(1)
    HexValue = CLng("&H" & valArray(0) + valArray(1))
End Function


Public Function DecValue(str As String) As Long
    DecValue = Right(str, 2)
End Function

Answer 1

You need three boolean functions, following your business logic and some of the Clean Code principles (although the author of the book does not recognize VBA people as programmers):

• IsHex()
• IsBoolean()
• IsPercentage()

---

Public Sub TestMe()

    Dim myInput As Variant
    myInput = Array("A7C8", "$01", "$30")        
    Dim i As Long        
    For i = LBound(myInput) To UBound(myInput)
        Debug.Print IsHex(myInput(i))
        Debug.Print IsBoolean(myInput(i))
        Debug.Print IsPercentage(myInput(i))
        Debug.Print "-------------"
    Next i        
    'or use this with the DetectType() function below:
    'For i = LBound(myInput) To UBound(myInput)
    '    Debug.Print DetectType(myInput(i))
    'Next i

End Sub

Public Function IsHex(ByVal str As String) As Boolean    
    On Error GoTo IsHex_Error       
    IsHex = (WorksheetFunction.Hex2Dec(str) <> vbNullString)        
    On Error GoTo 0
    Exit Function    
IsHex_Error:    
End Function

Public Function IsBoolean(ByVal str As String) As Boolean
    IsBoolean = CBool((str = "$00") Or (str = "$01"))
End Function

Public Function IsPercentage(ByVal str As String) As Boolean
    IsPercentage = (Len(str) = 3 And Left(str, 1) = "$" And IsNumeric(Right(str, 2)))
End Function

---

Then some additional logic is needed, because $01 is both Boolean and Percentage. In this case, you can consider it Percentage. This is some kind of a mapper, following this business logic:

Public Function DetectType(str) As String

    Select Case True
        Case IsHex(str)
            DetectType = "HEX!"
        Case IsPercentage(str) And IsBoolean(str)
            DetectType = "Boolean!"
        Case IsPercentage(str)
            DetectType = "Percentage!"
        Case Else
            DetectType = "ELSE!"
    End Select

End Function