Mixing data types

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,647
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 is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,501
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,172,134
Messages
5,879,246
Members
433,414
Latest member
mwtharp

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