ByRef Type Mismatch when calling another sub

user_15658445 0
Viewed 57

I am trying to have a sub in VBA to call for another sub by passing the i value using the for loop. The problem is when u run the forLoop() sub, there is an error ByRef Type Mismatch. I am just trying out this example method to see if it works so that i can put into my bigger macro.

I tried to add the ByVal method but the error still remains. Someone please help me.

The forLoop sub

Public Sub forLoop()

 For i = 1 To 3

    Call Fetch_data(i)
    
 Next i
End Sub

The Fetch_data() sub

Sub Fetch_data(num As Integer)
    
    Dim data As String
    Dim myCellValue As range
    Dim myCellValue1 As range
    Dim sht As String
    
    Set sht = "mySheet" & num
    Set myCellValue = sht.range("J6")
    Set myCellValue1 = sht.range("J8")
    myCellValue1.Value = myCellValue.Value
    
End Sub
vba excel
1 Answers

The issue is that

Sub Fetch_data(num As Integer)

is the same as

Sub Fetch_data(ByRef num As Integer)

so Call Fetch_data(i) gives i by reference. But since in forLoop() the variable i is not declared it is Variant by default and not Integer therefore the type mismatch. Make sure you use Option Explicit and declare all your variables properly:

Public Sub forLoop()
    Dim i As Integer
    For i = 1 To 3
        Fetch_data i
    Next i
End Sub

Also you can make the parameter ByVal as there is no need to have it ByRef (otherwise your sub Fetch_data could mess up the loops counter in forLoop:

Sub Fetch_data(ByVal num As Integer)
    Dim data As String
    Dim myCellValue As range
    Dim myCellValue1 As range
    Dim sht As Worksheet
    
    Set sht = ThisWorkbook.Worksheets("mySheet" & num)
    Set myCellValue = sht.range("J6")
    Set myCellValue1 = sht.range("J8")
    myCellValue1.Value = myCellValue.Value 
End Sub
user_32196130
Related