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

Exceptive

Welcome to Stack Overflow: Please read How to ask a good question, then edit your question and be sure to ask a good, clear, concise question, include the code, expected behaviour, and what is wrong... then we can try to help – Erinn

I see it possible, if you write 3 boolean functions, depending whether the variable is IsHex(), IsBoolean() and "IsPercentage()". – Jugate

You could use a condition on the string inside the cell, like comparing some part of the string with a left or instr if left("$01", 1)="$" then ... It is one of the possibility, but what have you done so far so we can help you with your code ? – Alphosis

Updated with my relevant code used – Exceptive

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

Jugate

I would like to clarify what you said about the clean code : I don't have access to the full book, but it seems that one of the principles is to minimize the number of functions. Wouldn't it be better in that case to simply use 3 IF statements in TestMe() to make the test ? – Exceptive

@Exceptive - one of the principles is to write as many functions as you can and to make them specialized. Thus, instead of combining the three IsHex(), IsBoolean() and IsPercentage() in one function/sub but it is a better idea to put it into 3. Btw, it is a nice book (not affiliated or so). – Jugate

That video does not definitively show that he "does not recognize VBA people as programmers". At best, he marginalizes VBA programmers, but I don't think anyone who's spent extensive time writing software would seriously discount VBA people as programmers. I would say the problem lies more in the accessibility of VBA in it's relationship to MS Office in that you can learn quote a bit just by simply recording macros, but not actually learn how to properly program in VBA or even use all the many tools VBA has to offer. Those types of people should be discounted, but not the entire class. – Mendenhall

@Jaberwocky - idk, people in general have somthing against VBA programmers. However, I usually do not care, but when it comes from someone as R.C.M. then it is a bit strange. Still, the book is really nice and a must read. If it was not for it, my answer should have been with one function. – Jugate

@Jugate I think it’s purely because of MS Office. If you look at some other software suites that use a VBA layer, it gets just as extensive as some of the other languages I know, like VB, C#, and Python. I just bought the book though. Always looking for new ways to improve :) – Mendenhall

@Jaberwocky - so you bought the book because of the reference in the post? So, hating VBA was actually profitable from him. – Jugate

@Jugate I didn’t interpret his comment as hate. I interpreted his comment as marginalization, because the VBA group can really be broken down into real programmers, and otherwise not; probably 40/60 split. I bought it used, no profits to be had for the author :). But still, I’m not going to prevent furthering myself just because of an author’s snipes. – Mendenhall

Let us continue this discussion in chat. – Jugate