Error handling of multiple class attributes

user_11931078 0
Viewed 62

I have a class that holds data in separate attributes (I chose this design instead of an array or scripting.dictionary because I will be using these data for the construction of a decision tree and I want to make use of IntelliType while I construct it.).

The data are loaded from an excel spreadsheet in the form of floats, so I am storing them in a long datatype, but from time to time it happens so that a value is missing and is replaced by an "NA" string.

I would like to create an error-handling routine, that, if a non-numeric value is encountered, would replace the content with a value -1.

I know I could do this with checking with IsNumeric() or error-handling, but I don't know how to make it work for every one of the many attributes the class holds and I don't really like the solution to write a specific error-handling code for every one of them (storing each of them in a separate attribute is not elegant as well, but I find this a price I am willing to pay for the advantage of shorter syntax in the decision tree).

Is there a way to pass a value to a variable, that just encountered a type-mismatch error, by the error-handling code independent of the variable name?

A simple example with several of the attributes:

Option Explicit
Dim B_LYM           As Long
Dim B_mem           As Long
Dim B_CXCR3         As Long
Dim B_CXCR4_MFI     As Long

Public Sub SetData(data as Range)
    On Error Goto err_handler

    B_LYM = data.Cells(1, 1)
    B_mem = data.Cells(1, 2)
    B_CXCR3 = data.Cells(1, 3)
    B_CXCR4_MFI = data.Cells(1, 4)

err_handler:
    'I need something like this:
    'if valuebeingstored = "NA" then targetvariable = -1
End Sub

It could be that some other approach could be better and I am gladly open to options, I only want to emphasize that I would really like make use of IntelliType when constructing the decision tree. I was considering using scripting.dictionary, but the syntax will bloat the code very quickly.

vba excel
1 Answers

You said you have a Class and therefore could include the function to check the input and return -1 inside the class and use the Get and Let properties to call the function.

Here is an example class (named clsDataStuff) demonstrating this:

Option Explicit

Private c_B_LYM As Double
Private c_B_mem As Double
Private c_B_CXCR3 As Double
Private c_B_CXCR4_MFI As Double

Public Property Let B_LYM(varValue As Variant)
    c_B_LYM = ParseDouble(varValue)
End Property
Public Property Get B_LYM()
    B_LYM = c_B_LYM
End Property

Public Property Let B_mem(varValue As Variant)
    c_B_mem = ParseDouble(varValue)
End Property
Public Property Get B_mem()
    B_mem = c_B_mem
End Property

Public Property Let B_CXCR3(varValue As Variant)
    c_B_CXCR3 = ParseDouble(varValue)
End Property
Public Property Get B_CXCR3()
    B_CXCR3 = c_B_CXCR3
End Property

Public Property Let B_CXCR4_MFI(varValue As Variant)
    c_B_CXCR4_MFI = ParseDouble(varValue)
End Property
Public Property Get B_CXCR4_MFI()
    B_CXCR4_MFI = c_B_CXCR4_MFI
End Property

Private Function ParseDouble(varValue As Variant) As Double
    If IsNumeric(varValue) Then
        ParseDouble = CDbl(varValue)
    Else
        ParseDouble = -1
    End If
End Function

Noting that:

  • the Let property expects a Variant because you say your input could be a number, or a string
  • the Get property returns Double as you said your inputs are floats so Double is better than Long
  • the ParseDouble function simply checks for a numeric input and returns -1 otherwise

Then, in your module code:

Option Explicit

Dim B_LYM           As Long
Dim B_mem           As Long
Dim B_CXCR3         As Long
Dim B_CXCR4_MFI     As Long

Public Sub Test()

    Dim objDataStuff As clsDataStuff
    
    Set objDataStuff = New clsDataStuff
    
    objDataStuff.B_LYM = 1 'data.Cells(1, 1)
    objDataStuff.B_mem = 2 'data.Cells(1, 2)
    objDataStuff.B_CXCR3 = "a" 'data.Cells(1, 3)
    objDataStuff.B_CXCR4_MFI = True 'data.Cells(1, 4)

    Debug.Print objDataStuff.B_LYM
    Debug.Print objDataStuff.B_mem
    Debug.Print objDataStuff.B_CXCR3
    Debug.Print objDataStuff.B_CXCR4_MFI

End Sub

Returns an output of:

 1 
 2 
-1 
-1

Intellisense is available and you get validation of the input:

enter image description here

Edit - regarding the comment on dynamically setting a target variable.

Your class can be:

Option Explicit

Public B_LYM As Double
Public B_mem As Double
Public B_CXCR3 As Double
Public B_CXCR4_MFI As Double

Public Sub SetVar(ByVal strVarName As String, ByVal varValue As Variant)
    Dim dblValue As Double
    Dim strToEval As String
    
    If Not MemberExists(strVarName) Then Exit Sub
    
    dblValue = ParseDouble(varValue) ' do the parse
    CallByName Me, strVarName, VbLet, dblValue ' dynamically assign the value
    
End Sub

Private Function ParseDouble(varValue As Variant) As Double
    If IsNumeric(varValue) Then
        ParseDouble = CDbl(varValue)
    Else
        ParseDouble = -1
    End If
End Function

Private Function MemberExists(strVarName) As Boolean
    Dim blnTest As Boolean
    Dim varValue As Variant
    
    On Error GoTo ErrHandler
    
    varValue = CallByName(Me, strVarName, VbGet)
    blnTest = True
    GoTo ExitFunction

ErrHandler:
    blnTest = False
ExitFunction:
    MemberExists = blnTest

End Function

Where:

  • All the variables are Public and you still get Intellisense but avoid all the repetitive Let and Get code
  • A single SetVar method uses CallByName to dynamically set a target variable

Two problems:

  1. You need the clunky MemberExists function to prevent SetVar trying to assign a value to a member that does not exist - otherwise this generates an error (438) but perhaps this is something you need in your logic ?

  2. You can still assign values to the target variable with e.g. objDataStuff.B_CXR3 = "foo" which alsos produces an error for anything other than a number.

The example code shows the problem below. But sticking with SetVar method will produce the same output as above.

Option Explicit
 
Dim B_LYM           As Long
Dim B_mem           As Long
Dim B_CXCR3         As Long
Dim B_CXCR4_MFI     As Long

Public Sub Test()

    Dim objDataStuff As clsDataStuff
    
    Set objDataStuff = New clsDataStuff
       
    objDataStuff.SetVar "B_LYM", 1
    objDataStuff.SetVar "B_mem", 2
    objDataStuff.SetVar "B_CXCR3", -1
    objDataStuff.SetVar "B_CXCR4_MFI", True
    objDataStuff.SetVar "foobar", 999
    ' working around SetVar here generates an error
    objDataStuff.B_CXCR3 = "bad"
    
    Debug.Print objDataStuff.B_LYM
    Debug.Print objDataStuff.B_mem
    Debug.Print objDataStuff.B_CXCR3
    Debug.Print objDataStuff.B_CXCR4_MFI

End Sub
user_7653950
Related