[Solved] Why is VBA throwing type mismatch errors when passing a Range object?

by


So the problem turned out to be that because this code was run from word, with the Excel library as a reference, the Range type defaulted to a MS-Word Range object instead of a MS-Excel Range object, meaning that my function was looking for the wrong type of parameter. Here is the corrected code (notice the explicit reference to the Excel lib when defining Foo and the Range variable r): 

Function Foo(r As Excel.Range) As String
    MsgBox "WHAT. The function worked? Wow!"
    Foo = "Test"
End Function

Sub main()
    Set workbook = Excel.Workbooks.Open("example.xlsx")
    Set datasheet = workbook.ActiveSheet

    Dim rng As Excel.Range
    Set rng = datasheet.Range("A2:A100")
    Dim t As String
    t = Foo(rng)
    Debug.Print t
End Sub

As @ScottCraner and others pointed out the issue could easily have been that I had a variable/function called Range defined somewhere else. In a way that is sort of what happened, only it was defined in the Word Library, not in my code.

1

solved Why is VBA throwing type mismatch errors when passing a Range object?