VBA处理多种自定义数据类型的可能性

您需要三个布尔函数,遵循您的业务逻辑和一些清洁代码原则(尽管本书的作者不承认VBA人员是程序员):

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

然后需要一些额外的逻辑,因为$ 01既是布尔值也是百分比.在这种情况下,您可以将其视为百分比.这是一种映射器,遵循以下业务逻辑:

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