CODEWITHSUNDEEP
excelvba
MK01111000
MK01111000

Reputation: 822

How do I return a value from a function where the type of the passed variables aren't the same

I am trying to comapre two values and return the differences. I am getting an error ByRef argument type mismatch This error seems to be triggered by Null values, while I defined them as String in the function. The thing is that not all cells contain values.

However, I am not sure how to circumvent this problem

Below a simplified example of my situation

Sub MacroX()
    For Each C In Sheets("ADG 2020").Range("C6:C50")
        PI2020 = Sheets("ADG 2020").Cells(C.Row, C.Column + 7).Value
        For Each CC In Sheets("ADG 2018").Range("C6:C50")
            PI2018 = Sheets("ADG 2018").Cells(CC.Row, CC.Column + 7).Value
            Worksheets("ADG 2020").Cells(C.Row, C.Column + 12).Value = CHARDIF(PI2020, PI2018)
        Next CC
    Next C
End Sub

Public Function CHARDIF(ByRef rngA As String, rngB As String)
    Dim strA As String, strB As String
    Dim i As Long, strTemp As String
    strA = rngA
    strB = rngB
    If Len(strA) > Len(strB) Then
        strTemp = strA
        strA = strB
        strB = strTemp
        strTemp = ""
    End If
        
    For i = 1 To Len(strB)
        If i > Len(strA) Then
            strTemp = strTemp & Mid(strB, i, 1)
        ElseIf UCase(Mid(strA, i, 1)) <> UCase(Mid(strB, i, 1)) Then
            strTemp = strTemp & Mid(strB, i, 1)
        End If
    Next i
    
    CHARDIF = strTemp
    
End Function

Upvotes: 0

Views: 26

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Use the next approach, please:

In MacroX sub, declare the used variables as string. Otherwise, they are of type Variant and may create confusions:

Dim PI2020 As String, PI2018 As String

Then your function does not need to change string variables between each other:

Public Function CHARDIF(strA As String, strB As String) As String
    Dim i As Long, strTemp As String
    If Len(strA) > Len(strB) Then
        strTemp = strA
        strA = strB
        strB = strTemp
        strTemp = ""
    End If
        
    For i = 1 To Len(strB)
        If i > Len(strA) Then
            strTemp = strTemp & Mid(strB, i, 1)
        ElseIf UCase(Mid(strA, i, 1)) <> UCase(Mid(strB, i, 1)) Then
            strTemp = strTemp & Mid(strB, i, 1)
        End If
    Next i
    
    CHARDIF = strTemp
End Function

Upvotes: 1

Related Questions