Mixing data types

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,455
Office Version
  1. 2019
Platform
  1. Windows
Is it OK to mix data types?

Code:
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
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,658
Office Version
  1. 2019
Platform
  1. Windows
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:

Code:
 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.
 
Last edited:

Forum statistics

Threads
1,136,303
Messages
5,674,971
Members
419,537
Latest member
ucatchy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top