Mixing data types

[tiredofit]

Is it OK to mix data types?

Function Matching(ByRef Startstring As Variant, ByRef Sheetname As Worksheet, ByRef Ref As String)
                                   As Variant

    Matching = Application.Match(Startstring, Sheetname.Range(Ref), 0)
    
End Function

Sub Start()

    Dim SheetCol As Variant
            
    SheetCol = Matching(Startstring:="Sheet", Sheetname:=Me, Ref:=MyRow & ":" & MyRow)
   
    Dim j As integer

    j = SheetCol

End Sub

In the code above, despite SheetCol being defined as a Variant, I know (just believe me!) that it'll always return an Integer.

(The reason I've declared it as a Variant is that if it was declared as an Integer, I would need error handling if no match was found).

As can be seen, j is declared as an Integer and its value set to be equal to SheetCol.

Does VBA implicitly convert different data types?

Thanks

 

[dmt32]

The Variant data type is VBA's default and is the most flexible data type as it can be used to store numeric & non-numeric values - you generally use it when you are uncertain of the data's type being processed or very usefully, when you need to manage error values.

Although this data type is flexible, VBA processes them a little slower & there are some because of this, who prefer not to use it. Another disadvantage is the data type's lack of readability in code to determine the appropriate data type being processed which could be a problem when trying to resolve bug issues. One solution to this is to coerce the variant by using a Type Conversion Function.

To manage error value - Your code could look like this:

 Sub Start()    
    Dim j As Integer
    Dim SheetCol As Variant
            
    SheetCol = Matching(Startstring:="Sheet", Sheetname:=Me, Ref:=MyRow & ":" & MyRow)
'check for error
    If Not IsError(SheetCol) Then
'use type conversion function
        j = CInt(SheetCol)
    Else
'report error
        MsgBox "No Match Found"
    End If


End Sub

Hope helpful but perhaps another here may be able to offer additional guidance.

 

[tiredofit]

Thanks for your suggestion.

 

[dmt32]

Thanks for your suggestion.

welcome - feedback very much appreciated.

Dave